How to restore from backup to 8.4.3 server using 9.0 dump/restore

Server is

PostgreSQL 8.4.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
4.3.2-1.1) 4.3.2, 32-bit

Backup is created using 9.0RC pg_dump.exe file

Trying to restore from this backup to same server using 9.0RC pg_restore.exe
causes error

"..\pg_dump\pg_restore.exe" -h mysite.com -U
eur1_owner -i --no-privileges --no-owner -d "eur1" "C:\mybackup.backup"

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 929; 2612 16389 PROCEDURAL
LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at
or near "PROCEDURAL"
LINE 1: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

How to restore from this backup to 8.4.3 server using 9.0 pg_restore ?

Andrus.

You can test restore by change CREATE OR REPLACE PROCEDURAL LANGUAGE
plpgsql; to CREATE PROCEDURAL LANGUAGE plpgsql;

Tuan Hoang Anh

2010/12/15 Andrus Moor <eetasoft(at)online(dot)ee>

> Server is
>
> PostgreSQL 8.4.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.2-1.1) 4.3.2, 32-bit
>
> Backup is created using 9.0RC pg_dump.exe file
>
> Trying to restore from this backup to same server using 9.0RC
> pg_restore.exe
> causes error
>
> "..\pg_dump\pg_restore.exe" -h mysite.com -U
> eur1_owner -i --no-privileges --no-owner -d "eur1" "C:\mybackup.backup"
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 929; 2612 16389 PROCEDURAL
> LANGUAGE plpgsql postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: syntax error
> at
> or near "PROCEDURAL"
> LINE 1: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
>
>
> How to restore from this backup to 8.4.3 server using 9.0 pg_restore ?
>
> Andrus.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Wednesday 15 December 2010 1:29:09 am Andrus Moor wrote:
> Server is
>
> PostgreSQL 8.4.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.2-1.1) 4.3.2, 32-bit
>
> Backup is created using 9.0RC pg_dump.exe file
>
> Trying to restore from this backup to same server using 9.0RC
> pg_restore.exe causes error
>
> "..\pg_dump\pg_restore.exe" -h mysite.com -U
> eur1_owner -i --no-privileges --no-owner -d "eur1" "C:\mybackup.backup"
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 929; 2612 16389 PROCEDURAL
> LANGUAGE plpgsql postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: syntax error
> at or near "PROCEDURAL"
> LINE 1: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
>
>
> How to restore from this backup to 8.4.3 server using 9.0 pg_restore ?
>
> Andrus.

I am not sure I follow. Are you taking a pg_dump of a 9.0 database using a 9.0
version of pg_dump and trying to restore to a 8.4.3 database or are using the
9.0 pg_dump against the 8.4.3 server and then restoring back to it? In either
case the problem you see above will probably be only the first. Going backwards
using pg_dump/pg_restore is not guaranteed to work. Your best hope if you must
do that is to do the dump in plain text format and change the problems manually
as you proceed.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Adrian,

thank you.

> I am not sure I follow. Are you taking a pg_dump of a 9.0 database using a
> 9.0
> version of pg_dump and trying to restore to a 8.4.3 database or are using
> the
> 9.0 pg_dump against the 8.4.3 server and then restoring back to it? In
> either
> case the problem you see above will probably be only the first.

I used only 9.0 dump and restore. I did the following:

1. Created backup copy from 8.4.3 using 9.0 pg_dump
2. Restored from this backup to 8.4.3 using 9.0 pg_restore

9.0 pg_restore fails since 8.4.3 server reports invalid sql command in
create
language plpgsql statement. previous pg_restores worked OK.
Is this 9.0 pg_restore bug ?

> Going backwards
> using pg_dump/pg_restore is not guaranteed to work. Your best hope if you
> must
> do that is to do the dump in plain text format and change the problems
> manually
> as you proceed.

Application executes 9.0 pg_dump/pg_restore
Application can connect to different servers starting and 8.1 and need to
able for backup/restore for every this server.
For single backup copy, dump and restore are executed for same server
version,

e.q backup created from site A using Postgres version x will used to
restore
only to this site for same postgres version x
Backup created from site B using Postgres version y will used to restore
only to
this site to same Postgres version y

