Overview

In PostgreSQL the simplest way of replication is called File-Based or (Log shipping) replication. It is important to note that File based replication uses ARCHIVED XLOG (WAL = Write Ahead Log) files specified in the archive_command in the postgresql.conf file. Also File based replication means that the slave will pull logs from master and master will not give a damn about the slave. If the slave is running or now, if it has the archived XLOG or not, master wouldn't care at all. Because of that, the master can delete/recycle a XLOG (WAL) as soon as he wants and there lays the problem. If the master recycles a XLOG which is needed by the slave, we will have interrupted recovery, therefore our slave will become kaput :). In order to avoid this. we can configure slots for streaming replication.

Creating hot standby / replication in PostgreSQL has very similar process to creating replication in MySQL.

As with MySQL, in PostegreSQL, your backup can be either:

1. Export of the database
2. Warm backup and by extension: performing snapshot, scp, tar and others.

We will be using Warm backup in our example:

-bash-4.1$ psql -c "select pg_start_backup('initial_backup');"
 pg_start_backup
-----------------
 0/14000028
(1 row)

-bash-4.1$ rsync -cva --inplace --exclude=*pg_xlog* /var/lib/pgsql/9.6/data/ 192.168.0.11:/var/lib/pgsql/9.6/data/
[email protected]'s password:
sending incremental file list
./
backup_label
postgresql.conf
postmaster.pid
base/13275/
base/13275/16384
base/13275/pg_internal.init
global/
global/pg_control
global/pg_internal.init
pg_log/postgresql-Thu.log
pg_notify/
pg_stat/
pg_stat_tmp/
pg_stat_tmp/db_0.stat
pg_stat_tmp/db_13275.stat
pg_stat_tmp/global.stat

sent 152804 bytes  received 2005 bytes  20641.20 bytes/sec
total size is 22936284  speedup is 148.16

-bash-4.1$ psql -c "select pg_stop_backup();"
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/14000130
(1 row)

-bash-4.1$

Or you can it automatically with pg_basebackup:

-bash-4.2$:pg_basebackup -U postgres -D /walshared/backup

As it has been shown already in the Backup section.

Before we setup, replication we restore the backup taken from the previous step. The restore is simple copy paste (and possible decompress, if needed) from the based backup:

-bash-4.2$ cd /walshared/backup
-bash-4.2$ cp -R * /var/lib/pgsql/10/data

From this point on, we have three options we can go with: In order to configure the salve to receive and apply the logs, we can configure either:

• File based replication
• Stream based replication
• Both

In file based replication, we don't need a user. All we need is a shared storage between the master and the slave where the WAL files are put in and taken from. That kind of replication allows PITR because the slave is ALWAYS behind, however that is also the problem: To configure file based replicaiton, you can use the following recovery.conf file:

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

</sxh>

In stream replication, we need a user on the master which will be used by the slave to access the WAL chunks, by default the streaming is asynchronous but can be configured to be synchronous.

If you want to do it correctly, we have to configure it to use both:

So let's configure both for higher security. As already mentioned, replication on PostgreSQL is job of the slave, not the master. I repeat, master doesn't care at all if the slave is running, available or existing at all. The only job which the master has is to provide archive of the XLOG. In order to configure the slave, we have to create the recovery.conf as follows:

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.10 port=5432 user=rep password=yourpassword' <- This lines adds the streaming
restore_command = 'cp /var/lib/pgsql/9.6/archive/%f %p' - This lines enables the file based replication
trigger_file = '/tmp/postgresql.trigger.5432' - If this file is created, the slave will be promoted to master
primary_slot_name='repl_slot' - usage of a physical slot, you can check it in the section: Slots

In ALL cases the postgresql.conf file should be modified as follows:

hot_standby='on' - Should be not commented 

To start the slave just restore a base or operation backup and start the process:

[root@postegresqlslave pg_log]# service postgresql-9.6 start
Starting postgresql-9.6 service:                           [  OK  ]
[root@postegresqlslave pg_log]#

And check the log file:

[root@postegresqlslave pg_log]# pwd
/var/lib/pgsql/9.6/data/pg_log
[root@postegresqlslave pg_log]# ls -alrt
total 124
-rw-------.  1 postgres postgres  4184 Feb 21 16:47 postgresql-Wed.log
-rw-------.  1 postgres postgres 30660 Feb 22 21:31 postgresql-Thu.log
drwx------.  2 postgres postgres  4096 Feb 24 16:28 .
drwx------. 20 postgres postgres  4096 Feb 24 16:49 ..
-rw-------.  1 postgres postgres 66015 Feb 24 16:49 postgresql-Sat.log
[root@postegresqlslave pg_log]# tail -100f postgresql-Sat.log
*************************************************************************************
< 2018-02-24 16:49:42.681 CET > LOG:  redo starts at 0/16000098
< 2018-02-24 16:49:42.681 CET > LOG:  consistent recovery state reached at 0/160000D0
< 2018-02-24 16:49:42.681 CET > LOG:  record with incorrect prev-link 0/14000060 at 0/160000D0
< 2018-02-24 16:49:42.682 CET > LOG:  database system is ready to accept read only connections
< 2018-02-24 16:49:42.685 CET > LOG:  started streaming WAL from primary at 0/16000000 on timeline 1