You can use below options based on your requirement and frequency of data refresh.
- Native pg_dump/pg_restore :
pg_dump and pg_restore is a native PostgreSQL client utility.
[+] pg_dump and pg_restore - https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-pd_dump.html
- Using AWS Database Migration Service (DMS):
AWS Database Migration Service (AWS DMS) is a web service that you can use to migrate data from a source data store to a target data store and you can replicate ongoing changes to keep sources and targets databases in sync.
[+] Getting Started with AWS DMS - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html
[+] Using RDS/Aurora PostgreSQL as a source for DMS - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html
[+] Using RDS/Aurora PostgreSQL as a target for DMS - https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html
- Using PostgreSQL logical replication with Aurora:
Another method is using pglogical to replicate tables (not whole database) from an aurora PostgreSQL cluster to another aurora PostgreSQL cluster (you might need to setup cross account VPC peering between the VPCs in the two accounts).
[+] Using PostgreSQL logical replication with Aurora - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Replication.Logical.html
[+] Working with extensions and foreign data wrappers - Using pglogical to synchronize data across instances - https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.pglogical
Since your use case is to refresh few rows from couple of tables of Prod to Dev DB, you can use AWS DMS or logical replication option.
DMS between your instances can capture all changes on Prod and replicate them on Dev: in real-time or a set of changes (since last refresh) when you want
- asked a year ago
- AWS OFFICIALUpdated 12 days ago
- AWS OFFICIALUpdated a year ago
- How can I associate a Route 53 health check from a different AWS account to a record set in my account?AWS OFFICIALUpdated 8 months ago
- AWS OFFICIALUpdated 3 months ago
- EXPERTpublished a month ago