Обсуждение: pg_dump vs pg_dumpall - small database cluster, complete recovery method needed
pg_dump vs pg_dumpall - small database cluster, complete recovery method needed
От
"C. Bensend"
Дата:
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.