Set Up a Local PostgreSQL Database#

For any project using a PostgreSQL database, it is essential to be able to run your own copy of the database on your development machine. This enables you to test changes, and make potentially destructive mistakes without affecting your production website.

Step 1: Install PostgreSQL#

Download the PostgreSQL installer provided by EnterpriseDB. Choose the appropriate version of PostgreSQL (CodeRed Cloud currently uses 15): https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

During the installation, you will be asked to set a system password. We recommend using postgres as the password in the development environment.

Next, let’s add Postgres to PATH. If Postgres is not on the PATH, then you’ll get the error: command not found: psql. In the terminal, open your .zprofile file as so:

% nano ~/.zprofile

Use the arrow keys to scroll down to the bottom of this file, then enter the following lines (replace “15” with your version):

PATH="/Library/PostgreSQL/15/bin:${PATH}"
export PATH

Then to save the file, press the Control and X keys at the same time. Then press Y to save. Now quit and re-open the terminal for your changes to take effect.

In a new terminal, the psql command should now work:

% psql --version

Open a terminal and install using winget, which is included with Windows 10 and 11. Choose the appropriate version of PostgreSQL (CodeRed Cloud currently uses 15):

PS> winget install PostgreSQL.PostgreSQL.15

Alternatively, download the PostgreSQL installer provided by EnterpriseDB: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

During the installation, you might be asked to set a system password. We recommend using postgres as the password in the development environment. If you are not asked to set a password, the default will be postgres.

Next, let’s add the psql command to your PATH:

In the start bar, search for: Edit the system environment variables.
Click Environment Variables… near the bottom of the window.
In the top box, scroll down and click Path then click Edit….
Then click New and enter the following (replace “15” with the version you installed):
```
C:\Program Files\PostgreSQL\15\bin
```
Now click OK in each window to close it and save the changes.

After changing the PATH, close your Terminal and open a new one for the changes to take effect. The psql command should now work:

PS> psql --version

Step 2: Create a database#

Now that Postgres is up and running, let’s log in through the terminal as so:

% psql -U postgres

PS> psql -U postgres

$ psql -U postgres

Enter your password, and now you’ll be in the Postgres console.

Next, let’s create an empty database. (TIP: If you previously created a database, and want to start fresh, delete it first using a “drop”.) If you’re working on a website hosted on CodeRed Cloud, you should name this the same as your CodeRed Cloud databse. Replace myproject with your database name below:

postgres=# drop database myproject;
postgres=# create database myproject;

To exit the Postgres console:

postgres=# exit

Step 3: Load a database from backup#

Frequently our development databases become out of sync during the course of development, and it is helpful start fresh with a copy from staging or production.

For reference, database backups are usually referred to as a “database dump”.

If you are a CodeRed Cloud customer, download a backup from the dashboard under Website > Backups. Then unzip the file and locate the myproject.sql file.

If you are not a CodeRed Cloud customer, then you’ll need to find your platform’s instructions for getting a database dump.

Now load the dump into your newly created database using the terminal:

% psql --file myproject.sql "user=postgres dbname=myproject"

PS> psql --file myproject.sql "user=postgres dbname=myproject"

$ psql --file myproject.sql "user=postgres dbname=myproject"

Now your database is fully functional, and ready for development.

Step 4: Development connection strings#

If you’re working with a Django or Wagtail project, you’ll want to update your development settings to use your new local database. Edit your Django settings file (typically myproject/settings/dev.py) and add or edit the DATABASES setting:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "localhost",
        "NAME": "myproject",
        "USER": "postgres",
        "PASSWORD": "postgres",
        "OPTIONS": {
            "client_encoding": "UTF8",
        },
    }
}