1

I am not a DBA but i am using postgresql for production server and i am using postgresql 10 database. I am using Bigsql and i started replication of my production server to other server and on replication server everything is working but on my production server their is no space left. And after du command on my production server i am getting that pg_wal folder have 17 gb file and each file is of 16 mb size.

After some google search i change my postgresql.conf file as:

wal_level = logical
archive_mode = on
archive_command = 'cp -i %p /etc/bigsql/data/pg10/pg_wal/archive_status/%f'

i install postgresql 10 from Bigsql and did above changes.

After changes the dir /pg_wal/archive_status had 16 gb of log. So my question is that should i delete them manually or i have to wait for system delete them automatically. And is that if i write archive_mode to on should that wal file getting removed automatically??

Thanks for your precious time.

Manish Yadav
  • 435
  • 2
  • 8
  • 24

1 Answers1

4

This depends on how you do your backups and whether you'd ever need to restore the database to some point in time.

Only a full offline filesystem backup (offline meaning with database turned off) or an on-line logical backup with pg_dumpall will not need those files for a restore.

You'd need those files to restore a filesystem backup created while the database is running. Without them the backup will fail to restore. Though there exist backup solutions that copy needed WAL files automatically (like Barman).

You'd also need those files if your replica database will ever fall behind the master for some reason. Or you'd need to restore the database to some past point-in-time.

But these files compress pretty well - should be less than 10% size after compression - you can write your archive_command to compress them automatically instead of just copying.

And you should delete them eventually from the archive. I'd recommend to not delete them until they're at least a month old and also at least 2 full successful backups are done after creating them.

Tometzky
  • 22,573
  • 5
  • 59
  • 73
  • First thank you for your response @Tometzky . I am creating daily backup using pg_dump command and store them on other server.So i want to know that the files in pg_wal are automatically getting deleted by system when i turn on the archive mode or i have to do that manually. – Manish Yadav Nov 19 '17 at 17:36
  • Files from pg_wal are deleted. But not from it's subfolders - you're actually not supposed to create any subfolders there, so your archive_command should use a different target folder. – Tometzky Nov 19 '17 at 18:30
  • So if i remove `archive_mode = on archive_command = 'cp -i %p /etc/bigsql/data/pg10/pg_wal/archive_status/%f'` my pg_wal log are getting removed automatically. – Manish Yadav Nov 19 '17 at 18:43
  • Yes. And you can configure how much of them is stored with `max_wal_size` configuration parameter. The higher value the lower checkpoint frequency - you should aim that most of your checkpoints are timeout forced, not `max_wal_size` forced. Enable `log_checkpoints` to find out. – Tometzky Nov 19 '17 at 19:36
  • So i change my postgresql.conf file to `checkpoint_completion_target 0.5 checkpoint_flush_after 32 checkpoint_timeout 300 checkpoint_warning 30 min_wal_size 80 max_wal_size 1024` – Manish Yadav Nov 20 '17 at 06:14