How to support backup restore for all >=8.1 servers using single
pg_dump/pg_restore ?

Currently I looks like for 8.4 serves 9.0 backup/restore cannot used.

Andrus.

"Andrus Moor" <eetasoft(at)online(dot)ee> writes:
> I used only 9.0 dump and restore. I did the following:

> 1. Created backup copy from 8.4.3 using 9.0 pg_dump
> 2. Restored from this backup to 8.4.3 using 9.0 pg_restore

> 9.0 pg_restore fails since 8.4.3 server reports invalid sql command in
> create
> language plpgsql statement. previous pg_restores worked OK.
> Is this 9.0 pg_restore bug ?

No, this is just pilot error. Any version of pg_dump will produce
output that is meant to be loaded into the matching server version
(or a later version). If you are intending to load back into 8.4,
use the 8.4 pg_dump.

You may have been reading the recommendation to use the later version's
pg_dump when dumping an older server to perform an upgrade. It's good
advice, but only for upgrades.

regards, tom lane

> No, this is just pilot error. Any version of pg_dump will produce
> output that is meant to be loaded into the matching server version
> (or a later version). If you are intending to load back into 8.4,
> use the 8.4 pg_dump.
>
> You may have been reading the recommendation to use the later version's
> pg_dump when dumping an older server to perform an upgrade. It's good
> advice, but only for upgrades.

Windows application needs to support backup/restore for all servers >=8
between same server version.
So it must distibute 8.0, 8.1, 8.2, 8.3, 8.4, 9.0 pg_dump/pg_restore with
application,
including all VC++ runtime versions and all dlls specific to this version
and invoke
specific pg_dump/pg_restore depending on server version ?

Do you really think that this is reasonable ?
I'nt there a simpler way ?

I havent seen that pg_admin includes every pg_dump / pg_restore version.

Andrus.

"Andrus Moor" <eetasoft(at)online(dot)ee> writes:
>> No, this is just pilot error. Any version of pg_dump will produce
>> output that is meant to be loaded into the matching server version
>> (or a later version). If you are intending to load back into 8.4,
>> use the 8.4 pg_dump.

> Windows application needs to support backup/restore for all servers >=8
> between same server version.

Why does it have that requirement? And why doesn't it use the pg_dump
that came with the server? It seems pretty lame to assume that your app
has to provide pg_dump and not any other part of the Postgres
installation.

regards, tom lane

On 12/15/2010 07:34 AM, Andrus Moor wrote:
>> No, this is just pilot error. Any version of pg_dump will produce
>> output that is meant to be loaded into the matching server version
>> (or a later version). If you are intending to load back into 8.4,
>> use the 8.4 pg_dump.
>>
>> You may have been reading the recommendation to use the later version's
>> pg_dump when dumping an older server to perform an upgrade. It's good
>> advice, but only for upgrades.
>
> Windows application needs to support backup/restore for all servers >=8
> between same server version.
> So it must distibute 8.0, 8.1, 8.2, 8.3, 8.4, 9.0 pg_dump/pg_restore
> with application,
> including all VC++ runtime versions and all dlls specific to this
> version and invoke
> specific pg_dump/pg_restore depending on server version ?

No it only needs to use the pg_dump/pg_restore that exist for each Pg
instance.

>
> Do you really think that this is reasonable ?
> I'nt there a simpler way ?
>
> I havent seen that pg_admin includes every pg_dump / pg_restore version.

I don't use pgAdmin so I am flying blind here, but I guess they use some
variation of what I suggest above.

>
> Andrus.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> Why does it have that requirement? And why doesn't it use the pg_dump
> that came with the server? It seems pretty lame to assume that your app
> has to provide pg_dump and not any other part of the Postgres
> installation.

Application is like pg_admin.
It is typical client application which is used to edit data in existing
servers running in different sites over internet.
Application must have function to backup and restore whole database in same
>=8 server where it connects. Only 5432 port is open to internet.

Andrus.

