my topology/environment is :
test linux , pgsql 10.13 (subscription) <--- prod linux, pgsql 10.13 (publication)
In order to connect to our accounting system (ms sql) , I needed
to do cross-db queries, so I setup freetds (1.1.42) and
tds_fdw-2.0.1 and tested them on the test system (subscription
node). This involved the IMPORT of some 1000 ms sql tables in a
dedicated schema, plus a few others in a second dedicated schema.
All went fine, most tests worked nice, so I thought of installing
tds_fdw in the prod host. Needless to say, the new mssql dedicated
schemas did not involve any logical replication . After importing
the 1000 tables in prod with,
IMPORT FOREIGN SCHEMA dbo EXCEPT ("....") FROM SERVER
mssql_bdynacom_srv INTO mssql_bdynacom OPTIONS (import_default
after 15 mins (wal_sender_timeout / wal_receiver_timeout) I started getting in the test node :
 5ee085dc.1d64a 2020-06-16 17:10:26.450 EEST @ line:5
ERROR: terminating logical replication worker due to timeout
in the meantime the "wal sender" on the prod node is idle. And
seems stuck. Apparently the addition of those 1000 foreign tables
messed up with the replication slot. I tried to move forward the
replication slot, but even right after restarting the two systems
, I can't seem to find any handle to call :
So, I am pretty much at loss here . Go the guarantied way : drop
the two mssql schemas (i.e. revert to pre-incident state) and
re-doing the whole subscription node initialization and setup from
scratch seems like a possible scenario but I'd like to avoid that,
logical replication works fine for two years, and it will take
maybe days to catch up, I am not at the office, I work from home
and this makes things worse. Another option is to leave the
tds_fdw and the foreign table definitions and re-do the logical
replication setup. Please help!
just an update to this.
On 16/6/20 7:04 μ.μ., Achilleas Mantzios wrote:
apparently I was looking in the wrong node arghhh. It should have
been the subscriber node. Anyways.
I checked the number of tables the IMPORT FOREIGN SCHEMA defined
: over 13000 . So in one transaction, I got 13000+ implicit CREATE
FOREIGN TABLE definitions. I overcame this by altering the
subscriber to use a new replication slot, created by hand the new
replication slot on the publisher side, and had to kill -9 the old
wal sender , (otherwise the db would not restart, and of course
the old repl slot would not be dropped). Then I was able to drop
the old replication slot, and finally see the space in data/pg_wal
decreasing at last. So the 1000 tables figure was a lie, because
\d lied, this is what it reported, this is what I naively
reported. The true figure was about 13000+ tables.
So all in all I lost about a little less than a day worth of data (not the best experience I gotta say).
Bottom line : this is clearly a bug. Also wal_sender_timeout didn't seem to work either. This wal sender should have exited (after 15 mins) as no receiver was connected to it. I didn't have the resources to test how it would behave in a more generic foreign table conf (e.g. pgsql_fdw), to test if it would still give problems.
|Free forum by Nabble||Edit this page|