Skip to content

Instantly share code, notes, and snippets.

@umarizulkifli
Last active February 22, 2023 10:06
Show Gist options
  • Save umarizulkifli/57f706b4f6669e9e171932dc99a835fb to your computer and use it in GitHub Desktop.
Save umarizulkifli/57f706b4f6669e9e171932dc99a835fb to your computer and use it in GitHub Desktop.
Postgres Upgrade with logical replication
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