On Wed, May 20, 2015 at 12:40 PM, David G. Johnston < david.g.johns...@gmail.com> wrote:
> Yes. The entire dump is performed within a single transaction. > > On Wed, May 20, 2015 at 9:24 AM, Michael Nolan <htf...@gmail.com> wrote: > >> The documentation for pg_dump says that dump files are created in a >> consistent state. >> >> Is that true across multiple tables in the same pg_dump command? >> (Obviously it would not be true if I dumped tables using separate pg_dump >> commands. >> > > > > > Yes. The entire dump is performed within a single transaction. > > But if I put the database into a backup state using 'pg_start_backup', >> would separately executed pg_dump commands be in a consistent state across >> the set of dump files?) >> >> > pg_start_backup and pg_dump are not designed to work together. Namely, > pg_start_backup is mostly concerned with making sure future writes are > accounted for in the final backup while pg_dump says to ignore everything > that happens after the command begins. > > The documentation for pg_dumpall does not say that its dump file is in a >> consistent state (eg, across all tables), but it does say that it uses >> pg_dump to dump clusters. So, how consistent are the tables in pg_dumpall >> files? >> > > Each database is internally consistent. There is no guarantee that > databases and globals are consistent with each other (though those are > typically seldom changed) but different databases will to represent the > same point in time vis-a-vis each other. > > > > You might want to describe what you are trying to do here. > > David J. > I'm getting ready for a security audit and I want to make sure I have the database backup procedures properly documented, including what the limitations are on each type of backup . We us a combination of low level backups with log shipping, dumping of key individual tables, dumping of entire databases and dumping the entire system (pg_dumpall.) Hardware for setting up a slave server may be in a future budget, though I hope to be able to test having a slave server in the cloud later this year. (I'm not sure we have enough network bandwidth for that, hence the test.) When I moved to a new release of pg (9.3) last December, I stopped all transaction processing first so that pg_dumpall had no consistency issues. -- Mike Nolan
