Обсуждение: pg_dump vs pg_dumpall - small database cluster, complete recovery method needed

Hey folks,

  I'm a systems/network guy and not a developer/DBA, FYI.  I'm in the
process of redoing a personal, low-traffic website to use PHP and
PostgreSQL as a learning exercise.  I've got all the code done and the
database is populated, so now I'm tying up the loose ends, namely backups.
I am using PostgreSQL 7.3 on OpenBSD 3.4-STABLE.

  I've been going over the docs for pg_dump and pg_dumpall, and I just
want to make sure I'm understanding things correctly.  If I'm reading
things right, pg_dump is probably better suited to dumping individual
databases, and pg_dumpall is suited to dumping a database _system_ (ie,
multiple databases on one host).

  What I need:  In case of complete system failure, I need to be able to
recreate a database cluster from step 0 through accepting connections.
I can easily reproduce the database machine with PostgreSQL in a matter
of an hour.  From that specific point, having a completely clean DB, I
need to restore all users, passwords, databases, sequences, etc.

  pg_dumpall seems to do this for me, without any command-line args
needed.  Am I correct in saying that 'pg_dumpall > filename' will produce
a PostgreSQL dump that includes _everything_ I need to go from a clean
PG install to accepting connections again with data intact?  Users,
passwords, etc?

  I just want to be _very_ sure of this, before I automate the entire
process.  :)  I did attempt to search the archives for pgsql-admin, but
the archives seem to not be happy at the moment.  Hopefully, this isn't
a FAQ, I did read through the FAQs on postgresql.org (it's always
possible I missed something).

Thanks folks!  PostgreSQL is a very impressive project.

Benny


--
"Hey, get in here, Boozy!  You're late for your drunkening!"
                                                    -- Moe, "The Simpsons"

> databases, and pg_dumpall is suited to dumping a database _system_ (ie,
> multiple databases on one host).
>

Right.

>   pg_dumpall seems to do this for me, without any command-line args
> needed.  Am I correct in saying that 'pg_dumpall > filename' will produce
> a PostgreSQL dump that includes _everything_ I need to go from a clean
> PG install to accepting connections again with data intact?  Users,
> passwords, etc?
>

and the owner of pg_dumpall process must be a database superuser.

regards,
bhuvaneswaran

>>   pg_dumpall seems to do this for me, without any command-line args
>> needed.  Am I correct in saying that 'pg_dumpall > filename' will
>> produce
>> a PostgreSQL dump that includes _everything_ I need to go from a clean
>> PG install to accepting connections again with data intact?  Users,
>> passwords, etc?
>>
>
> and the owner of pg_dumpall process must be a database superuser.

Absolutely...  I'm running this directly on the database server, as the
PostgreSQL superuser, so permissions aren't an issue.  I just want to be
very sure that pg_dumpall is getting absolutely everything I need,
because I don't have the resources at this site to do full filesystem
backups of this host.

Thanks much!

Benny


--
"Hey, get in here, Boozy!  You're late for your drunkening!"
                                                    -- Moe, "The Simpsons"

On Sat, 20 Dec 2003, C. Bensend wrote:

>
> >>   pg_dumpall seems to do this for me, without any command-line args
> >> needed.  Am I correct in saying that 'pg_dumpall > filename' will
> >> produce
> >> a PostgreSQL dump that includes _everything_ I need to go from a clean
> >> PG install to accepting connections again with data intact?  Users,
> >> passwords, etc?
> >>
> >
> > and the owner of pg_dumpall process must be a database superuser.
>
> Absolutely...  I'm running this directly on the database server, as the
> PostgreSQL superuser, so permissions aren't an issue.  I just want to be
> very sure that pg_dumpall is getting absolutely everything I need,
> because I don't have the resources at this site to do full filesystem
> backups of this host.

Note that in the past, occasional dependency issues have cause pg_dumpall
to fail for certain users.  I myself had it happen with a customer
sequence a little while ago on 7.2.4.  Unfortunately, I'm unable to
reproduce the sequence of events that caused it, so I can't really report
it as a bug.