Last active
February 22, 2023 10:06
-
-
Save umarizulkifli/57f706b4f6669e9e171932dc99a835fb to your computer and use it in GitHub Desktop.
Postgres Upgrade with logical replication
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1. dump source db instance | |
pg_dumpall -g --no-role-passwords > global.sql | |
pg_dump --schema-only --section=pre-data > pre-schema.sql | |
pg_dump --schema-only --section=post-data > post-schema.sql | |
2. load schema to target db | |
global and pre-schema | |
3. create publication on source db instance | |
CREATE PUBLICATION pub FOR ALL TABLES; | |
CREATE PUBLICATION; | |
4. create subscription on target db instance | |
CREATE SUBSCRIPTION sub CONNECTION 'dbname=postgres host=source.us-east-1.rds.amazonaws.com user=postgres password=postgres' PUBLICATION pub; | |
5. load post schema to target db | |
6. ALTER TABLE alltable REPLICA IDENTITY FULL; | |
7. stop subscription on target db instance | |
DROP SUBSCRIPTION sub; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment