1

We have two different postgres databases one for STAGING and one for PROD disconnected from each other but they have same schema, same tables with same structures and same data. The requirement is Sometimes the STAGING database is used to create/update/delete entries. Periodically these changes in STAGING can be tested and brought (merged) into PROD database.

Since these CRUD changes in STAGING database can be huge, once in 15 days we are planning to merge the new changes in STAGING database into the PROD database.

I tried copying the WAL files from STAGING database and put them in PROD database WAL folder and restarting the service. But unfortunately nothing was merged as I could see only the PROD database with unchanged data.

My questions is, can WAL files from the two different databases help the merge?

Improve this question

1 Answer 1

Reset to default
1

No, you cannot replay WAL from a different database. You could in theory use logical decoding to reverse engineer DML statements from WAL and try to apply these to the other database.

What I would do is

  • make sure that these modifications on the staging database are restricted to certain well-defined cases

  • write triggers that capture these data modifications

  • write a program that can apply the recorded changes to the prod database

In my opinion, randomly modifying one database and then hoping that some semi-magical tool can carry those changes over is a recipe for unhappiness.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.