Upgrading to postgres-13 with minimum/without downtime

Today we will discuss about upgrading to postgres-13 with two methods:

• With minimum downtime (via pg_upgrade)
• No downtime.

Let’s start with the first approach:

• Upgrade with minimum downtime: I’m using postgres-12 already. I want to upgrade it to postgres-13. Let’s start with installing it.

yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install postgresql13-server postgresql13 -y

Initialize initdb:

/usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

My existing database with postgres-12 is using port 5432 already. So for now, before upgrade -I’m going to run postgres-13 on any other port. For this, I’m modifying postgresql.conf file of 13 version and replacing port 5432 to 5433 and uncommenting it.

FYI..I’m using default directory structure here. You can modify as per your configuration.

cat /var/lib/pgsql/13/data/postgresql.conf|grep port
port = 5433                             # (change requires restart)

Let’s start db services for postgres-13 now and verify if it’s working properly:

systemctl start postgresql-13.service
systemctl enable  postgresql-13.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-13.service to /usr/lib/systemd/system/postgresql-13.service.
systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2020-12-26 15:44:39 IST; 22s ago

I have verified my postgres-13 db services are working as expected.

Now, before proceeding with upgrade — first I’m going to perform consistency check. (Consistency check is required to find out missing extensions/plugin which you might be using already with older version and haven’t installed for newer version).

sudo   -u postgres /usr/pgsql-13/bin/pg_upgrade  --old-bindir=/usr/pgsql-12/bin/ --new-bindir=/usr/pgsql-13/bin/  --old-datadir=/var/lib/pgsql/12/data/ --new-datadir=/var/lib/pgsql/13/data/  -c
Performing Consistency Checks on Old Live Server
------------------------------------------------
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 presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok
Checking for new cluster tablespace directories             ok
*Clusters are compatible*

I have verified that there is no consistency issue. Now, I can proceed with upgrade.

In order to proceed with upgrade — I’ll need to stop postgres-12 services only for 1 minute.

service postgresql-12 stop
Redirecting to /bin/systemctl stop postgresql-12.service
sudo -u postgres /usr/pgsql-13/bin/pg_upgrade  --old-bindir=/usr/pgsql-12/bin/ --new-bindir=/usr/pgsql-13/bin/  --old-datadir=/var/lib/pgsql/12/data/ --new-datadir=/var/lib/pgsql/13/data/  -k
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
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
Checking for new cluster tablespace directories             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
Adding ".old" suffix to old global/pg_control               ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/pgsql/12/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking 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

Let’s copy configurations from postgres-12 directory to postgres-13 directory. So that, we will have same configurations as old version:

Note: If you want to check for differences/make any new changes then you can make those changes into config files here.

cp /var/lib/pgsql/12/data/postgresql.conf /var/lib/pgsql/13/data/postgresql.conf
cp: overwrite ‘/var/lib/pgsql/13/data/postgresql.conf’? y
cp /var/lib/pgsql/12/data/pg_hba.conf /var/lib/pgsql/13/data/pg_hba.conf
cp: overwrite ‘/var/lib/pgsql/13/data/pg_hba.conf’? y

Now, it’s time to start postgresql -13.

service postgresql-13 start
sudo su - postgres -c "psql"
psql (13.1)
Type "help" for help.
postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

We have now upgraded to postgres-13. You can now, erase the installation and directory structure of postgres-12.

• Upgrading without downtime: In this approach , we require another server to which we can use as a logical standby. For this, what at all we need to do — Get another machine -> Install and run postgres-13 there(follow same installation procedure I mentioned above) -> Then , we will take a schema dump of our database and restore it on new server.

So I have installed postgres-13 on my new server and it’s running. Also, I have created a database with the same name as primary (test) here (After starting replication data will start syncing here).Now, I’m taking schema dump of my database which is running on postgres-12.

pg_dump -h <host-pg-12> -p 5432 -d test -Fc -s -U postgres | pg_restore -d test -h localhost -p 5432 -U postgres

Now, let’s switch to our postgres — 12(primary db server) and create a publication there for all servers.

CREATE PUBLICATION pub_test  FOR ALL TABLES;

Modify postgresql.conf file of primary db server and update wal_level = logical.

Now, connect back to postgres-13 server’s test db and create subscription here.

CREATE SUBSCRIPTION sub_test CONNECTION 'host=<host-pg-12>   dbname=test user=postgres password=xxxx port=5432' PUBLICATION pub_test   ;

Once you create subscription here, you will see — it will start syncing data with primary db. You will need to monitor it for some time. Once data for all tables get in sync -it’s good time to switchover application services to this db.

Note: With logical replication sequence value will not be reflected automatically on your postgres-13 server. So, you will need to restart all sequences there from the value where it last used on primary.

Before pointing application services to postgres-13 server , first run below script on your primary db in order to get next values for all sequences from primary db server:

with cte as (SELECT c.relname as seq_name FROM pg_class c WHERE c.relkind = 'S' )
select 'alter sequence '|| seq_name || ' restart with ' || concat (nextval( ''|| seq_name ||'' ) , ' ;') from cte ;

This script will generate another script -> run that script of postgres-13 server and switch app services for 13 server. Now, your standby (postgres-13) server is in use.

Later on, you can drop subscription from postgres-13 server and also drop publication from postgres-12 server.