0

Local pg_wal is cleaning up like it should. And pg_wal\archive_status has all the file with .done (Example: 0000000100000004000000DE.done). Archive_mode is set to on and archive_command is copying to a data domain. I'm also running pg_basebackup nightly at 11:00pm (still need to write batch to clean anything older than x days). Requirement is to be able to restore to anytime since last backup; which in my case will be last night.

The problem that I'm trying to resolve now is cleaning up the archive_command data domain. It doesn't look like the files had been cleaned up since I set it up 1/11/2024 (per advise from PostgreSQL 16 pg_wal folder vs pg_wal\archive_status). Will PostgreSQL automatically cleanup the data domain archive files? The files I have are 16.0MB (example: 0000000100000000000000F9) with a (0000000100000000000000F9.00000380.backup) during pg_basebackup time at 11:00pm.

Will I have to write a .bat to delete those files?

Thank you

1
  • Perhaps if you used a program designed for the purpose, such as Barman, you wouldn't need to roll your own scripts.
    – mustaccio
    Feb 21 at 15:07

2 Answers 2

0

PostgreSQL doesn't take care of your archived WAL segments. It doesn't even know they exist — all it does is execute the archive_command.

You can use pg_archivecleanup with the file from the oldest backup you may need to recover:

pg_archivecleanup /archived/wal/location 0000000100000000000000F9.00000380.backup

or you can simply have a regularly scheduled job that removes all archived WAL that is older than X days.

4
  • A little weird to me - knows to execute archive_command ; but, doesn't know they exists for clean up. Weird to me anyway. Appreciate the confirmation. Thank you.
    – Student
    Feb 22 at 14:40
  • What's the archive_cleanup_command on postgresql.conf used for?
    – Student
    Feb 22 at 14:53
  • Never mind. - archive_cleanup_command is for hot_standby replica server?
    – Student
    Feb 22 at 15:29
  • @Student Right. Feb 22 at 15:42
1

Appreciate your suggestions mustaccio. We just have the one dev server and one prod server to support one 3rd party software. Management doesn't see this growing for the next 5 years. Vendor of the 3rd party doesn't even provide any support. We are primarily Oracle environment.

Got pg_archivecleanup working manually based on https://documentation.tricentis.com/qtest/9910/en/content/qtest_onpremise/advanced_server_administration/continuous_wal_archiving_for_windows.htm

Will research on automating it via postgresql.conf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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