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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Gerhard Wiesinger <lists(at)wiesinger(dot)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(at)iol(dot)ie
> Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
> ------------------------------------------------------------------
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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.

regards, tom lane

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

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

Jeff Frost <jeff(at)frostconsultingllc(dot)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