Grabing and applying a dump - Mailing list pgsql-novice
From | Marc Richter |
---|---|
Subject | Grabing and applying a dump |
Date | |
Msg-id | trinity-0c1100ac-c381-40db-8371-4ceae9097d8f-1407250538034@3capp-gmx-bs10 Whole thread Raw |
Responses |
Re: Grabing and applying a dump
|
List | 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 by date: