Setup replication

• Establish passwordless connectivity

ssh-keygen -t rsa
ssh postgres@192.168.1.9 mkdir -p .ssh
cat .ssh/id_rsa.pub | ssh postgres@192.168.1.9 'cat >> .ssh/authorized_keys'
ssh postgres@192.168.1.9 "chmod 700 .ssh; chmod 640 .ssh/authorized_keys"
ssh postgres@192.168.1.9

On primary

• postgresql.conf

archive_mode = on
archive_command = 'scp %p 192.168.1.17:/u01/archives/%f'
archive_timeout = 5min
wal_keep_segments = 10

• Login and create replication user

postgres=# CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'replicauser@';
CREATE ROLE

• pg_hba.conf

host    replication     replica         0.0.0.0/0            trust
host    all             all             0.0.0.0/0            trust

• Make sure firewall disabled

[root@postgres03 ~]# service iptables stop
iptables: Flushing firewall rules:                       [  OK  ]
iptables: Setting chains to policy ACCEPT: filter        [  OK  ]
iptables: Unloading modules:                             [  OK  ]

On standby

• Take full backup

pg_basebackup  -D /u01/pgsql/data -F t -z -U replica -w -v -h 192.168.1.9

[postgres@postgres03 ~]$ pg_basebackup  -D /u01/pgsql/data -F t -z -U replica -w -v -h 192.168.1.9
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/50000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[postgres@postgres03 ~]$

• Create recovery.conf file

[postgres@postgres03 data]$ cat recovery.conf

standby_mode = 'on'
restore_command = 'cp /u01/archives/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

• Start your standby.

Hot standby

• Primary postgresql.conf

listen_addresses = '*'
**wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /u02/New_PostgreData/archivedlogs/%f'
archive_timeout = 5min
**max_wal_senders = 3
wal_keep_segments = 8
**hot_standby = on

• Standby recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=replica password=replicauser@ host=192.168.1.9 port=5432 sslmode=disable sslcompression=1'
restore_command = 'cp /u01/archives/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

Synchrounous replication

• Primary conf

synchronous_standby_names = ‘*’ # standby servers that provide sync rep

• Standby recovery.conf

primary_conninfo = 'user=replica password=replicauser@ host=192.168.1.9 port=5432 sslmode=disable sslcompression=1 application_name=slave1'

PostgreSQL Switchover

• current standy

touch /tmp/postgresql.trigger.5432

• old primary

Stop the cluster and rewind the cluster

pg_rewind –target-pgdata=/u01/pgsql/data –source-server=”port=5432   host=192.168.1.17″

[postgres@postgres02 data]$ pg_ctl stop -D /u01/pgsql/data

waiting for server to shut down…. done

server stopped

[postgres@postgres02 data]$ pg_rewind --target-pgdata=/u01/pgsql/data --source-server="port=5432   host=192.168.1.17"

servers diverged at WAL location 0/150000D0 on timeline 1

rewinding from last common checkpoint at 0/15000028 on timeline 1

Replication slots

select pg_create_physical_replication_slot(‘slot1’,true);

The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won’t be automatically removed during checkpoints.

postgres=# select pg_create_physical_replication_slot('slot2',true);
-[ RECORD 1 ]-----------------------+-------------------

pg_create_physical_replication_slot | (slot2,0/1602B1E8)

• standby recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=replica password=replicauser@ host=192.168.1.17 port=5432 sslmode=disable sslcompression=1'
restore_command = 'cp /u01/archives/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
primary_slot_name = 'slot2'