469,626 Members | 1,812 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

pg_restore doesn't work for postgresql 7.2.3


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 -p 5432 -U livo -F t -c -C -D -v -f c:\backup.tar livo
pg_restore -i -c -F t -v -h -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.
Apr 17 '07 #1
2 3512
534 Expert 512MB
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 -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 -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:

Expand|Select|Wrap|Line Numbers
  1. -- connect to the template
  2. \connect template1
  3. -- drop database which needs to be restored
  4. drop database livo;
  6. -- create database (encoding may vary)
  7. -- this is not needed if pg_dump was created with the -C option
  9. \connect livo
  11. -- finally import the dump-file
  12. \i  <path_name_of_backup>
  14. --  optionally update statistics for faster queries
  15. vacuum analize;
Look at these links for more information:

Apr 18 '07 #2
Thank you very much.

It really helped.
Jun 11 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Sam | last post: by
reply views Thread by andy morrow | last post: by
reply views Thread by Joshua D. Drake | last post: by
7 posts views Thread by Howard Lowndes | last post: by
6 posts views Thread by Mike Charnoky | last post: by
1 post views Thread by Plant Thomas | last post: by
7 posts views Thread by Tim Penhey | last post: by
1 post views Thread by Phil Endecott | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.