Sep 24 2017

PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations

Category: AdministrationFatih Acar @ 09:33

PostgreSQL has few feature for standby technology. I generally use streaming replication. This is very easy and confident. Streaming replication use a replication user for replication operations.Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the primary, which streams WAL records to the standby as they’re generated, without waiting for the WAL file to be filled.

Streaming replication is asynchronous by default, in which case there is a small delay between committing a transaction in the primary and the changes becoming visible in the standby.

If you use streaming replication without file-based continuous archiving, the server might recycle old WAL segments before the standby has received them. If this occurs, the standby will need to be reinitialized from a new base backup. You can avoid this by setting wal_keep_segments to a value large enough to ensure that WAL segments are not recycled too early, or by configuring a replication slot for the standby. If you set up a WAL archive that’s accessible from the standby, these solutions are not required, since the standby can always use the archive to catch up provided it retains enough segments.

Test Systems Features

PG Version : 9.5
Primary DB IP : 192.168.10.11
Pimary DB Hostname : pg01
Hot Standby DB IP : 192.168.10.12
Host Standby DB Hostname : pg02
Virtual Service IP : 192.168.10.10
OS : Centos 7 x64
Data directory : /pgdata/data
Archive direcytory : /pgdata/ARCHIVELOG

streaming_replication

1- Keygen Generate And Copy

[postgres@pg01 ~] ssh-keygen -t rsa
[postgres@pg02 ~] ssh-keygen -t rsa
[postgres@pg01 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg02
[postgres@pg02 ~] ssh-copy-id -i .ssh/id_rsa.pub postgres@pg01

2- Create Streaming Replication User on Primary DB

psql# createuser -U postgres -p 5432 repuser -P -c 10 –replication

3- Primary DB pg_hba.conf configuration

[root@pg01 ~] vi /pgdata/data/pg_hba.conf

Add below line

host replication repuser 192.168.10.12/32 md5

[root@pg01 ~] systemctl reload postgresql.service

4- Create Archive Directory on Hot Standby Server
Continue reading “PostgreSQL Hot Standby (Streaming Replication) Database Installation and Failover Operations”

18,338 total views, 6 views today

Tags: Database Administration, PostgreSQL Administration, PostgreSQL High Availability, PostgreSQL Hot Standby