Обсуждение: 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

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