Postgres RDS Schema Upgrade (migration) with no downtime
Hello, What is the recommended way to create a snapshot (for backup) of an RDS Postgres instance to apply schema changes? We want to maintain both replicas in sync, minimize downtime and keep the new replica up to date. We want to create and keep a snapshot in case we need to rollback these schema changes. I have made research and found https://aws.amazon.com/getting-started/hands-on/move-to-managed/migrate-postgresql-to-amazon-rds/ not sure if this feature can be used for this purpose. Will live schema changes break the migration? Our schema changes are simple, we are adding nullable new columns to existing tables and we are also creating new tables. Thank you in advance.
Hello, Is the requirement only to being able to restore the database to a previous point in time? If yes, then this can be done without any replication, just with regular database backups.
When people speak about replicas, this means there is a secondary instance that is receiving all the changes in (near) real time, but it doesn't seam to me you're asking for that?
Regards
for managed database offering you can set backup 0-35 days and for issues or rollback you can do a point in time for that period to a new instance. thus any schema changes could be rolled back as long as the change was done in last n days (n=backup days set on instance )
Relevant questions
aurora postgres major upgrade - pg_dumpall
asked 3 years agoRDS Database Instance does not show up in dropdown when creating RDS Proxy
asked 3 months ago60K updates to RDS Postgres - performance challenge
Accepted Answerasked 2 years agoHow to restrict database users for RDS Postgres using AWS Managed AD trusted with customer on-prem AD
Accepted Answerasked 2 years agoMigration from Aurora PostgreSQL back to regular RDS
Accepted Answerasked 3 years agoEnable RDS Encryption with minimal downtime after creation
Accepted Answerasked a year ago"schema with OID does not exist" error during Postgres 12.8 to 13.6 upgrade
asked 19 days agoCan I import an RDS uncrypted mysql snapshot to an RDS encrypted mysql?
Accepted Answerasked 2 months agoPostgres RDS Schema Upgrade (migration) with no downtime
asked 2 months agoHow to create SSL dblink connection from RDS to postgres on premise?
asked 4 months ago
Yeah I would ask the same as ADolganov mentioned that are we talking about read replica or backups/snapshots?
For replicas: We can use native RDS Read Replica or We can use DMS to create replication between two PostreSQL instances. (RDS PostgreSQL to RDS PostgreSQL or RDS PG to on-prem PG or RDS PG to Aurora).
By the way, DMS does support DDL change add columns and create tables: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.SupportedDDL.html but test is suggested before applying this to Production.