Dear list,
I've got an application using PostgreSQL which is due to be moved servers.
I've been able to backup on the old and restore on the new server but only
once and as the current application is regularly being updated I'd like to
be able to do this repeatedly using the "clean" option. I think this is
something to do with the rights I have on the new system and would like to
know what I need to ask for from my ISP in order to get things to work
properly. My ISP seems to have less PostgreSQL experience than I do.
I've created a backup like this
pg_backup -f charlie.dmp -F c charlie
I've been able to restore on my development machine without any problems
pg_restore -d test -c -O charlie.dmp
(I have been given database and user names on the target machine than on
the current one).
but I get CONSTRAINT errors when I try this on the target machine unless I
manually DROP all TABLES and SEQUENCES in the database.
Having looked at the code generated it seems that I am missing the rights
to DROP my own SCHEMA (called public) which would be inline with the fact
that I am not able to delete my own database.
Is it possible that I'm over looking a simple command switch or are there
other ways of doing what I want: drop the existing schema, import the
backup, or do I need exta permissions for my user?
Thank you very much.
Charlie Clark