knowledge is power

0%

How to setup Postgres 13 WAL streaming replication on Ubuntu 18.04

Posted on In , , ,

Featured image

1. Overview

PostgreSQL supports different type of replications, i.e. logical and physical. 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:

1
2
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.

1
2
3
4
5
$ 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,

1
2
3
$ 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,

1
2
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,

1
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.

1
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.

1
2
3
listen_addresses = '*'

synchronous_standby_names = '*'

After all the changes changes, we can start the Primary server,

1
pg_ctl -D $PGDATA -l /tmp/logfile start

4. Setup Standby Server

Once the Primary server is up, we need to create a backup base. This can be done on either Primary side or Standby side. Below is the command we can run on Standby server directly.

1
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,

1
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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.

1
2
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.

1
2
3
4
5
6
7
8
9
10
11
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.

1
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.

1
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.

1
2
3
4
5
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,

1
2
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,

1
2
3
4
5
6
7
8
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,

1
2
3
4
5
6
7
8
9
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 walk-through 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:

1. Database World