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 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
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 Mon, Sep 13, 2010 at 1:29 AM, Mikko Partio mpar...@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. -- 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 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
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
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 klep...@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 signature.asc Description: Digital signature
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 7:39 AM, J. Roeleveld jo...@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. -- 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 br...@momjian.ushttp://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:51 AM, Bruce Momjian br...@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. -- 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 br...@momjian.ushttp://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: 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 br...@momjian.ushttp://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
