Skip site navigation (1) Skip section navigation (2)

Re: WAL FILES

From: "Ashish Karalkar" <ashish(dot)karalkar(at)info-spectrum(dot)com>
To: "Khan, Mahmood Ahram" <Mahmood(dot)Khan(at)in(dot)unisys(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: WAL FILES
Date: 2007-05-18 05:26:27
Message-ID: 005601c7990d$1acbfff0$170211ac@LIONKING.COM (view raw or flat)
Thread:
Lists: pgsql-admin
I give you idea to reduce archive time out parameter just to check wether  archiving is working or not.

Yes you are right,PostgreSQL will generate 16 MB of WAL file each time. So if u keep para archive timeout to 0 then after filling 16 MB data in WAL file postgre will switch over to new file.

From Section 23.3.1. Setting up WAL archiving

"The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are ended early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout - it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. "


Hope this will help

With Regards
Ashish...


  ----- Original Message ----- 
  From: Khan, Mahmood Ahram 
  To: Ashish Karalkar 
  Sent: Friday, May 18, 2007 10:49 AM
  Subject: RE: [ADMIN] WAL FILES


  One thing which I observed in PostgreSQL WAL files archiving is that it archives 16mb file at a given timeout period. 

  Example I gave 25 seconds, it is generating each 16mb file after every 25 sec. If there are no transaction & entries also.

  With this performance will also be down & space will also be utilized. Instead it should make archives when there is data, not on time basis.

  For this I think I have to make timeout parameter 0. Means disabling the force feature for archiving.

   

   

  Thanks & Best Regards

   

  M.AHRAM KHAN 


------------------------------------------------------------------------------

  From: Ashish Karalkar [mailto:ashish(dot)karalkar(at)info-spectrum(dot)com] 
  Sent: Friday, May 18, 2007 10:25 AM
  To: Khan, Mahmood Ahram
  Cc: pgsql-admin(at)postgresql(dot)org
  Subject: Re: [ADMIN] WAL FILES

   

  take care to inclue a parameter in the copy command that will make sure the archived WAL will not be overwritten

   

   

  for your reference(on Linux)

   

   

      "It is important that the archive command return zero exit status if and only if it succeeded. Upon getting a zero result, PostgreSQL will assume that the WAL segment file has been successfully archived, and will remove or recycle it. However, a nonzero status tells PostgreSQL that the file was not archived; it will try again periodically until it succeeds. 

  The archive command should generally be designed to refuse to overwrite any pre-existing archive file. This is an important safety feature to preserve the integrity of your archive in case of administrator error (such as sending the output of two different servers to the same archive directory). It is advisable to test your proposed archive command to ensure that it indeed does not overwrite an existing file, and that it returns nonzero status in this case. We have found that cp -i does this correctly on some platforms but not others. If the chosen command does not itself handle this case correctly, you should add a command to test for pre-existence of the archive file. "

   

    ----- Original Message ----- 

    From: Khan, Mahmood Ahram 

    To: Ashish Karalkar 

    Sent: Friday, May 18, 2007 9:07 AM

    Subject: RE: [ADMIN] WAL FILES

     

    Thanks Ashish,

     

    Its working fine now. And I set the archive timeout parameter to 25. (i,e 25 seconds).

     

    So Thanks once again.

     

     

     

     

    Thanks & Best Regards

     

    M.AHRAM KHAN 


----------------------------------------------------------------------------

    From: Ashish Karalkar [mailto:ashish(dot)karalkar(at)info-spectrum(dot)com] 
    Sent: Thursday, May 17, 2007 6:26 PM
    To: Khan, Mahmood Ahram
    Subject: Re: [ADMIN] WAL FILES

     

    I think u should try

     

    archive_command = ' copy  %p d:\archive\%f '

     

     

    I hope this should work.

    I am not  windows user but still i think.

    Please let me know if it works.

     

    set the archive timeout para to lower value to see it works or nt immediately.

     

    With regards

    Ashish...

      ----- Original Message ----- 

      From: Khan, Mahmood Ahram 

      To: pgsql-admin(at)postgresql(dot)org 

      Sent: Thursday, May 17, 2007 4:42 PM

      Subject: [ADMIN] WAL FILES

       

       

      Hi All,

       

      I am archiving PostgreSQL Database. I want to copy all my WAL files from pg_xlogs folder to d:\archive

      I am using PostgreSQL 8.2 on Windows XP.

      And my archive_command looks like this      ( archive_command  copy c:\Program Files\PostgreSQL\8.2\data\pg_xlog d:\archive\  )

      But it is loading the files in pg_xlogs folder itself. So Can anybody have idea about this.

       

       

      Thanks & Regards

       

      M.AHRAM KHAN

       

       

       

In response to

pgsql-admin by date

Next:From: Ashish KaralkarDate: 2007-05-18 05:29:51
Subject: Re: Postgres error
Previous:From: Ritu KhetanDate: 2007-05-18 05:24:05
Subject: Re: Postgres error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group