On 12/15/2010 08:43 AM, Andrus Moor wrote:
>> Why does it have that requirement? And why doesn't it use the pg_dump
>> that came with the server? It seems pretty lame to assume that your app
>> has to provide pg_dump and not any other part of the Postgres
>> installation.
>
> Application is like pg_admin.
> It is typical client application which is used to edit data in existing
> servers running in different sites over internet.
> Application must have function to backup and restore whole database in
> same >=8 server where it connects. Only 5432 port is open to internet.
>
> Andrus.

Well that is a problem :) No solution comes immediately to mind. Re my
previous comment about pgAdmin, I went ahead and read the docs and it
uses the pg_dump that it finds in the path or is in the same place as
its executable.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Wednesday 15 December 2010 8:43:18 am Andrus Moor wrote:
> > Why does it have that requirement? And why doesn't it use the pg_dump
> > that came with the server? It seems pretty lame to assume that your app
> > has to provide pg_dump and not any other part of the Postgres
> > installation.
>
> Application is like pg_admin.
> It is typical client application which is used to edit data in existing
> servers running in different sites over internet.
> Application must have function to backup and restore whole database in same
>server where it connects. Only 5432 port is open to internet.
>
> Andrus.

I got to thinking more about this. How are the databases administered? In other
words how are they started/stopped, upgraded, logs read, etc?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> I got to thinking more about this. How are the databases administered? In
> other
> words how are they started/stopped, upgraded, logs read, etc?

Databases are working many years in 24x7 mode without administration.
For every new new site newest PostgreSql was installed.

Andrus.

Le 15/12/2010 17:26, Adrian Klaver a écrit :
> On 12/15/2010 07:34 AM, Andrus Moor wrote:
>>> No, this is just pilot error. Any version of pg_dump will produce
>>> output that is meant to be loaded into the matching server version
>>> (or a later version). If you are intending to load back into 8.4,
>>> use the 8.4 pg_dump.
>>>
>>> You may have been reading the recommendation to use the later version's
>>> pg_dump when dumping an older server to perform an upgrade. It's good
>>> advice, but only for upgrades.
>>
>> Windows application needs to support backup/restore for all servers >=8
>> between same server version.
>> So it must distibute 8.0, 8.1, 8.2, 8.3, 8.4, 9.0 pg_dump/pg_restore
>> with application,
>> including all VC++ runtime versions and all dlls specific to this
>> version and invoke
>> specific pg_dump/pg_restore depending on server version ?
>
> No it only needs to use the pg_dump/pg_restore that exist for each Pg
> instance.
>
>>
>> Do you really think that this is reasonable ?
>> I'nt there a simpler way ?
>>
>> I havent seen that pg_admin includes every pg_dump / pg_restore version.
>
> I don't use pgAdmin so I am flying blind here, but I guess they use some
> variation of what I suggest above.
>

pgAdmin has the same issue. If you use the pg_dump distributed with
pgAdmin 1.12 to backup a 8.4 PostgreSQL server, you won't be able (or
have difficulties) to restore it on the 8.4 server. Or IOW, you're screwed.

That's why you can change the binaries or change the location, where the
binairies are found, in the options dialog.

And we don't distribute each pg_dump/pg_dumpall/pg_restore releases. It
would make the package so much bigger it isn't really worth it. Anyway,
even if we did, the UI can't choose the right release to use because it
would need to know on which release you want to restore it. Meaning that
the user would need to change the target release. Meaning he should
understand the issue underneath.

My whole point it that there is no best way to handle this, and probably
no good one either.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

On Wednesday 15 December 2010 11:55:24 am Andrus Moor wrote:
> > I got to thinking more about this. How are the databases administered? In
> > other
> > words how are they started/stopped, upgraded, logs read, etc?
>
> Databases are working many years in 24x7 mode without administration.
> For every new new site newest PostgreSql was installed.
>
> Andrus.

Another testimonial to the stability of Postgres :) We may be arguing semantics
here but I would consider dump/restore an admin function. How do you handle a
client restoring a database currently? I could see a client connecting to one
of the system dbs and doing a DROP DATABASE. From your earlier messages the
implication was that you used pg_restore to repopulate the db. My question then
is how do the clients make sure that they are not doing this on an active
database and keep it from going active during the process?

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> Another testimonial to the stability of Postgres :)
> We may be arguing semantics
> here but I would consider dump/restore an admin function. How do you
> handle a
> client restoring a database currently?

