postgresql from the library building – logical replication

Physical and logical replicate 1 replicate Comparative

Previously done a physical copy of the deployment of PostgreSQL, which has the following main advantages

    Exactly the same physical level is the main means of copying, which is similar to Oracle’s DG

    Low delay, during the transaction generated REDO record, in real time Apply standby database, when the transaction ends, prepared by the library data to see immediately

    Physical replication consistency, high reliability, do not worry about inconsistent data logic level

But they can not meet demand, there are some scenes in actual use, for example:

    Unable to meet the needs of the specified library or copy part of the table

    It will bring together data from multiple databases into a single instance of the target library or a library of data distributed to a plurality of different library

    Replication between different versions

    Table Synchronization between different library name

For the above scenario, when a physical copy can not be met, and therefore a logical copy came into being.

Logical replicate replication architecture is as follows:


Photos from the “PostgreSQL combat”

Parsing logic is based on the replication logic, the core principle is the redo log WAL after the main logic library Publication WAL log tables in a certain format and sends the parsed into logical standby database, the logical parsing the received Subscription library prepared, in order to achieve table data synchronization.


2. Copy the deployment logic

PS: The following logic can be deployed from the library on the new machine, as in the original modified from the library, the need to stop the original instance and recovery.conf delete or rename the file (as amended as recovery.conf.done)

2.1 modify postgresql.conf main library

May be modified based on the physical copy of the original configuration file, configuration logic to copy the main parameters to be adjusted as follows

wal_level = logical
max_wal_senders = 10
max_replication_slots = 8

A brief description of the parameters is as follows

    wal_level: logic configured to support only logical replication

    max_wal_senders: Since each subscription node and stream replication reserve will occupy a WAL sending process on the master library, this parameter value needs to be greater than max_replication_slots parameter value plus the number of physical reserves

    max_replication_slots: set value must be greater than the number of nodes subscribe

2.2 modify the logical library from postgresql.conf

Postgresql.conf logic can be modified from the library on the basis of the physical replication, the main difference is the main repository modify the following parameters

wal_level = logical
max_replication_slots = 8
max_logical_replication_workers = 8

Brief description of the parameters

    wal_level: logic configured to support only logical replication, logical, as the case may be set from the library

    max_replication_slots: Set the number of database replication slots, nodes should be greater than the number of subscribers

    max_logical_replication_workers: copy number setting process logic, should be greater than the number of nodes subscribe, and reservation number to a number of table synchronization process, this parameter defaults 4

2.3 Create a logical replication account



Logic users need REPLICATION permissions to copy may not be required SUPERUSER authority, after publishing on the need to synchronize the nodes need to empower logical_repl user table, so logical_repl account has read access to these tables.

2.4 Create a library table and logically the main library


* Create a logical copy of the main library

[[email protected] data]$ createdb sourcedb
[[email protected] data]$ psql -d sourcedb
psql (11.4)
Type "help" for help.
sourcedb=# create table logical_tb1(id int primary key,name varchar(20));

2.5 Logic and create a library table from the library


* Create different libraries from the library logic

[[email protected] data]$ createdb desdb
[[email protected] data]$ psql -d desdb
psql (11.4)
Type "help" for help.
desdb=# create table logical_tb1(id int primary key,name varchar(20));

Note: The logical replication table structures need to manually create from the library

2.6 Creating posted on the main logic libraries


* Create a publication pub1 on the publication of the main library, pay attention to it at the library to perform sourcedb

*/ sourcedb=# CREATE PUBLICATION pub1 FOR TABLE logical_tb1;

If you need to publish multiple tables (,) separated by commas between the name of the table, to be released if all the libraries, will be adjusted FOR TABLE FOR ALL TABLES.

At this point you can view the following information

sourcedb=# SELECT * FROM pg_publication;
 pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate 
 pub1    |       10 | f            | t         | t         | t         | t
(1 row)


Fields are as follows:

    pubname: refers to the publication of the name

    pubowner: refers to the release of the owner, you can associate usesysid field and pg_user view of the main query to get specific information belongs

    puballtables: whether to publish all tables in the database, t represents the release of all tables in the database already exists and after the new table

    pubinsert: t represents the only issue on the table INSERT operation

    pubupdate: t represents the only issue UPDATE operations on the table

    pubdelete: t represents the only issue on the table DELETE operations

2.7 Create a subscription from the library logic

desdb=# CREATE SUBSCRIPTION sub1 CONNECTION 'host= port=5432 user=logical_repl dbname=sourcedb password=logical_repl' PUBLICATION pub1;
NOTICE:  created replication slot "sub1" on publisher

