PostgreSQL 10 and Logical replication - Setup
This article is the result of a series of articles on logical replication in PostgreSQL 10
This one will focus on the implementation of logical replication.
- PostgreSQL 10 : Logical replication - Overview
- PostgreSQL 10 : Logical replication - Setup
- PostgreSQL 10 : Logical replication - Limitations
Table of Contents
Installation
When writing this article Postgres 10 is not released yet. However, the community provides packages of beta versions. Of course, do not use in production.
Repository installation (PostgreSQL Developpement Group)
From http://www.postgresql.org then “download” -> “debian”. On this page, the site tells you how to install the pgdg repository. However it will only offer stable versions. But, the site sends you to a wiki page:
For more information about the apt repository, including answers to frequent questions, please see the apt page on the wiki.
You will find :
For packages of development/alpha/beta versions of PostgreSQL, see the FAQ entry about beta versions.
Who tells you :
To use packages of postgresql-10, you need to add the 10 component to your
/etc/apt/sources.list.d/pgdg.listentry, so the 10 version of libpq5 will be available for installation
So for debian, commands are :
1wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
2sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main 10" > /etc/apt/sources.list.d/pgdg.list'
3sudo apt-get update
Packages installation
1sudo apt install postgresql-10
Create instances
A first instance is created during installation. We will install a second one. Thus one will be the publisher and the other will be subscriber. So for debian, commands are:
1# pg_lsclusters
2Ver Cluster Port Status Owner Data directory Log file
310 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
4
5# pg_createcluster 10 sub
6Creating new PostgreSQL cluster 10/sub ...
7/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/sub --auth-local peer --auth-host md5
8The files belonging to this database system will be owned by user "postgres".
9This user must also own the server process.
10
11The database cluster will be initialized with locale "en_US.UTF-8".
12The default database encoding has accordingly been set to "UTF8".
13The default text search configuration will be set to "english".
14
15Data page checksums are disabled.
16
17fixing permissions on existing directory /var/lib/postgresql/10/sub ... ok
18creating subdirectories ... ok
19selecting default max_connections ... 100
20selecting default shared_buffers ... 128MB
21selecting dynamic shared memory implementation ... posix
22creating configuration files ... ok
23running bootstrap script ... ok
24performing post-bootstrap initialization ... ok
25syncing data to disk ... ok
26
27Success. You can now start the database server using:
28
29/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/sub -l logfile start
30
31Ver Cluster Port Status Owner Data directory Log file
3210 sub 5433 down postgres /var/lib/postgresql/10/sub /var/log/postgresql/postgresql-10-sub.log
33
34# pg_ctlcluster 10 sub start
35
36# pg_lsclusters
37Ver Cluster Port Status Owner Data directory Log file
3810 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
3910 sub 5433 online postgres /var/lib/postgresql/10/sub /var/log/postgresql/postgresql-10-sub.log
Logical replication setup
Prerequisites
There are very few changes to the default configuration. Most parameters are already set to set up logical replication. However, there is one parameter to modify on the instance that “publishes”: wal_level. Set to “replica”, it must be “logical”.
In /etc/postgresql/10/main/postgresql.conf
1wal_level = logical
To apply changes we must restart the cluster:
1pg_ctlcluster 10 main restart
Publication
Let’s create a database b1 that contains a table t1.
1postgres=# create database b1;
2CREATE DATABASE
3postgres=# \c b1
4You are now connected to database "b1" as user "postgres".
5
6b1=# create table t1 (c1 text);
7CREATE TABLE
8b1=# insert into t1 values ('un');
9INSERT 0 1
10b1=# select * from t1;
11 c1
12----
13 un
14(1 row)
Then, we use this order to create publication:
1b1=# CREATE PUBLICATION pub1 FOR TABLE t1 ;
And that’s all! Note that it is possible to use the keyword FOR ALL TABLES to add all present and future tables to the publication.
We can verify that the publication was created with the following psql meta-command:
1b1=# \dRp+
2 Publication pub1
3 All tables | Inserts | Updates | Deletes
4------------+---------+---------+---------
5 f | t | t | t
6Tables:
7 "public.t1"
Subscription
Logical replication does not replicate DDL orders, so we must create the table on the “sub” instance. It is not necessary to have the same database name, so we will create a database b2.
1postgres@blog:~$ psql -p 5433
2psql (10beta2)
3Type "help" for help.
4
5postgres=# CREATE DATABASE b2;
6CREATE DATABASE
7postgres=# \c b2
8You are now connected to database "b2" as user "postgres".
9b2=# create table t1 (c1 text);
10CREATE TABLE
As for the publication, the subscription is created with an SQL order:
1b2=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=/var/run/postgresql port=5432 dbname=b1' PUBLICATION pub1;
2NOTICE: created replication slot "sub1" on publisher
3CREATE SUBSCRIPTION
4b2=# select * from t1;
5 c1
6----
7 un
8(1 row)
Most importing part is: CONNECTION 'host=/var/run/postgresql port=5432 dbname=b1'
Indeed, we indicate to the subscriber how to connect to the publication. In my example the two instances are on the same server and listen on a local socket. If you have instances on different server you will have to specify IP address. Then the port and do not forget the database where the publication was created.
As you can see, Postgres automatically synchronize data that was already present in table t1.
Now if I add rows in table t1, they will automatically be replicated to the instance “sub”:
1postgres@blog:~$ psql b1
2psql (10beta2)
3Type "help" for help.
4
5b1=# insert into t1 values ('deux');
6INSERT 0 1
7postgres=# \q
8postgres@blog:~$ psql -p 5433 b2
9psql (10beta2)
10Type "help" for help.
11b2=# select * from t1;
12 c1
13------
14 un
15 deux
16(2 rows)
As for the publication, there is a meta-command to display subscriptions:
1\dRs+
2 List of subscriptions
3 Name | Owner | Enabled | Publication | Synchronous commit | Conninfo
4------+----------+---------+-------------+--------------------+----------------------------------------------
5 sub1 | postgres | t | {pub1} | off | host=/var/run/postgresql port=5432 dbname=b1
6(1 row)
That’s all for this article, in a future article we will see limitations.
For seven years, I held various positions as a systems and network engineer. And it was in 2013 that I first started working with PostgreSQL. I held positions as a consultant, trainer, and also production DBA (Doctolib, Peopledoc).
This blog is a place where I share my findings, knowledge and talks.