I think there should be an easier way to backup a single database and
restore it on another server.
In my case we are developing a db so there are many schema changes to
that. When there is a significant change we find it easier to drop and
recreate the db from the backup - withouth affecting the other
databases that are running on the server. For that now I am using a
batch file that
creates the db from template0,
creates language pgsql,
runs pg_restore,
alters the db to set searchpath
The way I found out that I have to create language before and set the
searchpath after running restore is that I found that the restored db
doesn't work - which was quite scary at first. The fact that you have
to create roles before restore is well documented, but I didn't find
the others, so maybe there are still steps missing I just didn't find
the error in the restored db yet - which is still scary. If you are
saying that I should read the backup-restore in the manual more
carefully I can't argue with that, but I have to say for myselft that I
have read it several times by now.
On one hand it seems that 'PostgreSQL is simply very granular about
what it lets you dump' on the other hand it seems that the only unit
that you can restore on a clean server without scripting is the whole
cluster - which if I understand it correctly is not something you can
define, but is simply given as all the databases on the server.
I apreciate that my case is special and once things settled people
aren't messing so much with their schemas, but how do providers do
this?
Users of a provider surely can only create the backup of their own db?
How would the user restore that backup on his own server?
Thanks for the help.
Bal�zs