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

On 09/16/2014 10:33 AM, Michael Paquier wrote:
> On Tue, Sep 16, 2014 at 8:43 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2014-09-16 17:39 GMT+02:00 Kevin Grittner <kgrittn(at)ymail(dot)com>:
>>> Abelard Hoffman <abelardhoffman(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com