Once created, you can query the following information in a logical primary database:

sourcedb=# SELECT slot_name,plugin,slot_type,database,active,restart_lsn    FROM pg_replication_slots where slot_name='sub1';
 slot_name |  plugin  | slot_type | database | active | restart_lsn 
 sub1      | pgoutput | logical   | sourcedb | t      | 0/6022D30
(1 row)

In the logic from the database can query the following information:

desdb=# SELECT * FROM pg_subscription;
 subdbid | subname | subowner | subenabled |                                      subconninfo                                      | subslotname | subsynccommit | subpublications 
   24995 | sub1    |       10 | t          | host= port=5432 user=logical_repl dbname=sourcedb password=logical_repl | sub1        | off           | {pub1}
(1 row)

2.8 Authorization to copy account

Because the step 2.3 to create a copy of an account does not need to copy the table to be authorized, therefore, at this time there follows a logical error logs from library

2019-10-10 15:57:21.847 CST [27443] ERROR: could not start initial contents copy for table "public.logical_tb1": ERROR: permission denied for table logical_tb1
2019-10-10 15:57:21.848 CST [24722] LOG: background worker "logical replication worker" (PID 27443) exited with exit code 1

So now require authorization to copy the user.

sourcedb=# GRANT USAGE ON SCHEMA public TO logical_repl;
sourcedb=# GRANT SELECT ON logical_tb1 TO logical_repl; 

After authorization, the normal display

    2019-10-10 16:00:25.959 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has started
    2019-10-10 16:00:25.967 CST [28204] LOG: logical replication table synchronization worker for subscription "sub1", table "logical_tb1" has finished


2.9 Test Data Synchronization

Inserting data in a logical primary library


Inserting data in the main database *

*/ sourcedb=# insert into logical_tb1(id,name) values(1,'a'),(2,'bca'); INSERT 0 2

In the logical view the results from the library


* Check whether the data synchronization is complete

*/ desdb=# select * from logical_tb1; id | name ----+------ 1 | a 2 | bca (2 rows)

This shows that the data has been synchronized completed.

2.10 Adding required for replication table

In the logic of the main library and the logic are adding a new table from the library, and added to the list released


* Create table structure on the main library

*/ sourcedb=# create table logical_tb2(id int primary key ,addr varchar(100));
CREATE TABLE sourcedb=# /*

* Create table structure from the library

*/ desdb=# create table logical_tb2(id int primary key ,addr varchar(100));

* Authorization to account replicated logic in the main library

*/ sourcedb=# GRANT SELECT ON logical_tb2 TO logical_repl; GRANT /*

* Add a new table to a list released

*/ sourcedb=# ALTER PUBLICATION pub1 ADD TABLE logical_tb2; ALTER PUBLICATION /*

* In the main gallery view published table name in the list

*/ sourcedb=# SELECT * FROM pg_publication_tables; pubname | schemaname | tablename ---------+------------+------------- pub1 | public | logical_tb1 pub1 | public | logical_tb2 (2 rows)

At this point a table has been added into the publishing list.

At this time, the write data in the master library, from the library to view the following cases:


* Insert a record into the main library

*/ sourcedb=# insert into logical_tb2(id,addr) values(1,'beijing'); INSERT 0 1 /*

* At this point in the logical view from the library, the result is no data

*/ desdb=# select * from logical_tb2; id | addr ----+------ (0 rows)

Because also you need to refresh from the library subscription


* At this point in the refresh subscriptions from libraries


* Refresh after the investigation is completed the data already has data

*/ ALTER SUBSCRIPTION desdb=# select * from logical_tb2; id | addr ----+--------- 1 | beijing (1 row)

At this point, the logical copy PostgreSQL also deployed.


3. Physical and logical replication replication features and scenarios

PostgreSQL physical and logical replication replication diff projecting, in use, which can be selected using the copy mode according to their characteristics.

    Subscribe logic, suitable for publishing cases end with the end of the subscription has read and write.

    Subscribe logic, more suitable for small transactions, or low-density write (mild write) synchronization. If there is a large transaction, high-density write, subscribe to the logic of delays compared to physical replication higher.

    Subscribe logic, suitable for bi-directional, multi-directional synchronization.

    Physical replication suitable for one-way synchronization.

    Physical copy, suitable for any business, any write density (heavy write) synchronization.

    Physical replication suitable for the HA, disaster recovery, separate read and write.

    Physical copy, suitable for library equipment did not write, just read the scene.


Would like to further communication or other article would like to know the students can focus on my micro-channel number for the public exchange



Leave a Reply