Thread: Incrementally Updated Backups

Incrementally Updated Backups

From
Gabe Nell
Date:
Hello,

I'm interested in the "incrementally updated backups" scenario
described in section 25.6 of the Postgres 9 documentation. I've
configured streaming replication for my warm standby server.

Step 2 in this procedure is to note pg_last_xlog_replay_location at
the end of the backup. However it seems like this requires hot standby
to be configured; otherwise there is no way of connecting to the
standby machine to make the required query. That does not seem clear
from the documentation. Is there a way to get this without using hot
standby?

Thanks

Gabe

Re: Incrementally Updated Backups

From
Bruce Momjian
Date:
Gabe Nell wrote:
> Hello,
>
> I'm interested in the "incrementally updated backups" scenario
> described in section 25.6 of the Postgres 9 documentation. I've
> configured streaming replication for my warm standby server.
>
> Step 2 in this procedure is to note?pg_last_xlog_replay_location at
> the end of the backup. However it seems like this requires hot standby
> to be configured; otherwise there is no way of connecting to the
> standby machine to make the required query. That does not seem clear
> from the documentation. Is there a way to get this without using hot
> standby?

That section has been removed from the current 9.0 docs because we are
unsure it works.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: Incrementally Updated Backups

From
Gabe Nell
Date:
> That section has been removed from the current 9.0 docs because we are
> unsure it works.

Hmm. So the only way to make a consistent backup from a standby server
is to shut down the standby first? Or is even that problematic?

Would it change anything if we are able to guarantee that the
filesystem is snapshotted as a point-in-time snapshot by using LVM or
on Amazon EC2 by snapshotting an EBS volume?

Thanks

Gabe

Re: Incrementally Updated Backups

From
Bruce Momjian
Date:
Gabe Nell wrote:
> > That section has been removed from the current 9.0 docs because we are
> > unsure it works.
>
> Hmm. So the only way to make a consistent backup from a standby server
> is to shut down the standby first? Or is even that problematic?
>
> Would it change anything if we are able to guarantee that the
> filesystem is snapshotted as a point-in-time snapshot by using LVM or
> on Amazon EC2 by snapshotting an EBS volume?

I believe a snapshot of the standby is fine even if it is running, just
like on the master.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: Incrementally Updated Backups

From
"J. Roeleveld"
Date:
On Sunday 12 September 2010 00:43:19 Bruce Momjian wrote:
> Gabe Nell wrote:
> > > That section has been removed from the current 9.0 docs because we are
> > > unsure it works.
> >
> > Hmm. So the only way to make a consistent backup from a standby server
> > is to shut down the standby first? Or is even that problematic?
> >
> > Would it change anything if we are able to guarantee that the
> > filesystem is snapshotted as a point-in-time snapshot by using LVM or
> > on Amazon EC2 by snapshotting an EBS volume?
>
> I believe a snapshot of the standby is fine even if it is running, just
> like on the master.

How can you ensure the snapshot is in a consistent state if the server is
running?

If a snapshot is taken between 2 updates in a single transaction, only half of
this transaction is included in the snapshot.
I would never take an LVM (or similar) snapshot of an application that can't
be paused in a way to provide a consistent filesystem.

--
Joost

Re: Incrementally Updated Backups

From
Martijn van Oosterhout
Date:
On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote:
> How can you ensure the snapshot is in a consistent state if the server is
> running?
>
> If a snapshot is taken between 2 updates in a single transaction, only half of
> this transaction is included in the snapshot.
> I would never take an LVM (or similar) snapshot of an application that can't
> be paused in a way to provide a consistent filesystem.

That's the trick, the filesystem is always in a consistant state,
otherwise how could a database survive a power failure?

The trick is WAL, which ensure that changes are logged consistantly and
replays them if the database crashes.

If you take a snapshot the database will simply startup and replay the
log as if the machine crashed at the point. All committed transactions
appears anything uncommitted vanishes.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
>                                       - Charles de Gaulle

Attachment

Re: Incrementally Updated Backups

From
"J. Roeleveld"
Date:
On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote:
> On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote:
> > How can you ensure the snapshot is in a consistent state if the server is
> > running?
> >
> > If a snapshot is taken between 2 updates in a single transaction, only
> > half of this transaction is included in the snapshot.
> > I would never take an LVM (or similar) snapshot of an application that
> > can't be paused in a way to provide a consistent filesystem.
>
> That's the trick, the filesystem is always in a consistant state,
> otherwise how could a database survive a power failure?

This is something you want to try to avoid.
Recovery situations are not always reliable.

> The trick is WAL, which ensure that changes are logged consistantly and
> replays them if the database crashes.
>
> If you take a snapshot the database will simply startup and replay the
> log as if the machine crashed at the point. All committed transactions
> appears anything uncommitted vanishes.

Nice in theory.
Except backups can not be fully trusted if they rely on database recovery
mechanics as part of the restore process.

How certain can you be that the data you have in your backup will always
result to being able to recover 100%?

--
Joost

Re: Incrementally Updated Backups

