By using AWS re:Post, you agree to the Terms of Use

Postgres RDS Schema Upgrade (migration) with no downtime

0

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.

2 Answers
1

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

answered 6 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.

0

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 )

answered 6 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions