On 09/16/2014 10:33 AM, Michael Paquier wrote:
On Tue, Sep 16, 2014 at 8:43 AM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
2014-09-16 17:39 GMT+02:00 Kevin Grittner <kgrittn@xxxxxxxxx>:
Abelard Hoffman <abelardhoffman@xxxxxxxxx> 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
