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

Abelard Hoffman <abelardhoffman@gmail.com> wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2014-09-16 17:39 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:

Abelard Hoffman <abelardhoffman@gmail.com> wrote:

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

On Tue, Sep 16, 2014 at 8:43 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2014-09-16 17:39 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:

Abelard Hoffman <abelardhoffman@gmail.com> wrote:

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 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,
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 09/16/2014 10:33 AM, Michael Paquier wrote:

On Tue, Sep 16, 2014 at 8:43 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2014-09-16 17:39 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:

Abelard Hoffman <abelardhoffman@gmail.com> wrote:

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.

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
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Sep 16, 2014 at 12:39 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 09/16/2014 10:33 AM, Michael Paquier wrote:

On Tue, Sep 16, 2014 at 8:43 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2014-09-16 17:39 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:

Abelard Hoffman <abelardhoffman@gmail.com> wrote:

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