Backup Compatibility between minor versions.

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:

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

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:

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

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:

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: