PG Online Migration

How to perform zero-downtime migration with pg logical replication

An auxiliary playbook, pgsql-migration.yml, provides a battery-included migration method based on logical replication.

By filling in the information about the source and host clusters, the playbook will automatically create the scripts needed for the migration and simply execute them in sequence during the database migration.

  1. activate                        # activate migration context
  2. check-replica-identity          # prepare: make sure all table have replica identity
  3. check-replica-identity-solution # prepare: fix table without replica identity
  4. check-special-objec             # prepare: check special object: matrialized view
  5. compare            # compare: fast check on data consistency (by row count)
  6. copy-schema        # migration: copy schema from src to dst cluster
  7. create-pub         # migration: create publication on source cluster
  8. create-sub         # migration: build logical replication between src & dst clusters
  9. progress           # migration: print logical replication progress
  10. copy-seq           # migration: copy sequence number from src to dst cluster
  11. next-seq           # migration: advance dst cluster by 10000 to fix primary confliction
  12. remove-sub         # remove subscription from dst cluster

Prepare

SRC and DST Clusters

Suppose you want to migrate the pg-meta cluster in the sandbox (containing the Pigsty meta DB with the pgbench test tables) to the pg-test cluster.

  1. pg-meta-1    10.10.10.10  --> pg-test-1    10.10.10.11 (10.10.10.12,10.10.10.13)

First, create a new empty target cluster pg-test, then edit the variables list in pgsql-migration.yml and fill in the relevant information (connection information for the host cluster’s primary).

  1. #--------------------------------------------------------------#
  2. #                   MIGRATION CONTEXT                          #
  3. #--------------------------------------------------------------#
  5. # src cluster (the old cluster)
  6. src_cls: pg-meta                       # src cluster name
  7. src_db: meta                           # src database name
  8. src_ip: 10.10.10.10                    # ip address of src cluster primary
  9. src_list: [ ]                          # ip address list of src cluster members (non-primary)
  11. #--------------------------------------------------------------#
  12. # dst cluster (the new cluster)
  13. dst_cls: pg-test                       # dst cluster name
  14. dst_db: test                           # dst database name
  15. dst_ip: 10.10.10.11                    # dst cluster leader ip addressh
  16. dst_list: [ 10.10.10.12, 10.10.10.13 ] # dst cluster members (non-primary)
  18. # dst cluster access information
  19. dst_dns: pg-test                       # dst cluster dns records
  20. dst_vip: 10.10.10.3                    # dst cluster vip records
  22. #--------------------------------------------------------------#
  23. # credential (assume .pgpass viable)
  24. pg_admin_username: dbuser_dba          # superuser @ both side
  25. pg_replicatoin_username: replicator    # repl user @ src to be used
  26. migration_context_dir: ~/migration     # this dir will be created
  27. #--------------------------------------------------------------#

Execute pgsql-migration.yml, which by default creates the ~/migration/pg-meta.meta dir on the meta node, containing the resources and scripts used for the migration.

Manual Template

Announcement

  • Operation Notice
  • Business Party Notification

Preparations

  • Prepare source and host clusters
  • Repair Source HBA
  • Create Source Replication User
  • External Resource Request
  • Create Cluster Profile
  • Configure business users
  • Configure business database
  • Configure business whitelist
  • Create business cluster
  • Fix Replication Identity
  • Identify migration target
  • Generate schema synchronization command
  • Generate serial number synchronization command
  • Generate create publish command
  • Generate create subscription command
  • Generate progress check command
  • Generate check command

Stock Migration

  • Synchronize database schema
  • Create publish at the source
  • Create a subscription to host
  • Wait for logical replication sync

Switch moment

  • Prepare
  • Stop source write traffic
  • Synchronize sequence numbers with other objects
  • Verify data consistency
  • Flow Switching
  • Aftercare

Last modified 2022-06-04: fill en docs (5a858d3)