Thread: DB upgrade
Hi folks, please forgive what feels like a no-brainer even as I ask it, but... I've read dozens of times in these lists that when one is upgrading from an older to newer version of PG, the DB being dumped (older version) should be done so using pg_dump from the newer version. I think I've probably absorbed that bit of wisdom, but I've never actually had to put it to use so it's always just been an academic understanding and a quick scan every time I see the topic come up. Well I'm actually going to need to dump an older DB and restore to a newer version very soon, and I'd just like a point of clarification please. Which bit exactly are we supposed to use from the newer version? I mean, I've used pg_dump hundreds of times but I've never had need to actually look at it and I've always assumed that it been a binary file, especially since the advise is always to use the newer version's pg_dump to start the migration, intimating the working bits are inside there somewhere, and that they're different in every version. Unless my installation is unique in some way of which I'm yet unaware, pg_dump seems to be just a handful of lines in a perl script. In fact, pg_dump, pg_restore and pg_dumpall are all simlinks to the same simple perl script, the contents of which seem to be identical in both my 'older' and 'newer' versions of PG. Does this mean I can trust any old dump from my older server to seamlessly plug into my newer version? Or does thin mean there are other 'gizmos' than pg_dump which I need to copy from new machine to old machine to perform the dump? Andy
Am Dienstag, 9. Oktober 2007 schrieb Andrew Kelly: > Unless my installation is unique in some way of which I'm yet unaware, Yes, it's a Debian package. > pg_dump seems to be just a handful of lines in a perl script. In fact, > pg_dump, pg_restore and pg_dumpall are all simlinks to the same simple > perl script, the contents of which seem to be identical in both my > 'older' and 'newer' versions of PG. Does this mean I can trust any old > dump from my older server to seamlessly plug into my newer version? Or > does thin mean there are other 'gizmos' than pg_dump which I need to > copy from new machine to old machine to perform the dump? The advice remains: Install the newest client package and use that pg_dump. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Andrew Kelly wrote: > I've read dozens of times in these lists that when one is > upgrading from an older to newer version of PG, the DB > being dumped (older version) should be done so using > pg_dump from the newer version. [...] > Which bit exactly are we supposed to use from the > newer version? [...] > I mean, I've used pg_dump hundreds of times but I've never > had need to actually look at it and I've always assumed > that it been a binary file, [...] > Unless my installation is unique in some way of which > I'm yet unaware, pg_dump seems to be just a handful of > lines in a perl script. In fact, pg_dump, pg_restore and > pg_dumpall are all simlinks to the same simple perl script, > the contents of which seem to be identical in both my > 'older' and 'newer' versions of PG. What is your PostgreSQL version and your operating system? pg_dump, pg_restore, and pg_dumpall should all be executables: On my Linux system: file `which pg_dump` /magwien/postgres-8.2.5/bin/pg_dump: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), stripped The database dump that you generate with pg_dump is either an SQL script (if you use the default 'plain' format) or binary data (if you use one of the other formats). With pg_dumpall, you can only generate an SQL script. In any case, the dump will contain commands that can be used to recreate the database. The reason why you should always use pg_dump from the newer database version is the following: The newer pg_dump "knows" about what is different in the new PostgreSQL version and can create a dump that will "do what you mean" on the new PostgreSQL server. An example: In 8.2, there is a new database parameter standard_conforming_strings. pg_dump from 8.2 knows about this and will add the following into your dump: SET standard_conforming_strings = off; pg_dump from 8.1 will not do that, so when you import the dump from 8.1 into an 8.2 database, the interpretation of Strings in the dump will depend on the current setting of standard_conforming_strings, which may lead to data loss. Yours, Laurenz Albe
On Tue, 2007-10-09 at 13:58 +0200, Peter Eisentraut wrote: > Am Dienstag, 9. Oktober 2007 schrieb Andrew Kelly: > > Unless my installation is unique in some way of which I'm yet unaware, > > Yes, it's a Debian package. Indeed, yes. Where can I read what that means in the great scheme of things? Are you saying that Deb is markedly different from other packages (.rpm) or that any packaged version of PG is different from the recommended source install? > > pg_dump seems to be just a handful of lines in a perl script. In fact, > > pg_dump, pg_restore and pg_dumpall are all simlinks to the same simple > > perl script, the contents of which seem to be identical in both my > > 'older' and 'newer' versions of PG. Does this mean I can trust any old > > dump from my older server to seamlessly plug into my newer version? Or > > does thin mean there are other 'gizmos' than pg_dump which I need to > > copy from new machine to old machine to perform the dump? > > The advice remains: Install the newest client package and use that pg_dump. [grumble] I had a remark here about how confused I still was, since my client package is installed and still only offering the perl wrapper script. Then, after a 2 hour meeting and getting back to answering this mail, I kicked my Debian boxes around an bit and found the actual binaries. So, I think I'm settled now. Thanks for your input Peter, and you as well, Albe. I really appreciate it. Andy
Le mardi 09 octobre 2007, Andrew Kelly a écrit : > On Tue, 2007-10-09 at 13:58 +0200, Peter Eisentraut wrote: > Where can I read what that means in the great scheme of things? > Are you saying that Deb is markedly different from other packages (.rpm) > or that any packaged version of PG is different from the recommended > source install? man pg_wrapper debian provides this script for you to easily connect to whichever PostgreSQL installation (different version, different clusters) with the same tool and without manually messing with PATHs etc. psql --cluster 8.2/main ... pg_dump --cluster 8.2/main -V pg_lsclusters > Then, after a 2 hour meeting and getting back to answering this mail, I > kicked my Debian boxes around an bit and found the actual binaries. So, > I think I'm settled now. dpkg -L <package> will give you the exact list of installed files. Hope this helps, regards, -- dim
Attachment
Andrew Kelly wrote: > On Tue, 2007-10-09 at 13:58 +0200, Peter Eisentraut wrote: >> Am Dienstag, 9. Oktober 2007 schrieb Andrew Kelly: >>> Unless my installation is unique in some way of which I'm yet unaware, >> Yes, it's a Debian package. > > Indeed, yes. > > Where can I read what that means in the great scheme of things? > Are you saying that Deb is markedly different from other packages (.rpm) > or that any packaged version of PG is different from the recommended > source install? All packaged versions will differ from the source tarball in some way. At one end of the scale, the ./configure options for where files go by default will be different. At the other end of the scale the Windows package bundles pgAdmin. Debian have a (somewhat complex at first but very clever) system that lets you have multiple installations of PG at the same time. This is easy to do with source installs (because you'll make sure each has its own directories and port by hand) but harder with packaging systems (because the default settings for 8.1 and 8.2 will be the same). To see all your installed packages, try: dpkg-query --list 'postgresql*' If you're interested in the details try --listfiles: dpkg-query --listfiles postgresql-common Basically version-specific binaries etc go in their own directories and what you run is just a wrapper that redirects to the appropriate version. -- Richard Huxton Archonet Ltd
On Tue, 2007-10-09 at 11:46 +0200, Andrew Kelly wrote: > Hi folks, > > please forgive what feels like a no-brainer even as I ask it, but... <snip> Just wanted to thank everybody who's provided feedback. I'm squared away now, and very appreciative of all the help. Andy