Thread: Incrementally Updated Backups
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
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. +
> 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
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. +
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
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
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
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.
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. +
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.
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
> I'm interested in the "incrementally updated backups" scenarioThat section has been removed from the current 9.0 docs because we are
> 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?
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
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.
I don't think you can take a pg_dump of a warm standby without making>> 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?
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
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
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'.
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