Best way to copy data between 2 postgres databases in AWS

Questions / Help
1

We have 2 databases and would like to sync data in one direction between db1.table and db2.table. They are both postgres databases. One is a AWS Aurora DB, second is the postgres db in our application.

What are my available options? So far I have:

  1. pg_dump and pg_restore. This has the downside that I need local space on the hard-drive to get the dump. Though the table currently are quite small, the might grow in the future and I would love to build an future-proof solution.

  2. dump table to S3, import from S3. Could work, in fact we have some imports done like this. But I don’t like the complexity of having an extra system we rely on.

  3. Stream with Ecto from Repo1 and directly upsert to Repo2. Nor sure yet if this is a good idea, but I like the idea that I keep disk space and memory under control.

Are there any other options? Thoughts?

1 Like
2

I’ve used this in the past and it worked pretty well.

Is it a one-time thing or you want to keep it syncing?

1 Like
3

It should be synced daily

4

You should be able to do that with DMS

5

Thanks, I will look into it. Looks very promising.

Still want to explore some other solutions without proprietary AWS tools.

6

I am very interested in this but had no time to properly tackle it for months. In the meantime, check if any of these help:

I’ll start working on CDC and various code generation / instrumentation solutions after I get a job and after I release a first working variant of my Elixir sqlite connector and Ecto driver. For now I don’t have solid leads for you though.

1 Like
7

check out PG’s logical replication, i.e. pub/sub

2 Likes