How to Upgrade PostgreSQL 10 Cluster to 12 in Ubuntu 20.04

When I upgraded my Ubuntu 18.04 to 20.04, the PostgreSQL package also got upgraded from version 10 to 12. The upgrade said in order to remove the v10 I have to migrate the clusters first. This is how I migrate the v10 cluster to v12 cluster.

To begin with, Postgre has this pg_upgrade command. Here is the link to the documentation. The syntax is as follows:

pg_upgrade -b oldbindir -B newbindir -d oldconfigdir -D newconfigdir [option...]

It is in /usr/lib/postgresql/<version>/bin/ directory. But when I ran this command in my user context, I got Permission Denied. It also forbids running as sudo either. The only way that makes sense now is running this command in postgres user context. Please note that I use the --check flag below to avoid running the real migration. And don't forget to stop the postgresql service before.

chris@focal:~$ sudo systemctl stop postgresql
chris@focal:~$ sudo su - postgres
postgres@focal:~$ /usr/lib/postgresql/12/bin/pg_upgrade --check \
-b /usr/lib/postgresql/10/bin \
-B /usr/lib/postgresql/12/bin \
-d /var/lib/postgresql/10/main \
-D /var/lib/postgresql/12/main

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

connection to database failed: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/lib/postgresql/.s.PGSQL.50432"?

could not connect to source postmaster started with the command:
"/usr/lib/postgresql/10/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/10/main" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start
Failure, exiting

Still unsuccesfull :/

Checking into that pg_upgrade_server.log as it mentions, I found that it was looking for /var/lib/postgresql/10/main/postgresql.conf. We know that postgresql.conf was not supposed to be in the var/lib/postgresql. It is in /etc/postgresql. Replacing /var/lib/postgresql with /etc/postgresl, yield this:

postgres@focal:~$ /usr/lib/postgresql/12/bin/pg_upgrade --check \
-b /usr/lib/postgresql/10/bin \
-B /usr/lib/postgresql/12/bin \
-d /etc/postgresql/10/main \
-D /etc/postgresql/12/main

Finding the real data directory for the source cluster      ok
Finding the real data directory for the target cluster      ok
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Voila! But I felt a bit uneasy about this. So, I went for a googling and found this official wiki page. It says that we should put the etc/postgresql path into the -c flag. I tried that and it worked nice.

Now, before getting our hands into the real migration, we might want to compare our postgresql.conf and pg-hba.conf as suggested by this blog post.

diff /etc/postgresql/10/main/postgresql.conf /etc/postgresql/12/main/postgresql.conf
diff /etc/postgresql/10/main/pg_hba.conf /etc/postgresql/12/main/pg_hba.conf

Ok, we are ready now. There are 2 ways of really running the upgrade though. First, from the above blog post, keep running as sudo su - postgres, just omitting the --check flag. Or, according to the wiki page, we can also do it this way:

cd /tmp
sudo -H -u postgres /usr/lib/postgresql/12/bin/pg_upgrade \
   -b /usr/lib/postgresql/10/bin \
   -B /usr/lib/postgresql/12/bin \
   -d /var/lib/postgresql/10/main \
   -D /var/lib/postgresql/12/main \
   -o ' -c config_file=/etc/postgresql/9.1/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/9.2/main/postgresql.conf'

Note that we need to cd /tmp because the command needs permission to write as postgres user into the current directory. It will generate two additional scripts, as you can see below, I was running it with sudo su - postgres.

postgres@focal:~$ /usr/lib/postgresql/12/bin/pg_upgrade \
-b /usr/lib/postgresql/10/bin \
-B /usr/lib/postgresql/12/bin \
-d /var/lib/postgresql/10/main \
-D /var/lib/postgresql/12/main \
-o '-c config_file=/etc/postgresql/10/main/postgresql.conf' \
-O '-c config_file=/etc/postgresql/12/main/postgresql.conf'

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster
                                                            ok
Copying user relation files
                                                            ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

The first script, analyze_new_cluster.sh, will optimize our new cluster using the vacuum analyze, and the second script is for removing the old clusters.

There is actually another flag of this command that is interesting to explore. It is the --link or -k flag. The documentation said that it will create a hard-link, instead of copying the files. I searched for what that actually means. And found out that in linux file-system, one can create link to an inode. An inode is a data structure that contains metadata and location of the file So, instead of copying the data files pg_upgrade will probably create a hard-link that points to the old data files. Unfortunately, I missed the chance to use this flag. It is not recommended if you want to be careful though, since you will not be able to access your old cluster once you start the new cluster after the upgrade. If you happened to be one of the people that brave enough to try this flag, please comment or share your experience in the comment section below.

Next, we might want to try the new cluster. But first let's swap the DB ports so that the v12 port is at 5432.

sudo vim /etc/postgresql/12/main/postgresql.conf
# ...and change "port = 5433" to "port = 5432"

sudo vim /etc/postgresql/10/main/postgresql.conf
# ...and change "port = 5432" to "port = 5433"

Start the postgresql service.

sudo systemctl start postgresql

Check the new postgresql version.

psql -c "SELECT version();"

Remove the old v10 package.

apt list --installed | grep postgresql
sudo apt remove postgresql-10 postgresql-client-10

Also remove the config dir.

sudo rm -rf /etc/postgresql/10/

Run the two scripts.

./analyze_new_cluster.sh
./delete_old_cluster.sh

Remove the /var/lib/postgresql/10 directory.

sudo rm -r /var/lib/postgresql/10

We're done!