11

I have found myself in a situation where the pg_xlog contains 60GB of data due to a wrong configuration option set.

I had setup the wal_level, archive_mode=on and archive_command for barman which then got uninstalled. Can I simply comment these options as per default postgresql.conf config file and delete the files in the pg_xlog directory?

What is the correct procedure to remove these files in order to avoid database corruption?

Improve this question
3
  • stackoverflow.com/a/41161495/330315
    – user1822
    Commented Mar 4, 2017 at 8:05
  • 3
    I think pg_archivecleanup would have been another option if you check the "Latest checkpoint's REDO WAL file" from pg_controldata
    – user1822
    Commented Mar 4, 2017 at 8:11
  • I did read about that but couldn't find the pg_controldata command. It's not present in my installation (Debian)
    – Fabrizio Mazzoni
    Commented Mar 4, 2017 at 10:55

2 Answers 2

Reset to default
8

Managed to solve it quite simply. Stopped the db, commented the options in postgresql.conf and restarted. PostgreSQL took care of deleting everything automatically.

Improve this answer
4
  • 2
    Correct. Congratulations, you didn't corrupt your database! pg_archivecleanup would also be OK if you didn't have WAL archiving (barman, etc) or streaming replicas.
    – Craig Ringer
    Commented Mar 5, 2017 at 11:26
  • What all are the options did you comment in postgresql.conf file ?
    – Raviteja
    Commented Nov 8, 2017 at 10:33
  • The ones stated in the question.
    – Fabrizio Mazzoni
    Commented Nov 8, 2017 at 10:35
  • Actually, my PG started outputting 'archive command failed' after I used pg_archivecleanup. I had to nevertheless stop the DB, comment-out those params and restart.
    – gilad905
    Commented Mar 21, 2018 at 18:07
0

In cases when the database does not clean this directory automatically, you can use pg_resetxlog: https://www.postgresql.org/docs/9.2/app-pgresetxlog.html

This command was renamed to pg_resetwal in the latest database version: https://www.postgresql.org/docs/15/app-pgresetxlog.html

https://www.postgresql.org/docs/15/app-pgresetwal.html

Be very careful with this command.

Create a full database backup before such a procedure because this command might break some DB transactions.

It should be run in the PostgreSQL directory. Probably, this command will also prompt you to add some missing files or delete a lock file before running.

Improve this answer
3
  • So bold of you to suggest this; have you ever tried it? My guess would be, you haven't, since you're linking to the 9.2 version of the docs.
    – mustaccio
    Commented Apr 13, 2023 at 19:25
  • To anyone crazy enough to follow this advice, be sure to read the documentation page in full before proceeding.
    – mustaccio
    Commented Apr 13, 2023 at 19:27
  • yes, I tried it in postgres 9. my xlog grew very big due to some database crashes. This was the only way to fix its size. Database was safely dumped and restored after that. I got no issues at all. I don't understand your panic - this method was also tested by my co-worker before and guess what, no issues.
    – t7e
    Commented Apr 13, 2023 at 20:21

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.