WAL streaming and dropping a large table

From: Rory Falloon <rfalloon(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: WAL streaming and dropping a large table
Date: 2021-10-21 20:23:44
Message-ID: CANP_6+PuOvRxOrmEZH74vfRiQ00WtiJ3AQfd_rZ7507iuJkHPA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

My postgres server setup is thus: a production database, which is using WAL
streaming (hot standby) to four other hosts of various latencies. I Have a
table that is ~200GB which has been backed up, and now I Want to remove it.
The table is not in use, it is a child table to a parent table that _is_ in
use but I foresee no issues here. The total DB size is roughly 300GB. The
main reason for needing to remove it is to recover the space back on the
partition which is humming away at 88% usage, and the table I wish to drop
is better off in an archive somewhere.

I just removed around 10% of it with 'delete from', which of course
cascaded to the replication hosts. This increased the size of my pg_xlog
folder (from ~3GB to ~6.5GB) which of course increased my partition usage.
Obviously this is expected, but I wonder what would happen if I had issued
the 'drop table'?

I expect the nature of enabling max_replication_slots would mean the
database would retain the wal segments until all have caught up; it could
take quite a long time to 'catch up' after the 'drop table' command? It
took about 10 minutes before the pg_xlog folder size had 'settled down' to
what I normally see as default which is ~3GB.

> wal_keep_segments is defined as 128,
> wal_max_size is not defined,
> max_replication_slots is enabled

I'd prefer to use 'drop table' as it would recover the data immediately,
but not if it will impact the production database in a way that the
partition will become full, which defeats the purpose. Is it advisable to
move the pg_xlog folder to another volume on the system with more space
(which I have) and symlink - obviously a stop & start of the db required -
and then let the WAL archives fill up as need be? Or am I missing something
obvious (likely)

Thanks

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2021-10-21 20:36:17 Re: Looking for a doc section that presents the overload selection rules
Previous Message David G. Johnston 2021-10-21 20:19:13 Re: Looking for a doc section that presents the overload selection rules