Обсуждение: Backup Compatibility between minor versions.
Hi, Should it be possible to create a compressed backup of a version 8.0.1 database running on linux and restore that backup on version 8.0.3 running on XP? I ask this because it does not seem to work for me. Many problems seem to arise to do with tsearch2 extensions to tables, even though tsearch2 is installed on the XP machine. The backup was created using pgAdminIII. Howard. www.selestial.com
To improve compatibility, I created a backup on the linux system (8.0.1) using plain format, data only with triggers disabled. However, when I try to restore the plain sql on the windows machine (8.0.3) it fails because the first insert command fails a foreign key constraint. Can I disable foreign key checks for the duration of the restore? Howard www.selestial.com Howard Cole wrote: > Hi, > > Should it be possible to create a compressed backup of a version 8.0.1 > database running on linux and restore that backup on version 8.0.3 > running on XP? I ask this because it does not seem to work for me. > Many problems seem to arise to do with tsearch2 extensions to tables, > even though tsearch2 is installed on the XP machine. > > The backup was created using pgAdminIII. > > Howard. > www.selestial.com > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
There appears to be a problem with pgadminIII where the option to disable triggers does not actually do anything! If I switch to the pg_dump command line then the disable triggers works. Unfortunately I still cannot restore a database backed up from a linux machine running 8.0.1 to an windows machine running 8.0.3. My problem seems to be: 1. I use a archive backup, which contains tsearch2 tables causes conflict with existing tsearch2 tables created from template1. or... 2. I use plain backup and restore to a blank database created from template0 - and the restore fails because it can't find tsearch2 types. My head hurts. I shall try to manually hack the plain backup file to remove references to the tsearch2 tables. Will all of these problems disappear if I upgrade the linux machine to 8.0.3? Howard. www.selestial.com Howard Cole wrote: > To improve compatibility, I created a backup on the linux system > (8.0.1) using plain format, data only with triggers disabled. However, > when I try to restore the plain sql on the windows machine (8.0.3) it > fails because the first insert command fails a foreign key constraint. > Can I disable foreign key checks for the duration of the restore? > > Howard > www.selestial.com > > > Howard Cole wrote: > >> Hi, >> >> Should it be possible to create a compressed backup of a version >> 8.0.1 database running on linux and restore that backup on version >> 8.0.3 running on XP? I ask this because it does not seem to work for >> me. Many problems seem to arise to do with tsearch2 extensions to >> tables, even though tsearch2 is installed on the XP machine. >> >> The backup was created using pgAdminIII. >> >> Howard. >> www.selestial.com >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Howard Cole <howardnews@selestial.com> writes: > To improve compatibility, I created a backup on the linux system > (8.0.1) using plain format, data only with triggers disabled. However, > when I try to restore the plain sql on the windows machine (8.0.3) it > fails because the first insert command fails a foreign key > constraint. Can I disable foreign key checks for the duration of the > restore? I would suggest upgrading to 8.0.3 on the linux machine, just to make sure you get all pg_dump bugfixes, then retrying the dump/restore. AFAIK there's no reason it shouldn't work. If you still have trouble, post the exact dump and restore commands you're using and the exact error message you get. -Doug
Thanks for the response Doug. I am frightened to upgrade the linux
machine to 8.0.3 at the moment because the server is live and I want to
make sure that 8.0.3 will fix it. I have extracted the relevant parts of
the restore as follows:
1. The restore command
pg_restore.exe -i -h 10.202.6.195 -p 5432 -U postgres -d export -l
"backup9.backup"
2. The first error in the output.
pg_restore: creating FUNCTION dex_init(text)
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 25; 1255 72303
FUNCTION dex_init(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: unsafe
use of INTERNAL pseudo-type
DETAIL: A function returning "internal" must have at least one
"internal" argument.
Command was: CREATE FUNCTION dex_init(text) RETURNS internal
AS '$libdir/tsearch2', 'dex_init'
LANGUAGE c;
This was intalled to a clean database on XP with no tsearch2 installed.
To me it looks as if there has been some modification in the use of
INTERNAL from 8.0.1 to 8.0.3.
FYI here is a dump of that tsearch2 function:
CREATE OR REPLACE FUNCTION dex_init(text)
RETURNS internal AS
'$libdir/tsearch2', 'dex_init'
LANGUAGE 'c' VOLATILE;
ALTER FUNCTION dex_init(text) OWNER TO postgres;
Is it possible to change that function to be compatible with both 8.0.1
and 8.0.3 - or am I looking in the wrong place?
Howard
www.selestial.com
>I would suggest upgrading to 8.0.3 on the linux machine, just to make
>sure you get all pg_dump bugfixes, then retrying the dump/restore.
>AFAIK there's no reason it shouldn't work. If you still have trouble,
>post the exact dump and restore commands you're using and the exact
>error message you get.
>
>-Doug
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
Interestingly, this is the latest tsearch2 function that ships with 8.0.3 - note the slightly different syntax to the one below. It looks like the compatibility issue is caused by this. I shall try and modify the function in the 8.0.1 database, then try and restore it to a 8.0.3 server and see what happens! Howard Cole www.selestial.com CREATE OR REPLACE FUNCTION dex_init(internal) RETURNS internal AS '$libdir/tsearch2', 'dex_init' LANGUAGE 'c' VOLATILE; ALTER FUNCTION dex_init(internal) OWNER TO postgres; Howard Cole wrote: > > CREATE OR REPLACE FUNCTION dex_init(text) > RETURNS internal AS > '$libdir/tsearch2', 'dex_init' > LANGUAGE 'c' VOLATILE; > ALTER FUNCTION dex_init(text) OWNER TO postgres; > > > Is it possible to change that function to be compatible with both > 8.0.1 and 8.0.3 - or am I looking in the wrong place? > > > >
Howard Cole <howardnews@selestial.com> writes: > Interestingly, this is the latest tsearch2 function that ships with > 8.0.3 - note the slightly different syntax to the one below. It looks > like the compatibility issue is caused by this. Read the 8.0.3 release notes ... http://www.postgresql.org/docs/8.0/static/release.html#RELEASE-8-0-3 regards, tom lane
Thanks Tom... this reports that there were modifications to the tsearch2 functions. But this is how I worked around the restore problem, for anyone that has similar issues: 1. pg_dump the 8.0.1 database in archive format. 2. Create an empty database on the 8.0.3 server 3. Run the contrib/tsearch2.sql to add tsearch2 to the empty database 4. Run "pg_restore --list" on the archive and pipe to a file (restore.txt) 5. Edit the restore.txt file and remove all reference to the tsearch2 functions/tables/indexes 6. Run "pg_restore --disable-triggers -L restore.txt" on the archive file and pipe the output to a sql file "restore.sql" 7. Run the resultant sql file using psql to the restore the database. I also needed to recreate the users and the access permissions to get fully up to speed. I think I could also have fixed the tsearch2 functions in the 8.0.1 database directly, and then the restore would have worked, but as I said before this was a live system so I did not want to fiddle. There is now an issue with case sensitivity. But I will start that in a clean thread. Regards. Howard Cole www.selestial.com Tom Lane wrote: >Howard Cole <howardnews@selestial.com> writes: > > >>Interestingly, this is the latest tsearch2 function that ships with >>8.0.3 - note the slightly different syntax to the one below. It looks >>like the compatibility issue is caused by this. >> >> > >Read the 8.0.3 release notes ... >http://www.postgresql.org/docs/8.0/static/release.html#RELEASE-8-0-3 > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > >