Re: is it safe to drop 25 tb schema with cascade option?

On 9/19/19 2:42 PM, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 9/19/19 2:24 PM, Julie Nishimura wrote:
>>> Or you meant we need to run vacuum on 'my_db_name' without parameters,
>>> that it runs for every table? I am just not sure how long it will take
>>> to run for 39 tb...:(
>
>> Not sure. The bottom line is you are running out of transaction ids and
>> if the txid counter wraps things get ugly. You could try vacuuming
>> individual non-system tables that have a lot of churn(UPDATES/DELETES)
>> and see if that buys you some ids.
>
> You're going to have to vacuum *all* of them to get the global freeze
> counter to advance, as it's basically just the min of the per-table
> pg_class.relfrozenxid values. It might be worth looking at
> age(relfrozenxid) to see which tables are furthest back, as those
> are the one(s) holding things up; but if you've not been running
> autovacuum then it's likely that they are all old.

So this is the part I need some clarification on. If you start vacuuming
the tables that are setting the min then that should buy you some time?

>
> Do NOT NOT NOT use VACUUM FULL here. It will not make things any
> better than a plain VACUUM, and it will take a lot longer ... time
> you maybe haven't got.
>
> regards, tom lane
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com