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:
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:
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