Thread: 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
Tuan Hoang Anh
2010/12/15 Andrus Moor <eetasoft@online.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@postgresql.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.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ö
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.klaver@gmail.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 whatyou'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 worksas expected. Of course, the big drawback is that you need to write them first and that (as with all home-cooked software) there will bebugs 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.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.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