Shulou(Shulou.com)06/03 Report--

Logical replication from PG10 to PG11

The PG environment I demonstrate below is deployed on the same physical machine in a single machine with multiple instances. For deployment, please refer to the previous blog.

1. The old PG10 main library (you need to set wal_level = logical first):

Su-postgres cd / usr/local/pgsql-10.10/./bin/psql-- port 5433postgres=# create database testdb1;CREATE DATABASEpostgres=# create database testdb2;CREATE DATABASEpostgres=#\ c testdb1You are now connected to database "testdb1" as user "postgres" .testdb1 = # create table tb1 (an int, b int, c int); testdb1=# create table tb2 (an int, b int, c int); testdb1=# create table tb3 (an int, b int, c int)

Create an account for replication

CREATE USER repuser REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'repuser'; in addition, repuser users need to be given rights to source database, source table, and source schmea\ c testdb1grant connect on database testdb1 to repuser;grant usage on schema public to repuser;grant select on all tables in schema public to repuser;-- this authorization is a bit big, but the problem is not too serious.

Host all repuser 192.168.2.1/24 md5

Then, the configuration of pg under reload.

2. Initialize the new PG11 library

Cd / usr/local/pgsql-11.5mkdir data./bin/initdb-D data./bin/pg_ctl-D data/-o "- p 5434"-l pg.log start./bin/psql-- port 5434

3. Export the schema definitions of PG10

Cd / usr/local/pgsql-10.10./bin/pg_dumpall-s-- port 5433-- no-subscriptions >. / schemadump.sql

4. Import the export data of PG10 into PG11

Su-postgres cd / usr/local/pgsql-11.5./bin/psql-- port 5434-d postgres-f / usr/local/pgsql-10.10/schemadump.sql

5. Create a publication that captures all tables in each database in the source instance PG10

Note: logical replication works separately in each database, so it needs to be repeated in each database. On the other hand, you don't have to upgrade all databases at once, so you can complete one database at a time without even upgrading some databases.

Cd / usr/local/pgsql-10.10./bin/psql-- port 5433postgres=#\ c testdb1testdb1=# CREATE PUBLICATION p_upgrade FOR ALL TABLES Testdb1=#\ dRp+ Publication p_upgrade Owner | All tables | Inserts | Updates | Deletes-+-postgres | t | t (1 row) testdb1=#\ c testdb2testdb2=# CREATE PUBLICATION p_upgrade2 FOR ALL TABLES Testdb2=#\ dRp+ Publication p_upgrade2 Owner | All tables | Inserts | Updates | Deletes-+-postgres | t | t (1 row)

6. In each database in the target instance PG11, create a subscription to the publication you just created.

Su-postgres cd / usr/local/pgsql-11.5. / bin/psql-- port 5434

Testdb2=#\ c testdb1testdb2=# CREATE SUBSCRIPTION s_sub CONNECTION 'host=192.168.2.4 port=5433 dbname=testdb1 user=repuser password=repuser' PUBLICATION p_upgrade Testdb1=#\ dRs+ List of subscriptions Name | Owner | Enabled | Publication | Synchronous commit | Conninfo-+- -- +-s_sub | postgres | t | {p_upgrade} | off | | host=192.168.2.4 port=5433 dbname=testdb1 user=repuser password=repuser (1 row) testdb2=#\ c testdb2testdb2=# CREATE SUBSCRIPTION s_sub2 CONNECTION 'host=192.168.2.4 port=5433 dbname=testdb2 user=repuser password=repuser' PUBLICATION p_upgrade2 | Testdb2=#\ dRs+ List of subscriptions Name | Owner | Enabled | Publication | Synchronous commit | Conninfo-+- -+-s_sub2 | postgres | t | {p_upgrade2} | off | | host=192.168.2.4 port=5433 dbname=testdb2 user=repuser password=repuser (1 row) |

7. On PG10, create some data:

Postgres=#\ c testdb1testdb1=# insert into tb1 (a recorder bpender c) values (1 recorder 1), (2 recorder 2), (3 recorder 3); testdb1=# insert into tb2 (a recorder bpencil c) values (1)

Then, in the testdb1 library on PG11, you can see that the data has been synchronized.

