Re: pg_restore exclude schema from being droped option

Hi list,

I am using pg_dump and pg_restore to backup and restore a database but
there is something
that I believe is missing from the restore process:
an option in pg_restore to exclude a schema from being dropped when -c
option is defined.

And here is why:

Suppose that I have a database with about 12 tables of customer data
(address, notes, configuration ... no more than 10.000 rows each) and 50
tables of history data (history files with about 1.000.000 rows each).
Now, to backup the database I choose to create 2 scripts, BackupData.sh
to backup all small tables and BackupHist.sh to backup history tables.
When I call pg_restore -c to restore data tables, pg_restore report a
failure because it is trying to drop a schema that it is not empty. So
it would be very helpful to have an option to exclude the schema (for
example: public) from being dropped.

I now that I there are some alternatives for example: pg_restore -l,
comment out the drop schema line and pg_restore -L or put data tables
and hist tables into separate schemas but an option to pg_restore would
be a lot easier, I guess.

What do you thing?

regards,
Kostis Mentzelos

Kostis Mentzelos <mentzelos(at)gmx(dot)net> writes:
> Now, to backup the database I choose to create 2 scripts, BackupData.sh
> to backup all small tables and BackupHist.sh to backup history tables.
> When I call pg_restore -c to restore data tables, pg_restore report a
> failure because it is trying to drop a schema that it is not empty.

Why (or how) is the schema part of the backup at all? If you used
pg_dump's -t switch to select the tables to back up, there should not be
a schema entry in the dump. So there's something you're not telling us
about how you are using the tools.

regards, tom lane