Обсуждение: PITR archive_timeout Command
Hi,
I have a basic and quick question related to "archive_timeout" command in
PITR.
I've set "archive_timeout" to 1800 seconds (30 minutes) in
"postgresql.conf", which means WAL archives are generated every 30 minutes.
So, if for an example my WAL archives are generated at the following time:
9:00 am
9:30 am
10:00 am
10:30 am
11:00 am, etc.
I specify PITR recovery time at 10:15 am in "recovery.conf". My question
is, to what time my database is recovered back? At which stage of the
following my database is reverted back:
1) Exactly the database was at 10:15 am
2) Exactly the database was at 10:00 am, because the last WAL archive
before 10:15 am was at 10:00 am.
Regards,
Gnanam
--
View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp24788681p24788681.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
1) Exactly the database was at 10:15 am From http://www.postgresql.org/docs/8.3/static/continuous-archiving.html "There is nothing that says we have to replay the WAL entries all the way to the end. We could stop the replay at any point and have a consistent snapshot of the database as it was at that time. Thus, this technique supports point-in-time recovery: it is possible to restore the database to its state at any time since your base backup was taken." -- Ian. On Mon, Aug 3, 2009 at 11:50 AM, Gnanam<gnanam@zoniac.com> wrote: > > Hi, > > I have a basic and quick question related to "archive_timeout" command in > PITR. > > I've set "archive_timeout" to 1800 seconds (30 minutes) in > "postgresql.conf", which means WAL archives are generated every 30 minutes. > So, if for an example my WAL archives are generated at the following time: > 9:00 am > 9:30 am > 10:00 am > 10:30 am > 11:00 am, etc. > > I specify PITR recovery time at 10:15 am in "recovery.conf". My question > is, to what time my database is recovered back? At which stage of the > following my database is reverted back: > 1) Exactly the database was at 10:15 am > 2) Exactly the database was at 10:00 am, because the last WAL archive > before 10:15 am was at 10:00 am. > > Regards, > Gnanam > -- > View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp24788681p24788681.html > Sent from the PostgreSQL - admin mailing list archive at Nabble.com. > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
Hi Ian, Ian Lea wrote: > > "We could stop the replay at any point and have a > consistent snapshot of the database as it was at that time. Thus, this > technique supports point-in-time recovery: it is possible to restore > the database to its state at any time since your base backup was > taken." > I got your point, again I've another simple question which I still don't understand clearly. Can I set my "archive_timeout" to 3600 seconds (1 hour), so that atleast I save my WAL archive storage space, because if it is set to 1800 seconds (30 minutes), it is fast filling. Setting it in this way affect the PITR recovery process to consistent snapshot as the database was at that time? Will I be able to recover my database upto 10:15 am? -- View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp24788681p24789154.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Essentially, my question here is 1) What is the advantage / disadvantage of setting "archive_timeout" command to too small or too high value? 2) What is the impact of setting this value during PITR recovery process? -- View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp24788681p24789235.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
> 1) What is the advantage / disadvantage of setting "archive_timeout" command > to too small or too high value? The advantage of setting it high is that you'll use less disk space and have fewer files to archive. The disadvantage of setting it high is that you might lose more data. In your 30 minute example you could recover to 10:15 any time after 10:30, because a log file was archived at 10:30. If archive_timeout was set to 1 hour the log file wouldn't have been written till 11:00 and you would only have been able to roll forward to 10:00. > 2) What is the impact of setting this value during PITR recovery process? You mean whilst recovering the database? I'm not aware of it having any relevance during recovery. -- Ian.
Thank you Ian. I'm clear now. -- View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp24788681p24790968.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
Ian Lea <ian.lea@gmail.com> wrote: > The advantage of setting it high is that you'll use less disk space > and have fewer files to archive. Although you can mitigate the space problem by using pg_clearxlogtail (combined with gzip) or pglesslog from pgfoundry. -Kevin
Ian Lea wrote: > > The advantage of setting it high is that you'll use less disk space > and have fewer files to archive. > > The disadvantage of setting it high is that you might lose more data. > This is not *entirely* true. The archive_timeout setting only indicates the time at which a new WAL archive is forced, no matther how much data has been written. If a WAL file fills up before the timeout is reached, it will fire the archive_command immediately and the timeout "timer" is reset. For example, we have a primary database instance where we see the archive_command fire every 1-2 minutes during peak, but our archive_timeout is set to 15 minutes. This ensures that the secondary database instance is getting updated even when there's not enough write traffic to fill up a WAL file. --Matthew Wood -- View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp24788681p24923536.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.