I am trying to set up an architecture with two databases, for example preview and live , which have the same schemas. A use case is that you can make changes to the preview database , and then after they are verified and approved, they will be transferred to the database in real time . The production application will be read from the database in real time .
What would be the most suitable way to pull all the data from the preview database into the database in real time without driving the current database? Ideally, the copy from the preview would be an atomic transaction.
I worked with this type of customization in MSSQL, but I'm pretty new to Postgres. So, I'm open to listening to other ways to create it (perhaps with the help of the Scheme?).
EDIT: The main reason for using separate databases is that I may need more than 1 target database (and not just one live database). I may also need to switch target databases on the fly without changing the schema of the source database.
source
share