pg_restore doesn't work for postgresql 7.2.3

[amievil]

Hello.

I'm developing an application and it has to backup and restore database using pg_dump & pg_restore.

the database is PostgreSql7.2.3 and pg_dump.exe & pg_restore.exe is from pg admin III.

The command I've used for backup and restore are:
pg_dump -i -h 172.17.5.82 -p 5432 -U livo -F t -c -C -D -v -f c:\backup.tar livo
pg_restore -i -c -F t -v -h 172.17.5.82 -p 5432 -U livo -d livo -v c:\backup.tar

it backs up fine, but errors occur while restoring.

the error says: could not set search_path to "public", parse error at or near "public", could not execute query. Command was:
DROP TABLE public.myTable;
could not execute query. relation "myTable" already exists:

It fails to drop an table because it doesnt understand "public" and copies the rows in table myTable. Therefore, it's end up with doubled duplicated rows.


I guessed it should be fine if I add "public" to an environment variable 'search_path', but I just found that 'search_path' was first introduced at PostgreSQL 7.3. ( mine is 7.2.3 ).


It's just simple backup & restore. just backup several tables and restore.
no functions and no complicated work.

I suggested that let's just manually(?) drop the tables in the application before restoring but my boss said it's not an option.

Is there any smart solution on this?

Thank you.

[michaelb]

Looks like you have an array of problems.
You're right to say that search_path would be useless for v.7.2, and this is because the concept of schema wasn't introduced until v.7.3.
So why do you have this public qualifier in your dump?
First, you are using the wrong versions of pg_dump and pg_restore - you should be using those that came with the database.
Second, even if pg_dump and pg_restore that came with pgAdmin III are capable of working with the old databases, you should never use the -i flag. It means "Ignore version mismatch" and it is harmful in your case.

Next, lets look at some other flags that you pass to pg_dump and pg restore.

pg_dump -i -h 172.17.5.82 -p 5432 -U livo -F t -c -C -D -v -f c:\backup.tar livo
The -C and -c flags are mutually exclusive, the only reason that you are not getting an error right there, because neither of them makes sense unless you're using the plain format; and you are using tar (-F t)
The -D flag should not cause a problem, but do you really need it?
It can make the restore process quite slow.


pg_restore -i -c -F t -v -h 172.17.5.82 -p 5432 -U livo -d livo -v c:\backup.tar
The -i flag should not be here. It means "Restore definition for named index only", and you are not passing any index-name to it.
The -c flag is just ignored because of the format of your data-dump.

One approach you may consider is to use pg_dump to create a plain-text datafile. In this case you can use the -C option.

Then use a small wrapper script which you submit via psql.
The script may look like this:

-- connect to the template
\connect template1
-- drop database which needs to be restored
drop database livo;
 
-- create database (encoding may vary)
-- this is not needed if pg_dump was created with the -C option
CREATE DATABASE livo WITH TEMPLATE = template0 ENCODING = 'UNICODE';"
\connect livo
 
-- finally import the dump-file
\i  <path_name_of_backup>
 
--  optionally update statistics for faster queries
vacuum analize;
 

Look at these links for more information:

http://www.postgresql.org/docs/7.2/static/backup.html
http://www.postgresql.org/docs/7.2/s...pp-pgdump.html
http://www.postgresql.org/docs/7.2/s...pgrestore.html

[amievil]

Thank you very much.

It really helped.