Upgrading to PostgreSQL 14

Django 4.1 was released on August 3, 2022 and I was excited to upgrade to it. I did the testing locally and then pushed my changes up to GitHub to deploy. The deployment was successful, but when I went to visit my sites ... womp womp. I got a Server Error 5XX.

What happened? Well, it turns out that Django 4.1 dropped support for Postgres 10 and that just so happens to be the version I was running on my production server (but not on my local dev machine ... I was running Postgres 14).

OK, so I am going to need to upgrade in order to get the features of anything above Django 4.0 ... and honestly, I've needed to upgrade past Postgres 10 for a while.

I found this StackOverflow question and answer and it helped me a ton! It was to upgrade from Psotgres 10 to 12, but the ideas were the same (but replace 12 with 14). There is also a step that indicates you need to run ./analyze_new_cluster.sh but that seems to be only for version 12(maybe 13) and lower.

Everything was fine until I visited my site and got a Server Error 5XX AGAIN!

What gives?

My first assumption was that maybe the postgres server didn't start back up properly after the upgrade. I checked the service to verify that it was running, and it was

ps -aux | grep postgres

which returned

postgres   988  0.0  1.3 321668 27588 ?        Ss   16:55   0:01 /usr/lib/postgresql/14/bin/postgres -D /var/lib/postgresql/14/main -c config_file=/etc/postgresql/14/main/postgresql.conf
postgres  1034  0.0  0.2 321788  6112 ?        Ss   16:55   0:00 postgres: 14/main: checkpointer
postgres  1035  0.0  0.2 321800  5996 ?        Ss   16:55   0:00 postgres: 14/main: background writer
postgres  1036  0.0  0.4 321668  9388 ?        Ss   16:55   0:00 postgres: 14/main: walwriter
postgres  1039  0.0  0.3 322356  8080 ?        Ss   16:55   0:00 postgres: 14/main: autovacuum launcher
postgres  1040  0.0  0.2 176828  5108 ?        Ss   16:55   0:00 postgres: 14/main: stats collector
postgres  1041  0.0  0.3 322224  6628 ?        Ss   16:55   0:00 postgres: 14/main: logical replication launcher
root      4868  0.0  0.0  14860  1072 pts/0    S+   18:47   0:00 grep --color=auto postgres

I also checked

systemctl status postgresql

which returned as expected

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sun 2022-08-28 16:55:32 UTC; 1h 54min ago
  Process: 1169 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 1169 (code=exited, status=0/SUCCESS)

Aug 28 16:55:32 server-name systemd[1]: Starting PostgreSQL RDBMS...
Aug 28 16:55:32 server-name systemd[1]: Started PostgreSQL RDBMS.

One last thing to try

python manage.py makemigrations

This gave me a hint as to what the issue was:

RuntimeWarning: Got an error checking a consistent migration history performed for database connection 'default': connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "user" connection to server at "127.0.0.1", port 5432 failed: FATAL:

Hmmm ... a quick google search doesn't specifically answer it, but it helps me to get the to answer.

The 'user' isn't able to connect to the database. Maybe the upgrade process resets the password of users in the database or it just doesn't keep the users.

A quick look at the users on the database showed me that the users were still there, so the only thing left to do at this point was to set the user passwords to be what my settings are expecting.

To do that I ran

ALTER USER user WITH PASSWORD 'password';

I did this for the databases that were associated with my websites that were returning 5XX errors and voila! That fixed the issue.

I'm sure that there is a way to keep the passwords for the users after the upgrade, but I haven't been able to find it.

The next time I need to upgrade PostgreSQL I am going to refer back to this post to remind myself what I did last time 😀

Posted by ryan on Sun 28 August 2022

Using PostgreSQL

Once you’ve deployed your code to a web server, you’ll be pretty stoked. I know I was. One thing you’ll need to start thinking about though is converting your SQLite database to a ‘real’ database. I say ‘real’ because SQLite is a great engine to start off with, but once you have more than 1 user, you’ll really need to have a database that can support concurrency, and can scale when you need it to.

Enter PostgreSQL. Django offers built-in database support for several different databases, but Postgres is the preferred engine.

We’ll take care of this in stages:

  1. Create the database
  2. Prep project for use of Postgres
    1. Install needed package
    2. Update settings.py to change to Postgres
    3. Run the migration locally
  3. Deploy updates to server
  4. Script it all out

Create the database

I’m going to assume that you already have Postgres installed locally. If you don’t, there are many good tutorials to walk you through it.

You’ll need three things to create a database in Postgres

  1. Database name
  2. Database user
  3. Database password for your user

For this example, I’ll be as generic as possible and choose the following:

  • Database name will be my_database
  • Database user will be my_database_user
  • Database password will be my_database_user_password

From our terminal we’ll run a couple of commands:

# This will open the Postgres Shell

psql

# From the psql shell

CREATE DATABASE my_database;
CREATE USER my_database_user WITH PASSWORD 'my_database_user_password';
ALTER ROLE my_database_user SET client_encoding TO 'utf8';
ALTER ROLE my_database_user SET default_transaction_isolation TO 'read committed';
ALTER ROLE my_database_user SET timezone TO 'UTC'

The last 3 ALTER commands are based on Django recommendations for Postgres user.

One thing to note, before you go creating databases and users, you should make sure that they don’t already exist. The \l will list the various databases present. If this is your first time in the psql shell you’ll see three databases list:

postgres
template0
template1

To see a list of the users \du will display that. If this is your first time in the psql shell you’ll see one user listed:

postgres

OK … the database has been created. Next, we start updating our project to use this new database engine

Prep project for use of Postgres

Install Needed Package

The only python package needed to use Postgres is psycopg2-binary so we’ll

pip install psycopg2-binary

Update settings.py

The DATABASES portion of the settings.py is set to use SQLite by default and will look (something) like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': 'mydatabase',
    }
}

The Django documentation is really good on what changes need to be made. From the documentation we see that we need to update the DATABASES section to be something like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'mydatabase',
        'USER': 'mydatabaseuser',
        'PASSWORD': 'mypassword',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

With our database from above, ours will look like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'my_database',
        'USER': 'my_database_user',
        'PASSWORD': 'my_database_user_password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

The HOST is changed to localhost and we remove the value for PORT

Once we get ready to push this to our web server we’ll want to replace the NAME, USER, and PASSWORD with environment variables, but we’ll get to that later

Run migrations

OK, we’ve got our database set up, we’ve got our settings updated to use the new database, now we can run set that database up.

All that we need to do is to:

python manage.py migrate

This will run any migrations that we had created previously on our new Postgres database.

A few things to note:

  1. You will need to create a new superuser
  2. You will need to migrate over any data from the old SQLite database1

Congratulations! You’ve migrated from SQLite to Postgres!

  1. This can be done with the datadump and dataload commands available in manage.py ↩︎

Posted by ryan on Sun 28 February 2021