Thread: 7.5-dev, pg_dumpall, dollarquoting
Hi! since we have a lot of databases here that suffer from pg_dump's deficits in 7.3 and 7.4 regarding dependencies, we tried pg_dump from the upcoming 7.5 release. This version works much better, but it is not possible to dump a complete cluster using pg_dumpall in a 7.3 or 7.4 compatible way because pg_dumpall lacks the "-X disable-dollar-quoting" switch. Would it be possible to modify pg_dumpall to accept the same commands as pg_dump (at least those that make sense) - or am I missing something here ? thanks Stefan
Hi Stefan, > since we have a lot of databases here that suffer from pg_dump's > deficits in 7.3 and 7.4 regarding dependencies, we tried pg_dump from > the upcoming 7.5 release. Would you be able to specify exactly the deficiences? It's my mission at the moment to make pg_dump 7.5 known-issue free :) > This version works much better, but it is not possible to dump a > complete cluster using pg_dumpall in a 7.3 or 7.4 compatible way because > pg_dumpall lacks the "-X disable-dollar-quoting" switch. > Would it be possible to modify pg_dumpall to accept the same commands as > pg_dump (at least those that make sense) - or am I missing something here ? I can add it - do the other hackers want it? The main problem stefan is that 7.5 dump is seriously only guaranteed to restore to 7.5. You can use the 7.5 dump to upgrade to 7.5, but not to restore to 7.4. At the moment, dollar quoting is your main problem, but there's also the set default_with_oids stuff, plus I'm also hopefully soon going to fix the dumping owners issue, which will most likely make pg_dump 7.5 less likely to restore to 7.4... Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> pg_dumpall lacks the "-X disable-dollar-quoting" switch.
> I can add it - do the other hackers want it?
It should be there --- in general pg_dumpall should be able to pass down
any pg_dump switch that makes sense.
regards, tom lane
Tom Lane wrote: >Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > >>>pg_dumpall lacks the "-X disable-dollar-quoting" switch. >>> >>> > > > >>I can add it - do the other hackers want it? >> >> > >It should be there --- in general pg_dumpall should be able to pass down >any pg_dump switch that makes sense. > > > > Perhaps rather than replicating every pg_dump option pg_dumpall needs a single option that can encapsulate them? e.g. --dump-options="--foo --bar --baz" Just a thought cheers andrew
KL- > Would you be able to specify exactly the deficiences? It's my mission > at the moment to make pg_dump 7.5 known-issue free :) Well, since you asked: (please excuse me if I'm covering old ground. I was off Hackers for almost a month this spring) 1) When pg_dump 7.4.1 (I have not tested on CVS) pulls a dump from a 7.2 database with confusing dependancies (e.g. functions depend on views which depend on multiple tables and other views containing other functions), some objects (almost always functions) still get silently dropped from the dump file. This "silent dropping" was also a problem in 7.3 (pulling from 7.2), but nobody wanted to work on it -- especially as it's only possible to demonstrate with a sufficiently complex early 7.2 database.I have a good test database for this, I will test with CVS. 2) pg_restore needs to be more tolerant with certain kinds of errors. For example, if an object already exists in the target database, due to being from template1, it should be possible to tell pg_restore to ignore the error with a switch. Currently, this issue prevents me from using pg_restore on some systems, where the restore isn't run as the superuser. Another switch, telling pg_restore to attempt to ignore all errors and restore anyway, would also be keen (though I can see potential abuse issues).Has this already been addressed in CVS? -- Josh Berkus Aglio Database Solutions San Francisco
> 1) When pg_dump 7.4.1 (I have not tested on CVS) pulls a dump from a 7.2 > database with confusing dependancies (e.g. functions depend on views which > depend on multiple tables and other views containing other functions), some > objects (almost always functions) still get silently dropped from the dump > file. This "silent dropping" was also a problem in 7.3 (pulling from 7.2), > but nobody wanted to work on it -- especially as it's only possible to > demonstrate with a sufficiently complex early 7.2 database. > I have a good test database for this, I will test with CVS. Nothing gets silently dropped. It will cause an ERROR on creation and then keep going, but it won't silently drop it... (unless it's a binary dump thing...) Anyway, pg_dump in CVS does correct dump ordering based on a topological sort of the pg_depend relation (thank Tom for that). It will of course only work on a 7.3 or higher backend. > 2) pg_restore needs to be more tolerant with certain kinds of errors. For > example, if an object already exists in the target database, due to being > from template1, it should be possible to tell pg_restore to ignore the error > with a switch. Currently, this issue prevents me from using pg_restore on > some systems, where the restore isn't run as the superuser. Another switch, > telling pg_restore to attempt to ignore all errors and restore anyway, would > also be keen (though I can see potential abuse issues). > Has this already been addressed in CVS? Hmmm, dunno about this - it wasn't on my radar really. I'll experiment with it, but I don't think I'm going to have time before June 30th :( Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> 2) pg_restore needs to be more tolerant with certain kinds of errors.
> Hmmm, dunno about this - it wasn't on my radar really. I'll experiment
> with it, but I don't think I'm going to have time before June 30th :(
I think we dealt with this already: pg_restore has been taught that the
correct response to a SQL command error is to report it and forge ahead,
not curl up and die.
regards, tom lane
Chris, > Nothing gets silently dropped. It will cause an ERROR on creation and > then keep going, but it won't silently drop it... (unless it's a > binary dump thing...) Silently dropped on dump, not on restore. > Anyway, pg_dump in CVS does correct dump ordering based on a topological > sort of the pg_depend relation (thank Tom for that). It will of course > only work on a 7.3 or higher backend. Right. The issue is really complex databases which were developed in 7.2 or 7.1, which is missing the pg_depends information. In that case, pg_dump seems to get confused about dependency sorting, and a few objects with long dependency chains simply don't get backed up. I'll see if I can put together a nice demo for this. My concern in reporting this is that there are still a lot of commercial users out there running 7.2, and I'd like to minimize upgrade pain. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Right. The issue is really complex databases which were developed in 7.2 or
> 7.1, which is missing the pg_depends information. In that case, pg_dump
> seems to get confused about dependency sorting, and a few objects with long
> dependency chains simply don't get backed up.
That would be a flat-out bug. Can we see a test case?
regards, tom lane
> It should be there --- in general pg_dumpall should be able to pass down > any pg_dump switch that makes sense. Do the -S and -O switches also make sense? Should I add them? Chris