Re: Full backup - pg_dumpall sufficient?

Tom Lane wrote:
> Gerhard Wiesinger <lists(at)wiesinger(dot)com> writes:
>
>> Hello Ray,
>> Yes, that's clear. But there was even some stuff which isn't dumped with
>> pg_dumpall (as far as I read).
>>
>
> Perhaps you were reading some extremely obsolete information?
> It used to be that pg_dumpall couldn't dump large objects,
> but that was a long time back.
>
>
Tom one thing I noticed recently is that pg_dumpall --globals doesn't
seem to pick up when you alter the GUCs at the database level and
neither does pg_dump. How should you dump to grab that per-database
stuff?

For example on 8.3.5:

discord:~ $ psql jefftest
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

jefftest=# show default_statistics_target ;
default_statistics_target
---------------------------
10
(1 row)

Time: 0.139 ms

jefftest=# ALTER DATABASE jefftest SET default_statistics_target = 100;
ALTER DATABASE
Time: 46.758 ms

jefftest=# \q
discord:~ $ psql jefftest
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

jefftest=# show default_statistics_target ;
default_statistics_target
---------------------------
100
(1 row)

Time: 0.318 ms
jefftest=# \q
discord:~ $ pg_dumpall --globals|grep default_statistics_target
discord:~ $ pg_dump jefftest | grep default_statistics_target
discord:~ $

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032