Re: Full backup - pg_dumpall sufficient?

[Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx>]

Tom Lane wrote:

Gerhard Wiesinger <lists@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC 	http://www.frostconsultingllc.com/
Phone: 916-647-6411	FAX: 916-405-4032