How to Switch a Secondary Physical Database to a Secondary Logical Database on PostgreSQL

In this article, the author explains switching a secondary physical database to a secondary logical database based on PostgreSQL.

By digoal

1) Configure wal_level = logical for the primary database.

2) Establish physical data flow diagrams to replicate the secondary database.

3) Create a logical slot for the primary database, and create the slot decode = pgoutput for the databases that require logical replication.

4) Activate read and write access to the secondary database and record the wal offset during the activation process, which you can view in the pg_wal/history file.

5) Create a pub by the table that requires logical replication in the primary database.

6) Set the slot offset as the wal activation offset in the primary database.

7) Create a sub that assigns slots without initial data in the secondary database.

copy_data (Boolean): Specifies whether the existing data in the publications being subscribed to should be copied once the replication starts. The default is true.

create_slot (Boolean): Specifies whether the command should create the replication slot on the publisher. The default is true.

enabled (Boolean): Specifies whether the subscription should be actively replicating or it should be just set up but not started yet. The default is true.

slot_name (String): Name of the replication slot to use. The default behavior is to use the name of the subscription for the slot name.

When slot_name is set to NONE, no replication slot will be associated with the subscription. You can use this if you manually created the replication slot later. Such subscriptions must also have both enabled and create_slot set to false.

postgres=# \h create pub  
Command:     CREATE PUBLICATION  
Description: define a new publication  
Syntax:  
CREATE PUBLICATION name  
    [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]  
      | FOR ALL TABLES ]  
    [ WITH ( publication_parameter [= value] [, ... ] ) ]  
  
postgres=# \h create sub  
Command:     CREATE SUBSCRIPTION  
Description: define a new subscription  
Syntax:  
CREATE SUBSCRIPTION subscription_name  
    CONNECTION 'conninfo'  
    PUBLICATION publication_name [, ...]  
    [ WITH ( subscription_parameter [= value] [, ... ] ) ]

References

0 0 0

Share on

: digoal

282 posts | 24 followers
Follow

Community

How to Switch a Secondary Physical Database to a Secondary Logical Database on PostgreSQL

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Backup

Database for FinTech Solution

Oracle Database Migration Solution