Sync production with dev database

fogg

Hi Team,
what is the best / easiest way to synchronize two environments?
Like I develop on a local dev server. I also have another (dockerized) server which serves as my production system with other people accessing it.
After some time, I would like to 'clone' the production data into my dev system so i can easier debug things or add new features with live data.
Thanks!

gabriel

The best way would be DB dump and then restore. Then you have a 1:1 copy to work on. You can also script it to make it a single-click operation.

You could also live-sync via REST (or other means) but that is tedious to implement and error-prone.

fogg

Live sync sounds like too much hassle. A dump / restore would be ok. Would you have some examples how to script it? Bear in mind, the production is in a docker container :D
Thanks!

gabriel

I have very little experience in Docker. But to my understanding, you should be able to just copy+paste the docker volumes to use as clones, running on different app configurations (eg. ports, container names, etc.).

I cannot help you here with Docker. But to execute a dump & restore, you need just 2 lines of code - see our admin docs.

fogg

As for docker, I found this to be helpful:

docker exec -i <r3containerid> /usr/bin/pg_dump \
 -U app app > postgres-backup.sql

This will dump the postgres-back.sql in local folder.

fogg

Ok, restoring it on windows took a bit longer. Also, i did not use the dump file from the command above, but I used the automatically created back files from my prod server.

Here is the command I used:

pg_restore -c -h localhost -p 5432 --no-owner -U app -d app c:\temp\database

However, I had to check the config.json to find the admin-password for the db. The restore than still complained about a few objects it could not delete first as they are in use somewhere. Example message:

Die Anweisung war: DROP SCHEMA instance;
pg_restore: Fehler: could not execute query: ERROR:  schema "instance" already exists

After that i could login again and my data is there. Not sure if something else is broken now..... will continue with this setup for some time unless one of you tells me not to :D