Postgres: my “data/backup” directory is way too large

Posted on

Question :

In my postgres 9.3 data directory, there exists a subdir called “backup” that is 6.1G compared to my pg_xlog which is only 130M. I understand that pg_xlog is the WAL logs, so what is in the backup?

There are files there from a month ago so not sure why they’re there, if I need them, and how I can prevent them from being created if not needed.

Any explanations?

Answer :

Here’s a du -sh * output of a typical PostgreSQL 9.3 data directory:

59G     base
47M     global
20M     pg_clog
28K     pg_multixact
80K     pg_notify
4.0K    pg_serial
4.0K    pg_snapshots
4.0K    pg_stat
368K    pg_stat_tmp
168K    pg_subtrans
4.0K    pg_tblspc
4.0K    pg_twophase
1.1G    pg_xlog
4.0K    postmaster.opts

Notice that there’s no backup directory: a PG instance does not need or create such a directory. It had to be created by a person or a script that is not part of PostgreSQL itself.

Since you mention pg_xlog, if the files in this directory follow the naming convention of WAL files (24 numbers in hexadecimal in a 3×8 layout) found in pg_xlog, it’s plausible that they’re WAL files copied for continuous archiving. Check the archive_command in postgresql.conf.

In this case, the name backup for the target directory make sense, but its location doesn’t. Such backups are useful for disaster recovery when the data directory has disappeared, so obviously the data directory itself is the last place where you want such backups.

Leave a Reply

Your email address will not be published. Required fields are marked *