Refresh limited set of data in PostgreSQL

0

Is there a way we can refresh a limited set of data from Prod AWS account to Dev AWS account PostgreSQL database? For e.g., I have to refresh few rows from couple of tables of Prod to Dev DB. I don't want to do a full refresh of the data using snapshots. Database: AWS Aurora PostgreSQL

posta un anno fa328 visualizzazioni
2 Risposte
0

You can use below options based on your requirement and frequency of data refresh.

  1. 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

  1. 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

  1. 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.

AWS
con risposta un anno fa
0

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

https://aws.amazon.com/dms/

profile pictureAWS
ESPERTO
con risposta un anno fa

Accesso non effettuato. Accedi per postare una risposta.

Una buona risposta soddisfa chiaramente la domanda, fornisce un feedback costruttivo e incoraggia la crescita professionale del richiedente.

Linee guida per rispondere alle domande