PostgreSQL 12 can be considered revolutionary considering the performance boost we observe with partitioning enhancements, planner improvements, several SQL features, Indexing improvements, etc. You may see some of such features discussed in future blog posts. But, let me start this blog with something interesting. You might have already seen some news that there is no recovery.conf file in standby anymore and that the replication setup (streaming replication) has slightly changed in PostgreSQL 12. We have earlier blogged about the steps involved in setting up a simple Streaming Replication until PostgreSQL 11 and also about using replication slots for the same. Let’s see how different is it to set up the same Streaming Replication in PostgreSQL 12.
Installing PostgreSQL 12 on Master and Standby
On CentOS/RedHat, you may use the rpms available in the PGDG repo (the following link may change depending on your OS release).
1 2 3 | # as root: yum install -y https://yum.postgresql.org/12/redhat/rhel-7.4-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y yum install -y postgresql12-server |
Steps to set up Streaming Replication in PostgreSQL 12
In the following steps, the Master server is: 192.168.0.108 and the Standby server is: 192.168.0.107
Step 1 :
Initialize and start PostgreSQL, if not done already on the Master.
1 2 3 4 5 6 7 8 | ## Preparing the environment $ sudo su - postgres $ echo "export PATH=/usr/pgsql-12/bin:$PATH PAGER=less" >> ~/.pgsql_profile $ source ~/.pgsql_profile ## As root, initialize and start PostgreSQL 12 on the Master $ /usr/pgsql-12/bin/postgresql-12-setup initdb $ systemctl start postgresql-12 |
Step 2 :
Modify the parameter listen_addresses to allow a specific IP interface or all (using *). Modifying this parameter requires a restart of the PostgreSQL instance to get the change into effect.
1 2 3 4 5 6 | # as postgres $ psql -c "ALTER SYSTEM SET listen_addresses TO '*'"; ALTER SYSTEM # as root, restart the service $ systemctl restart postgresql-12 |
You may not have to set any other parameters on the Master for simple replication setup, because the defaults hold good.
Step 3 :
Create a User for replication in the Master. It is discouraged to use superuser postgres in order to setup replication, though it works.
1 2 | postgres=# CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'secret'; CREATE ROLE |
Step 4 :
Allow replication connections from Standby to Master by appending a similar line as following to the pg_hba.conf file of the Master. If you are enabling automatic failover using any external tool, you must also allow replication connections from Master to the Standby. In the event of a failover, the Standby may be promoted as a Master and the Old Master need to replicate changes from the New Master (previously a standby). You may use any of the authentication methods as supported by PostgreSQL today.
1 2 3 4 5 | $ echo "host replication replicator 192.168.0.107/32 md5" >> $PGDATA/pg_hba.conf ## Get the changes into effect through a reload. $ psql -c "select pg_reload_conf()" |
Step 5 :
You may use pg_basebackup to backup the data directory of the Master from the Standby. While creating the backup, you may also tell pg_basebackup to create the replication specific files and entries in the data directory using "-R" .
1 2 3 4 | ## This command must be executed on the standby server. $ pg_basebackup -h 192.168.0.108 -U replicator -p 5432 -D $PGDATA -Fp -Xs -P -R Password: 25314/25314 kB (100%), 1/1 tablespace |
You may use multiple approaches such as rsync or any other disk backup methods to copy the master’s data directory to the standby. But, there is an important file (standby.signal) that must exist in a standby data directory to help postgres determine its state as a standby. It is automatically created when you use the "-R" option while taking pg_basebackup. If not, you may simply use touch to create this empty file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | $ touch $PGDATA/standby.signal $ ls -l $PGDATA total 60 -rw-------. 1 postgres postgres 224 Oct 8 16:41 backup_label drwx------. 5 postgres postgres 41 Oct 8 16:41 base -rw-------. 1 postgres postgres 30 Oct 8 16:41 current_logfiles drwx------. 2 postgres postgres 4096 Oct 8 16:41 global drwx------. 2 postgres postgres 32 Oct 8 16:41 log drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_commit_ts drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_dynshmem -rw-------. 1 postgres postgres 4581 Oct 8 16:41 pg_hba.conf -rw-------. 1 postgres postgres 1636 Oct 8 16:41 pg_ident.conf drwx------. 4 postgres postgres 68 Oct 8 16:41 pg_logical drwx------. 4 postgres postgres 36 Oct 8 16:41 pg_multixact drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_notify drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_replslot drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_serial drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_snapshots drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_stat drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_stat_tmp drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_subtrans drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_tblspc drwx------. 2 postgres postgres 6 Oct 8 16:41 pg_twophase -rw-------. 1 postgres postgres 3 Oct 8 16:41 PG_VERSION drwx------. 3 postgres postgres 60 Oct 8 16:41 pg_wal drwx------. 2 postgres postgres 18 Oct 8 16:41 pg_xact -rw-------. 1 postgres postgres 288 Oct 8 16:41 postgresql.auto.conf -rw-------. 1 postgres postgres 26638 Oct 8 16:41 postgresql.conf -rw-------. 1 postgres postgres 0 Oct 8 16:41 standby.signal |
One of the most important observations should be the contents of the postgresql.auto.conf file in the standby server. As you see in the following log, an additional parameter primary_conninfo has been added to this file. This parameter tells the standby about its Master. If you haven’t used pg_basebackup with -R option, you would not see this entry (of primary_conninfo) in this file, on the standby server. Which means that you have to add this manually.
1 2 3 4 5 | $ cat $PGDATA/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. listen_addresses = '*' primary_conninfo = 'user=replicator password=secret host=192.168.0.108 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' |
postgresql.auto.conf file is the configuration file that is read at the end when you start Postgres. So, if there is a parameter that has different values in postgresql.conf and postgresql.auto.conf files, the value set in the postgresql.auto.conf is considered by PostgreSQL. Also, any parameter that has been modified using ALTER SYSTEM would automatically be written to postgresql.auto.conf file by postgres.
How was the replication configuration handled until PostgreSQL 11?
Until PostgreSQL 11, we must create a file named: recovery.conf that contains the following minimalistic parameters. If the standby_mode is ON, it is considered to be a standby.
1 2 3 | $ cat $PGDATA/recovery.conf standby_mode = 'on' primary_conninfo = 'host=192.168.0.8 port=5432 user=replicator password=secret' |
So the first difference between PostgreSQL 12 and earlier (until PostgreSQL 11) is that the standby_mode parameter is not present in PostgreSQL 12 and the same has been replaced by an empty file standby.signal in the standby’s data directory. And the second difference is the parameter primary_conninfo. This can now be added to the postgresql.conf or postgresql.auto.conf file of the standby’s data directory.
Step 6 :
Start PostgreSQL using pg_ctl on the Standby.
1 | $ pg_ctl -D $PGDATA start |
Step 7 :
Verify the replication between the Master and the Standby. In order to verify, run this command on the Master. In the following log, you see a lot of details of the standby and the lag between the Master and Standby.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | $ psql -x -c "select * from pg_stat_replication" -[ RECORD 1 ]----+------------------------------ pid | 2522 usesysid | 16384 usename | replicator application_name | walreceiver client_addr | 192.168.0.107 client_hostname | client_port | 36382 backend_start | 2019-10-08 17:15:19.658917-04 backend_xmin | state | streaming sent_lsn | 0/CB02A90 write_lsn | 0/CB02A90 flush_lsn | 0/CB02A90 replay_lsn | 0/CB02A90 write_lag | 00:00:00.095746 flush_lag | 00:00:00.096522 replay_lag | 00:00:00.096839 sync_priority | 0 sync_state | async reply_time | 2019-10-08 17:18:04.783975-04 |
Enabling Archiving on Master and the Standby recovery using Archives.
Most of the time, the default or modified retention settings of WAL segments on the Master may not be enough to maintain a healthy replication between itself and its standby. So, we need the WALs to be safely archived to another disk or a remote backup server. These archived WAL segments can be used by the standby to replay them when the WALs are gone from the Master.
To enable archiving on the Master, we can still use the same approach of setting the following 2 parameters.
1 2 | archive_mode = ON archive_command = 'cp %p /archives/%f' ## Modify this with an appropriate shell command. |
But to enable recovery from archives on a standby, we used to add a parameter named restore_command to the recovery.conf file until PostgreSQL 11. But starting from PostgreSQL 12, we can add the same parameter to postgresql.conf or postgresql.auto.conf file of the standby. Please note that it requires a restart of PostgreSQL to update the changes made to archive_mode and restore_command parameters.
1 2 | echo "restore_command = 'cp /archives/%f %p'" >> $PGDATA/postgresql.auto.conf pg_ctl -D $PGDATA restart -mf |
In my next blog post, I shall talk about Point-in-time-recovery on PostgreSQL 12, where I will discuss a few more parameters related to recovery in detail. Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.
Our white paper, “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.
Thank you for a very good post. Could you explain also how the fail over works, promoting slave to master. And how it works with cascade replication, another slave after slave, after switching first slave to master. The second question is how to switch back to origin master.
Appreciated and Thanks for sharing good document. I am very new in this technology, after done all the steps from the above document, i restarted in postgressql in stand by server but the server can’t get back,please suggest me what wrong with in.
Thanks
Step 6) i got error
“lock file “postmaster.pid” already exists”
Postgres is already running on your machine.
When will PostgreSQL support Master – Master Replication. i.e. what 2ndQuadrant did with BDR-3 in place of BDR-1. 2nd Quadrant made BDR-3 NOT open source, and charges around 9,000 USD per server, at least that was the figure I was given. I looked at porting BDR-1 to PG 10.0, I figured it is around a 470+ hour job to do so, if not more, – and IF PostgreSQL 13 or 14 is going to support BDR Master/Master, that is what interests me. As it stands I am simply porting over my Application to MySQL – or rather that is my solution, verse paying 2ndQuadrant for each server. Am I barking up the wrong tree?
Hi Avi:
I see the below error:
tgres@postgresql:/etc/postgresql/12/main$ pg_ctl -D /etc/postgresql/12/main/ start
waiting for server to start….postgres: could not access the server configuration file “/etc/postgresql/12/main/postgresql.conf”: No such file or directory
stopped waiting
pg_ctl: could not start server
Examine the log output.
The pg_basebackup dint copy the postgresql.conf file form the master:
pg_basebackup -h 192.168.56.7 -U replicator -p 5432 -D /etc/postgresql/12/main/ -Fp -Xs -P -R
Thanks Avinash, good post
Hi Avinsah,
can you please let me know how to replicate only one database for example A from one primary to secondary, if i have more than 1 database in primary but just want to replicated 1 db to secondary rest can stay without replication or dr
Logic replication should be used in this case to synchronize tables, but not DB instance level.
Hi Avinsah,
thank you, good and very useful post!