postgreSQL High Availability
Examples
Replication in PostgreSQL
Configuring the Primary Server
○Requirements:
○ Replication User for replication activities
○ Directory to store the WAL archives
○Create Replication user
createuser -U postgres replication -P -c 5 --replication
+ option -P will prompt you for new password
+ option -c is for maximum connections. 5 connections are enough for replication
+ -replication will grant replication privileges to the user○Create a archive directory in data directory
mkdir $PGDATA/archive
○Edit the pg_hba.conf file
This is host base authentication file, contains the setting for client autherntication. Add below entry:
#hosttype database_name user_name hostname/IP method
host replication replication <slave-IP>/32 md5○Edit the postgresql.conf file
This is the configuration file of PostgreSQL.
wal_level = hot_standby
This parameter decides the behavior of slave server.
`hot_standby` logs what is required to accept read only queries on slave server.
`streaming` logs what is required to just apply the WAL's on slave.
`archive` which logs what is required for archiving.archive_mode=on
This parameters allows to send WAL segments to archive location using archive_command parameter.
archive_command = 'test ! -f /path/to/archivedir/%f && cp %p /path/to/archivedir/%f'
Basically what above archive_command does is it copies the WAL segments to archive directory.
wal_senders = 5 This is maximum number of WAL sender processes.
Now restart the primary server.