Re: [GENERAL] Incrementally Updated Backups
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
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'.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
> > >> 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: [GENERAL] Incrementally Updated Backups
On Mon, Sep 13, 2010 at 1:29 AM, Mikko Partio 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
> > > 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: [GENERAL] Incrementally Updated Backups
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sun, Sep 12, 2010 at 7:51 AM, Bruce Momjian 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sun, Sep 12, 2010 at 7:39 AM, 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. 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
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-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
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 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 signature.asc Description: Digital signature
Re: [GENERAL] Incrementally Updated Backups
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
> 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Incrementally Updated Backups: Docs Clarification
I'm about to begin playing with incrementally updated backups for a warm standby scenario, but I need some help understanding this paragraph in postgres terms. From 23.4.5 in the 8.2.3 docs: "If we take a backup of the standby server's files while it is following logs shipped from the primary, we will be able to reload that data and restart the standby's recovery process from the last restart point. We no longer need to keep WAL files from before the restart point. If we need to recover, it will be faster to recover from the incrementally updated backup than from the original base backup." I'm specifically confused about the meaning of the following phrases: "backup of the standby server's files" - Which files? "reload that data" - What does this mean in postgres terms? "last restart point" - What is this? Wouldn't it be able to restart from the last recovered file, which would presumably occur later than the last restart point? Does this mean make a filesystem backup of the standby server's data directory while it's stopped, and then start it again with that data and the restricted set of WAL files needed to continue recovery? I'd like to see the language here converted to words that have more meaning in the context of postgres. I'd be happy to attempt a revision of this section once I'm able to complete an incrementally updated backup successfully. Here's how I envision it playing out in practice: 1. stop standby postgres server 2. [optional] preserve data directory, remove unnecessary WAL files 3. restart standby server Is that all there is to it? -- Thomas F. O'Connell optimizing modern web applications : for search engines, for usability, and for performance : http://o.ptimized.com/ 615-260-0005
