Setting up streaming replication in PostgreSQL 10
In today’s post, I will be setting up a primary and standby configuration for PostgreSQL 10 .
In PostgreSQL the DR configuration is called streaming after release 9 onward. All transaction in PostgreSQL are written to a transaction log called WAL (write-ahead log) (redo log in Oracle).
PostgreSQL followed terminology of master(primary) and slaves(standby can be more than one ). The slaves in PostgreSQL used the wal_segments to replicates changes from the master .
The background process involved in replication are as follow
1.wal writer : WAL writer is a background process to check the WAL buffer periodically and write all unwritten XLOG records into the WAL segments
2. wal sender : process runs on a master , sends the latest WAL data(LSN) to WAL receiver.
3. wal receiver : This process runs on slave servers, it sends the details on the reecent LSN , that has been applied on the slave or standby to master .
4. startup : This is process responsible for applying the WAL on the slaves .
The replication in PostgreSQL happen at cluster level and not at individual table or database level . all the databases created on a particular pg server , will be replicated using the streaming .
In this example , I will be creating the slave(standby) cluster on the same host for illustration , In actual scenaio, we need to create the slave cluster on different hosts .
Creating a new cluster(for standby) in Postgres . We use initdb utility to create new cluster in PostgreSQL
Create the directory, where you want to create all your database files and configuration files related to the new cluster.
-bash-4.2$ cd /opt/
-bash-4.2$ mkdir -p newclusterdb
-bash-4.2$ pwd
/opt/newclusterdb
-bash-4.2$ init
init initdb
-bash-4.2$ initdb -D /opt/newclusterdb -W
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Enter new superuser password:
Enter it again:
fixing permissions on existing directory /opt/newclusterdb … ok
creating subdirectories … ok
selecting default max_connections … 100
selecting default shared_buffers … 128MB
Success. You can now start the database server using:
pg_ctl -D /opt/newclusterdb -l logfile start
Please verify , if it has created all the files,directory and config files related to new cluster.
-bash-4.2$ ls -ltr
total 48
-rw------- 1 postgres postgres 3 Jul 22 09:44 PG_VERSION
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_twophase
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_tblspc
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_stat_tmp
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_stat
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_snapshots
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_serial
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_replslot
drwx------ 4 postgres postgres 36 Jul 22 09:44 pg_multixact
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_dynshmem
drwx------ 2 postgres postgres 6 Jul 22 09:44 pg_commit_ts
-rw------- 1 postgres postgres 22969 Jul 22 09:44 postgresql.conf
-rw------- 1 postgres postgres 88 Jul 22 09:44 postgresql.auto.conf
-rw------- 1 postgres postgres 1636 Jul 22 09:44 pg_ident.conf
-rw------- 1 postgres postgres 4513 Jul 22 09:44 pg_hba.conf
drwx------ 2 postgres postgres 18 Jul 22 09:44 pg_xact
drwx------ 3 postgres postgres 60 Jul 22 09:44 pg_wal
drwx------ 2 postgres postgres 18 Jul 22 09:44 pg_subtrans
drwx------ 2 postgres postgres 18 Jul 22 09:44 pg_notify
drwx------ 2 postgres postgres 4096 Jul 22 09:44 global
drwx------ 5 postgres postgres 41 Jul 22 09:44 base
drwx------ 4 postgres postgres 68 Jul 22 09:44 pg_logical
Now , start the new clsuter which will be our slave or standby cluster for relplication.
-bash-4.2$ pg_ctl -D /opt/newclusterdb -l logfile start
waiting for server to start…. done
Login and check conifrm the config regarding the new clsuter
-bash-4.2$ psql
Password:
psql.bin (10.13)
Type "help" for help.
postgres=#
postgres=# show data_directory ;
data_directory
/opt/newclusterdb
(1 row)
Now that we have out standby cluster ready , we need to make changes in primary and standby clusters to make the replication work .
Please note the the below cluster with data directory path will be our primary and standby cluster .
Primary : Data Directory: /opt/PostgreSQL/10/data Standby : Data Directory: /opt/newclusterdb
Preparing the Primary or master cluster
- Make a directory to store a copy the WAL segments to another location in addition to wal segment . When archiving WAL data, we need to capture the contents of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse.
2. Edit the postgresql.conf file of primary cluster , this files will be located in data direcory $PGDATA .
Make the below changes in the PostgreSQL config files.
archive_command = 'test ! -f /opt/pgfiles/pg_archive_log/primary%f && cp %p /opt/pgfiles/pg_archive_log/primary%f' wal_level = replica # minimal, replica, or logical archive_mode = on # enables archiving; off, on, or always hot_standby = on # For read only db opening max_wal_senders = 10 # max number of walsender processes-bash- wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables archive_command: Use to copy command to copy the WAL segments to another location archive_mode : To enable archiving of WALs. wal_level : Must be set to replica, for replication. max_wal_senders : required 3 if you are starting with one slave. For every slave, you may add 2 wal senders. hot_standby: This parameter is used to enable READS on slave. Otherwise, you cannot run your SELECT queries against slave
3. Create the user in master using whichever slave should connect for streaming the WALs. This user must have REPLICATION ROLE.
4. Add an entry to pg_hba.conf of the master to allow replication connections from the slave. The location of pg_hba.conf is the $PGDATA. Since we will be connecting to the same server , since I have both primary and standby cluster on same node . we will use local . The trust clause is there to avoid password entries.
5. Reload the primary cluster to make sure all changes made to postgresql.conf and pg_hba.conf are applied successfully.
Preparing the Standby or Slave cluster
- Stop the standby/slave cluster , which we have created earlier .
2. Take a backup of existing data directory of slave cluster , just in case anything goes wrong . we can be able to recover
3. Remove all the data files and configuration files created during the new slave cluster creation , because we need to replicate the the primary cluster.In the next step we will take a backup which will create all cluster files as it is in primary cluster and then we will start the replicate .
4. Take the backup using pg_basebackup . pg_basebackup will stream the data through the wal sender process from the master to a slave to set up replication.Its kind of full backup of existing primary cluster and restored onto the slaves . You can also take a tar format backup from master and copy that to the slave server. But , will go with pg_basebackup as it very much simpler .
After pg_basebackup , go the slave data directory and verify all the data files,WAL segments and config files of Primary data directory arecopied or not.
You see on the last there is an additional files generated called recovery.conf
In the above pg_basebackup command, There is optional argument -R. When we pass -R, it automatically creates a recovery.conf file that contains the role of the DB instance and the details of its master. It is mandatory to create the recovery.conf file on the slave in order to set up a streaming replication. If you are not using the backup type mentioned above, and choose to take a tar format backup on master that can be copied to slave, you must create this recovery.conf file manually. Here are the contents of the recovery.conf file:
Now , start the slave cluster , make sure the port number in postressql.conf must be different from the primary cluster . other wise it will give error during clsuter restart
Now login to primary cluster psql prompt,and create a new database an insert some data in tables. and check whether the data are being replicated to the slave or not .
Now login to slave cluster , since slave clsuter is running on port 5433, just login with psql -p 5433 , it will connect to the slave cluster , to verify you are connected to correct cluster just query the data_directory.
Finally check the replication status using pg_stat_replication
To check if the cluster is in standby mode
postgres=# select pg_is_in_recovery(); pg_is_in_recovery t (1 row) Should be true standby_sync=# select * from checkstdby ; id | walfile | content ----+----------------------+----------------------------- 1 | check wal generation | table to check stnadby sync 2 | check stnadby alert | normal check (2 rows) standby_sync=# insert into checkstdby values(4,'check stnadby read only','check for insert') ; ERROR: cannot execute INSERT in a read-only transaction Since , the standby is in read only mode , you cannot do dml on standby .
Query to check the standby gap( must be executed from primary ) postgres=# select postgres-# pid, postgres-# application_name, postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) sending_lag, postgres-# pg_wal_lsn_diff(sent_lsn, flush_lsn) receiving_lag, postgres-# pg_wal_lsn_diff(flush_lsn, replay_lsn) replaying_lag, postgres-# pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) total_lag postgres-# from pg_stat_replication; pid | application_name | sending_lag | receiving_lag | replaying_lag total_lag ------+------------------+-------------+---------------+---------------+----------- 7800 | walreceiver | 0 | 0 | 0 | 0 (1 row) From primary the status of replication and WAL -bash-4.2$ psql -c "\x" -c "select * from pg_stat_replication;" Password: Expanded display is on. -[ RECORD 1 ]----+------------------------------ pid | 3793 usesysid | 16414 usename | rep_user application_name | walreceiver client_addr | client_hostname | client_port | -1 backend_start | 2020-08-20 22:09:29.783113+00 backend_xmin | state | streaming sent_lsn | 0/D000140 write_lsn | 0/D000140 flush_lsn | 0/D000140 replay_lsn | 0/D000140 write_lag | flush_lag | replay_lag | sync_priority | 0 sync_state | async From standby the status of replication and WAL -bash-4.2$ psql -p 5433 -c "\x" -c "select * from pg_stat_wal_receiver;" pid | 3792 status | streaming receive_start_lsn | 0/A000000 receive_start_tli | 1 received_lsn | 0/D000140 received_tli | 1 last_msg_send_time | 2020-08-20 22:42:05.41319+00 last_msg_receipt_time | 2020-08-20 22:42:05.413217+00 latest_end_lsn | 0/D000140 latest_end_time | 2020-08-20 22:38:34.893921+00 slot_name | conninfo | user=rep_user passfile=/opt/PostgreSQL/10/.pgpass dbname=replication port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
839 total views, 1 views today