Grabing and applying a dump

From: "Marc Richter" <richter_marc(at)gmx(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Grabing and applying a dump
Date: 2014-08-05 14:55:38
Message-ID: trinity-0c1100ac-c381-40db-8371-4ceae9097d8f-1407250538034@3capp-gmx-bs10
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi everyone,

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. This is where my lack of Postgres Know-How might kick in ... I expected that all a Postgres server, geting a command and query set from another Postgres server of the same version, replies on insert is "I know exactly what's to do - no worries!". Instead 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...
^

2)
You are now connected to database "db1" as user "postgres".
SET default_transaction_read_only = off;
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
CREATE SCHEMA _db1cluster;
ALTER SCHEMA _db1cluster OWNER TO slony;
CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
ERROR: syntax error at or near "PROCEDURAL"
LINE 1: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
^
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
ERROR: syntax error at or near "PROCEDURAL"
LINE 1: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

There are more errors. But I'd prefer to explain one issue by another, as much as you for sure prefer to read them one by one instead of all in one go :)

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.

Best regards,
Marc

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Kevin Grittner 2014-08-05 17:00:55 Re: Grabing and applying a dump
Previous Message Jayadevan 2014-08-02 04:03:09 Re: Better use of memory