1. Overview
PostgreSQL supports different type of replications, i.e. logical and physical, and there are many tutorials discussed about the replications. This blog is a simply walk-through of the WAL Streaming replication using the latest Postgresql-13 on Ubuntu 18.04.
2. Install Postgresql-13 from source code
In this blog, we will install Postgresql from the source code, so that this environment can be used for later development. Simply prepare two Ubuntu 18.04 using VirtualBox with below network settings.
Primary: 192.168.0.181
Standby: 192.168.0.182
It is pretty simple to install postgres from source code. Here we checkout the Postgresql-13 stable branch from github.
$ mkdir sandbox
$ cd sandbox/
$ git clone https://github.com/postgres/postgres
$ cd postgres/
$ git checkout REL_13_STABLE
Install the basic build environment on Ubuntu 18.04 and then compile and install Postgres by using below commands,
$ sudo apt install -y pkg-config build-essential libreadline-dev bison flex
$ ./configure --prefix=$HOME/sandbox/postgres --enable-debug CFLAGS='-ggdb -O0'
$ make clean && make && make install
Setup the environment for Postgres database, for example,
export PGDATA=$HOME/sandbox/pgdata
export PATH=$HOME/sandbox/pgapp/bin:$PATH
3. Setup Primary Server
In order to setup the WAL streaming replication, first, let’s create a new database by running the command below,
initdb -D $PGDATA
then we need to add the permission to pg_hba.conf
file to enable a Standby server to access a Primary server.
host replication all 192.168.0.182/32 trust
and then update postgresql.conf
file to let the Primary server listen on all network interface so that a standby can connect to it.
listen_addresses = '*'
synchronous_standby_names = '*'
After all the changes changes, we can start the Primary server,
pg_ctl -D $PGDATA -l /tmp/logfile start
4. Setup Standby Server
Once the Primary server is up, we need to create a base backup. This can be done on either Primary side or Standby side. Below is the command we can run on Standby server directly.
pg_basebackup --pgdata=$PGDATA --format=p --write-recovery-conf --checkpoint=fast --label=mffb --progress --host=192.168.0.181 --port=5432 --username=vbox1804
At this point, most of the tutorial will discuss about the recovery.conf
setup. However, PG has removed recovery.conf
file and merged the corresponding configuration to postgresql.conf
. Without knowing this, if you simply follow some old tutorials, you may get an error like below,
FATAL: using recovery command file "recovery.conf" is not supported
The reason is that the recovery.conf
has been removed and the relevant configuration has been merged into postgresql.conf
. For details, please check the commit below.
commit 2dedf4d9a899b36d1a8ed29be5efbd1b31a8fe85
Author: Peter Eisentraut <peter_e@gmx.net>
Date: Sun Nov 25 16:31:16 2018 +0100
Integrate recovery.conf into postgresql.conf
recovery.conf settings are now set in postgresql.conf (or other GUC
sources). Currently, all the affected settings are PGC_POSTMASTER;
this could be refined in the future case by case.
Recovery is now initiated by a file recovery.signal. Standby mode is
initiated by a file standby.signal. The standby_mode setting is
gone. If a recovery.conf file is found, an error is issued.
The trigger_file setting has been renamed to promote_trigger_file as
part of the move.
The documentation chapter "Recovery Configuration" has been integrated
into "Server Configuration".
pg_basebackup -R now appends settings to postgresql.auto.conf and
creates a standby.signal file.
Author: Fujii Masao <masao.fujii@gmail.com>
Author: Simon Riggs <simon@2ndquadrant.com>
Author: Abhijit Menon-Sen <ams@2ndquadrant.com>
Author: Sergei Kornilov <sk@zsrv.org>
Discussion: https://www.postgresql.org/message-id/flat/607741529606767@web3g.yandex.ru/
Now, let’s just make a simple change for some basic parameters to Standby Server postgresql.conf
file.
primary_conninfo = 'host=192.168.0.181 port=5432 user=xbox1804 password=mypassword' # connection string to sending server
primary_slot_name = 'standby1_slot' # replication slot on sending server
5. Create replication slot on Primary Server
In order to allow the Standby to connect to Primary, we need to create the corresponding replication slot like below.
postgres=# select * from pg_create_physical_replication_slot('standby1_slot');
slot_name | lsn
---------------+-----
standby1_slot |
(1 row)
postgres=# select slot_name, slot_type, active, wal_status from pg_replication_slots;
slot_name | slot_type | active | wal_status
---------------+-----------+--------+------------
standby1_slot | physical | f |
(1 row)
6. Start Standby Server
Once the replication slot has been created i.e. ‘standby1_slot’, let’s start the Standby server.
pg_ctl -D $PGDATA -l /tmp/logfile start
If the Standby server is setup properly, then you should see below message from the log file.
LOG: started streaming WAL from primary at 0/3000000 on timeline 1
Now, if you check the replication slot on Primary server again, you should see the replication slot is active
and the wal_status
has been changed to reserved
.
postgres=# select slot_name, slot_type, active, wal_status from pg_replication_slots;
slot_name | slot_type | active | wal_status
---------------+-----------+--------+------------
standby1_slot | physical | t | reserved
(1 row)
7. Test WAL Streaming replication
After all the settings has been done properly, we can start a simple test to verify the wal streaming replication setup.
First, let’s check if there is any relations on Primary server, for example,
postgres=# \d
Did not find any relations.
You can do the same check on Standby server side. After that, let’s create a table on Primary server,
postgres=# create table tbl1(id int, text varchar(10));
CREATE TABLE
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tbl1 | table | vbox1804
(1 row)
Then simply insert one record like below,
postgres=# insert into tbl1 values (1, 'helloworld');
INSERT 0 1
postgres=# select * from tbl1;
id | text
----+------------
1 | helloworld
(1 row)
postgres=#
Now, if you check on the Standby server, you should be able to see the table and a record have been replicated.
postgres=# \d
Did not find any relations.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | tbl1 | table | vbox1804
(1 row)
postgres=# select * from tbl1;
id | text
----+------------
1 | helloworld
(1 row)
postgres=#
8. Summary
This blog simply demonstrates a very basic WAL streaming replication, if you want to know more about the replication, you can always check it out on Postgresql official website.
Reference:
A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.
Recent Comments