1

I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and recovers very slowly. Periodic CHECKPOINT's subsequently help to kick the xlog clean up into action. However, this laborious severely limits the amount of data I can shift in a short space of time. Upgrades and restarts are very tricky since the database records continuous long running collections from various data sources.

My gut feeling is that SET TABLESPACE prevents updates from WAL being committed, but this doesn't make much sense...

Any help or insight greatly appreciated!

1

When you move a table to a new table space and your wal_level is above "minimal", then the entire contents of the table are copied to the WAL stream. That is why pg_xlog grows quickly. Shrinking slowly could simply be because your archive_command can't keep up, and WAL files can't be removed until they are archived if archiving is on. What are your settings for wal_level, archive_mode, and archive_command?

  • Hi @jjanes - thanks! I'm running a warm standby so: wal_level = replica, archive_mode = on, and archive_command = 'rsync -a %p [privatedbserver]:/var/lib/pgsql/9.6/backup/%f' Is there anyway around that? I know it's recommended to keep WAL on a separate , reasonably sized partition... – simonw Oct 16 '18 at 15:43
  • Can you tell if rsyncing the WAL file is a bottleneck? But if you have stringent uptime requirements, I think your only real solution is to avoid needing to do set tablespace in the first place. For example, use a storage system that can dynamically resize disk arrays and partitions. – jjanes Oct 17 '18 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.