Обсуждение: Full backup - pg_dumpall sufficient?
Hello! I recently read some Mail on the mailinglist where some parts of PostgreSQL were not dumped with pg_dumpall and additionally some pg_dump was necessary (it was something like internals, catalog, etc.) Any ideas what additionally has to be dumped to pg_dumpall for a full backup? Thnx. Ciao, Gerhard -- http://www.wiesinger.com/
On 29/01/2009 16:31, Gerhard Wiesinger wrote: > I recently read some Mail on the mailinglist where some parts of > PostgreSQL were not dumped with pg_dumpall and additionally some pg_dump > was necessary (it was something like internals, catalog, etc.) It's the other way around - pg_dump dumps just the specified database, but not cluster-wide stuff like login roles; you need to do a pg_dumpall to get those as well. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Gerhard Wiesinger <lists@wiesinger.com> writes: > Any ideas what additionally has to be dumped to pg_dumpall for a full > backup? The configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf), plus any SSL server keys/certs you might be using --- basically, all the static text files in the toplevel $PGDATA directory. Those things are not accessible to a client so pg_dump can't dump them. Some people put these files in a different directory where they'll be caught by their regular filesystem backup procedures for the server. regards, tom lane
Hello Ray, Yes, that's clear. But there was even some stuff which isn't dumped with pg_dumpall (as far as I read). So it was like to run 2 statements like: 1.) Run pg_dumpall 2.) Run pg_dump additionally ... Ciao, Gerhard -- http://www.wiesinger.com/ On Thu, 29 Jan 2009, Raymond O'Donnell wrote: > On 29/01/2009 16:31, Gerhard Wiesinger wrote: > >> I recently read some Mail on the mailinglist where some parts of >> PostgreSQL were not dumped with pg_dumpall and additionally some pg_dump >> was necessary (it was something like internals, catalog, etc.) > > It's the other way around - pg_dump dumps just the specified database, > but not cluster-wide stuff like login roles; you need to do a pg_dumpall > to get those as well. > > Ray. > > ------------------------------------------------------------------ > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > rod@iol.ie > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > ------------------------------------------------------------------ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Gerhard Wiesinger <lists@wiesinger.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. regards, tom lane
Tom Lane wrote:
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:~ $
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?Gerhard Wiesinger <lists@wiesinger.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.
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@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost <jeff@frostconsultingllc.com> writes: > 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? Regular pg_dumpall will catch that. There's been some previous discussion about redrawing the dividing lines so that this doesn't fall between the cracks when you try to use --globals plus per-database pg_dump, but AFAIR nothing's been done about it yet. It's a bit tricky since it's not entirely clear who's responsible for creating the individual databases when you restore in that scenario. regards, tom lane
On Thu, 29 Jan 2009, Tom Lane wrote: > Jeff Frost <jeff@frostconsultingllc.com> writes: >> 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? > > Regular pg_dumpall will catch that. > > There's been some previous discussion about redrawing the dividing lines > so that this doesn't fall between the cracks when you try to use > --globals plus per-database pg_dump, but AFAIR nothing's been done about > it yet. It's a bit tricky since it's not entirely clear who's > responsible for creating the individual databases when you restore in > that scenario. I guess I could pg_dumpall -s | grep "ALTER DATABASE" to grab that stuff. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost wrote: > On Thu, 29 Jan 2009, Tom Lane wrote: > >> Jeff Frost <jeff@frostconsultingllc.com> writes: >>> 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? >> >> Regular pg_dumpall will catch that. >> >> There's been some previous discussion about redrawing the dividing lines >> so that this doesn't fall between the cracks when you try to use >> --globals plus per-database pg_dump, but AFAIR nothing's been done about >> it yet. It's a bit tricky since it's not entirely clear who's >> responsible for creating the individual databases when you restore in >> that scenario. > > I guess I could pg_dumpall -s | grep "ALTER DATABASE" to grab that stuff. > That seems silly. Is this the best way to find this data: SELECT name, setting FROM pg_settings where source = 'database' ORDER BY name; ? -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost <jeff@frostconsultingllc.com> writes: >> I guess I could pg_dumpall -s | grep "ALTER DATABASE" to grab that stuff. > That seems silly. Is this the best way to find this data: > SELECT name, setting FROM pg_settings where source = 'database' ORDER BY > name; No, you'd miss anything overridden locally in your session. I'd think about getting it out of pg_database.datconfig, instead. Or really the easiest way is to tweak the logic in pg_dumpall about what to dump when ... regards, tom lane