Обсуждение: 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

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

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