1

I'm a complete newbie with PostgreSQL - please forgive my gaffs - and have inherited a service using a master-slave cluster at the backend.

Currently on the slave, the disk is filling up with WAL files in the archive directory.

On a slave (replica) I presume the archived WAL files been applied to the local instance of the database since there are files in pg_xlog with current filestamps.

Q1: Is this correct?

Q2: What is the criteria by which the system decides that a WAL file is eligible to be moved from pg_xlog to the archive?

The backup/restore model is predicated around filesystem snapshots - so a restore operation would rely on crash recovery. I infer from this that I should only need the artchived WAL files since the last file-system snapshot to be available (although keeping at least 2 generations would be a better idea).

Q3: Is this correct?

Improve this question

3 Answers 3

Reset to default
4

Who is writing to the replica's archive directory, the master (via scp or something like that) or the replica?

The replica only archives if archive_mode=always. If it just set to 'on', then only the master archives.

What is the criteria by which the system decides that a WAL file is eligible to be moved from pg_xlog to the archive?

They are never moved. They are copied to the archive. And then at some later point (after at least a checkpoint), they are deleted/overwritten from pg_xlog/pg_wal. The criteria for deletion are all of: a successful checkpoint/restartpoint, wal_keep_segments satisfied, all replication slots (if any) satisfied, archive_command (if in use) returns success.

The backup/restore model is predicated around filesystem snapshots - so a restore operation would rely on crash recovery.

Who are you trying to restore here, the master or the replica? I just recreate a replica if it gets lost, but some people find that too slow and want to be able to restore it. And some people wouldn't recovery the master at all, they would just promote the replica.

I infer from this that I should only need the artchived WAL files since the last file-system snapshot to be available (although keeping at least 2 generations would be a better idea).

Unless your file-system snapshot has itself been backed up somewhere, I would assume it is no longer available to be used after a major crash. So you need all the WAL since the beginning of the most recent snapshot which has itself been backed up offsite. If you want to retain the ability to do PITR, you need more than that.

Improve this answer
1

PostgreSQL will never remove archived WAL files, you have to do that yourself.

If you use the archive only for log shipping to the standby, a convenient way to get rid of already consumed files is to set archive_cleanup_command on the standby to pg_archivecleanup /path/to/walarchive %r. Then the recovery process will delete all archied WALs it doesn't need any more.

Improve this answer
2
  • Sorry if I was not clear - when does PG move a WAL file from pg_xlog to archive?
    – symcbean
    Nov 14, 2019 at 13:59
  • That is covered by Jeff Janes' answer. I wasn't sure if that was what you wanted to know.
    – Laurenz Albe
    Nov 14, 2019 at 16:09
0

Looking at the documentation, the WAL files aaccumulate in the pg_xlog directory up to the limit defined in wal_keep_segments.

Rather worryingly, the creation, archiving and replication of WAL files appear to be autonomous processes.

Improve this answer

Your Answer

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

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