On 10/21/21 16:23, Rory Falloon wrote:
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
Well, the difference between "drop table" and "delete" is that "drop
table" is transaction on the data dictionary. PostgreSQL has
transactional DDL, which means that "drop table" can be rolled back, so
that transaction has to be protected by WAL, but it is just the log of
the dictionary tables. There is the 3rd option, called "truncate" which
is also a DDL which creates a new empty table with the same description
and discards the old files. Both 'drop" and "truncate" will not
generate much WAL logs. On the other hand, "delete" is a regular DML
transaction which will generate logs needed to recover the 200GB table.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