Database is 8.0 compliant.
In this case 8.4 pg_dump/pg_restore is used to dump and restore with any
same version 8 of server witthout issues.

This was broken in 9:
Postgres 9 emits invalid "create procedural language plpgsql" command which
does not work in any other version.
How to fix this without distributing two copies of pg_dump/pg_restore ?
Is it reasonable to create database and plpgsql language manually before
running pg_restore ? In this case invalid "create procedural language
plpgsql" issued by pg_restore gets ignored and maybe restore succeeds?

> I could see a client connecting to one
> of the system dbs and doing a DROP DATABASE.
> From your earlier messages the
> implication was that you used pg_restore to repopulate the db. My question
> then
> is how do the clients make sure that they are not doing this on an active
> database
> and keep it from going active during the process?

Applicaton asks for new database name and verifies that this does not exist
before executing
pg_restore.

Another requirement is to clone existing database in server with data. I
posted question about it and it seems that PostgreSql does not have any
capability to do this in server side in plpgsql fast.
So we need to use slow and unsafe dump/restore over internet for this also.

Andrus.

On 2010-12-16 09.16, Andrus Moor wrote:

>
> Another requirement is to clone existing database in server with data. I
> posted question about it and it seems that PostgreSql does not have any
> capability to do this in server side in plpgsql fast.

I'm probably misunderstanding but "CREATE DATABASE foo TEMPLATE bar"
will clone bar as foo including data. Of course this only works within
the same cluster.

> So we need to use slow and unsafe dump/restore over internet for this also.
>
> Andrus.
>

--
Regards,
Robert "roppert" Gravsjö

Robert,

> I'm probably misunderstanding but "CREATE DATABASE foo TEMPLATE bar" will
> clone bar as foo including data. Of course this only works within the same
> cluster.

Than you.
You are genious
I haven't never tought about this.

Will this work if database bar is accessed by other users ? Probably it
fails, so it cannot used.
Will this command create exact copy ?

Andrus.

On 16/12/2010 10:12, Andrus Moor wrote:
> Robert,
>
>> I'm probably misunderstanding but "CREATE DATABASE foo TEMPLATE bar"
>> will clone bar as foo including data. Of course this only works within
>> the same cluster.
>
> Than you.
> You are genious
> I haven't never tought about this.
>
> Will this work if database bar is accessed by other users ? Probably it
> fails, so it cannot used.

I don't know for sure, but I don't see why it should fail - it's only
reading it, not writing data to it or making any changes.

> Will this command create exact copy ?

Why wouldn't it? :-)

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

Hello,
> I don't know for sure, but I don't see why it should fail - it's only
> reading it, not writing data to it or making any changes.
Probably it will fail...
http://www.postgresql.org/docs/9.0/static/sql-createdatabase.html
Although it is possible to copy a database other than template1 by
specifying its name as the template, this is not (yet) intended as a
general-purpose "COPY DATABASE" facility. The principal limitation is that
no other sessions can be connected to the template database while it is
being copied. CREATE DATABASE will fail if any other connection exists
when it starts;
Regards,
Jayadevan

DISCLAIMER:

"The information in this e-mail and any attachment is intended only for
the person to whom it is addressed and may contain confidential and/or
privileged material. If you have received this e-mail in error, kindly
contact the sender and destroy all copies of the original communication.
IBS makes no warranty, express or implied, nor guarantees the accuracy,
adequacy or completeness of the information contained in this email or any
attachment and is not liable for any errors, defects, omissions, viruses
or for resultant loss or damage, if any, direct or indirect."

On 2010-12-16 11.12, Andrus Moor wrote:
> Robert,
>
>> I'm probably misunderstanding but "CREATE DATABASE foo TEMPLATE bar"
>> will clone bar as foo including data. Of course this only works within
>> the same cluster.
>
> Than you.
> You are genious
> I haven't never tought about this.
>
> Will this work if database bar is accessed by other users ? Probably it
> fails, so it cannot used.

