PostgreSQL DB log based shipping replication

 PostgreSQL DB log based shipping replication

Initial setup on Master server:

1) Create ssh key on master and copy it to standby

Syntax : ssh-keygen

           Copy the ssh key to standby server

             Syntax : ssh-copy-id  postgres@euls31064

cat /home/postpoc/.ssh/id_rsa.pub

 

ssh-keygen

ssh-copy-id or 

standby --> /home/postgres/.ssh/authorized_keys

copy public key value - id_rsa.pub

 

 

2) Test copying a file from master to standby without entering password

scp /tmp/test postgres@hostname:/tmp/

3) Shutdown Master database 

 pg_ctl -D /postgres_dbpoc1/databases stop

4) Modify the content of postgres.conf file and edit parameters archive_on, archive_command and archive_timeout.

archive_mode = on 

archive_command = 'rsync -a %p postpoc@euls31064:/postgres_dbpoc1/arch' 

archive_timeout=60

Initial Setup Standby Database:

1) Create a archive directory on standby which is accessible by Master user.

/postgres_dbpoc1/arch

2) Shutdown postgresql on standby

pg_ctl -D /postgres_dbpoc1/databases stop

3) Delete all the contents of /Data directory

rm -rf /postgres_dbpoc1/databases

Steps:

1) Startup postgresql on Master database

pg_ctl -D /postgres_dbpoc1/databases start

2) Connect to the database using psql shell

3) Issue connect pg_start_backup

Syntax : select pg_start_backup('dbrep');

4) Copy Data directory for master to standby.

Syntax : rsync -avz /postgres_dbpoc1/databases/* postpoc@euls31064:/postgres_dbpoc1/databases

5) End backup on master.

Syntax : select pg_stop_backup();

6) Comment out archive_on, archive_command and archive_timeout parameters in postgresql.conf in standby server.

#archive_mode = on 

archive_command = 'rsync -a %p postpoc@euls31064:/postgres_dbpoc1/arch' 

#archive_timeout=60

7) Setup recovery command in postgresql.conf

restore_command = 'cp /postgres_dbpoc1/arch/%f %p'

8) Create a standby.signal file in data directory.

touch standby.signal

9) Start postgres on standby server

pg_ctl -D /postgres_dbpoc1/databases start

10) Check the log files whether the postgres has entered standby mode and accepting read only connections.

      Test Standby Database:

1) Try to create table and insert few records on master and check whether they are populated

On the standby . 

create table asp(no int);

insert into asp values (1);

insert into asp values (2);

insert into asp values (3);

insert into asp values (4);

insert into asp values (5);

insert into asp values (6);

select * from asp;

2 )  try to create a table on standby or delete any records on standby

create table asp(no int);

insert into asp values (1);

select * from asp;

Failover:

1) Shutdown postgresql on master and check whether it affects standby

 pg_ctl -D /postgres_dbpoc1/databases stop

2) Promote your standby database to primary. Use any of the 3 methods

pg_ctl promote -D /postgres_dbpoc1/databases

3) Check signal.standy exist or not.

psql -h /tmp -U postpoc -p 5434 -d postgres