From
Scott Marlowe
Date:
On Sun, Sep 12, 2010 at 7:39 AM, J. Roeleveld <joost@antarean.org> wrote:
> On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote:
>> On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote:
>> > How can you ensure the snapshot is in a consistent state if the server is
>> > running?
>> >
>> > If a snapshot is taken between 2 updates in a single transaction, only
>> > half of this transaction is included in the snapshot.
>> > I would never take an LVM (or similar) snapshot of an application that
>> > can't be paused in a way to provide a consistent filesystem.
>>
>> That's the trick, the filesystem is always in a consistant state,
>> otherwise how could a database survive a power failure?
>
> This is something you want to try to avoid.
> Recovery situations are not always reliable.

When hardware doesn't fsync properly, this can be a problem.

>> The trick is WAL, which ensure that changes are logged consistantly and
>> replays them if the database crashes.
>>
>> If you take a snapshot the database will simply startup and replay the
>> log as if the machine crashed at the point. All committed transactions
>> appears anything uncommitted vanishes.
>
> Nice in theory.
> Except backups can not be fully trusted if they rely on database recovery
> mechanics as part of the restore process.

Why?

> How certain can you be that the data you have in your backup will always
> result to being able to recover 100%?

100% certain if you test said restores often.  It's not uncommon to
use this method to bring up a slave which you then run pg_dump on to
see if you get any errors.

Re: Incrementally Updated Backups

From
Bruce Momjian
Date:
J. Roeleveld wrote:
> On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote:
> > On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote:
> > > How can you ensure the snapshot is in a consistent state if the server is
> > > running?
> > >
> > > If a snapshot is taken between 2 updates in a single transaction, only
> > > half of this transaction is included in the snapshot.
> > > I would never take an LVM (or similar) snapshot of an application that
> > > can't be paused in a way to provide a consistent filesystem.
> >
> > That's the trick, the filesystem is always in a consistant state,
> > otherwise how could a database survive a power failure?
>
> This is something you want to try to avoid.
> Recovery situations are not always reliable.

With Postgres, they are always reliable, unless your hardware isn't.

> > The trick is WAL, which ensure that changes are logged consistantly and
> > replays them if the database crashes.
> >
> > If you take a snapshot the database will simply startup and replay the
> > log as if the machine crashed at the point. All committed transactions
> > appears anything uncommitted vanishes.
>
> Nice in theory.
> Except backups can not be fully trusted if they rely on database recovery
> mechanics as part of the restore process.

> How certain can you be that the data you have in your backup will always
> result to being able to recover 100%?

If you don't want to trust our recovery, that is your decision.  We are
telling you others do trust it.

If you don't trust our crash recovery, perhaps you shouldn't trust
Postgres and consider another database that you trust more.

I am afraid you have used some untrustworth databases in your past ---
Postgres is not one of them.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: Incrementally Updated Backups

From
Scott Marlowe
Date:
On Sun, Sep 12, 2010 at 7:51 AM, Bruce Momjian <bruce@momjian.us> wrote:
> J. Roeleveld wrote:
>> Nice in theory.
>> Except backups can not be fully trusted if they rely on database recovery
>> mechanics as part of the restore process.
>
>> How certain can you be that the data you have in your backup will always
>> result to being able to recover 100%?
>
> If you don't want to trust our recovery, that is your decision.  We are
> telling you others do trust it.

Agreed.  If you can't trust your database to recover from a power
failure, you can't trust your database, period.

Re: Incrementally Updated Backups

From
"Joshua D. Drake"
Date:
On Sun, 2010-09-12 at 12:18 +0200, J. Roeleveld wrote:
> On Sunday 12 September 2010 00:43:19 Bruce Momjian wrote:
> > Gabe Nell wrote:
> > > > That section has been removed from the current 9.0 docs because we are
> > > > unsure it works.
> > >
> > > Hmm. So the only way to make a consistent backup from a standby server
> > > is to shut down the standby first? Or is even that problematic?
> > >
> > > Would it change anything if we are able to guarantee that the
> > > filesystem is snapshotted as a point-in-time snapshot by using LVM or
> > > on Amazon EC2 by snapshotting an EBS volume?
> >
> > I believe a snapshot of the standby is fine even if it is running, just
> > like on the master.
>
> How can you ensure the snapshot is in a consistent state if the server is
> running?
>
> If a snapshot is taken between 2 updates in a single transaction, only half of
> this transaction is included in the snapshot.
> I would never take an LVM (or similar) snapshot of an application that can't
> be paused in a way to provide a consistent filesystem.


You need to read up on things like WAL and MVCC.

Joshua D. Drake


>
> --
> Joost
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Re: Incrementally Updated Backups

From
Mikko Partio
Date:
> I'm interested in the "incrementally updated backups" scenario
> described in section 25.6 of the Postgres 9 documentation. I've
> configured streaming replication for my warm standby server.
>
> Step 2 in this procedure is to note?pg_last_xlog_replay_location at
> the end of the backup. However it seems like this requires hot standby
> to be configured; otherwise there is no way of connecting to the
> standby machine to make the required query. That does not seem clear
> from the documentation. Is there a way to get this without using hot
> standby?

That section has been removed from the current 9.0 docs because we are
unsure it works.


