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');
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.

  • Are you creating any WAL traffic? – Peter Eisentraut Jan 13 '11 at 7:22
  • Only one or two insert statements at a time. That's why I set archive_timeout = 30s. My understanding is that that config will cause the current WAL file to be archived every 30 seconds. In any case, I wouldn't expect the pg_stop_backup command to just wait forever if there's no activity. – Whatsit Jan 13 '11 at 16:34

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.

  • This was the problem. I didn't realize it was trying to archive as user postgres -- I was running my tests as root which of course has access to everything. – Whatsit Jan 13 '11 at 16:54

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.

  • The touch command simply creates an empty file with the path specified (or updates the 'last modified' time if it already exists). I'm doing it to simplify the troubleshooting process. I did try it with appropriate copy commands using %f and %p. Updated question to make this clear. – Whatsit Jan 13 '11 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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