PostgreSQL is one of the most advanced open source databases globally with a lot of great features. One of them is Streaming Replication which was introduced in PostgreSQL 9.0—known as Physical Replication. It works by sending the WAL segments generated by the primary PostgreSQL to another standby server also running PostgreSQL. However, if we only want to replicate a portion of the database tables, then Physical Replication in this case is not ideal. To improve the replication feature, PostgreSQL 9.4 came up with a feature called Logical Replication, in which we can perform efficient synchronous streaming replication on data-object levels.
The logical replication model needs one publisher and one or more subscribers. Subscribers pull data from the publisher they subscribe to and subsequently re-publish data to allow cascading replication or more complex configurations. Every change should be replication in real-time, so these two databases remain synchronized.
Use-Cases for Logical Replication: Let’s take a look at why we need this feature in the database system.
- Sending incremental changes in a single database
- Consolidation multiple databases in a single one
- Replicating between different major versions of PostgreSQL
- Sharing a subset of the database between various databases
A subscriber can be a publisher for other databases by defining its publications. Conflicts can arise when writing to the same table from either side. Therefore, we use a read-only application to avoid this problem.
Step by step set up introduction
1.Build Publisher Servers
Create a Publisher database cluster using initdb tools:
$ postgres/bin/initdb /rep_example/db_master
You can install this database in your directory. db_mater is the name of our database cluster, and I put it under /rep_example/.
Now we need to set wal_level to ‘logical’. If you want to have another database cluster as the subscriber in the same machine(both servers are in the localhost), it needs to change the port number in postgresql. conf which inside db_master:
$ cd db_master
$ vi postgresql.conf
#wal_level = logical
#port = 54321
Here I use 54321 as a master port, and you can choose your own.
Now let’s start this new database cluster using pg_ctl:
$ postgres/bin/pg_ctl -D db_master start
-D following by the name of the database cluster. You could find further information about pg_ctl here :
https://www.postgresql.org/docs/10/app-pg-ctl.html
2.Build Subscriber Servers
Create a Subscriber database cluster using initdb tools:
$ postgres/bin/initdb /rep_example/db_slave
Repeat above steps, changing the wal_level to ‘logical’ in postgresql.conf file.
$ cd db_slave
$ vi postgresql.conf
#wal_level = logical
#port = 54322
Here I use 54322 as a slave port. And also don’t forget to start this server.
$ postgres/bin/pg_ctl -D db_slave start
3.Create Publication in the db_mater
First, get into db_master and add a table to this server:
$postgres/bin/psql -p 54321 postgres
postgres=# CREATE TABLE test_table(x int);
postgres=# INSERT INTO test_table(x) SELECT y FROM
generate_series(1, 100) a(y);
postgres=# SELECT count(*) from test_table;
count
-------
100
(1 row)
Create the Publication for all relations in this database named postgres.
postgres=# CREATE PUBLICATION my_publication FOR ALL TABLES;
Of course we could specify publication targets by calling table names. Also, we can specify which actions should be logically replicated. For example:
CREATE PUBLICARION my_publication FOR TABLE table1, table2;
CREATE PUBLICARION my_publication FOR TABLE table1, table2 WITH (publish = 'insert', 'update');
4.Create SUBSCRIPTION in the db_slave
Secondary, create the subscription in the db_slave:
$postgres/bin/psql -p 54322 postgres
postgres=# CREATE SUBSCRIPTION my_subscription CONNECTION 'host=localhost port=54321 user=me dbname=postgres' PUBLICATION my_publication WITH (copy_data=false);
Don’t forget to replace the host, port, user, and dbname with yours. A reminder that the dbname here references to database name, not the name of the cluster.
Then select our test table to see the replication:
postgres=# SELECT count(*) from test_table;
count
-------
100
(1 row)
And you will verify the synchronization.
OTHER Features
We could check replication status use catalog table in Postgres:
postgres=# select * from pg_replication_slots;
This will return a table listing all replication slots that exist on this database cluster. We usually select slot_name, slot_type and active to check the replication status. More details here: https://www.postgresql.org/docs/9.4/catalog-pg-replication-slots.html
After we know what replication is and how to set it up. Here is the way we delete a replication slot.
postgres=# select pg_drop_replication_slot('slot_name');
In summary, the blog introduced you to PostgreSQL Logical replication slots, their purpose, and the step-to-step setup introduction. Its functional and convenient features are widely used in data backpacks and analytical purposes.
Internship in Highgo as a junior software engineer.
Recent Comments