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 <email@example.com> 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 <firstname.lastname@example.org> Author: Simon Riggs <email@example.com> Author: Abhijit Menon-Sen <firstname.lastname@example.org> Author: Sergei Kornilov <email@example.com> Discussion: https://firstname.lastname@example.org/
Now, let’s just make a simple change for some basic parameters to Standby Server
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
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=#
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.