Configure File Based Log Shipping in PostgreSQL

Jamsher Khan 22 mins0
()

Configure File Based Log Shipping in PostgreSQL


In the previous blog, we discuss briefly on Replication available options in PostgreSql Click here to read more. In this blog, we will implement File Based Log Shipping in PostgreSQL. Below are setup details and the same will be used in this demonstration.

Sr NoHostnameIPRole
1test-machine01192.168.114.177Master / Primary Server
2test-machine02192.168.114.176Standby / Secondary Server

Below are the high level steps we will follow to setup our replication.

1. Configure Passwordless Authentication SSH connection from test-machine01 to test-machine02.
2. Configure Primary Server for File based Replication.
3. Replication Entry in pg_hba.conf File in Primary Server.
4. Take a Base Backup of Primary Server.
5. Configuring Standby Server.
6. Start Standby Server.
7. Promote the Standby Server to be the new Primary. 


Step 1. Configure Passwordless Authentication: We need to configure the Passwordless Authentication SSH connection from test-machine01 to test-machine02 as we will be using rsync OS utility for continuous WAL archiving in test-machine02. Use OS command ssh-keygen to generate public and private keys in test-machine01. Once keys are generated use OS command ssh-copy-id to transfer the public key to  test-machine02.

[root@test-machine01 ~]# su - postgres
Last login: Wed Jun 16 17:44:29 +03 2021 on pts/2
-bash-4.2$
-bash-4.2$
-bash-4.2$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/pgsql/.ssh/id_rsa):
Created directory '/var/lib/pgsql/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/pgsql/.ssh/id_rsa.
Your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:jiouqVEgyZz+dmsHM+H0yzn4GgdhYnqBkbJ3Es/ralw postgres@test-machine02
The key's randomart image is:
+---[RSA 2048]----+
| ..              |
|+o=              |
|=* B o           |
|+.= Bo.          |
| +.+ooo S        |
| .o E=.+         |
|.o = oB.+        |
|o.= o++*         |
|o+oooo+..        |
+----[SHA256]-----+
-bash-4.2$
 
-bash-4.2$ ls -ltr .ssh
total 8
-rw-r--r--. 1 postgres postgres  405 Jun 22 10:12 id_rsa.pub
-rw-------. 1 postgres postgres 1679 Jun 22 10:12 id_rsa
-bash-4.2$
 
-bash-4.2$ ssh-copy-id -i ~/.ssh/id_rsa.pub postgres@test-machine02
/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/var/lib/pgsql/.ssh/id_rsa.pub"
/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
barman@test-machine01's password:
 
Number of key(s) added: 1
 
Now try logging into the machine, with:   "ssh 'postgres@test-machine02'"
and check to make sure that only the key(s) you wanted were added.
 
-bash-4.2$
-bash-4.2$ ssh 'postgres@test-machine02'
Last login: Tue Jun 22 10:32:10 2021
-bash-4.2$
-bash-4.2$ exit
logout
Connection to test-machine02 closed.
-bash-4.2$


Step 2.1. Configure Primary Server: Configure the below parameters in primary server postgresql.conf. Please note we have configured archive_timeout to 900 sec as this will trigger WAL switch every 15 mins.

[root@test-machine01 ~]# su - postgres
Last login: Thu Jul 22 12:06:05 +03 2021 on pts/3
-bash-4.2$ psql
psql (13.1)
Type "help" for help.

postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/13/data
(1 row)

                                     List of tablespaces
    Name    |  Owner   |   Location    | Access privileges | Options |  Size   | Description
------------+----------+---------------+-------------------+---------+---------+-------------
 myts01     | postgres | /u01/pg1_tbls |                   |         | 30 MB   |
 myts02     | postgres | /u01/pg2_tbls |                   |         | 0 bytes |
 pg_default | postgres |               |                   |         | 38 MB   |
 pg_global  | postgres |               |                   |         | 559 kB  |
(4 rows)

