Re: Grabing and applying a dump

Marc Richter <richter_marc@gmx.net> wrote:

> I am not very familiar with Postgres yet, but I'm very interested and these
> are my very first steps to get in touch with the software.
> I'm a system administrator from Cologne, Germany. I'm trying the
> following on PostgreSQL 8.2.5 currently:
>
> 1) create a dump from a source server using pg_dumpall with
> 'column-inserts' option.
> 2) Applying that dump into another server using psql.
>
> Normally, no big deal.
> Before the "8.2.5 is sooooo outdated" rumors begin: There are a lot of
> ancient servers on my current site, which normally noone would ever imagine to
> use nowadays, but these are still productive. So, the steps I'm trying to
> take here are very first on the long way to a recent Postgres version.
>
> Both, the source and the destination server, are using Postgres binaries which
> were compiled from source (using the same environments and options). So there
> shouldn't be any versioning issues or such.
>
> The commandline used to create the dump is:
>   pg_dumpall -c --column-inserts -v -h 192.168.20.12 -U postgres | gzip >
> dump.gz
>
> The commandline used to apply the dump to the destination server is:
>   zcat dump.gz | psql -U postgres -h ip.of.destination.server
>
> I'm facing some issues by that which I wouldn't have expected between
> two servers of the same version.

> I get several errors like the following:
>
> 1)
>   ...
>   CREATE ROLE role1;
>   ALTER ROLE role1 WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN
> NOREPLICATION PASSWORD 'MD5HASH-HERE' VALID UNTIL 'infinity';
>
>   ERROR:  syntax error at or near "NOREPLICATION"
>     LINE 1: ...SUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICAT...
>                                                                 ^

NOREPLICATION would not be included in pg_dumpall output unless you
are using executables from 9.1 or later.  Try running pg_dumpall
--version and see what it says.

> What do I have to do to make the dump being able to insert correctly? I know
> this might be difficult to tell, since Postgres 8.2.5 outdated a long time
> already. But there must be a way to migrate to a recent version. Please help me
> with this.

If you want to upgrade using pg_dump and/or pg_dumpall, use the
dump executable from the new version.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company