Обсуждение: syntax error at or near "PROCEDURAL"
Hello,
I try to restore a dump file on a new Database. But unfortunatly i get this error:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 532; 2612 16386 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "PROCEDURAL"
LINE 1: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
^
Command was: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
and I don't know how I can avoid it.
My steps:
==========
First dump the current DB (postgres version 8.2.6) with the following command:
pg_dump -h v-oracle-4 -p 5432 -UAdministrator -Fc -o -f C:\test2.backup MyDB
Second try to restore Dump on another server with postgres 8.2.6 with the folowing command:
pg_restore.exe -h 192.168.30.107 -p 5432 -UAdministrator -C -e -d Mirror c:\test2.backup 2>c:\t.txt
The script stops and the errormessage mentioned above apears in the file.
Do you have any hints why the error appears and how i can sove it? Please help.
Thanks Andreas
ps:Another hint perhaps: I get a second password prompt after i entered the first one. So i have to insert the password
twice.
On Mon, Feb 25, 2008 at 06:40:43PM +0100, Andreas Lau wrote: > Hello, > > I try to restore a dump file on a new Database. But unfortunatly i get this error: > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 532; 2612 16386 PROCEDURAL LANGUAGE plpgsql postgres > pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "PROCEDURAL" > LINE 1: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres; > ^ > Command was: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres; > > and I don't know how I can avoid it. > > My steps: > ========== > First dump the current DB (postgres version 8.2.6) with the following command: > pg_dump -h v-oracle-4 -p 5432 -UAdministrator -Fc -o -f C:\test2.backup MyDB > > Second try to restore Dump on another server with postgres 8.2.6 with the folowing command: > pg_restore.exe -h 192.168.30.107 -p 5432 -UAdministrator -C -e -d Mirror c:\test2.backup 2>c:\t.txt > > The script stops and the errormessage mentioned above apears in the file. > > Do you have any hints why the error appears and how i can sove it? Please help. Any chance you are running pg_dump/pg_restore version 8.3? The syntax appears to be what 8.3 uses, not 8.3.. You can use (and should use) an 8.3 pg_dump to dump your 8.2 database when upgrading to 8.3, but you often can't reload a dump created by 8.3 on an 8.3 database. //Magnus
On Tue, Feb 26, 2008 at 10:23:05AM +0100, Magnus Hagander wrote: > Any chance you are running pg_dump/pg_restore version 8.3? The syntax > appears to be what 8.3 uses, not 8.3.. > > You can use (and should use) an 8.3 pg_dump to dump your 8.2 database when > upgrading to 8.3, but you often can't reload a dump created by 8.3 on an > 8.3 database. I think some of those 3's need to be 2's... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Вложения
On Tue, Feb 26, 2008 at 10:26:25AM +0100, Martijn van Oosterhout wrote: > On Tue, Feb 26, 2008 at 10:23:05AM +0100, Magnus Hagander wrote: > > Any chance you are running pg_dump/pg_restore version 8.3? The syntax > > appears to be what 8.3 uses, not 8.3.. > > > > You can use (and should use) an 8.3 pg_dump to dump your 8.2 database when > > upgrading to 8.3, but you often can't reload a dump created by 8.3 on an > > 8.3 database. > > I think some of those 3's need to be 2's... Argh. Yes. The last one in each paragraph. I blame fosdem-recovery ;-) //Magnus
Thanks for your hinds. It's true i used pg_dump and pg_restore version 8.3 to dump and restore 8.2 databases. I wouldn't have thought that therecould be problems. But it seems so. I will give it a try an report the result. Thanks Andreas -----Ursprüngliche Nachricht----- Von: Magnus Hagander [mailto:magnus@hagander.net] Gesendet: Dienstag, 26. Februar 2008 10:30 An: Andreas Lau; pgsql-general@postgresql.org Betreff: Re: [GENERAL] syntax error at or near "PROCEDURAL" On Tue, Feb 26, 2008 at 10:26:25AM +0100, Martijn van Oosterhout wrote: > On Tue, Feb 26, 2008 at 10:23:05AM +0100, Magnus Hagander wrote: > > Any chance you are running pg_dump/pg_restore version 8.3? The > > syntax appears to be what 8.3 uses, not 8.3.. > > > > You can use (and should use) an 8.3 pg_dump to dump your 8.2 > > database when upgrading to 8.3, but you often can't reload a dump > > created by 8.3 on an > > 8.3 database. > > I think some of those 3's need to be 2's... Argh. Yes. The last one in each paragraph. I blame fosdem-recovery ;-) //Magnus
My problem seems to be solved. Actually it was because of the differences between the version of pg_dump/pg_restore (8.3)and of my postgres DB (8.2.6). As i used the appropriate versions for pg_dump/pg_restore the restauration runs through without any errors. Thanks for that Andreas Actually i used version 8.2.6 both for pg_restore and pg_dump. On Tue, Feb 26, 2008 at 10:48:06AM +0100, Andreas Lau wrote: > Thanks for your hinds. > It's true i used pg_dump and pg_restore version 8.3 to dump and restore 8.2 databases. I wouldn't have thought that therecould be problems. But it seems so. I will give it a try an report the result. > > Thanks Andreas > > -----Ursprüngliche Nachricht----- > Von: Magnus Hagander [mailto:magnus@hagander.net] > Gesendet: Dienstag, 26. Februar 2008 10:30 > An: Andreas Lau; pgsql-general@postgresql.org > Betreff: Re: [GENERAL] syntax error at or near "PROCEDURAL" > > On Tue, Feb 26, 2008 at 10:26:25AM +0100, Martijn van Oosterhout wrote: > > On Tue, Feb 26, 2008 at 10:23:05AM +0100, Magnus Hagander wrote: > > > Any chance you are running pg_dump/pg_restore version 8.3? The > > > syntax appears to be what 8.3 uses, not 8.3.. > > > > > > You can use (and should use) an 8.3 pg_dump to dump your 8.2 > > > database when upgrading to 8.3, but you often can't reload a dump > > > created by 8.3 on an > > > 8.3 database. > > > > I think some of those 3's need to be 2's... > > Argh. Yes. The last one in each paragraph. I blame fosdem-recovery ;-) > > //Magnus