Is the feature (or the documentation) still being worked on, or is pg_dump the only way to take a backup of a warm standby while the database is running?

Regards

Mikko

Re: Incrementally Updated Backups

From
Scott Marlowe
Date:
On Mon, Sep 13, 2010 at 1:29 AM, Mikko Partio <mpartio@gmail.com> wrote:
>> > I'm interested in the "incrementally updated backups" scenario
>> > described in section 25.6 of the Postgres 9 documentation. I've
>> > configured streaming replication for my warm standby server.
>> >
>> > Step 2 in this procedure is to note?pg_last_xlog_replay_location at
>> > the end of the backup. However it seems like this requires hot standby
>> > to be configured; otherwise there is no way of connecting to the
>> > standby machine to make the required query. That does not seem clear
>> > from the documentation. Is there a way to get this without using hot
>> > standby?
>>
>> That section has been removed from the current 9.0 docs because we are
>> unsure it works.
>
> Is the feature (or the documentation) still being worked on, or is pg_dump
> the only way to take a backup of a warm standby while the database is
> running?

I don't think you can take a pg_dump of a warm standby without making
recover.  But I can't see why you can't use a snapshot to recover a
warm standby, since the file system will be just a base snapshot and a
bunch of wal files.

Docs on continuous archiving are here:

http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html

--
To understand recursion, one must first understand recursion.

Re: Incrementally Updated Backups

From
Mikko Partio
Date:
>> That section has been removed from the current 9.0 docs because we are
>> unsure it works.
>
> Is the feature (or the documentation) still being worked on, or is pg_dump
> the only way to take a backup of a warm standby while the database is
> running?

I don't think you can take a pg_dump of a warm standby without making
recover.  But I can't see why you can't use a snapshot to recover a
warm standby, since the file system will be just a base snapshot and a
bunch of wal files.

Sorry, I got confused with the terms. What I meant was 'hot standby', the new feature implemented in 9.0. I guess you can take a pg_dump out of a hot standby, right?

Regards

Mikko 

Re: Incrementally Updated Backups

From
"Joshua D. Drake"
Date:
On Sun, 2010-09-12 at 12:18 +0200, J. Roeleveld wrote:
> On Sunday 12 September 2010 00:43:19 Bruce Momjian wrote:
> > Gabe Nell wrote:
> > > > That section has been removed from the current 9.0 docs because we are
> > > > unsure it works.
> > >
> > > Hmm. So the only way to make a consistent backup from a standby server
> > > is to shut down the standby first? Or is even that problematic?
> > >
> > > Would it change anything if we are able to guarantee that the
> > > filesystem is snapshotted as a point-in-time snapshot by using LVM or
> > > on Amazon EC2 by snapshotting an EBS volume?
> >
> > I believe a snapshot of the standby is fine even if it is running, just
> > like on the master.
>
> How can you ensure the snapshot is in a consistent state if the server is
> running?
>
> If a snapshot is taken between 2 updates in a single transaction, only half of
> this transaction is included in the snapshot.
> I would never take an LVM (or similar) snapshot of an application that can't
> be paused in a way to provide a consistent filesystem.


You need to read up on things like WAL and MVCC.

Joshua D. Drake


>
> --
> Joost
>

--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


Re: Incrementally Updated Backups

From
RB
Date:
On Sep 12, 2:39 pm, jo...@antarean.org ("J. Roeleveld") wrote:
> On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote:
>
> > On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote:
> > > How can you ensure the snapshot is in a consistent state if the server is
> > > running?
>
> > > If a snapshot is taken between 2 updates in a single transaction, only
> > > half of this transaction is included in the snapshot.
> > > I would never take an LVM (or similar) snapshot of an application that
> > > can't be paused in a way to provide a consistent filesystem.
>
> > That's the trick, the filesystem is always in a consistant state,
> > otherwise how could a database survive a power failure?
>
> This is something you want to try to avoid.
> Recovery situations are not always reliable.
>
> > The trick is WAL, which ensure that changes are logged consistantly and
> > replays them if the database crashes.
>
> > If you take a snapshot the database will simply startup and replay the
> > log as if the machine crashed at the point. All committed transactions
> > appears anything uncommitted vanishes.
>
> Nice in theory.
> Except backups can not be fully trusted if they rely on database recovery
> mechanics as part of the restore process.
>
> How certain can you be that the data you have in your backup will always
> result to being able to recover 100%?
>
> --
> Joost
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Just to add that it is standard practice to use database recovery to
bring up a 'Hot Backup'. For example Oracle's default RMAN online
backup if used to restore from will put back the files it backed up
before recovering using the archived and current redo logs (ie the
postgres WAL). It is pretty standard practice to abort 'crash' the
database which is the equivalent to a machine power outage. Recovering
from your 'inconsistent' backup is identical to recovering from you
user generated 'abort'.

Re: Incrementally Updated Backups

From
Simon Riggs
Date:
On Sat, 2010-09-11 at 14:21 -0700, Gabe Nell wrote:

> Is there a way to get this without using hot standby?

Why would you want to avoid using hot standby?

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Development, 24x7 Support, Training and Services