pg_dump does not include database-level user-defined GUC variables?

[Abelard Hoffman]

I have a user-defined GUC variable that was set at the db level. e.g.,

ALTER DATABASE mydb SET myapp.user_id TO '1'

Works fine. When I do a pg_dump, however, that variable isn't included.
Is that expected? It's not really an attribute of the database?

Thanks.

[Kevin Grittner]

Post by Abelard Hoffman
I have a user-defined GUC variable that was set at the db level. e.g.,
ALTER DATABASE mydb SET myapp.user_id TO '1'
Works fine. When I do a pg_dump, however, that variable isn't included.
Is that expected? It's not really an attribute of the database?

That sort of information *about the database* is stored at the
cluster level, not in the database itself. Take a look at
pg_dumpall.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Pavel Stehule]

Post by Kevin Grittner

Post by Abelard Hoffman
I have a user-defined GUC variable that was set at the db level. e.g.,
ALTER DATABASE mydb SET myapp.user_id TO '1'
Works fine. When I do a pg_dump, however, that variable isn't included.
Is that expected? It's not really an attribute of the database?

That sort of information *about the database* is stored at the
cluster level, not in the database itself. Take a look at
pg_dumpall.

aha, I though it is bug

I don't think so this design is well - this settings is strictly related to
database. So there should be some option for dumping these options too.

Regards

Pavel

Post by Kevin Grittner
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
http://www.postgresql.org/mailpref/pgsql-general

[Michael Paquier]

Post by Pavel Stehule

Post by Kevin Grittner

Post by Abelard Hoffman
I have a user-defined GUC variable that was set at the db level. e.g.,
ALTER DATABASE mydb SET myapp.user_id TO '1'
Works fine. When I do a pg_dump, however, that variable isn't included.
Is that expected? It's not really an attribute of the database?

That sort of information *about the database* is stored at the
cluster level, not in the database itself. Take a look at
pg_dumpall.

aha, I though it is bug

That's a feature :)

Post by Pavel Stehule
I don't think so this design is well - this settings is strictly related to
database. So there should be some option for dumping these options too.

It would be tempting to include parameters of pg_db_role_setting where
role setrole = 0 by default and I recall that there have been some
debate about that as well (this would roughly need to move
dumpDatabaseConfig out of pg_dumpall.c in a more generic place), but
nothing has actually been done. Note that this information is included
in pg_dumpall without -g either way.
Regards,

[Adrian Klaver]

Post by Michael Paquier

Post by Pavel Stehule

Post by Kevin Grittner

Post by Abelard Hoffman
I have a user-defined GUC variable that was set at the db level. e.g.,
ALTER DATABASE mydb SET myapp.user_id TO '1'
Works fine. When I do a pg_dump, however, that variable isn't included.
Is that expected? It's not really an attribute of the database?

That sort of information *about the database* is stored at the
cluster level, not in the database itself. Take a look at
pg_dumpall.

aha, I though it is bug

That's a feature :)

I would lean more to bug:(

If I do:

/usr/local/pgsql93/bin/pg_dumpall -U postgres -p 5452 -f dumpall.sql

I get:

CREATE DATABASE test WITH TEMPLATE = template0 OWNER = postgres;
ALTER DATABASE test SET "my.guc" TO 'on';

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

If I do:

/usr/local/pgsql93/bin/pg_dump -C -U postgres -Fp -p 5452 -f
test_only.sql test

I get:

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

If I am looking to recreate a database I am not getting the same one. At
the least it should be accessible via pg_dumpall -g so you could do
individual database dumps and get the database guc without having to
dump the entire cluster.

Post by Michael Paquier

Post by Pavel Stehule
I don't think so this design is well - this settings is strictly related to
database. So there should be some option for dumping these options too.

It would be tempting to include parameters of pg_db_role_setting where
role setrole = 0 by default and I recall that there have been some
debate about that as well (this would roughly need to move
dumpDatabaseConfig out of pg_dumpall.c in a more generic place), but
nothing has actually been done. Note that this information is included
in pg_dumpall without -g either way.
Regards,

[Abelard Hoffman]

Post by Adrian Klaver

Post by Kevin Grittner

Post by Abelard Hoffman
I have a user-defined GUC variable that was set at the db level. e.g.,
ALTER DATABASE mydb SET myapp.user_id TO '1'
Works fine. When I do a pg_dump, however, that variable isn't included.
Is that expected? It's not really an attribute of the database?

That sort of information *about the database* is stored at the
cluster level, not in the database itself. Take a look at
pg_dumpall.

[snip]

If I am looking to recreate a database I am not getting the same one. At
the least it should be accessible via pg_dumpall -g so you could do
individual database dumps and get the database guc without having to dump
the entire cluster.

Yes, that's exactly what bit me. I was trying to figure out why a restore
of a db was failing all tests, and discovered the missing GUCs in the dump.
There may be reasons for it living at the cluster level, but I suspect most
users will expect pg_dump to include them.

AH