[root@test-machine02 ~]#cd  /var/lib/pgsql/13/data
[root@test-machine02 ~]# vi postgresql.conf
archive_command = 'test ! -f postgres@test-machine02:/u01/wal_archive/%f && rsync -a %p postgres@test-machine02:/u01/wal_archive/%f'
archive_mode = on  
archive_timeout = 900
:wq!
 
[root@test-machine01 ~]# systemctl restart postgresql-13
[root@test-machine01 ~]#

postgres=#  select name,setting,unit from pg_settings where name in ('wal_level','archive_mode','archive_command','archive_timeout');
      name       |                                                     setting                                                      | unit
-----------------+------------------------------------------------------------------------------------------------------------------+------
 archive_command | test ! -f postgres@test-machine02:/u01/wal_archive/%f && rsync -a %p postgres@test-machine02:/u01/wal_archive/%f |
 archive_mode    | on                                                                                                               |
 archive_timeout | 900                                                                                                              | s
 wal_level       | replica                                                                                                          |
(4 rows)


Step 2.2 Verify WAL Arching working fine: Perform a manual WAL switch using function pg_switch_wal() in Primary DB and verify WAL archived to Standby Server.

[root@test-machine01 ~]# su - postgres
Last login: Thu Jul 22 12:06:05 +03 2021 on pts/3
-bash-4.2$ psql
psql (13.1)
Type "help" for help
postgres=#
postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/240000F0
(1 row)

-bash-4.2$ hostname
test-machine02
-bash-4.2$ pwd
/u01/wal_archive
-bash-4.2$
-bash-4.2$ ls -ltr
total 16384
-rw-r-----. 1 postgres postgres 16777216 Jul 22 13:51 000000050000000000000024
-bash-4.2$


Step 3. Replication Entry in pg_hba.conf File in Primary Server: To execute pg_basebackup command from the Remote Server you need to update pg_hba.conf and tell PostgreSQL Server that you will get a replication connection request from Remote Server. Otherwise, you will receive an error. Once changes are done in pg_hba.conf reload the changes with pg_ctl reload or SELECT pg_reload_conf(), Please note we added line “host replication all 192.168.114.0/24 scram-sha-256“ at end of the file.

-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$
-bash-4.2$ vi pg_hba.conf
host    replication     all             192.168.114.0/24        scram-sha-256
:wq!

-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$ cat pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.114.0/24        scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    replication     all             192.168.114.0/24        scram-sha-256
-bash-4.2$

postgres=#  SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#


Step 4. Take a Base Backup of Primary Server from Standby Server: Before we take base backup of Primary DB. We need to create the same directory structure for non-default tablespace in Standby Server. In Primary we have 2 non default tablespace myts01:/u01/pg1_tbls & myts02:/u01/pg2_tbls. Also remove all files from PG_DATA of standby database. We will take a remote backup from Standby Server using utility pg_basebackup. For more commands of pg_basebackup Click here.

[root@test-machine02 ~]#
[root@test-machine02 ~]# systemctl stop postgresql-13
[root@test-machine02 ~]#

[root@test-machine02 u01]# mkdir /u01/pg1_tbls
[root@test-machine02 u01]# mkdir  /u01/pg2_tbls
[root@test-machine02 u01]#
[root@test-machine02 u01]# chown postgres:postgres /u01/pg1_tbls
[root@test-machine02 u01]#
[root@test-machine02 u01]# chown postgres:postgres /u01/pg2_tbls
[root@test-machine02 u01]#

[root@test-machine02 u01]#
[root@test-machine02 u01]# cd /var/lib/pgsql/13/data/
[root@test-machine02 data]# rm -rf *
[root@test-machine02 data]# ls -ltr
total 0
[root@test-machine02 data]#


[root@test-machine02 ~]# su - postgres
Last login: Fri Jul 23 15:37:42 +03 2021 from test-machine01 on pts/2
-bash-4.2$
-bash-4.2$ hostname
test-machine02
-bash-4.2$
-bash-4.2$ pg_basebackup -h test-machine01 -p 5432 -U postgres -D /var/lib/pgsql/13/data  -Fp -Xs -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2D000028 on timeline 5
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_16227"
74375/74375 kB (100%), 3/3 tablespaces
pg_basebackup: write-ahead log end point: 0/2D000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
-bash-4.2$
-bash-4.2$

