Thread: pg_dump locking info
Is this right? I'm looking at http://www.postgresql.org/docs/current/static/backup-dump.html It says, "pg_dump does not block other operations on the database while it is working. (Exceptions are those operations that need to operate with an exclusive lock, such as most forms of ALTER TABLE.)" So pg_dump actually performs an ALTER TABLE sometimes? :S -- Thom Brown Registered Linux user: #516935
On 15 August 2010 10:01, Thom Brown <thom@linux.com> wrote: > Is this right? I'm looking at > http://www.postgresql.org/docs/current/static/backup-dump.html > > It says, "pg_dump does not block other operations on the database > while it is working. (Exceptions are those operations that need to > operate with an exclusive lock, such as most forms of ALTER TABLE.)" > > So pg_dump actually performs an ALTER TABLE sometimes? :S > And whilst I was perusing the docs, I also noticed this on http://www.postgresql.org/docs/current/static/backup.html "Each has its own strengths and weaknesses. Each is discussed in turn below." That sentence is at the bottom of the page. It would make sense in a PDF, but might be a confusing in section-by-section HTML documentation. -- Thom Brown Registered Linux user: #516935
On 15 August 2010 10:29, Thom Brown <thom@linux.com> wrote: > On 15 August 2010 10:01, Thom Brown <thom@linux.com> wrote: >> Is this right? I'm looking at >> http://www.postgresql.org/docs/current/static/backup-dump.html >> >> It says, "pg_dump does not block other operations on the database >> while it is working. (Exceptions are those operations that need to >> operate with an exclusive lock, such as most forms of ALTER TABLE.)" >> >> So pg_dump actually performs an ALTER TABLE sometimes? :S >> > > And whilst I was perusing the docs, I also noticed this on > http://www.postgresql.org/docs/current/static/backup.html > > "Each has its own strengths and weaknesses. Each is discussed in turn below." > > That sentence is at the bottom of the page. It would make sense in a > PDF, but might be a confusing in section-by-section HTML > documentation. > Another thing I noticed, going back to http://www.postgresql.org/docs/current/static/backup-file.html , is that it makes no mention of the fact that file system level backups are useless if being used to restore in a different major version. Maybe "There are two restrictions, however, which make this method impractical, or at least inferior to the pg_dump method" should be changed to "There are three..." and add the point that pg_dump/pg_dumpall is mostly immune to such limitations. -- Thom Brown Registered Linux user: #516935
On 08/15/2010 11:01 AM, Thom Brown wrote: > Is this right? I'm looking at > http://www.postgresql.org/docs/current/static/backup-dump.html > > It says, "pg_dump does not block other operations on the database > while it is working. (Exceptions are those operations that need to > operate with an exclusive lock, such as most forms of ALTER TABLE.)" > > So pg_dump actually performs an ALTER TABLE sometimes? :S No it does not - it however blocks operations like ALTER TABLE while it is running(which is exactly how I read the above statement). Stefan
On 15 August 2010 10:38, Thom Brown <thom@linux.com> wrote: > On 15 August 2010 10:29, Thom Brown <thom@linux.com> wrote: >> On 15 August 2010 10:01, Thom Brown <thom@linux.com> wrote: >>> Is this right? I'm looking at >>> http://www.postgresql.org/docs/current/static/backup-dump.html >>> >>> It says, "pg_dump does not block other operations on the database >>> while it is working. (Exceptions are those operations that need to >>> operate with an exclusive lock, such as most forms of ALTER TABLE.)" >>> >>> So pg_dump actually performs an ALTER TABLE sometimes? :S >>> >> >> And whilst I was perusing the docs, I also noticed this on >> http://www.postgresql.org/docs/current/static/backup.html >> >> "Each has its own strengths and weaknesses. Each is discussed in turn below." >> >> That sentence is at the bottom of the page. It would make sense in a >> PDF, but might be a confusing in section-by-section HTML >> documentation. >> > > Another thing I noticed, going back to > http://www.postgresql.org/docs/current/static/backup-file.html , is > that it makes no mention of the fact that file system level backups > are useless if being used to restore in a different major version. > Maybe "There are two restrictions, however, which make this method > impractical, or at least inferior to the pg_dump method" should be > changed to "There are three..." and add the point that > pg_dump/pg_dumpall is mostly immune to such limitations. > In the docs for version 9.0, I think the following from http://www.postgresql.org/docs/9.0/static/migration.html should be updated to make mention of streaming replication: "It is also possible to use replication methods, such as Slony, to create a standby server with the updated version of PostgreSQL." Of course, this page doesn't make any mention of pg_upgrade for upgrading from 8.3+ and probably should. -- Thom Brown Registered Linux user: #516935
On 15 August 2010 10:45, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: > On 08/15/2010 11:01 AM, Thom Brown wrote: >> >> Is this right? I'm looking at >> http://www.postgresql.org/docs/current/static/backup-dump.html >> >> It says, "pg_dump does not block other operations on the database >> while it is working. (Exceptions are those operations that need to >> operate with an exclusive lock, such as most forms of ALTER TABLE.)" >> >> So pg_dump actually performs an ALTER TABLE sometimes? :S > > No it does not - it however blocks operations like ALTER TABLE while it is > running(which is exactly how I read the above statement). > I'm not sure why I didn't read it that way now. :S My bad. -- Thom Brown Registered Linux user: #516935
On 15 August 2010 10:47, Thom Brown <thom@linux.com> wrote: > On 15 August 2010 10:38, Thom Brown <thom@linux.com> wrote: >> On 15 August 2010 10:29, Thom Brown <thom@linux.com> wrote: >>> On 15 August 2010 10:01, Thom Brown <thom@linux.com> wrote: >>>> Is this right? I'm looking at >>>> http://www.postgresql.org/docs/current/static/backup-dump.html >>>> >>>> It says, "pg_dump does not block other operations on the database >>>> while it is working. (Exceptions are those operations that need to >>>> operate with an exclusive lock, such as most forms of ALTER TABLE.)" >>>> >>>> So pg_dump actually performs an ALTER TABLE sometimes? :S >>>> >>> >>> And whilst I was perusing the docs, I also noticed this on >>> http://www.postgresql.org/docs/current/static/backup.html >>> >>> "Each has its own strengths and weaknesses. Each is discussed in turn below." >>> >>> That sentence is at the bottom of the page. It would make sense in a >>> PDF, but might be a confusing in section-by-section HTML >>> documentation. >>> >> >> Another thing I noticed, going back to >> http://www.postgresql.org/docs/current/static/backup-file.html , is >> that it makes no mention of the fact that file system level backups >> are useless if being used to restore in a different major version. >> Maybe "There are two restrictions, however, which make this method >> impractical, or at least inferior to the pg_dump method" should be >> changed to "There are three..." and add the point that >> pg_dump/pg_dumpall is mostly immune to such limitations. >> > > In the docs for version 9.0, I think the following from > http://www.postgresql.org/docs/9.0/static/migration.html should be > updated to make mention of streaming replication: > > "It is also possible to use replication methods, such as Slony, to > create a standby server with the updated version of PostgreSQL." > > Of course, this page doesn't make any mention of pg_upgrade for > upgrading from 8.3+ and probably should. > -- Also on http://www.postgresql.org/docs/9.0/static/continuous-archiving.html "Such dumps are logical and do not contain enough information to used by WAL reply." s/to used by WAL reply/to be used by WAL replay/ -- Thom Brown Registered Linux user: #516935
On Sun, Aug 15, 2010 at 3:47 AM, Thom Brown <thom@linux.com> wrote: > On 15 August 2010 10:38, Thom Brown <thom@linux.com> wrote: >> >> Another thing I noticed, going back to >> http://www.postgresql.org/docs/current/static/backup-file.html , is >> that it makes no mention of the fact that file system level backups >> are useless if being used to restore in a different major version. >> Maybe "There are two restrictions, however, which make this method >> impractical, or at least inferior to the pg_dump method" should be >> changed to "There are three..." and add the point that >> pg_dump/pg_dumpall is mostly immune to such limitations. >> > > In the docs for version 9.0, I think the following from > http://www.postgresql.org/docs/9.0/static/migration.html should be > updated to make mention of streaming replication: But you can't use streaming replication for migration...
On 15 August 2010 15:38, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Sun, Aug 15, 2010 at 3:47 AM, Thom Brown <thom@linux.com> wrote: >> On 15 August 2010 10:38, Thom Brown <thom@linux.com> wrote: >>> >>> Another thing I noticed, going back to >>> http://www.postgresql.org/docs/current/static/backup-file.html , is >>> that it makes no mention of the fact that file system level backups >>> are useless if being used to restore in a different major version. >>> Maybe "There are two restrictions, however, which make this method >>> impractical, or at least inferior to the pg_dump method" should be >>> changed to "There are three..." and add the point that >>> pg_dump/pg_dumpall is mostly immune to such limitations. >>> >> >> In the docs for version 9.0, I think the following from >> http://www.postgresql.org/docs/9.0/static/migration.html should be >> updated to make mention of streaming replication: > > But you can't use streaming replication for migration... > Hmm... yes, that's referring to migration to a new major version. Ignore the streaming replication comment. -- Thom Brown Registered Linux user: #516935
On Sun, Aug 15, 2010 at 5:29 AM, Thom Brown <thom@linux.com> wrote: > And whilst I was perusing the docs, I also noticed this on > http://www.postgresql.org/docs/current/static/backup.html > > "Each has its own strengths and weaknesses. Each is discussed in turn below." > > That sentence is at the bottom of the page. It would make sense in a > PDF, but might be a confusing in section-by-section HTML > documentation. I don't find it so, but I suppose if it's a big deal we could change it to read "in the following sections". -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Sun, Aug 15, 2010 at 5:38 AM, Thom Brown <thom@linux.com> wrote: > Another thing I noticed, going back to > http://www.postgresql.org/docs/current/static/backup-file.html , is > that it makes no mention of the fact that file system level backups > are useless if being used to restore in a different major version. > Maybe "There are two restrictions, however, which make this method > impractical, or at least inferior to the pg_dump method" should be > changed to "There are three..." and add the point that > pg_dump/pg_dumpall is mostly immune to such limitations. Probably we need to say something a bit more nuanced than that, as there can certainly be wrinkles in that area. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
On Sun, Aug 15, 2010 at 5:47 AM, Thom Brown <thom@linux.com> wrote: > In the docs for version 9.0, I think the following from > http://www.postgresql.org/docs/9.0/static/migration.html should be > updated to make mention of streaming replication: > > "It is also possible to use replication methods, such as Slony, to > create a standby server with the updated version of PostgreSQL." Presumably this would be a negative mention, since SR wouldn't work for this case. Really, though, the core technology here is not SR but WAL-shipping, which we've had for a long time. > Of course, this page doesn't make any mention of pg_upgrade for > upgrading from 8.3+ and probably should. Yeah. Another random nitpick on this page is that "Or use an intermediate file if you wish." isn't actually a complete sentence. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes: > On Sun, Aug 15, 2010 at 5:29 AM, Thom Brown <thom@linux.com> wrote: >> And whilst I was perusing the docs, I also noticed this on >> http://www.postgresql.org/docs/current/static/backup.html >> >> "Each has its own strengths and weaknesses. Each is discussed in turn below." >> >> That sentence is at the bottom of the page. �It would make sense in a >> PDF, but might be a confusing in section-by-section HTML >> documentation. > I don't find it so, but I suppose if it's a big deal we could change > it to read "in the following sections". Yeah, that's what I was thinking of doing in response to Thom's gripe. regards, tom lane
I did something about Thom's various gripes. See what you think ...
regards, tom lane
On 16 August 2010 00:05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I did something about Thom's various gripes. See what you think ... > > regards, tom lane > That's great Tom. Thanks. :) The note about restoring to a different architecture hadn't crossed my mind. -- Thom Brown Registered Linux user: #516935