The previous post was mentioning the log shipping form of the replication which is the most primitive type of replication. There are some drawbacks of log shipping;
Changes on the primary database, apply to standby only when the wal file ships to the standby
If there is not enough time passed the last checkpoint (checkpoint_timeout) or archive_timeout parameter, standby will not replicate the current state of the primary.
Streaming replication is a way of continuous application of the changes on the primary database to the standby regardless of the wal archives to be sent on regular basis.
Infrastructure
Setting of the blog is going to be two virtual machines with centos stream 9 and postgresql 15 installed;
centos01: 10.211.55.4
centos02: 10.211.55.5
centos01 is going to be the primary and centos02 will be the standby postgresql servers.
Primary Database Configuration
Some of the configurations are still apply on the primary database which is archive_mode=on and archive_command setting. This configuration are mainly for pg_basebackup consistent backups.
postgres=# alter system set archive_mode=on;
ALTER SYSTEM
postgres=# alter system set archive_command='cp %p /pg_data/15/wal_archive/%f';
ALTER SYSTEMAddition to the basic configuration on the primary database, there is going to be a direct connection to fetch the changes from the standby database. To establish a connection from standby to primary database a database user should be created on the primary and allow connection request from the standby by using pg_hba.conf
postgres=# create user replication_user with replication encrypted password 'replication_password';
CREATE ROLE
postgres=# alter user replication_user with password 'replication_user';Now, we need to configure pg_hba.conf file.
postgres=# select name, setting from pg_settings where name like '%hba%';
name | setting
----------+--------------------------------
hba_file | /etc/postgresql/15/pg_hba.conf
(1 row)
postgres=# show hba_file;
hba_file
--------------------------------
/etc/postgresql/15/pg_hba.conf
(1 row)Add the following line to pg_hba.conf file for enabling the standby to be able to connect to the primary database.
host replication all 10.211.55.5/32 md5Standby Database Configuration
Now we need to take a backup of the primary database and copy the backup to secondary, or (as in this example) we run the pg_basebackup from the secondary database to bypass the copy workload.
pg_basebackup -h 10.211.55.4 -p 5432 -U replication_user -W -Ft -Xn -v -D /pg_data/15/backups/base_backup-Ft : Output of the format is going to be tar.
-Xn: Backup Method of the wal files. In this case we are going to copy the wal files manually.
-v: Verbose output
-D: Target directory of the backup
Restore the backup taken from the primary database to the standby database.
rm -rf /pg_data/15/data/*
cd /pg_data/15/backups/base_backup
tar -xvf base.tar -C /pg_data/15/dataAfter the successful restore we do not want to open this database, but only apply the changes on the primary only. For this purpose we need to create standby.signal file in the $PGDATA directory which is /pg_data/15/data in our case.
Now lets give a try and open the database with pg_ctl start and examine the log output.
touch /pg_data/15/data/standby.signal
pg_ctl start pg_ctl start command will wait forever and from the log files under the $PGDATA/log directory we can get the clue for that.
LOG: waiting for WAL to become available at 0/B000078You may need to send the last wal file produced by the primary database to the standby database manually in order to obtain a consistent state on the standby and let the postgresql accept read-only connections.
Since our database is in standby mode (because of the existence of the standby.signal file), the database is going to wait the next wal file forever.
This is the point where we setup a log shipping. If we set the restore_command on the standby database, standby database will start to apply the changes when the wal switch occurs and the wal file arrives to the secondary instance. (In this case you dont even need to copy the last wal archive file from the master database)
But this time we are going to do a different configuration and set the primary_conninfo parameter on the standby database. With this way our standby will directly connect to the primary database and pull all the changes without waiting the wal files to be switched and sent to the standby.
alter system set primary_conninfo = 'user=replication_user password=replication_password host=10.211.55.4 port=5432';
select pg_reload_conf();As soon as we reloaded the altered configuration, the log file indicates wal streaming has been started.
<time=2024-01-25 00:38:18.470 GMT app= host= user= db= pid=1842 line=10 trx=0>LOG: parameter "primary_conninfo" changed to "user=replication_user password=replication_password host=10.211.55.4 port=5432"<time=2024-01-25 00:38:18.471 GMT app= host= user= db= pid=1842 line=11 trx=0>LOG: could not open usermap file "/etc/postgresql/15/pg_ident.conf": No such file or directory<time=2024-01-25 00:38:18.471 GMT app= host= user= db= pid=1842 line=12 trx=0>LOG: pg_ident.conf was not reloaded<time=2024-01-25 00:38:18.471 GMT app= host= user= db= pid=1846 line=5 trx=0>LOG: WAL receiver process shutdown requested<time=2024-01-25 00:38:18.473 GMT app= host= user= db= pid=1847 line=1 trx=0>FATAL: terminating walreceiver process due to administrator command<time=2024-01-25 00:38:18.491 GMT app= host= user= db= pid=1850 line=1 trx=0>LOG: started streaming WAL from primary at 0/4000000 on timeline 1Check the Configuration
Now lets test the real-time replication of our database. I am going to create a table and insert a current timestamp to see if our replication works fine.
#primary
postgres=# create table ttest (col1 timestamp);
CREATE TABLE
postgres=# insert into ttest values (current_timestamp);
INSERT 0 1
postgres=# select * from ttest;
col1
---------------------------
2024-01-25 01:00:27.01916
(1 row)
#standby
postgres=# select * from ttest;
col1
----------------------------
2024-01-25 01:00:27.01916
2024-01-25 01:05:59.761726
(2 rows)
postgres=# select current_timestamp;
current_timestamp
-------------------------------
2024-01-25 01:06:21.777696+00
(1 row)
postgres=# Monitoring
There are two system view to check the ongoing replication process PG_STAT_REPLICATION can be used to view the sender progress on primary database. PG_STAT_WAL_RECIEVER view can be examined on the standby database.
#primary database
SELECT * FROM pg_stat_replication;
#standby datase
SELECT * FROM pg_stat_wal_receiver;Pros and Cons
Usually this kind of streaming replication is configured with the log shipping together because;
If the standby database lags for some reason, wal files could be deleted from the master database before they are applied to the standby. In this case wal archives are going to be needed on the standby before streaming replications kicks in.
So it is a best practice to set up wal archiving on the master and log shipping to standby and set restore_command on standby database.
It is obvious that without the streaming replication, standby database will always be behind the master database as long as new wal file is generated and this is bound to the archive_timeout parameter.
The monitoring of the replication is also enhanced in this replication method due to two new system views both on primary and standby databases.
Part 3 of this will cover the replication slot usage on the streaming replication which will also guarantee the existence of the wal archive on the primary, if the changes are not applied on the standby.
Comments