Do you think this plan will work?
Without a lot of headaches, no I don't. Most times I have witnessed an attempt to use replication for DR it has failed. Every time I've witnessed an attempt to 'fail-back' from a replication 'failover' it has failed miserably.
As you've mentioned, replication is not a HA/DR technology and shouldn't be used as such. If you'...
First, don't drop the table to do this. You have two better options:
Use the MERGE command
TRUNCATE the table before inserting, don't DROP and CREATE.
The main limitation on the TRUNCATE...INSERT pattern is permissions, since a simple read or update permission grant will not allow for TRUNCATE. ALTER permission on the table is needed to use this command. ...
Never mind, Its possible, I have tested with this. But we have to make sure that if you have any PK, Unique key it should unique across your source databases(all 4).Or on the subscription database create the table without the constraints.
Yes, that is the idea. In the case of a replication conflict PostgreSQL has only two options:
cancel the query
delay the application of replicated changes.
Setting max_standby_streaming_delay to -1 will delay replication indefinitely long.
There are ways to reduce replication conflicts:
Set hot_standby_feedback = on to remove replication conflicts caused ...
You could create a function-based indeed on the sdo_geometry column in this view. This might help the query speed.
If you can't create an MV (have you tried?), create a table (create table as select...), then write a procedure that executes a MERGE from the view in to the table, then schedule it to execute every X minutes using dbms_job or dbms_scheduler.
In my case, I had the same error message.
My error was that I had installed my new server (Windows 2019 Std) in English (Canada) and that when I updated the Snapshot via the agent, I always had the error "An unspecified error had occurred in the native SQL Server connection component. ".
To correct the problem, I reinstalled my server in English (United ...