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