pg_dumpall

Steve Clark <sclark@netwolves.com> writes:

does pg_dumpall make consistent backups if the database is being used
concurrently?
Even though the man page doesn't say it does.

That's intentional, because it doesn't. What you get is a pg_dump
snapshot of each database in sequence; those snapshots don't all
correspond to the same time instant. There isn't any good way to
guarantee time coherence of dumps across two databases.

regards, tom lane

Tom Lane wrote:

Steve Clark <sclark@netwolves.com> writes:

does pg_dumpall make consistent backups if the database is being used
concurrently?
Even though the man page doesn't say it does.

That's intentional, because it doesn't. What you get is a pg_dump
snapshot of each database in sequence; those snapshots don't all
correspond to the same time instant. There isn't any good way to
guarantee time coherence of dumps across two databases.

The fine point possibly being missed is that each database's dump
produced by pg_dumpall is, of course, self-consistent.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Out of interest, how does pg_dump manage to do a snapshot of a
database at an instant in time?

My mental picture of pg_dump was just a series of queries dumping out
the tables...

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

Steve Clark <sclark@netwolves.com> writes:

does pg_dumpall make consistent backups if the database is being

used

concurrently?
Even though the man page doesn't say it does.

That's intentional, because it doesn't. What you get is a pg_dump
snapshot of each database in sequence; those snapshots don't all
correspond to the same time instant. There isn't any good way to
guarantee time coherence of dumps across two databases.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

___________________________________________________________
Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/

Glyn Astill wrote:

Out of interest, how does pg_dump manage to do a snapshot of a
database at an instant in time?

My mental picture of pg_dump was just a series of queries dumping out
the tables...

begin;
set transaction isolation level serializable;

--- begin dumping stuff;

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:

Tom Lane wrote:

That's intentional, because it doesn't. What you get is a pg_dump
snapshot of each database in sequence; those snapshots don't all
correspond to the same time instant. There isn't any good way to
guarantee time coherence of dumps across two databases.

The fine point possibly being missed is that each database's dump
produced by pg_dumpall is, of course, self-consistent.

Right, but Steve already knew that.

Hmm ... it suddenly strikes me that Simon's "transaction snapshot
cloning" idea could provide a way to get exactly coherent dumps from
multiple databases in the same cluster. Maybe he already realized that,
but I didn't.

regards, tom lane

On Thu, 17 Jan 2008, Tom Lane wrote:

There isn't any good way to guarantee time coherence of dumps across two
databases.

Whether there's a good way depends on what you're already doing. If
you're going to the trouble of making a backup using PITR anyway, it's not
hard to stop applying new logs to that replica and dump from it to get a
point in time backup across all the databases. That's kind of painful now
because you have to start the server to run pg_dumpall, so resuming
recovery is difficult, but you can play filesystem tricks to make that
easier.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Alvaro Herrera <alvherre@commandprompt.com> wrote:
Glyn Astill wrote:

Out of interest, how does pg_dump manage to do a snapshot of a
database at an instant in time?

My mental picture of pg_dump was just a series of queries dumping

out

the tables...

begin;
set transaction isolation level serializable;

--- begin dumping stuff;

Wouldn't that just lock everything so nothing could be updated? Or
just the table it is outputting?

I'm guessing I need to go off and school myself on different
isolation levels etc to understand, but say I have 2 tables "sales"
and "sold", and users are selling items with inserts into the sales
table and a count updating manually in sold. Wouldn't these end up
inconsistant in the dump?

___________________________________________________________
Support the World Aids Awareness campaign this month with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/

On Thu, Jan 17, 2008 at 11:14:22AM -0800, Glyn Astill wrote:

begin;
set transaction isolation level serializable;

--- begin dumping stuff;

Wouldn't that just lock everything so nothing could be updated? Or
just the table it is outputting?

PostgreSQL uses MVCC, which means the whole thing is lock free. It just
requires more diskspace. To keep the older versions around.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Jan 17, 2008, at 1:08 PM, Greg Smith wrote:

On Thu, 17 Jan 2008, Tom Lane wrote:

There isn't any good way to guarantee time coherence of dumps
across two databases.

Whether there's a good way depends on what you're already doing.
If you're going to the trouble of making a backup using PITR
anyway, it's not hard to stop applying new logs to that replica and
dump from it to get a point in time backup across all the
databases. That's kind of painful now because you have to start
the server to run pg_dumpall, so resuming recovery is difficult,
but you can play filesystem tricks to make that easier.

Actually, this exact scenario brings up a question I was thinking of
last night. If you stop a PITR standby server and bring it up to
dump from, will all of the database file have something written to
them at some point during the dump? Transactional information is
what I'd assume would be written, if so, but I'm not really sure of
the low level details there.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Erik Jones wrote:

On Jan 17, 2008, at 1:08 PM, Greg Smith wrote:

On Thu, 17 Jan 2008, Tom Lane wrote:

There isn't any good way to guarantee time coherence of dumps
across two databases.

Whether there's a good way depends on what you're already doing.
If you're going to the trouble of making a backup using PITR
anyway, it's not hard to stop applying new logs to that replica and
dump from it to get a point in time backup across all the
databases. That's kind of painful now because you have to start
the server to run pg_dumpall, so resuming recovery is difficult,
but you can play filesystem tricks to make that easier.

Actually, this exact scenario brings up a question I was thinking of
last night. If you stop a PITR standby server and bring it up to
dump from, will all of the database file have something written to
them at some point during the dump? Transactional information is
what I'd assume would be written, if so, but I'm not really sure of
the low level details there.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Thanks for everyone that replied to my query about pg_dumpall.

Now another question/issue - anytime I usr createdb the resulting db
ends up
with UTF-8 encoding unless I use the -E switch. Is there a way to make
the
default be sql_ascii? postgres version is 8.2.5

Thanks again
Steve

On Friday 18 January 2008 14:38, Steve Clark wrote:

Thanks for everyone that replied to my query about pg_dumpall.

Now another question/issue - anytime I usr createdb the resulting db
ends up
with UTF-8 encoding unless I use the -E switch. Is there a way to make
the
default be sql_ascii? postgres version is 8.2.5

Thanks again
Steve

Hi Steve,

http://www.postgresql.org/docs/8.2/static/app-initdb.html

Best regards,

--
Aarni