Home / server / Questions / 221791
Accepted
Whatsit
Asked: 2011-01-13 14:50:14 +0800 CST

PostgreSQL continuous archiving not running archive_command

  • 772

I've been trying to set up continuous archiving for a simple, test PostgreSQL 9.0 database, as per the documentation. In postgres.conf I've set:

wal_level = archive
archive_mode = on
archive_command = 'touch /home/myusername/backup/testtouch'
archive_timeout = 30s

...and restarted PostgreSQL. The file listed by touch never appears. I can manually run the touch command and it works as expected.
If I try to create a backup, it waits forever for the archive_command. In psql;

postgres=# SELECT pg_start_backup('touchtest');
pg_start_backup
-----------------
0/14000020 (1 row)

postgres=# SELECT pg_stop_backup();
NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be archived > WARNING: pg_stop_backup still waiting for all required WAL segments to be archived (60 seconds elapsed)
HINT: Check that your archive_command is executing properly. pg_stop_backup can be cancelled safely, but the database backup will not be usable without all the WAL segments.

What would cause this? How can I troubleshoot it?

Additional info: Running on CentOS 5.4. PostgreSQL 9.0.2 installed as root.

Update: I first tried archiving with both cp -i %p /home/myusername/backup/%f </dev/null and test ! -f /home/myusername/backup/%f && cp %p /home/myusername/backup/%f to match the manual. I reduced it to the simpler touch call for troubleshooting.

backup postgresql

2 Answers

  1. Best Answer

    That Postgres configuration looks right

    CentOS by default sets the user directory mode to 700 so check if that's actually the case and if you can touch that file using su as the root user

    su - postgres -c "touch /home/myusername/backup/testtouch"

    If that does work then try to use verbose logging in postgres and check the postgres log for further errors.

    • 5

  2. Your archive command needs to contain more information.

    From the PostgreSQL doco

    In archive_command, %p is replaced by the path name of the file to archive, while %f is replaced by only the file name. (The path name is relative to the current working directory, i.e., the cluster's data directory.)

    archive_command = 'copy "%p" "C:\server\archivedir\%f"' # Windows

    In summary, it doesn't look to me (not being familiar with CentOS' touch command) like you're including variables in your archive command script. Keep in mind that the filename of the log file that's going to be archived is forever changing. The archive command also needs to know where you're archiving the file to.

    • 0