## How To Configure PostgreSQL 12 Streaming Replication in Ubuntu 18.04 * Build two boxes and install ubuntu18.04 - box1: master -> Ubuntu 18.04 with PG 12 - static ip: 192.168.33.33 - hostname: master - box2: slave -> Ubuntu 18.04 with PG 12 - static ip: 192.168.33.44 - hostname: slave * How to setup the boxes with Ubuntu 18.04 and PG12 - generated `Vagrantfile` file in current folder - vagrant init ubuntu/bionic64 - create shell script `bootstrap.sh` in current folder ``` #!/usr/bin/env bash ulimit -n 1024 # Repair "==> default: stdin: is not a tty" message sudo sed -i '/tty/!s/mesg n/tty -s \\&\\& mesg n/' /root/.profile # Install the postgres key echo "Importing PostgreSQL key and installing software" wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo bash -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee /etc/apt/sources.list.d/pgdg.list' sudo apt-get update 2>/dev/null | grep packages | cut -d '.' -f 1 sudo apt-get -y install postgresql-12 postgresql-client-12 > /dev/null 2>&1 ``` - make it a shell script - chmod a+x bootstrap.sh - update config in `Vagrantfile` of master - config.vm.network "private_network", ip: "192.168.33.33" - config.vm.hostname = 'master' - config.vm.provision :shell, path: "bootstrap.sh" - update config in `Vagrantfile` of slave - config.vm.network "private_network", ip: "192.168.33.44" - config.vm.hostname = 'slave' - config.vm.provision :shell, path: "bootstrap.sh" - vagrant up - vagrant ssh ### Configuring the PostgreSQL Master Database Server * 1.1. On the master server, switch to the postgres system account and configure the IP address(es) on which the master server will listen to for connections from clients. ``` $ vagrant@master:~$ sudo su $ root@master:/home/vagrant# su - postgres $ postgres@master:~$ psql -c "ALTER SYSTEM SET listen_addresses TO '*';" ALTER SYSTEM # postgres@master:$ cat /var/lib/postgresql/12/main/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. listen_addresses = '*' ``` The ALTER SYSTEM SET SQL command is a powerful feature to change a server’s configuration parameters, directly with a SQL query. The configurations are saved in the postgresql.auto.conf file located at the root of data folder (e.g /var/lib/postgresql/12/main) and read addition to those stored in postgresql.conf. But configurations in the former take precedence over those in the later and other related files. * 1.2. Then create a replication role that will be used for connections from the standby server to the master server, using the createuser program. In the following command, the -P flag prompts for a password for the new role and -e echoes the commands that createuser generates and sends to the database server. ``` # root@master:~$ su – postgres $ postgres@master:~$ createuser --replication -P -e replicator Enter password for new role: Enter it again: SELECT pg_catalog.set_config('search_path', '', false); CREATE ROLE replicator PASSWORD 'md5f259c3f62ade99c48b5929b2a0ad6d3c' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN REPLICATION; $ exit ``` * 1.3. Then enter the following entry at the end of the /etc/postgresql/12/main/pg_hba.conf client authentication configuration file with the database field set to replication as shown in the screenshot. ``` host replication replicator 192.168.33.44/32 md5 ``` * 1.4. Now restart the Postgres12 service using the following systemctl command to apply the changes. ``` $ systemctl restart postgresql@12-main.service ``` ### Configuring the PostgreSQL Standby Server * 2.1. Next, you need to make a base backup of the master server from the standby server; this helps to bootstrap the standby server. You need to stop the postgresql 12 service on the standby server, switch to the postgres user account, backup the data directory (/var/lib/pgsql/12/data/), then delete everything under it as shown, before taking the base backup. ``` # systemctl stop postgresql@12-main.service # rm -rf /var/lib/postgresql/12/main/* ``` * 2.2. Then use the pg_basebackup tool to take the base backup with the right ownership (the database system user i.e Postgres, within the Postgres user account) and with the right permissions. ``` $ root@slave:~$ su – postgres $ postgres@slave:/home/vagrant# pg_basebackup -h 192.168.33.33 -D /var/lib/postgresql/12/main/ -U replicator -P -v -R -X stream -C -S pgstandby1 Password: pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created replication slot "pgstandby1" 24506/24506 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/2000100 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: base backup completed ``` if got the error `could not send replication command "CREATE_REPLICATION_SLOT "pgstandby1" PHYSICAL RESERVE_WAL": ERROR: replication slot "pgstandby1" already exists`, need to brack to master server to delete it `pgstandby1` ``` $ postgres=# SELECT pg_drop_replication_slot('pgstandby1'); pg_drop_replication_slot -------------------------- (1 row) ``` In the following command, the option: -h – specifies the host which is the master server. -D – specifies the data directory. -U – specifies the connection user. -P – enables progress reporting. -v – enables verbose mode. -R – enables the creation of recovery configuration: Creates a standby.signal file and append connection settings to postgresql.auto.conf under the data directory. -X – used to include the required write-ahead log files (WAL files) in the backup. A value of stream means to stream the WAL while the backup is created. -C – enables the creation of a replication slot named by the -S option before starting the backup. -S – specifies the replication slot name. * 2.3. When the backup process is done, the new data directory on the standby server should look like that in the screenshot. A standby.signal is created and the connection settings are appended to postgresql.auto.conf. You can list its contents using the ls command. ``` # root@slave:~# ls -l /var/lib/postgresql/12/main total 88 -rw------- 1 postgres postgres 3 May 31 01:46 PG_VERSION -rw------- 1 postgres postgres 224 May 31 01:46 backup_label.old drwx------ 6 postgres postgres 4096 May 31 01:51 base drwx------ 2 postgres postgres 4096 May 31 01:50 global drwx------ 2 postgres postgres 4096 May 31 01:46 pg_commit_ts drwx------ 2 postgres postgres 4096 May 31 01:46 pg_dynshmem drwx------ 4 postgres postgres 4096 May 31 01:59 pg_logical drwx------ 4 postgres postgres 4096 May 31 01:46 pg_multixact drwx------ 2 postgres postgres 4096 May 31 01:49 pg_notify drwx------ 2 postgres postgres 4096 May 31 01:46 pg_replslot drwx------ 2 postgres postgres 4096 May 31 01:46 pg_serial drwx------ 2 postgres postgres 4096 May 31 01:46 pg_snapshots drwx------ 2 postgres postgres 4096 May 31 01:46 pg_stat drwx------ 2 postgres postgres 4096 May 31 01:46 pg_stat_tmp drwx------ 2 postgres postgres 4096 May 31 01:54 pg_subtrans drwx------ 2 postgres postgres 4096 May 31 01:46 pg_tblspc drwx------ 2 postgres postgres 4096 May 31 01:46 pg_twophase drwx------ 3 postgres postgres 4096 May 31 01:54 pg_wal drwx------ 2 postgres postgres 4096 May 31 01:46 pg_xact -rw------- 1 postgres postgres 318 May 31 01:53 postgresql.auto.conf -rw------- 1 postgres postgres 130 May 31 01:49 postmaster.opts -rw------- 1 postgres postgres 100 May 31 01:49 postmaster.pid -rw------- 1 postgres postgres 0 May 31 01:46 standby.signal ``` A replication slave will run in “Hot Standby” mode if the hot_standby parameter is set to on (the default value) in postgresql.conf and there is a standby.signal file present in the data directory. * 2.4. Now back on the master server, you should be able to see the replication slot called pgstandby1 when you open the pg_replication_slots view as follows. ``` $ root@master:/home/vagrant# su - postgres $ postgres@master:~$ psql -c "SELECT * FROM pg_replication_slots;" slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn ------------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+--------------------- pgstandby1 | | physical | | | f | f | | | | 0/2000000 | (1 row) ``` * 2.5. To view the connection settings appended in the postgresql.auto.conf file on Slave ``` $ root@slave:~# cat /var/lib/postgresql/12/main/postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. listen_addresses = '*' primary_conninfo = 'user=replicator password=123 host=192.168.33.33 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' primary_slot_name = 'pgstandby1' $ exit ``` * 2.6. Now commence normal database operations on the standby server by starting the PostgreSQL service as follows. ``` # systemctl start postgresql@12-main.service ``` ### Testing PostgreSQL Streaming Replication * 3.1. Once a connection is established successfully between the master and the standby, you will see a WAL receiver process in the standby server with a status of streaming, you can check this using the pg_stat_wal_receiver view. ``` # root@slave:~# su - postgres # postgres@slave:~$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;" Expanded display is on. -[ RECORD 1 ]---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pid | 5914 status | streaming receive_start_lsn | 0/3000000 receive_start_tli | 1 received_lsn | 0/3000A00 received_tli | 1 last_msg_send_time | 2020-05-31 02:43:08.441605+00 last_msg_receipt_time | 2020-05-31 02:43:08.472888+00 latest_end_lsn | 0/3000A00 latest_end_time | 2020-05-31 01:56:33.422365+00 slot_name | pgstandby1 sender_host | 192.168.33.33 sender_port | 5432 conninfo | user=replicator password=******** dbname=replication host=192.168.33.33 port=5432 fallback_application_name=12/main sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any ``` and a corresponding WAL sender process in the master/primary server with a state of streaming and a sync_state of async, you can check this pg_stat_replication pg_stat_replication view. ``` # postgres@master:~$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;" Expanded display is on. -[ RECORD 1 ]----+------------------------------ pid | 6139 usesysid | 16384 usename | replicator application_name | 12/main client_addr | 192.168.33.44 client_hostname | client_port | 54930 backend_start | 2020-05-31 01:49:30.689483+00 backend_xmin | state | streaming sent_lsn | 0/3000A00 write_lsn | 0/3000A00 flush_lsn | 0/3000A00 replay_lsn | 0/3000A00 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2020-05-31 02:44:18.59281+00 ``` From the screenshot above, the streaming replication is asynchronous. In the next section, we will demonstrate how to optionally enable synchronous replication. * 3.2. Now test if the replication is working fine by creating a test database in the master server and check if it exists in the standby server. ``` # postgres@master:~$ psql psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1)) Type "help" for help. # postgres=# create database testdb; CREATE DATABASE postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+---------+----------------------- postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | C.UTF-8 | C.UTF-8 | (4 rows) ``` ### Enabling Synchronous Replication on Master * Synchronous replication offers the ability to commit a transaction (or write data) to the primary database and the standby/replica simultaneously. It only confirms that a transaction is successful when all changes made by the transaction have been transferred to one or more synchronous standby servers. * To enable synchronous replication, the synchronous_commit must also be set to on (which is the default value, thus no need for any change) and you also need to set the synchronous_standby_names parameter to a non-empty value. For this guide, we will set it to all. ``` # postgres@master:~$ psql -c "ALTER SYSTEM SET synchronous_standby_names TO '*';" ALTER SYSTEM # postgres@master:/var/lib/postgresql/12/main# cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. listen_addresses = '*' synchronous_standby_names = '*' ``` * Then reload the PostgreSQL 12 service to apply the new changes. ``` # systemctl reload postgresql@12-main.service ``` * Now when you query the WAL sender process on the primary server once more, it should show a state of streaming and a sync_state of sync. ``` # postgres@master:~$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;" Expanded display is on. -[ RECORD 1 ]----+------------------------------ pid | 6139 usesysid | 16384 usename | replicator application_name | 12/main client_addr | 192.168.33.44 client_hostname | client_port | 54930 backend_start | 2020-05-31 01:49:30.689483+00 backend_xmin | state | streaming sent_lsn | 0/30019D8 write_lsn | 0/30019D8 flush_lsn | 0/30019D8 replay_lsn | 0/30019D8 write_lag | flush_lag | replay_lag | sync_priority | 1 sync_state | sync reply_time | 2020-05-31 02:48:43.264218+00 ``` We have come to the end of this guide. We have shown how to set up PostgreSQL 12 master-standby database streaming replication in Ubuntu 18.04. We also covered how to enable synchronous replication in a PostgreSQL database cluster. There are many uses of replication and you can always pick a solution that meets your IT environment and/or application-specific requirements. For more detail, go to [Log-Shipping Standby Servers](https://www.postgresql.org/docs/12/warm-standby.html) in the PostgreSQL 12 documentation. * References: https://www.postgresql.org/docs/12/warm-standby.html