Correct.

> Will this command create exact copy ?

Yes.

>
> Andrus.
>

--
Regards,
Robert "roppert" Gravsjö

On 2010-12-16 11.21, Jayadevan M wrote:
> Hello,
>> I don't know for sure, but I don't see why it should fail - it's only
>> reading it, not writing data to it or making any changes.
> Probably it will fail...
> http://www.postgresql.org/docs/9.0/static/sql-createdatabase.html
> Although it is possible to copy a database other than template1 by
> specifying its name as the template, this is not (yet) intended as a
> general-purpose "COPY DATABASE" facility. The principal limitation is that
> no other sessions can be connected to the template database while it is
> being copied. CREATE DATABASE will fail if any other connection exists
> when it starts;

Are there any other side effects to this besides failing CREATE DATABASE
command?

--
Regards,
Robert "roppert" Gravsjö

On Thursday 16 December 2010 12:16:47 am Andrus Moor wrote:
> > Another testimonial to the stability of Postgres :)
> > We may be arguing semantics
> > here but I would consider dump/restore an admin function. How do you
> > handle a
> > client restoring a database currently?
>
> Database is 8.0 compliant.
> In this case 8.4 pg_dump/pg_restore is used to dump and restore with any
> same version 8 of server witthout issues.

First from here:
http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
"Also, it is not guaranteed that pg_dump's output can be loaded into a server of
an older major version — not even if the dump was taken from a server of that
version. Loading a dump file into an older server may require manual editing of
the dump file to remove syntax not understood by the older server. "

The fact that it worked without intervention before is as much luck as anything
else. Depending on that behavior going forward is not prudent.

>
> This was broken in 9:
> Postgres 9 emits invalid "create procedural language plpgsql" command which
> does not work in any other version.
> How to fix this without distributing two copies of pg_dump/pg_restore ?
> Is it reasonable to create database and plpgsql language manually before
> running pg_restore ? In this case invalid "create procedural language
> plpgsql" issued by pg_restore gets ignored and maybe restore succeeds?

This is an example of the above. Two things at work here. First in 9.0 the
plpgsql language is loaded automatically. Two it is loaded using the CREATE OR
REPLACE LANGUAGE form of SQL, which did not exist prior to 9.0. It is the
REPLACE clause that is causing the problem. You could try creating the language
ahead of time and see if it just skips over the error. Another option would be
to use the -l switch to pg_restore to generate a list of the TOC entries for
the dump and store in a file. You then comment out the one for plpgsql and then
use the -L switch to read the edited file. See here for more detail:

http://www.postgresql.org/docs/9.0/interactive/app-pgrestore.html

>
> > I could see a client connecting to one
> > of the system dbs and doing a DROP DATABASE.
> > From your earlier messages the
> > implication was that you used pg_restore to repopulate the db. My
> > question then
> > is how do the clients make sure that they are not doing this on an active
> > database
> > and keep it from going active during the process?
>
> Applicaton asks for new database name and verifies that this does not exist
> before executing
> pg_restore.
>
> Another requirement is to clone existing database in server with data. I
> posted question about it and it seems that PostgreSql does not have any
> capability to do this in server side in plpgsql fast.
> So we need to use slow and unsafe dump/restore over internet for this also.

Yea the CREATE DATABASE TEMPLATE option is as close as it gets and you are
already aware of its limitations.

>
> Andrus.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

>Loading a dump file into an older server may require manual editing of
> the dump file to remove syntax not understood by the older server. "

pg_restore ignores erros during restore (it only returns exit code 1).
So "manual editing of the dump file to remove syntax not understood" is
never required.
Why this is not mentioned in docs ?

> It is the REPLACE clause that is causing the problem

How to force 9.0 to emit CREATE LANGUAGE command without replace clause ?

> Yea the CREATE DATABASE TEMPLATE option is as close as it gets and you
> are already aware of its limitations.

How to make CREATE DATABASE TEMPLATE to work if there are connected users
to template ?
Since pg_dump works if active users are connected, CREATE DATABASE TEMPLATE
should also work.

