I mean how to safely copy xlog files when postgresql is running? Any reply will be appreciated.

  • Why? What do you intend to achieve from doing this? The WAL archives are completely useless without a corresponding base backup. – Craig Ringer Jan 6 '14 at 13:52

The best (and recommended) way to do it is to configure the database server to copy them via the archive_command configuration variable. See also the chapter Setting up WAL Archiving.

If you are hell bent on copying them manually then another way might be to look at the contents of the archive_status subdirectory. When you see a file in this directory with the suffix .ready it means that the corresponding WAL file is completed and should be safe to copy. However I don't think this would happen unless WAL archiving was turned on in the first place - if it is not turned on then postgres will just delete the WAL files soon after they are written (which will not give you much chance to copy them!)

  • 1
    I'd go further, and say that the only safe way to copy WAL archives is with an archive_command except in the specific case of doing an initial database backup with pg_start_backup() and pg_stop_backup(), where a filesystem level copy is safe if you follow the rules in the documentation. These days it's much safer to use pg_basebackup for that, so you should never have to copy WAL manually. Ever. – Craig Ringer Jan 6 '14 at 13:51

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.