Re: DB upgrade

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