-bash-4.2$ cd /var/lib/pgsql/13/data
-bash-4.2$
-bash-4.2$ ls -ltr
total 380
drwx------. 3 postgres postgres     84 Jul 22 14:59 pg_wal
-rw-------. 1 postgres postgres    226 Jul 22 14:59 backup_label
drwx------. 6 postgres postgres     54 Jul 22 14:59 base
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_snapshots
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_serial
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_replslot
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_notify
drwx------. 4 postgres postgres     36 Jul 22 14:59 pg_multixact
drwx------. 4 postgres postgres     68 Jul 22 14:59 pg_logical
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_dynshmem
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_commit_ts
drwx------. 2 postgres postgres   4096 Jul 22 14:59 log
drwx------. 2 postgres postgres     18 Jul 22 14:59 pg_xact
-rw-------. 1 postgres postgres      3 Jul 22 14:59 PG_VERSION
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_twophase
drwx------. 2 postgres postgres     32 Jul 22 14:59 pg_tblspc
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_subtrans
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_stat_tmp
drwx------. 2 postgres postgres      6 Jul 22 14:59 pg_stat
-rw-------. 1 postgres postgres   1636 Jul 22 14:59 pg_ident.conf
-rw-------. 1 postgres postgres    583 Jul 22 14:59 old_postgresql.auto.conf
-rw-------. 1 postgres postgres    259 Jul 22 14:59 backup_label.old
-rw-------. 1 postgres postgres  28083 Jul 22 14:59 postgresql.conf
-rw-------. 1 postgres postgres   4704 Jul 22 14:59 pg_hba.conf
drwx------. 2 postgres postgres   4096 Jul 22 14:59 global
-rw-------. 1 postgres postgres     30 Jul 22 14:59 current_logfiles
-rw-------. 1 postgres postgres 319362 Jul 22 14:59 backup_manifest
-bash-4.2$


Step 5. Configuring Standby Server: First, create a file standby.signal in the data directory this file will cause PostgreSQL Server to enter in standby mode. Set below parameters in Standby postgresql.conf file.

-bash-4.2$ hostname
test-machine02
-bash-4.2$ cd /var/lib/pgsql/13/data
-bash-4.2$
-bash-4.2$ touch standby.signal
-bash-4.2$

-bash-4.2$ pwd
/var/lib/pgsql/13/data
-bash-4.2$
-bash-4.2$ vi postgresql.conf
restore_command = 'cp /u01/wal_archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup -d /u01/wal_archive %r'
recovery_target_timeline = 'latest'
:wq!


Step 6.1. Start Standby Server and Verify logfile: Once the above steps are done start Standby PostgreSql Server and verify logfile. You can also verify from function pg_is_in_recovery() whether Standby DB is in recovery mode.

[root@test-machine02 data]#
[root@test-machine02 data]# systemctl start postgresql-13
[root@test-machine02 data]#

[root@test-machine02 ~]# su - postgres
Last login: Fri Jul 23 15:37:42 +03 2021 from test-machine01 on pts/2
-bash-4.2$
-bash-4.2$ hostname
test-machine02
-bash-4.2$ psql
psql (13.2)
Type "help" for help.
postgres=#
postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)


postgres=# show hot_standby;
 hot_standby
-------------
 on
(1 row)

postgres=# CREATE TABLE my_table_test(col1 varchar, col2 varchar);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
postgres=#

