Thread: Grabing and applying a dump
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
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
Kevin Grittner-5 wrote > Marc Richter < > richter_marc@ > > wrote: >> >> 1) create a dump from a source server using pg_dumpall with >> 'column-inserts' option. >> 2) Applying that dump into another server using psql. >> > > 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. Marc, You need to be more precise on the different servers and versions. Note that you should not use the dump/restore tools from a version more recent than the version you are restoring into. It sounds like you may be trying to restore into a 8.2 server but using a newer toolset. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Grabing-and-applying-a-dump-tp5813794p5813810.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
Am 05.08.2014 19:33, schrieb David G Johnston: > Kevin Grittner-5 wrote: >>> 1) create a dump from a source server using pg_dumpall with >>> 'column-inserts' option. >>> 2) Applying that dump into another server using psql. >>> >> 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. > Marc, > > You need to be more precise on the different servers and versions. Note > that you should not use the dump/restore tools from a version more recent > than the version you are restoring into. It sounds like you may be trying > to restore into a 8.2 server but using a newer toolset. > > David J. > Hi David and Kevin, thank you for your helpful input! You are right: Some of my tests to apply the dump to a fresh 8.2.5 instance were done using recent tools. I thought this is not a problem or even: the better choice, since the official Postgres Docs seems to suggest this: http://www.postgresql.org/docs/9.1/static/upgrading.html: """ It is recommended that you use thepg_dumpandpg_dumpallprograms from the newer version ofPostgreSQL, to take advantage of enhancements that might have been made in these programs. Current releases of the dump programs can read data from any server version back to 7.0. """ But you are right: When I use only 8.2.5 tools, these kind of issues do not happen! Thank you. Best regards, Marc
Hi
On 07 Aug 2014 11:36 AM, "Marc Richter" <richter_marc@gmx.net> wrote:
>
>
> Am 05.08.2014 19:33, schrieb David G Johnston:
>
>> Kevin Grittner-5 wrote:
>>>>
>>>> 1) create a dump from a source server using pg_dumpall with
>>>> 'column-inserts' option.
>>>> 2) Applying that dump into another server using psql.
>>>>
>>> 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.
>>
>> Marc,
>>
>> You need to be more precise on the different servers and versions. Note
>> that you should not use the dump/restore tools from a version more recent
>> than the version you are restoring into. It sounds like you may be trying
>> to restore into a 8.2 server but using a newer toolset.
>>
>> David J.
>>
>
> Hi David and Kevin, thank you for your helpful input!
>
> You are right: Some of my tests to apply the dump to a fresh 8.2.5 instance were done using recent tools. I thought this is not a problem or even: the better choice, since the official Postgres Docs seems to suggest this:
>
> http://www.postgresql.org/docs/9.1/static/upgrading.html:
>
> """
> It is recommended that you use thepg_dumpandpg_dumpallprograms from the newer version ofPostgreSQL, to take advantage of enhancements that might have been made in these programs. Current releases of the dump programs can read data from any server version back to 7.0.
> """
>
> But you are right: When I use only 8.2.5 tools, these kind of issues do not happen! Thank you.
What that means is that when you are upgrading from an older version to a newer version you should use the pg_dumpall from the newer version (that you are upgrading to). It does not mean you should use pg_dumpall from a version newer than the version of Postgres that you are upgrading to.
Am 07.08.2014 21:58, schrieb Michael Wood: > Hi > > On 07 Aug 2014 11:36 AM, "Marc Richter" <richter_marc@gmx.net > <mailto:richter_marc@gmx.net>> wrote: > > > > > > Am 05.08.2014 19:33, schrieb David G Johnston: > > > >> Kevin Grittner-5 wrote: > >>>> > >>>> 1) create a dump from a source server using pg_dumpall with > >>>> 'column-inserts' option. > >>>> 2) Applying that dump into another server using psql. > >>>> > >>> 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. > >> > >> Marc, > >> > >> You need to be more precise on the different servers and versions. Note > >> that you should not use the dump/restore tools from a version more > recent > >> than the version you are restoring into. It sounds like you may be > trying > >> to restore into a 8.2 server but using a newer toolset. > >> > >> David J. > >> > > > > Hi David and Kevin, thank you for your helpful input! > > > > You are right: Some of my tests to apply the dump to a fresh 8.2.5 > instance were done using recent tools. I thought this is not a problem > or even: the better choice, since the official Postgres Docs seems to > suggest this: > > > > http://www.postgresql.org/docs/9.1/static/upgrading.html: > > > > """ > > It is recommended that you use thepg_dumpandpg_dumpallprograms from > the newer version ofPostgreSQL, to take advantage of enhancements that > might have been made in these programs. Current releases of the dump > programs can read data from any server version back to 7.0. > > """ > > > > But you are right: When I use only 8.2.5 tools, these kind of issues > do not happen! Thank you. > > What that means is that when you are upgrading from an older version to > a newer version you should use the pg_dumpall from the newer version > (that you are upgrading to). It does not mean you should use pg_dumpall > from a version newer than the version of Postgres that you are upgrading to. > I've read it like "if you have a newer version of client tools available, use it for all purposes, since they are fully backwards compatible anyway". I know this isn't what's written there. I just say what I have read it like. Best regards, Marc