Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: when will the archive_command be executed ?

    hello,

    I'm doing the postgresql backup with WAL archiving and I saw someone said that it might not be reliable enough to do backup only with archive_command automatically executed by the server itself because there might still be some complete and needed WAL files for recovery that have not been transferred by the archive_command when the server crashes(e.g. disk damage). Thus, we need to copy the WAL files in pg_xlog directory to another place periodically (like every 3 minutes) with additional script.

    So I wonder when the archive_command will be triggered or executed to transfer the WAL files to somewhere else, maybe not right after a new WAL file is finished I think. And need we additionally use script to copy the WAL files periodically ?

    Thanks ~

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    What is your archive_timeout configuration setting?

    Ref Continuous Archiving
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Sep 2010
    Posts
    2
    Quote Originally Posted by loquin View Post
    What is your archive_timeout configuration setting?

    Ref Continuous Archiving
    hi,

    I didn't set the archive_timeout in postgresql.conf, I think it's equivalent to turn it off, leaving it to be decided by the postgres server.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If you do not change the archive_timeout parameter, the WAL ONLY gets archived when it fills up (normally at the completion of 16 MB of changes.)

    If you DO set the archive_timeout parameter, the WAL gets closed and archived no later than once every archive_timeout seconds.

    Can you use an external script to copy the WAL files? Yes. But, you don't need to if you make judicious use of the archive_timeout parameter. Just remember that the WAL file will always be 16 MB in size, even though there may be only a few K stored in it...

    For PITR, you need to keep all WAL files between the most recent database backup and the present time. Once you perform another database backup, you can wipe the old WAL files you had stored.

    If you need to be SURE that WAL changes get committed, set the fsync configuration value to ON.
    Quote Originally Posted by pgDocs
    [The] PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods ... This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.
    Also, IF you decide to periodically copy the WAL file using an external script, rather than using pg's internally configured method (archive_timeout,) MAKE SURE that fsynch is turned on, else you run the risk of not copying all the WAL data for a the most recent transactions prior to the disk copy...
    Last edited by loquin; 09-27-10 at 16:29.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •