Upgrade PostgreSQL 11 to 13

Upgrade PostgreSQL 11 to 13

The newer version of Debian 11 comes with PostgreSQL 13. I have the original PostgreSQL 11 running with all my data. Now I’m going to walk through the process of upgrading from PostgreSQL 11 to 13.

Be aware this process will work when upgrading from PostgreSQL 9.6 to 11 or 13.

Lets see what’s going on.

Run pg_lsclusters to see which databases are live and running.

~$ sudo su - postgres
~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
11  main    5432 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
13  main    5433 online postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log

Also, lets double check each instance to make sure where our data truly resides.

~$ sudo su - postgres
~# psql --cluster 11/main
psql (13.15 (Debian 13.15-0+deb11u1), server 11.22 (Debian 11.22-0+deb10u2))
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \q
~# psql --cluster 13/main
psql (13.15 (Debian 13.15-0+deb11u1))
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

postgres=# \q

Drop the default databases found in the newer PostgreSQL versions.

~$ sudo su - postgres
~# pg_dropcluster --stop 13 main

Upgrade 11 to 13

# pg_upgradecluster 11 main

This will show a bunch of output about the process of upgrading the DB.

Warning: the cluster will not be running as a systemd service. Consider using systemctl:
  sudo systemctl start postgresql@13-main

Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
    pg_dropcluster 11 main

Ver Cluster Port Status Owner    Data directory               Log file
11  main    5433 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Lets see what is running now.

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
11  main    5433 down   postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log
13  main    5432 online postgres /var/lib/postgresql/13/main  /var/log/postgresql/postgresql-13-main.log

Remove all old versions of PostgreSQL.

$ sudo su - postgres
$ pg_dropcluster 11 main
$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
$ sudo apt remove postgresql-11 postgresql-client-11

Currently PostgreSQL is running as the postgres user, we need to stop the PostgreSQL daemon and start it with the normal system calls.

~$ sudo su - postgres
~$ pg_ctlcluster 13 main stop
~$ 
logout
~$ sudo systemctl daemon-reload
~$ sudo systemctl stop postgresql@11-main
~$ sudo systemctl stop postgresql@13-main
~$ sudo systemctl start postgresql@13-main
~$ sudo pg_lsclusters 
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Your upgrade is now complete!