0

We are trying to find a solution to synchronize the data from one database (let's call it Master) database to another database (let's call it Analytics) to perform ETL operations in Analytics.

The setup would be as follows:

  • Tables from Master would be replicated in Analytics
  • Replicated tables in Analytics would be read-only in analytics but used in certain ETL scripts (some of them purely SQL) to perform some insertions in other tables in Analytics

From our initial research it seems PGSync would be a good solution to perform this. We have been trying AWS DMS as well but it created too much of a drag on resources on Master.

Has anyone attempted a similar setup in the past? Would love to hear your thoughts!

Improve this question

2 Answers 2

Reset to default
2

I would recommend using DB links as I have used once to for analytics between Postgres to redshift so it will be feasible to use it between Postgres to Postgres

the example between Postgres to redshift.

https://aws.amazon.com/blogs/big-data/join-amazon-redshift-and-amazon-rds-postgresql-with-dblink/

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks this is the route we went with, works pretty well!
0

You can create read_replicas in AWS RDS here is some additional info. I think this is what you are searching for - note that for aurora Read replicas can also become master in case of a failure.

Improve this answer

2 Comments

Hi @FransDel , we wish to sync the data to another DB that will allow read-write operations. The data from the tables that are synced would be kept read-only but we'd insert data in other tables in that same database.
Okay then you could use AWS Batch or DataPipline to do such a job. Does the sync need to be real-time?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.