Thread: pg_dump does not include database-level user-defined GUC variables?
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.
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
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
Pavel
--
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
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
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