Synchronization of two PostgreSQL databases supported separately per application
I made a backup of the database used for the application as we are updating its functionality in the new version. The old app and the new app run separately with separate databases.
As per the requirement, now we need to sync all users between both databases. The problem is that there are many dependent tables in both databases. What's the best approach for synchronizing databases?
Any help in this regard is very helpful.
Edit
At launch, our application was with an older version, when we update it to a new version with a new design, we took a backup of our old database and started a new one from this old database. On average, we continued to work with both applications and new users who joined the old version, even after the release of the new site. So now both applications have been going on for quite a long time, now the user base is increasing on both sides according to the requirement we have to merge both databases and close the old application.
Here the custom table is linked to several other tables that support their subscription plans, their profiles, their game counter..etc
source to share
Given that these are different versions of the application and may have different table schemas over time, I think your best option is to have COPY
all the tables you need on each side, process them offline, and then load new rows using an external utility.
In theory, you can do all of this from within a database and even use Bucardo FDW, but the major problem you will have in these cases is the fact that it becomes fragile if the schema of a new application changes.
source to share