How to synchronize Test and Production databases?

Question

PostgreSQL

The question is probably hackneyed, but googling did not lead to anything.

We have:
production - works, takes orders, fixes them in the database, other scripts update something
test - we create new products on it, check that everything is ok, and only after that we want to upload them to production,

but how? doing a dump in production, stopping all work with the database and then unloading it back is not an option. clearly there is a solution.

Answer 1

Why do a full reupload to add multiple products? Add to the production base incrementally - only what has changed.

Answer 2

That's right - exclude 'manual' work in the process of transferring release changes to the production base.
Those. the task of the developer is to make scripts that will bring the database of the previous version to the next one, and this is not only updating the structure (by the way, this can be done automatically by comparing the databases of different versions, google ddl diff, for example, for postgres it is pgadmin shema diff ) but also data , for example, filling new fields with data, or for example, in the old version, the field was text in a 'free format', and in the new one, an entire structure was based on it (a degenerate example - there was an address as a text string and became almost the whole FIAS).
The goal - the update installation process may look like this: make a production replica -> roll up the update -> conduct testing -> if everything is fine, repeat to production, stopping it, otherwise we send a bug report to the developers. Moreover, all this is done by scripts automatically (of course, if there are any for testing).
Moreover, it is not always possible to suspend the database for the duration of the update, in which case the update process becomes even more complicated, and automation of all stages is extremely important.
The test database can be a replica of the database, it does not have to be with the latest data (although if the update is a bug fix for data that users have recently entered, then an online backup is needed).
ps To effectively create production database replicas, you can use a combination of master-slave replication and file system snapshots (the slave database can be temporarily suspended, create a file system snapshot and run a copy of the database from it, the first slave replica must continue to work, accumulating changes from production)