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-tp2478868 1p24788681.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

1) Exactly the database was at 10:15 am

=46rom http://www.postgresql.org/docs/8.3/static/continuous-archivi ng.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 [at] 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 minute=
s.
> So, if for an example my WAL archives are =A0generated at the following t=
ime:
> =A0 =A0 =A0 =A09:00 am
> =A0 =A0 =A0 =A09:30 am
> =A0 =A0 =A0 =A010:00 am
> =A0 =A0 =A0 =A010:30 am
> =A0 =A0 =A0 =A011:00 am, etc.
>
> I specify PITR recovery time at 10:15 am in "recovery.conf". =A0My questi=
on
> is, to what time my database is recovered back? =A0At which stage of the
> following my database is reverted back:
> =A0 =A0 =A0 =A01) Exactly the database was at 10:15 am
> =A0 =A0 =A0 =A02) 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 [at] postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] 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-tp2478868 1p24789154.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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-tp2478868 1p24789235.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

> 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.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Thank you Ian. I'm clear now.
--
View this message in context: http://www.nabble.com/PITR-archive_timeout-Command-tp2478868 1p24790968.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Ian Lea <ian.lea [at] 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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

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-tp2478868 1p24923536.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin