By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,463 Members | 774 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,463 IT Pros & Developers. It's quick & easy.

pg_restore doesn't work for postgresql 7.2.3

P: 8
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.
Apr 17 '07 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 534
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:

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;
  5.  
  6. -- create database (encoding may vary)
  7. -- this is not needed if pg_dump was created with the -C option
  8. CREATE DATABASE livo WITH TEMPLATE = template0 ENCODING = 'UNICODE';"
  9. \connect livo
  10.  
  11. -- finally import the dump-file
  12. \i  <path_name_of_backup>
  13.  
  14. --  optionally update statistics for faster queries
  15. vacuum analize;
  16.  
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
Apr 18 '07 #2

P: 8
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.