-bash-4.2$ pwd
/var/lib/pgsql/13/data/log
-bash-4.2$
-bash-4.2$
-bash-4.2$ vi postgresql-Thu.log
-bash-4.2$
2021-07-22 15:10:51.009 +03 [85329] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-07-22 15:10:51.011 +03 [85329] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-07-22 15:10:51.011 +03 [85329] LOG:  listening on IPv6 address "::", port 5432
2021-07-22 15:10:51.012 +03 [85329] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-07-22 15:10:51.013 +03 [85329] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-07-22 15:10:51.016 +03 [85333] LOG:  database system was interrupted; last known up at 2021-07-22 15:00:03 +03
cp: cannot stat â/u01/wal_archive/00000006.historyâ: No such file or directory
2021-07-22 15:10:51.043 +03 [85333] LOG:  entering standby mode
cp: cannot stat â/u01/wal_archive/00000005.historyâ: No such file or directory
2021-07-22 15:10:51.056 +03 [85333] LOG:  restored log file "00000005000000000000002D" from archive
2021-07-22 15:10:51.073 +03 [85333] LOG:  redo starts at 0/2D000028
2021-07-22 15:10:51.073 +03 [85333] LOG:  consistent recovery state reached at 0/2D000138
2021-07-22 15:10:51.074 +03 [85329] LOG:  database system is ready to accept read only connections
cp: cannot stat â/u01/wal_archive/00000005000000000000002Eâ: No such file or directory
cp: cannot stat â/u01/wal_archive/00000006.historyâ: No such file or directory
cp: cannot stat â/u01/wal_archive/00000005000000000000002Eâ: No such file or director


Step 6.2. Test Standby Configuration: Since this is configuration is File Based Log Shipping changes from Primary will be replicated to Standby after WAL is switch in Primary and then WAL archive is applied to Standby DB. WAL Switch in Primary will happen when 16MB (Default Size) of WAL records are generated. You can also control WAL switch in Primary with the parameter archive_timeout.


Primary DB

postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=# CREATE TABLE my_table_test(col1 varchar, col2 varchar);
CREATE TABLE
sample1=# INSERT INTO my_table_test VALUES ('row1', 'this is row one'), ('row2', 'this is row2');
INSERT 0 2
sample1=#

Standby DB

postgres=#
postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=#
sample1=# select * from  my_table_test;
ERROR:  relation "my_table_test" does not exist
LINE 1: select * from  my_table_test;

Primary DB

sample1=#
sample1=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/2F01AC10
(1 row)

sample1=#

Standby DB

postgres=# \connect sample1
You are now connected to database "sample1" as user "postgres".
sample1=# select * from  my_table_test;
 col1 |      col2
------+-----------------
 row1 | this is row one
 row2 | this is row2
(2 rows)

sample1=#


Step 7. Promote the Standby Server to be the new Primary : Let assume that Primary is unavailable due to Software or Hardware issue. You can use pg_ctl promote command to promote Standby DB to be new Primary.


Primary DB 

[root@test-machine01 data]#
[root@test-machine01 data]# systemctl stop postgresql-13
[root@test-machine01 data]#

Standby DB

-bash-4.2$
-bash-4.2$ /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data
waiting for server to promote.... done
server promoted
-bash-4.2$
-bash-4.2$


-bash-4.2$ pwd
/var/lib/pgsql/13/data/log
-bash-4.2$
-bash-4.2$ vi postgresql-Fri.log
cp: cannot stat â/u01/wal_archive/000000050000000000000032â: No such file or directory
cp: cannot stat â/u01/wal_archive/00000006.historyâ: No such file or directory
cp: cannot stat â/u01/wal_archive/000000050000000000000032â: No such file or directory
2021-07-23 15:01:03.527 +03 [85333] LOG:  received promote request
2021-07-23 15:01:03.527 +03 [85333] LOG:  redo done at 0/31001468
2021-07-23 15:01:03.527 +03 [85333] LOG:  last completed transaction was at log time 2021-07-22 15:29:06.600442+03
2021-07-23 15:01:03.596 +03 [85333] LOG:  restored log file "000000050000000000000031" from archive
cp: cannot stat â/u01/wal_archive/00000006.historyâ: No such file or directory
2021-07-23 15:01:03.626 +03 [85333] LOG:  selected new timeline ID: 6
2021-07-23 15:01:03.651 +03 [85333] LOG:  archive recovery complete
cp: cannot stat â/u01/wal_archive/00000005.historyâ: No such file or directory
2021-07-23 15:01:03.661 +03 [85329] LOG:  database system is ready to accept connections


Reference: PostgreSql Docs: https://www.postgresql.org/docs/13/warm-standby.html

 

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

 

 

Related articles

,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Related Articles