Andrus.

On 16 Dec 2010, at 9:16, Andrus Moor wrote:

> How to fix this without distributing two copies of pg_dump/pg_restore ?
> Is it reasonable to create database and plpgsql language manually before running pg_restore ? In

Are you sure that restoring dumps to your customers' sites is the best approach to install your software (if that's what you're doing)?

Most people seem to write scripts to install databases for their products, which has a few benefits:
- You can put those scripts under version control.
- You can write them in such a way that you can do incremental updates of a database corresponding to version X of your product, to version Y or Z of your product. With pg_dump/restore you can only restore an entire database at a time.
- You don't need to rely on the availability or installation location of pg_dump/pg_restore at your customer's site.
- You are much more flexible in what data gets inserted into your database and how that happens.
- It's easy to extend those scripts with, for example, some simple unit-tests to verify that your database on site works as expected.

Of course, the big drawback is that you need to write them first and that (as with all home-cooked software) there will be bugs in them.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4d0a5a32802651802549062!

On 12/16/2010 08:47 AM, Andrus Moor wrote:
>> Loading a dump file into an older server may require manual editing of
>> the dump file to remove syntax not understood by the older server. "
>
> pg_restore ignores erros during restore (it only returns exit code 1).
> So "manual editing of the dump file to remove syntax not understood" is
> never required.
> Why this is not mentioned in docs ?

That begs the question what is the problem then? If the 9.0 pg_restore
is skipping over the error there should be no problem. As to the docs,
when I have seen this besides skipping over the error pg_restore skipped
over creating the object also. This in worst case led to a cascading
problem where subsequent objects dependent on the skipped object where
not loaded either. Bottom line is you are depending on a behavior which
is documented not to be guaranteed to work. That is not going to go
away, so it would really be a good idea to come up with another
solution. This has been mentioned multiple times and now has reached the
point of beating a dead horse :)

>
>> It is the REPLACE clause that is causing the problem
>
> How to force 9.0 to emit CREATE LANGUAGE command without replace clause ?

As far as I know you can't, it goes to the whole backwards compatibility
issue already covered. I gave you an option in the previous post re
using -l and -L switches to pg_restore.

>
>> Yea the CREATE DATABASE TEMPLATE option is as close as it gets and you
>> are already aware of its limitations.
>
> How to make CREATE DATABASE TEMPLATE to work if there are connected users
> to template ?
> Since pg_dump works if active users are connected, CREATE DATABASE TEMPLATE
> should also work.
>
> Andrus.

At this point as I see it you have the following options:
1) Bundle version specific pg_dump/pg_restore code with your application
2) Give your users admin access. One option is phppgadmin:
http://phppgadmin.sourceforge.net/
3) An off the top of my head suggestion. Use one of the untrusted
languages to write functions that access the file system and run the
pg_restore/pg_dump programs. This would require that language be
included in each database as well as the function(s). Could be put in
template1 so subsequent databases created would pick them up. Does
create a potential security risk though.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> writes:
> On 12/16/2010 08:47 AM, Andrus Moor wrote:
>>> Loading a dump file into an older server may require manual editing of
>>> the dump file to remove syntax not understood by the older server. "

>> pg_restore ignores erros during restore (it only returns exit code 1).
>> So "manual editing of the dump file to remove syntax not understood" is
>> never required.
>> Why this is not mentioned in docs ?

> That begs the question what is the problem then?

The problem of course is that Andrus' statement is wrong. There is not,
and never has been, any guarantee that pg_dump output would load into an
older server without hand-editing to adjust uses of new syntax. Which
is exactly what the documentation says. He's apparently only had
experience with version combinations where no editing was needed, but he
was just lucky (and pretty conservative with what DDL he used). We have
changed the output in non-backward-compatible ways before now, and
doubtless will again in the future. As an example, 9.0 did add OR
REPLACE to its CREATE LANGUAGE commands, but the previous output
wouldn't have worked before 8.1 anyway --- before that CREATE LANGUAGE
had to fully specify all the language's options, because there was no
pg_pltemplate.

regards, tom lane