Thread: pg_restore should restore the schema comments and the database properties

On Tuesday, December 20, 2011 2:48:31 am Daniel Migowski wrote:
> Hi,
>
> I know this discussion has already been made, but for us it is a real
> problem that database properties are not restored with pg_restore. For me,
> the comment on a database, as well as the properties like pg_search_path
> are part of the data of the database. They are even contained in the
> dumps, so please add a function to pg_restore to let me restore this data
> also. It is really annoying not to be able to just use DROP DATABASE,
> CREATE DATABASE and pg_restore to get exactly the same thing  you had
> before.

search_path is in postgressql.conf, so you can get back to your state by copying
it over.

>
> I would also like a function that does a CREATE DATABASE on restore
> automatically, but I don't want to wish for too much here.

http://www.postgresql.org/docs/9.0/interactive/app-pgrestore.html

-C
--create

    Create the database before restoring into it. (When this option is used, the
database named with -d is used only to issue the initial CREATE DATABASE
command. All data is restored into the database name that appears in the
archive.)


If you are using plain text then you will need to supply the above to the
pg_dump command.

>
> Thank you very much in advance,
> Daniel Migowski
>
d
--
Adrian Klaver
adrian.klaver@gmail.com

On Tuesday, December 20, 2011 2:48:31 am Daniel Migowski wrote:
> Hi,
>
> I know this discussion has already been made, but for us it is a real
> problem that database properties are not restored with pg_restore. For me,
> the comment on a database, as well as the properties like pg_search_path
> are part of the data of the database. They are even contained in the
> dumps, so please add a function to pg_restore to let me restore this data
> also. It is really annoying not to be able to just use DROP DATABASE,
> CREATE DATABASE and pg_restore to get exactly the same thing  you had
> before.

Just confirmed that the database comment was dumped and restored. This is on
Postgres 9.0.5. How are you doing the dump/restore procedure?.
To elaborate on my previous answer, search_path is in postgresql.conf because it
is tied to the database cluster not a particular database.


>
> I would also like a function that does a CREATE DATABASE on restore
> automatically, but I don't want to wish for too much here.
>
> Thank you very much in advance,
> Daniel Migowski
>
> IKOffice
> UNTERNEHMENSSOFTWARE
>
> IKOffice GmbH
>
> Daniel Migowski
>

--
Adrian Klaver
adrian.klaver@gmail.com

On 20 December 2011 15:35, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> To elaborate on my previous answer, search_path is in postgresql.conf because it
> is tied to the database cluster not a particular database.

Not necessarily, it can also be tied to a schema or a role or (I
assume) a specific database in the cluster.

ALTER ROLE x SET search_path TO ...
ALTER SCHEMA x SET search_path TO ...

I don't have a PG instance available here, so I can't verify whether
those get dumped/restored. I assume they do though.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Tuesday, December 20, 2011 7:02:13 am Alban Hertroys wrote:
> On 20 December 2011 15:35, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> > To elaborate on my previous answer, search_path is in postgresql.conf
> > because it is tied to the database cluster not a particular database.
>
> Not necessarily, it can also be tied to a schema or a role or (I
> assume) a specific database in the cluster.
>
> ALTER ROLE x SET search_path TO ...
> ALTER SCHEMA x SET search_path TO ...

My mistake, I forgot about that.
For a database:
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }

>
> I don't have a PG instance available here, so I can't verify whether
> those get dumped/restored. I assume they do though.

--
Adrian Klaver
adrian.klaver@gmail.com