Based Shipping Standby

Initial setup on Master server:

[root@master data]#su - postgres 

[postgres@master data]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:lutY8BMXyqWrAqZLjiK9bBouY/Az0Txm87d3SvpmjLE root@master.server.com
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|                 |
|          o      |
|       . = .     |
|   o  . S .      |
|. + B  +.=       |
|o* = +  *=.      |
|@+* . .=E+= .    |
|OBo+ .o.+*oo     |
+----[SHA256]-----+

[postgres@master data]# ssh-copy-id postges@172.16.95.166
 

[root@master postgres]# systemctl stop postgresql-12.service
 

[root@master data]#cd /var/lib/pgsql/12/data/

[root@master data]#vim postgres.conf

archive_mode = on             
archive_command = 'rsync -a %p root@172.16.95.166:/var/lib/pgsql/12/archive/%f'        
archive_timeout = 60

 

[root@master postgres]# systemctl restart postgresql-12.service 

Create a archive directory on standby which is accessible by Master user.
#Shutdown postgresql on standby
#Delete all the contents of /Data directory

[postgres@standby ~]# mkdir   /var/lib/pgsql/12/archive 

[root@standby ~]# systemctl stop postgresql-12.service  

[root@standby ~]# su -postgres

[root@standby ~]# cd /var/lib/pgsql/12/data/ 

[root@standby ~]# rm -rf *

 [root@master ~]# su - postgres
 Last login: Thu Nov 23 13:38:41 PST 2023 on pts/1
-bash-4.2$ psql

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 

 postgres=# create database aws;

postgres=# \c aws;
You are now connected to database "aws" as user "postgres".
aws=# create table state(id int,state_name varchar);
CREATE TABLE
aws=# \q
-bash-4.2$ psql
psql (12.16)
Type "help" for help.

postgres=# select pg_start_backup('dbrep');
 pg_start_backup
-----------------
 0/18000028
(1 row) 

[postgres@master ~]# rsync -avz /var/lib/pgsql/12/data/* root@172.16.95.166:/var/lib/pgsql/12/data/
root@172.16.95.163's password:
sending incremental file list
pg_stat_tmp/
pg_stat_tmp/db_0.stat
pg_stat_tmp/db_14187.stat
pg_stat_tmp/global.stat

postgres=# select pg_stop_backup();
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
 pg_stop_backup
----------------
 0/2000138
(1 row)

psql
psql (12.16)
Type "help" for help.

postgres=# select pg_stop_backup();
NOTICE:  base backup done, waiting for required WAL segments to be archived

After This activity we have to comment this command 

 

 

[root@standby ~]# vim /var/lib/pgsql/12/data/postgresql.conf  

restore_command = 'cp /var/lib/pgsql/12/archive%f %p' 

 

postgres@standby$ cd /var/lib/pgsql/12/data/
postgres@standby$ touch standby.signal

Objective: Setup log based shipping in Linux.