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

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@online.ee>

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.klaver@gmail.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@online.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@online.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.klaver@gmail.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.klaver@gmail.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.klaver@gmail.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.klaver@gmail.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@iol.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ö