8. If there is a table addition operation on PG10, similar to the following:

On PG10, we add a table testdb1=#\ c testdb1testdb1=# create table tb_new (an int, b int); testdb1=# insert into tb_new values (1 int), (2), (3), (4); testdb1=# GRANT SELECT ON tb_new to repuser; needs authorization because we gave all table a copy configuration in step 5, so there is no need to execute the command added to the publisher after the Singapore table. Testdb1=#\ dRp+ p_upgrade-View the details of the publisher Publication p_upgradeOwner | All tables | Inserts | Updates | Deletes-+-postgres | t | t (1 row)

After adding the table to PG10, we can see that the tb_new table on PG11 does not exist. We need to create the following manually on PG11:

Execute the following command on PG11:

\ C testdb1create table tb_new (an int, b int); select count (*) from tb_new;-- when the data is still ALTER SUBSCRIPTION s_sub REFRESH PUBLICATION; of 0-- refresh the subscriber select count (*) from tb_new;-- then the data becomes four.

9. Clear replication settings (execute on the new PG11 library)

\ c testdb1DROP SUBSCRIPTION slots sub;\ c testdb2DROP SUBSCRIPTION s_sub2

You can then delete the publication on the source instance PG10, but this is not required.

10. Finally, if the traffic on the old PG10 is cut to PG11, you can take the PG10 instance offline.

Restrictions on native logical replication [critical]:

1. Only ordinary tables are supported, but sequences, views, materialized views, external tables, partitioned tables and large objects are not supported.

Some additional comments on workarounds for things that are not supported by logical replication. If you are using large objects, you can use pg_dump to move them, as long as they do not change during the upgrade process. This is an important limitation, so if you are a heavy user of large objects, this approach may not be suitable for you. If your application issues TRUNCATE during the upgrade process, these actions are not replicated. Maybe you can adjust your application to prevent it from doing this when upgrading, or you can replace DELETE. PostgreSQL 11 will support replication of TRUNCATE, but this is valid only if both the source and destination instances are PostgreSQL 11 or later.

2. Only DML (INSERT, UPDATE, DELETE) operations of ordinary tables are supported, but truncate and DDL operations are not supported.

3. The table that needs to be synchronized must set REPLICA IDENTITY not to noting (the default is default), and the table must contain a primary key, otherwise delete and update report errors

4. A publisher can contain one or more tables, and a table can have one or more publishers

5. A publisher can have multiple subscribers, or a subscriber can subscribe to multiple publishers at the same time. Subscribers cannot repeat subscriptions to the same publisher's tables under the same database (to avoid data conflicts).

6. Logical replication is different from stream replication in that it is not a strict master-slave relationship. Ordinary tables on the subscriber side can still be added, deleted and modified.

7. The table structure of the synchronous table needs to be consistent on both sides of the publisher and subscriber (the order of the columns is allowed to be different, but the corresponding data types of the columns must be the same)

8. If the data on the subscriber side is mistakenly deleted, if you want to copy the data of the synchronized table from the publisher, you can only do so by rebuilding the subscriber where the synchronized table resides.

Other considerations:

Publication-publisher

The premise of logical replication is to set the database wal_level parameter to logical

Users who are logically replicated on the source library must have the role of replicatoin or superuser

If you need to publish a logically copied table, you must configure the REPLICA IDENTITY feature of the table

Allow all tables to be published at once, syntax: CREATE PUBLICATION alltables FOR ALL TABLES

Subscription-subscriber

The subscription node needs to specify the connection information of the publisher

There can be multiple subscribers in a database

The subscription can be enabled / suspended using enable/disable

The schema name and table name of the publish node and subscription node table must be the same, and the subscription node allows the table to have additional fields.

The table name is added to the publication node, and the subscription node needs to execute: ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION

Reference:

Https://www.postgresql.org/docs/10/sql-createpublication.html

Https://www.postgresql.org/docs/10/sql-createsubscription.html

Https://www.postgresql.org/docs/10/sql-altersubscription.html

Https://yq.aliyun.com/articles/585446?spm=a2c4e.11153940.0.0.48e86e272CVXQp

Https://postgres.fun/20170528142004.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.