error: database is not accepting commands to avoid wraparound data loss in database ....

Hi,
I would very much appreciate any help / advise with this problem which
includes logical replication and transaction IDs wraparound error:

We have production database collecting metrics data from web widgets -
hundreds of millions of records per day, most of them inserted in separate
transactions. Data are partitioned by day, we have 10 different parent
tables for different types of metrics.

Collected data are also copied into our main data warehouse database.
Previously we synchronized daily partitions 1x per day but this became very
heavy task because partitions have like 20 - 40 GB each. In some days even
more.... So even with parallel run of dump-restore it took several hours to
synchronize data causing quite a big load on both replica dedicated just
for sync and data warehouse.

Therefore I implemented logical replication to shift data continuously from
collection database into our main data warehouse database + one other which
contains only some data. Publication and subscription contained always
partitions for tomorrow, today + 2 days backwards because sometimes we
experience quite big delays in delivering of data.

It worked absolutely great for some time but in last several days we had
really heavy spikes in usage of widgets so we collected much more records
and we ended up with both data warehouse database being blocked by
transaction IDs wraparound error.

It all happened very quickly during the night and once we got first alerts
databases were already in troubles. We so far did not have alerts for
messages "database "xxxxxx" must be vacuumed within xxxxxxx transactions"
because we so far never encountered them. It would help of course - I will
implement them once I solve main problem....

We have so far never seen this wraparound problem before. We also never had
it on collecting database even with this enormous amount of transactions.

But it looks like subscriptions on data warehouse databases conflicted with
autovacuum of partitions. Logical replication most likely transfers all
transactions 1:1 so suddenly we had incredible number of separate
transactions on data warehouse - which never happened before.

So we can now only select data from both data warehouse databases but we
cannot do anything else - we always get message:

error: database is not accepting commands to avoid wraparound data loss in
database "xxxxx"

Disabling and dropping subscription does not work either - same message.

Because logical replication was not running we have seen huge accumulation
of unarchived WAL segments on collecting database - they have been blocked
by logical replication slots. Because I needed to avoid any problems on
collecting database I decided to drop all publications and logical
replication slots there. This immediately solved the problem with WAL
segments.

I checked solution for wraparound error and since all sources I found
suggested to do VACUUM under single-user mode I decided to test it on
second smaller data warehouse database (~16TB of data) which is not so
crucial for us to have it still running since I knew it will be
inaccessible during that time. Vacuum is running for 24 hours already on
this database and I can only wait for it to finish....

But our main data warehouse database is even bigger ~22TB of data and I
cannot shut it down for several days. Therefore I started to prepare
replacement for it - since we can still read data from it I can copy them
somewhere else. I needed it also for some daily aggregations - so new
database currently contains only several days of data. Question is what to
do next....

Data warehouse database uses standard settings for vacuuming:

autovacuum on
autovacuum_analyze_scale_factor 0.1
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 60
autovacuum_vacuum_cost_delay 20
autovacuum_vacuum_cost_limit -1
autovacuum_vacuum_scale_factor 0.2
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1
log_autovacuum_min_duration -1
vacuum_cleanup_index_scale_factor 0.1
vacuum_cost_delay 0
vacuum_cost_limit 200
vacuum_cost_page_dirty 20
vacuum_cost_page_hit 1
vacuum_cost_page_miss 10
vacuum_defer_cleanup_age 0
vacuum_freeze_min_age 50000000
vacuum_freeze_table_age 150000000
vacuum_multixact_freeze_min_age 5000000
vacuum_multixact_freeze_table_age 150000000

Can I do something else with main data warehouse database than just this
painfully slow VACUUM? If I would change some settings for freeze would it
help in way that database would accept commands again so I would be able to
drop subscription and vacuum it in normal way?

Thank you very much in advance for any help / advice you might have.

Josef Machytka
Berlin