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:
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.
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.
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?