How to connect two PostgreSQL databases in two different locations

Question

There is a project running on PostgreSQL with postgres_fdw. Due to legal reason some user data should be stored only in the remote db on the remote server in the closed country. Now the table that is connected to the remote server becomes unavailable when we lose connection to the remote database and storage. The main database must be available even if the remote database crashed. It should not crash the main database. How to connect two different databases from different locations to ensure data integrity (now it implemented on postgres_fdw)?

And the main requirement of our customer, the data should storage at a remote database only.

A materialized view not suitable, because it stores physically data at main server side.

Any ideas?

• Paul White: Your question has been reopened, though I find the accepted status of the answer confusing. You can undo the acceptance by clicking the check mark again, if that is appropriate?

Answer 1

I would create a materialized view on the foreign tables.

You can refresh the materialized view while the remote database is available and use it in between.

• Max Sherbakov: If a remote table /data center crashed, it affect all dependent database tables
• Laurenz Albe: You don't use them, you use the materialized view instead.
• Max Sherbakov: Stop, I've forgotten, the data should storage at remote database only, its requirement of our customer.
• Laurenz Albe: Well, then it is obviously impossible. If you keep no copy, and the remote data is inaccessible, you cannot have it.
• Vérace: @LaurenzAlbe - even if you did have a materialised view, what would happen if the "crash" was only a network outage and the remote db was still receiving connections and changing data - referential integrity would sail out the window! Basically, I think the OP should consider the implications of the CAP theorem! Something like CockroachDB with it's PostgreSQL compatible protocol might be a possibility, but that would assume that the remote site would want to use it also (very new) - you could still read, but not write when remote unavailable!