Thread: pg_dumpall and version confusion
I inherited an existing system with no documents on how it was originally configuerd, so I'll provide as much as I can. OS is CentOS 4.3 Linux Distribution. When I search my system for anything postgres I find a boatload of 8.0.4 source, and a boatload of 7.4.8 documentation. postgres --version yields 8.1.4 (thats no typo) psql --version yields 7.4.8 pg_dump --version yields 7.4.8 pg_dumpall --version yields 7.4.8 When doing 'which' on all of the above binaries I get /usr/bin/____ for all 4. The original problem was recieving 'pg_dumpall: query failed: ERROR: column "datpath" does not exist' error when trying to run pg_dumpall. I learned that this is expected error if pg_dumpall is too new for the database, but it claims to be 7.4.8. I hate to ask a terribly vague question but how do I find out what version I'm running when postgres and psql don't agree? There's no trace of 8.1.4 files on the system so I'm without a lead as to how I have a binary for it. Thanks for any assistance you can provide.
Alan J Batsford <AJBatsford@uss.com> writes:
> When I search my system for anything postgres I find a boatload of 8.0.4
> source, and a boatload of 7.4.8 documentation.
"rpm -qa | grep postgres" would probably prove enlightening. It sounds
like you have postgresql-server at 8.1.4 and the other subpackages at
older releases, which is pretty odd; the RPMs should have had
cross-requires that would prevent that.
> The original problem was recieving 'pg_dumpall: query failed: ERROR:
> column "datpath" does not exist' error when trying to run pg_dumpall. I
> learned that this is expected error if pg_dumpall is too new for the
> database, but it claims to be 7.4.8.
No, usually you have version skew problems if pg_dumpall is too *old*
for the server, which is exactly the case here. We try to make pg_dump
cope with prior server releases, but since we lack a time machine we
can't go back and teach old versions about subsequent server changes...
regards, tom lane
Tom Lane wrote: > We try to make pg_dump > cope with prior server releases, but since we lack a time machine we > can't go back and teach old versions about subsequent server changes... > Tom, How come version 8.2 of pg_dump uses GRANT ON SEQUENCE when dumping a 8.1 or older database? Was it just a oversight? Seems it should be a simple matter to add some logic that says IF version >= 8.2 THEN use grant on seq else don't use it. Bruce told me just the opposite of what you said in the above message. Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
Tony Caduto wrote: > Tom Lane wrote: > >We try to make pg_dump > >cope with prior server releases, but since we lack a time machine we > >can't go back and teach old versions about subsequent server changes... > > > Tom, > How come version 8.2 of pg_dump uses GRANT ON SEQUENCE when dumping a > 8.1 or older database? What's wrong with that? 8.2 will understand the GRANT ON SEQUENCE without a problem. > Bruce told me just the opposite of what you said in the above message. So Bruce told you that hackers do have a time machine? If so, he hasn't let me know about it, and I'm very interested. I'll ask him. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > > What's wrong with that? 8.2 will understand the GRANT ON SEQUENCE > without a problem. > > Yes, but 8.1 and earlier wont! If you dump a 8.1 database (with 8.2 pg_dump) and then use that dump to restore to another 8.1 server, any permissions on sequences will fail to restore because the 8.2 pg_dump can't handle NOT using GRANT ON SEQUENCE on the lower version of the server. Would it really be that big of a deal to add some logic to 8.2 pg_dump to say: Hey I am not dumping a 8.2 server, so don't use GRANT ON SEQUENCE? most Admin tools ship with the latest version of pg_dump and restore, so If I attempt to restore that dump via pgAdmin III (or other tools) which is using 8.2 versions of dump and restore it will fail on a 8.1 server. Do you see the point I am trying to make? Should admin tool vendors start shipping every version of pg_dump now? In a earlier message about this same thing Bruce said (I am quoting from memory so it's not exact) that we don't try and make pg_dump backwards compatible and to use the dump that came with whatever version you are dumping from" So to me that seemed opposite of what Tom said. If I misquoted anyone I apologize in advance. Thanks, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
Tony Caduto wrote: > Alvaro Herrera wrote: >> >> What's wrong with that? 8.2 will understand the GRANT ON SEQUENCE >> without a problem. >> >> > Yes, but 8.1 and earlier wont! > > If you dump a 8.1 database (with 8.2 pg_dump) and then use that dump to > restore to another 8.1 server, any permissions on sequences will fail > to restore because the 8.2 pg_dump can't handle NOT using GRANT ON > SEQUENCE on the lower version of the server. > Would it really be that big of a deal to add some logic to 8.2 pg_dump > to say: Hey I am not dumping a 8.2 server, so don't use GRANT ON SEQUENCE? > > most Admin tools ship with the latest version of pg_dump and restore, so > If I attempt to restore that dump via pgAdmin III (or other tools) which > is using 8.2 versions of dump and restore it will fail on a 8.1 server. > > Do you see the point I am trying to make? Should admin tool vendors > start shipping every version of pg_dump now? This is a problem I've been thinking about on and off recently - and I am starting to come to the conclusion that shipping each version of the utilities is the only way things are likely to work unless someone puts some really significant effort into adding backwards compatibility modes to pg_dump (which I imagine is likely to meet resistance if offered as a patch anyway). That will be a real PITA though - we already have a bunch of code in pgAdmin to find just the current versions of PostgreSQL's and EDB's pg_dump et al. (yes, they are different), nevermind having to do it right back to 7.3 which is the earliest version we currently support. The other issue will be having to ship multiple copies of libpq and supporting libraries for each version :-( For the moment though, our SVN trunk code will allow you to override the paths to the PostgreSQL and EnterpriseDB utilities, so you can point pgAdmin at the appropriate verion for your server. Regards, Dave
Dave Page wrote: > This is a problem I've been thinking about on and off recently - and I > am starting to come to the conclusion that shipping each version of > the utilities is the only way things are likely to work unless someone > puts some really significant effort into adding backwards > compatibility modes to pg_dump (which I imagine is likely to meet > resistance if offered as a patch anyway). > I never had a issue before the 8.2 dump and the GRANT ON SEQUENCE. The version differences in PGSQL are nothing compared to what goes on with MySQL. Maybe that would be a good Google summer of code project :-) (make pg_dump more backwards compatible to at least 8.0) Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
On Thu, 2007-03-15 at 11:53, Tony Caduto wrote: > Dave Page wrote: > > This is a problem I've been thinking about on and off recently - and I > > am starting to come to the conclusion that shipping each version of > > the utilities is the only way things are likely to work unless someone > > puts some really significant effort into adding backwards > > compatibility modes to pg_dump (which I imagine is likely to meet > > resistance if offered as a patch anyway). > > > > I never had a issue before the 8.2 dump and the GRANT ON SEQUENCE. > The version differences in PGSQL are nothing compared to what goes on > with MySQL. > > Maybe that would be a good Google summer of code project :-) (make > pg_dump more backwards compatible to at least 8.0) It would be quite useful to have a -sourcever and -targetver flag in pg_dump that understood the latest version and the last two or three versions. So, if you had pg_dump for v8.2 you could do something like: pg_dump -sourcever 8.2 -targetver 8.0 > dump.sql
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> Alvaro Herrera wrote:
>> What's wrong with that? 8.2 will understand the GRANT ON SEQUENCE
>> without a problem.
>>
> Yes, but 8.1 and earlier wont!
You're missing the point entirely. The versioning contract for pg_dump
is that version M.N can dump from servers of versions <= M.N, but the
dump it produces is meant to load into server versions >= M.N. If you
want a dump that will reload into 8.1, use 8.1's pg_dump.
pg_dump is messy enough already just trying to support this contract.
I have *no* interest in trying to get it to support an --output-version
switch...
regards, tom lane
Tom Lane wrote: > I have *no* interest in trying to get it to support an --output-version > switch... There speaks a man who has the luxury of not having to worry about multi-version admin tools :-( /D
Dave Page wrote: > Tom Lane wrote: > >I have *no* interest in trying to get it to support an --output-version > >switch... > > There speaks a man who has the luxury of not having to worry about > multi-version admin tools :-( Is it more difficult for you to ship a pg_dump-8.0, pg_dump-8.1, etc along pgAdmin? I think you (and phpPgAdmin?) are already shipping pg_dump binaries, right? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Dave Page wrote: >> Tom Lane wrote: >>> I have *no* interest in trying to get it to support an --output-version >>> switch... >> There speaks a man who has the luxury of not having to worry about >> multi-version admin tools :-( > > Is it more difficult for you to ship a pg_dump-8.0, pg_dump-8.1, etc > along pgAdmin? I think you (and phpPgAdmin?) are already shipping > pg_dump binaries, right? > Yes, because we also have to ship the appropriate versions of libpq.dll and it's 5 or 6 dependencies as well, and keep them all in seperate directories. pgAdmin also support EnterpriseDB, so might also ship the different versions of those utilities in the future, along with their slightly different dependencies. Even if we went through the insane pain (for me at least) of maintaining multiple branches of the Windows build of PostgreSQL, *and* custom builds of the utilities built against a single libpq, we still couldn't ship a single set because pg_dumpall.v80.exe wouldn't find pg_dump.v80.exe without modification of the source. Whichever way you cut it, it'll be a ridiculous amount of work to maintain, unless there were a --match-version option in pg_dump to allow dumps of prior versions of the server, in a format appropriate for that version. Regards, Dave
On Thu, Mar 15, 2007 at 09:19:31PM +0000, Dave Page wrote: > Even if we went through the insane pain (for me at least) of maintaining > multiple branches of the Windows build of PostgreSQL, *and* custom > builds of the utilities built against a single libpq, we still couldn't > ship a single set because pg_dumpall.v80.exe wouldn't find > pg_dump.v80.exe without modification of the source. Debian for years has been running a system where multiple versions can be co-installed and if you call pg_dumpall it calls the right pg_dump, you just have to indicate the cluster you want to operate on. Porting it to Windows could be tricky though, given its use of symlinks and wrapper scripts. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
> Porting it to Windows could be tricky though, given its use of symlinks > and wrapper scripts. > FWIW, as of Vista and Server Longhorn, Windows now supports Symlinks. I don't know about wrapper scripts though. -- Paul Lambert Database Administrator AutoLedgers
Dave Page wrote: > Tom Lane wrote: >> I have *no* interest in trying to get it to support an --output-version >> switch... > > There speaks a man who has the luxury of not having to worry about > multi-version admin tools :-( I am afraid that I have to agree with Tom here. Pg_dump has plenty of other deficiencies let's not add to complexity by having an --output-version. If you want that, create a wrapper program that calls to different statically compiled versions of pg_dump. Joshua D. Drake > > /D > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
> ------- Original Message ------- > From: "Joshua D. Drake" <jd@commandprompt.com> > To: Dave Page <dpage@postgresql.org> > Sent: 15/03/07, 23:51:18 > Subject: Re: [GENERAL] pg_dumpall and version confusion > > If you want that, create a wrapper program that calls to different > statically compiled versions of pg_dump. I can't even begin to imagine how difficult that would be on Windows! /D
"Dave Page" <dpage@postgresql.org> writes: >> ------- Original Message ------- >> From: "Joshua D. Drake" <jd@commandprompt.com> >> To: Dave Page <dpage@postgresql.org> >> Sent: 15/03/07, 23:51:18 >> Subject: Re: [GENERAL] pg_dumpall and version confusion >> >> If you want that, create a wrapper program that calls to different >> statically compiled versions of pg_dump. > > I can't even begin to imagine how difficult that would be on Windows! As difficult as a new ".bat" file? -- Jorge Godoy <jgodoy@gmail.com>
Jorge Godoy wrote: > "Dave Page" <dpage@postgresql.org> writes: > >>> ------- Original Message ------- >>> From: "Joshua D. Drake" <jd@commandprompt.com> >>> To: Dave Page <dpage@postgresql.org> >>> Sent: 15/03/07, 23:51:18 >>> Subject: Re: [GENERAL] pg_dumpall and version confusion >>> >>> If you want that, create a wrapper program that calls to different >>> statically compiled versions of pg_dump. >> I can't even begin to imagine how difficult that would be on Windows! > > As difficult as a new ".bat" file? > Thats the easy bit. The difficult bit is building the statically linked utilities with ssl, gettext and kerberos support. We found when porting PostgreSQL in the first place that for many of the supporting libraries, Windows is an afterthought and where on unix there might be static and dynamic builds, on Windows once dynamic is done, static doesn't matter. Regards, Dave
Dave Page wrote:
>
>> ------- Original Message -------
>> From: "Joshua D. Drake" <jd@commandprompt.com>
>> To: Dave Page <dpage@postgresql.org>
>> Sent: 15/03/07, 23:51:18
>> Subject: Re: [GENERAL] pg_dumpall and version confusion
>>
>> If you want that, create a wrapper program that calls to different
>> statically compiled versions of pg_dump.
>
> I can't even begin to imagine how difficult that would be on Windows!
Well supporting Windows in general is difficult ;), but perhaps it would
only be hard the first time because the build process could be automated?
J
>
> /D
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote: > other deficiencies let's not add to complexity by having an > --output-version. > > If you want that, create a wrapper program that calls to different > statically compiled versions of pg_dump. > > Joshua D. Drake > > > > I am afraid that I have to agree with Tom here. Pg_dump has plenty of Well for me that would not be a option. I use object pascal and can't statically link C code into my apps. Doing the statically linked thing would also bloat any resulting binary. I don't think the whole --output-version thing would be a good idea either, but it would be a simple matter to add some logic for the GRANT ON SEQUENCE, which seems to be the only thing that is really causing problems at least between 8.1 and 8.2. The old way of granting permissions on sequences still works on 8.2 right? If so then maybe a switch to disable GRANT ON SEQUENCE would do the trick. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> I don't think the whole --output-version thing would be a good idea
> either, but it would be a simple matter to add some logic for the
> GRANT ON SEQUENCE, which seems to be the only thing that is really
> causing problems at least between 8.1 and 8.2.
You mean it's the only thing you've run into. There are a *lot* of
changes in the output of different pg_dump versions. Even focusing
on the narrow question of sequence changes between 8.1 and 8.2,
what of the change from emitting "SERIAL" to emitting a separate
CREATE SEQUENCE and ALTER SEQUENCE OWNED BY?
regards, tom lane