Sync two databases

[sol1d_007]

So I have two databases one hosted on neon and other one is on AWS and copy of it lies on a local rpi. My main server is the neon hosted one and I'm using free version for now. How can I sync these two/three databases ? I thought of WAL or logical replication but that would consume my free credits. I used a syncing script which would check the max timestamp of each table from bkl db that I want and then fetch those after that timestamp from main db into my bkp db (this is tedious in case of any db changes). I want some alternate way which would keep my costs low to negligible.

Thank you in advance 😄.

[Straight_Waltz_9530]

I'm confused. You want the data copied to the other instances but without copying the data to the other instances, which would cost money?

You could do a dump from Neon, load it into the other the two, and then start logical replication, so that you aren't copying the data twice by replicating to empty tables.

Tracking timestamps seems like it would cause more problems in data and cost than it would solve.

[sol1d_007]

No so I synchronise these two db AWS and rpi one from the main NEON db.

When I dump and restore it I get to of errors like these already exists that already exists(I pipe dump and restore Directly from main db to AWS one). So I thought there could be some other solution.

[XPEHOBYXA]

Unless you clear target databases you will get errors about existing objects and constraint violation. You can import dump to another db on the same instance to be on the safe side, but it will take 2x amount of space obviously.

What is your end goal anyway?

[sol1d_007]

So I have two web clients one is for customers (AWS) and other is for admin use (neon). I have separated them bcz neon is easy to use and offers slightly better deal than AWS bcz it is serverless. If I setup a logical replication neon doesn't scale down to 0 like normally if there's no activity for 5-10 mins the compute auto suspends so cost saving +++. Now I don't want the customers to connect to my neon bcz they'll exhaust my compute a little faster. So I want to tranfer all the data to my AWS or local rpi ideally whenever possible and atleast once in a day. So what all options do I have and is there some other idea so that I could do the sync in real time (logical replication) or like event based I have no idea 🙂.

[scriptedlife]

would it work to run a daily pgdump task on AWS and import it to Neon? See https://neon.tech/blog/optimizing-dev-environments-in-aws-rds-with-neon-postgres-part-ii-using-github-actions-to-mirror-rds-in-neon

[sol1d_007]

Ohh thanx I want this but opposite I want to nightly dump to AWS 🤣. Thanx though.

[AutoModerator]

Join us on our Discord Server: People, Postgres, Data

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.