Postgresql

postgresql-replication.md

Playbook to setup physical replication in Postgresql 13

1. Install the postgresql 13 for replica on another instance. sudo apt-get install postgresql-13. Install binary extension if theres any, ie sudo apt-get install postgresql-13-postgis-3
2. Create a user in master server for replication connection. create user replica with password <password>
3. Change the following postgres config in the master server

# in /etc/postgresql/13/main/postgres.conf

max_wal_senders = 5
synchronous_standby_names = 'pgslave001' # or any other you prefer
wal_level = hot_standby

# in /etc/postgresql/13/main/pg_hba.conf

# no need to create a db named replication, its just a keyword to let postgres knows its a replication access, things might be different for logical replication
host  replication replica <replica-instance-cidr-ip> trust

4. Restart master server by sudo service postgresql@13-main restart
5. Stop the replica server postgres process by sudo service postgresql@13-main stop
6. Delete the replica server postgres data dir, rm -f /var/lib/postgresql/13/main
7. Perform base backup on the replica server, pg_basebackup -h primary-ip-addr -p 5432 -U replica -D /var/lib/postgresql/13/main/ -Fp -Xs -R
8. Change the following postgres config in the replica server

# in /etc/postgresql/13/main/postgresql.conf
listen_addresses = <any-ip-you-want>
max_connection = <at-least-same-as-master-server>
hot_standby = on

# in /var/lib/postgresql/13/main/postgresql.auto.conf

## The following should be generated by the pg_basebackup command due to the "-R" flag. We just need to append the "application_name" to make sure synchronous stream work
primary_conninfo = 'user=replica passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer host=<master-server-ip> port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any application_name=pgslave001'

9. Stop the pg13 cluster pg_ctlcluster 13 main stop. Setup systemd service for postgresql by sudo systemctl enable postgresql@13-main and start it by sudo service postgresql@13-main start.

Reference

1. How To Set Up Physical Streaming Replication with PostgreSQL 12 on Ubuntu 20.04

postgresql-upgrade.md

Playbook for upgrade from Postgresql 9 (with Postgis 2.4) to Postgres 13

1. Our upgrade path would be from pg-9.6 -> pg11 -> pg13 due to the constraint of Postgis

Pre-requisites

1. postgresql-common package is required as we are using pg_ctlcluster, pg_lsclusters and pg_upgradecluster to perform the necessary upgrade tasks.
2. The existing postgres is installed in dir path (ie /usr/lib/postgresql/<version>/lib) that has its version so that newer version postgresql installation will not mess up the existing postgresql.
3. This guide works for ubuntu-16.04. It should work for later version as well, all you need is the correct apt source for posgresql installation.

Steps

Pg9 -> Pg11

1. Backup data using pg_dumpall --no-owner --no-acl -F c -f dbbackup.dump. This is a precaution measure so that you can undo everything by just retore this backup dump.
2. Switch to root user.
3. Add postgresql apt source deb http://apt.postgresql.org/pub/repos/apt xenial-pgdg main to /etc/apt/sources.list.d/pgdg.list (create this file if its not there)
4. Install pg11 by apt-get install postgresql-11.
5. Check if the installation success. Stop the pg9.5 cluster first so that we wont have port collision when starting pg11 cluster.
   1. Swith user to postgres by su postgres
   2. pg_lsclusters, list out the available clusters in case you are not sure about the cluster version and name.
   3. pg_ctlcluster 9.5 main stop
   4. pg_ctlcluster 11 main start
   5. connect to cluster by psql -U postgres -d postgres, you should not need password to connect to the db.
6. After verify pg11 installation is success, drop the pg11 cluster by pg_dropcluster --stop 11 main. pg11 cluster will be created during the upgrade process
7. Install postgis on pg11 as the upgrade task will try to recreate all postgis function when upgrade from pg9. apt-get install postgres-11-postgis-2.5 (you can install postgis-2.4 if you manage to find the apt source for postgres-11-postgis-2.4
8. If postgis-2.5 is installed on pg11, we need to duplicate all the postgis-2.5 .so files as the upgrade task expect postgis-2.4 is installed in pg11. So just duplicate all *postgis*.so files in /usr/lib/postgresql/11/lib/ but change the version 2.5 to 2.4 on those dup files.
9. Perform the upgrade via pg_upgradecluster -m upgrade 9.5 main
10. Upgrade postgis extension FOR EVERY DATABASE THAT HAS THIS EXTENSION INSTALLED as we are just hacking it in the previous steps. ALTER EXTENSION postgis UPDATE; SELECT postgis_extensions_upgrade();. Verify this using select postgis_full_version(); and you should not see it prompt you to upgrade anything.

Pg11 -> Pg13

1. Switch to root user
2. Install pg13 using apt-get install postgresql-13
3. Install postgis-2.5 for pg13 using apt-get install postgresql-13-postgis-2.5
4. Drop the pg13 cluster using pg_dropcluster 13 main --stop
5. Stop pg11 cluster using pg_ctlcluster 11 main stop
6. Perform upgrade pg_upgradecluster 11 main -m upgrade
7. Now if you check the postgis version select postgis_full_version();, the result would prompt you to do a upgrade.
8. Install postgis-3 for pg13 as its the minumum version to work in pg13, apt-get install postgresql-13-postgis-3
9. Upgrade postgis extension alter extension postgis update; SELECT postgis_extensions_upgrade(); for EVERY DATABASE THAT HAS THIS EXTENSION INSTALLED and verify it using select postgis_full_version()
10. Stop the pg13 cluster pg_ctlcluster 13 main stop. Setup systemd service for postgresql by sudo systemctl enable postgresql@13-main and start it by sudo service postgresql@13-main start.

Notes

1. Switch back to postgres user when you are going to run pg_x command.

Reference

1. Postgis compatibility matrix
2. Postgis issue during upgrade