Postgresql on local and remote: how do you sync?

Hi!!

I spent some time building the backend for my little webapp, and now I am finally startoing with the frontend, so I want to put it all online (Flask with Postgresql).

I already added a lot of data to my database locally, so I will do a dump-restore to my online postgresql server to have it copied to the remote server.

However, I still want to keep adding data to it locally, as well as, eventually, by using the backend online...

The online DB should always be the latest and greatest though. How to keep it all in sync?

I found info about logical replication, but it is all very technical and confusing to me... I am looking for real-world cases...

So if you do the same, how do you do it??

Greetings from Beijing
Daniel

Comments

[rhymes]

I think dump and restore is your best bet if you don't want to setup replication.

I would keep them separate in the end, because your production database (the online version) and its backups should be the "single source of truth" and your local copy probably contains all the experiments and maybe data that's not 100% clean. They might also have slightly different versions of PostgreSQL (not an issue unless it's a major version)

So if you need to keep them in sync decide who's the master and who's the replica and do the dump and restores in one direction unless you're sure you can rebuild the data at any time with seeding scripts.

Once the app is finished, stop restoring the production db from the local db and setup automatic backups :-)

[Daniel da Rocha]

Cool! This sounds so obvious and so much simpler... I was getting worried about all the setup I was gonna have to do :O

Thanks!!

[Evaldas Buinauskas]

I wouldn't setup replication from local to production, better keep different db environments separate and don't let them know about each other.

It's best to treat your database (schema and static data) as a code and deploy your database as you deploy code. So the workflow would be as follows:

• Prepare a database Git repository
• Commit your schema and static data there
• Deploy to production off your Git repository

Nothing really different from what you'd do with code. I've done many different and over-complicated database migrations and this is what I'd stick to, honestly.

There's a tool called Flyway that let's you run database migrations, I'd suggest to check it out, heard many good opinions about it, but never really used it.

[Daniel da Rocha]

Hi Evaldas!

I already have database migrations setup and running (Flask + SQLAlchemy + Alembic + Flask-Migrate).

My concern was mostly related to data, as I was feeding the database locally, and was wondering "what will happen when I put the admin online and want to feed it both locally AND remotely at times?".

Therefore what Rhymes described above sounds like a good solution...

Thanks!1
Best,
Daniel

[andreasneuman]

Hi Daniel,
IDK whether it could help you, but I would like to share my user experience.
Recently I've found a pretty good tool for comparison and synchronization PostgreSQL with additional support for Amazon Redshift