Mladen Gogala, 25.04.2010 18:42:
pg_dump *is* Postgres' backup tool (apart from a PITR backup).

What do you mean with "doesn't allow you to do recovery"?

The file that pg_dump creates (either the SQL script or the custom format) will allow me to restore the database to the state it was in when I took the backup.


Can you give some hard facts why you consider it having a "strong possibility of data loss"?
It is the equivalent to Oracle's (obsolete) "exp" tool

Thomas

On Mon, 26 Apr 2010 11:43:50 +0200, Thomas Kellerer wrote:

Apologies for the lengthy post, but this is an important topic.

pg_dump is a tool to take snapshots, not backups. Backup is done using OS
tools like tar, cpio or Bacula. What you call "PITR Backup" is actually a
db interface to OS backup which causes it to log the entire block into
the WAL file, not just the change record. You swich the database to
backup mode by issuing pg_start_backup('Label') and then do OS backup:


http://www.postgresql.org/docs/curre...archiving.html


The main difference between a backup and a snapshot is that backup allows
you to do point in time recovery. Snapshot does not.

There is no way to recover the data between the time the dump wa taken
and present.



And exp is not backup, either. BTW, I prefer pg_dump to exp because
pg_dump produces plain, readable SQL, unlike exp which produces a binary
file. To tell the truth, it pg_dump -F c also produces binary format,
suitable for parallel recovery, but that's another story.

Basically, it is now 07:45 AM on Monday, April 26th here in New York
City. If I take pg_dump now, I have no chance of recovering data entered
into my database at 08:00 AM. The "pg_dump" utility will issue a SQL
query, constrained by the ACID requirements and produce an output from
which I can recover my database to that state. That is called "snapshot",
it's like a photograph: still, frozen in time, you can't do much with it.

If you want to recover all of your data, you must do a proper online
backup, with the WAL archives. As for the export utility, here is what
Tom Kyte has to say about that: http://tinyurl.com/2domn66
Let me quote him:
"Export can make a logical copy of data, but that is all - never will it
create a backup"

The same, of course, holds true for the pg_dump utility. It's a logical
copy, a snapshot, a still photo, not a backup. Backup is taken by tar,
cpio or alike. Backup allows you to do a point in time recovery. Snapshot
does not.

Snapshots make sense when you want a *logical* state of your database at
some moment like end of a month, end of a quarter, project delivery or
some other significant event. Snapshots are usually taken so that you can
re-create the database in that state. That, for instance, may be reuired
by law. It's much cheaper to have a backup tape (or DVD) in a fire-proof
cabinet then to hold the entire database around for years. Backup is what
you take daily to be able to recover from a disasterous events like a
disk crash. The principal purpose of backup is to allow you to recover
all of your data, losing only the transactions that weren't committed at
the time of the failure.
If Postgres wants to find its way into a corporate server room, then this
is a distinction that has to be made. Corporate DR plan cannot rely on
pg_dump utility alone, it must include backup. Being a DBA, my primary
task is to make sure that the databases I am in charge of are available
to my users and that users do not lose data. I can't do that with pg_dump
alone.

--
http://mgogala.byethost5.com

On Mon, 26 Apr 2010 11:31:43 +0200, Erwin Moller wrote:


Yes. Please, read my reply to Thomas Kellerer. Once again, pg_dump is
*NOT* a backup tool.



--
http://mgogala.byethost5.com

Mladen Gogala, 26.04.2010 14:16:
It all depends on your requirements. If you can live with the fact that you might lose date between the last backup (or "snapshot" in your terms) and "now" then pg_dump *is* a valid "backup" tool.

If your requirement is, that you may not lose *any* committed transaction, then pg_dump is indeed the wrong tool.

Thomas

On Mon, 26 Apr 2010 14:27:33 +0200, Thomas Kellerer wrote:

This is a discussion of terminology. While the usual definition of
"backup" is just to store the data offline, I believe that the "real
backup" should allow me to restore the database to its most current
state. The distinction between a "backup" and a "snapshot" is indeed
something from the world of oracle. I do propose to adopt the similar
terminology in the world of Postgres, because the distinction is valid.
I may appear to be nitpicking, but I am a DBA, please forgive me.



--
http://mgogala.byethost5.com

Mladen Gogala schreef:

Hi again,

I read your response.
The difference between 'backup' and 'snapshot' you described is new to
me. (I use 'snapshot' and 'backup' interchangeably, but that is
apparently wrong.)

So, bottom line is (using your terminology in my wording):
-> As a snapshot tool pg_dump suffices.
Reason: It can take a certain point-in-time snapshot.

-> As a back-up tool is doesn't suffice.
Reason: A back-up tool also takes into account all committed
transactions performed after the back-up, allowing you to go back to
*any* point in time. Actually, I formulated that poorly too because
there is no 'after' the backup. There is no 'after the backup' because
the back upping is a continuous process.

Do I understand that right?

Anyway, for my client's purposes a snapshot is good enough, so pg_dump
is also good enough for me. Luckily me, otherwise I should get an extra
job to pay for Oracle's horrible licensing.

Thanks for the explanation. I wasn't aware of the distinction.

Regards,
Erwin Moller


--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare

On Mon, 26 Apr 2010 15:13:55 +0200, Erwin Moller wrote:

Yup.

Good, just don't do it over the web. Let your clients get the Postgres
client and backup their databases using their own client tool.

Why would you need Oracle? Postgres can do a perfectly good DB backup. I
tested it on a NetApp, using snapshot, and it worked like a charm. There
is a function pg_start_backup('label') which tells Postgres when should
it start writing the entire block into the WAL archive, not just the
change record, and you're good to go.




--
http://mgogala.freehostia.com

Mladen Gogala schreef:
Hi,

Yes, I found the WAL archive functionality shorty after reading your
messages.
I have never worked with it before, so I'll have to study it more before
using it.

For those like me (who never heard of it before), this is a good
introduction:
http://www.postgresql.org/docs/8.3/s...archiving.html

Thanks Mladen, for explaining the difference between snapshot and
continuous archiving. I never gave it much thought before.

Regards,
Erwin Moller


--
"There are two ways of constructing a software design: One way is to
make it so simple that there are obviously no deficiencies, and the
other way is to make it so complicated that there are no obvious
deficiencies. The first method is far more difficult."
-- C.A.R. Hoare