PostgreSQL introduced Streaming Replication after 9.0. Stream replication provides the ability to continuously send AND apply WAL records to slave servers to keep them up to date. With stream replication, the slave server synchronizes data from the master server and acts as a backup to the master server.

This article documents the deployment of PostgreSQL master/slave stream replication.

Server Planning

role address version
The primary server 172.31.5.1 Ubuntu 18.04, PostgreSQL 10
From the server 172.31.5.2 Ubuntu 18.04, PostgreSQL 10

PostgreSQL installed

# Add PostgresSQL Repository to Ubuntu
sudo sh -c "echo 'deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -c -s`-pgdg main' >> /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update and Install PostgreSQL
sudo apt-get update
sudo apt-get install -y postgresql-10
Copy the code

Master server configuration

Accessing the database

sudo -u postgres psql
Copy the code

Create a Replicator user for login and replication

create role replicator login replication encrypted password 'password';
Copy the code

Configuration pg_hba. Conf

sudo vim /etc/postgresql/10/main/pg_hba.conf
Copy the code

Add the following

Host all all 172.31.0.0/16 trust# allow connectionHost Replication Replicator 172.31.0.0/16 trustAllow replicator users to replicate local data
Copy the code

Configuration postgrsql. Conf

sudo vim /etc/postgresql/10/main/postgresql.conf
Copy the code

Configure the parameters as follows

listen_addresses = The '*'   Listen on all IP addresses
archive_mode = on   # Enable archive mode
archive_command = 'cp %p /var/lib/postgresql/10/main/%f'   # archive command
wal_level = replica    Decides how much information to write to WAL
max_wal_senders = 10   # Maximum flow replication connection, generally equal to slave service
wal_sender_timeout = 60s    # stream replication timeout
max_connections = 100   The maximum number of connections must not be greater than the configuration of the slave library
Copy the code

Restarting the Database

sudo service postgresql restart
Copy the code

Slave server configuration

Let’s test if we can connect to the primary server

PSQL -h 172.31.5.1 -u postgresCopy the code

Configuring postgresql. Conf

wal_level = replica    Decides how much information to write to WAL
max_connections = 300   The maximum number of connections must not be smaller than the main library configuration
hot_standby = on This machine is not only used for data archiving, but also for data query
max_standby_streaming_delay = 30s # Maximum latency for stream backup
wal_receiver_status_interval = 10s  The interval between reporting the local state to the master server
hot_standby_feedback = on  Whether to report faulty data replication to the master server
Copy the code

First empty the PostgreSQL data

sudo su - postgres  # Switch to a Postgresl user
rm -rf 10/main/*  Empty the data directory
Copy the code

Then back up the primary server data

Pg_basebackup -d 10/main/ -h 172.31.5.1 -u replicator -x stream -pCopy the code

Configuration recovery. Conf

vim 10/main/recovery.conf
Copy the code
standby_mode = on    # indicates that the node is from the server
primary_conninfo = 'the host = 172.31.5.1 port = 5432 user = replicator password = password'  The connection information of the primary server
recovery_target_timeline = 'latest'
Copy the code

Exit the user and restart the database

sudo service postgresql restart
Copy the code

Verify the deployment

Execute on the master service with the following result:

postgres=# select client_addr,sync_state from pg_stat_replication; Client_addr | sync_state -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- 172.31.5.2 | async (1 row)Copy the code

We can on both servers perform ps aux | grep postgres to verify the deployment is successful, you can see

There is a WAL Sender Process on the primary server

There is a WAL Receiver process on the slave server

test

Primary server creates database:

postgres=# create database test;
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
 test      | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(4 rows)
Copy the code

At this point it can be seen synchronously from the server

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
 test      | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(4 rows)
Copy the code

If we execute from the server:

postgres=# drop database test;
ERROR:  cannot execute DROP DATABASE in a read-only transaction
Copy the code

You can see that you cannot write on the slave server because the slave server is read-only.

This completes the deployment and validation tests for master/slave stream replication.

If you are interested in Python development and full stack engineering, please follow the wechat official account. There is more than Python here