Grabing and applying a dump

Поиск
Список
Период
Сортировка
От Marc Richter
Тема Grabing and applying a dump
Дата
Msg-id trinity-0c1100ac-c381-40db-8371-4ceae9097d8f-1407250538034@3capp-gmx-bs10
обсуждение исходный текст
Ответы Re: Grabing and applying a dump  (Kevin Grittner <kgrittn@ymail.com>)
Список 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
withthe 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
normallynoone would ever imagine to use nowadays, but these are still productive. So, the steps I'm trying to take here
arevery 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
sameenvironments 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
lackof Postgres Know-How might kick in ... I expected that all a Postgres server, geting a command and query set from
anotherPostgres server of the same version, replies on insert is "I know exactly what's to do - no worries!". Instead I
getseveral 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
byone 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
Postgres8.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


В списке pgsql-novice по дате отправления:

Предыдущее
От: Jayadevan
Дата:
Сообщение: Re: Better use of memory
Следующее
От: Kevin Grittner
Дата:
Сообщение: Re: Grabing and applying a dump