This snapshot is done at the LUN (filer) level, postgres is un-aware
we're creating a backup, so I'm not sure how pg_start_backup() plays
into this ...

Regards,
Dan Gorman

On Jun 22, 2007, at 3:55 AM, Simon Riggs wrote:

> On Fri, 2007-06-22 at 11:30 +0900, Toru SHIMOGAKI wrote:
>> Tom Lane wrote:
>>> Dan Gorman <dgorman@hi5.com> writes:
>>>> All of our databases are on NetApp storage and I have been
>>>> looking
>>>> at SnapMirror (PITR RO copy ) and FlexClone (near instant RW volume
>>>> replica) for backing up our databases. The problem is because there
>>>> is no write-suspend or even a 'hot backup mode' for postgres it's
>>>> very plausible that the database has data in RAM that hasn't been
>>>> written and will corrupt the data.
>>
>>> Alternatively, you can use a PITR base backup as suggested here:
>>> http://www.postgresql.org/docs/8.2/s...archiving.html
>>
>> I think Dan's problem is important if we use PostgreSQL to a large
>> size database:
>>
>> - When we take a PITR base backup with hardware level snapshot
>> operation
>> (not filesystem level) which a lot of storage vender provide,
>> the backup data
>> can be corrupted as Dan said. During recovery we can't even read
>> it,
>> especially if meta-data was corrupted.
>>
>> - If we don't use hardware level snapshot operation, it takes long
>> time to take
>> a large backup data, and a lot of full-page-written WAL files
>> are made.
>>
>> So, I think users need a new feature not to write out heap pages
>> during taking a
>> backup.
>
> Your worries are unwarranted, IMHO. It appears Dan was taking a
> snapshot
> without having read the procedure as clearly outlined in the manual.
>
> pg_start_backup() flushes all currently dirty blocks to disk as
> part of
> a checkpoint. If you snapshot after that point, then you will have all
> the data blocks required from which to correctly roll forward. On its
> own, the snapshot is an inconsistent backup and will give errors as
> Dan
> shows. It is only when the snapshot is used as the base backup in a
> full
> continuous recovery that the inconsistencies are removed and the
> database is fully and correctly restored.
>
> pg_start_backup() is the direct analogue of Oracle's ALTER DATABASE
> BEGIN BACKUP. Snapshots work with Oracle too, in much the same way.
>
> After reviewing the manual, if you honestly think there is a problem,
> please let me know and I'll work with you to investigate.
>
> --
> Simon Riggs
> EnterpriseDB http://www.enterprisedb.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match



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

On Fri, 2007-06-22 at 04:10 -0700, Dan Gorman wrote:
> This snapshot is done at the LUN (filer) level, postgres is un-aware
> we're creating a backup, so I'm not sure how pg_start_backup() plays
> into this ...

Postgres *is* completely unaware that you intend to take a backup, that
is *exactly* why you must tell the server you intend to make a backup,
using pg_start_backup() and pg_stop_backup(). That way Postgres will
flush its buffers, so that they are present on storage when you make the
backup.

Is the procedure for Oracle or any other transactional RDBMS any
different?

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Ah okay. I understand now. So how can I signal postgres I'm about to
take a backup ? (read doc from previous email ? )

Regards,
Dan Gorman

On Jun 22, 2007, at 4:38 AM, Simon Riggs wrote:

> On Fri, 2007-06-22 at 04:10 -0700, Dan Gorman wrote:
>> This snapshot is done at the LUN (filer) level, postgres is un-aware
>> we're creating a backup, so I'm not sure how pg_start_backup() plays
>> into this ...
>
> Postgres *is* completely unaware that you intend to take a backup,
> that
> is *exactly* why you must tell the server you intend to make a backup,
> using pg_start_backup() and pg_stop_backup(). That way Postgres will
> flush its buffers, so that they are present on storage when you
> make the
> backup.
>
> Is the procedure for Oracle or any other transactional RDBMS any
> different?
>
> --
> Simon Riggs
> EnterpriseDB http://www.enterprisedb.com
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

You can use the psql command line to run:

"select pg_start_backup();"

....then when you're done,

"select pg_stop_backup();"

if you want an example from the unix command line:

psql -c "select pg_start_backup();" database_name

then

psql -c "select pg_stop_backup();" database_name

/kurt


On Jun 22, 2007, at 7:51 AM, Dan Gorman wrote:

> Ah okay. I understand now. So how can I signal postgres I'm about
> to take a backup ? (read doc from previous email ? )
>
> Regards,
> Dan Gorman
>
> On Jun 22, 2007, at 4:38 AM, Simon Riggs wrote:
>
>> On Fri, 2007-06-22 at 04:10 -0700, Dan Gorman wrote:
>>> This snapshot is done at the LUN (filer) level, postgres is un-aware
>>> we're creating a backup, so I'm not sure how pg_start_backup() plays
>>> into this ...
>>
>> Postgres *is* completely unaware that you intend to take a backup,
>> that
>> is *exactly* why you must tell the server you intend to make a
>> backup,
>> using pg_start_backup() and pg_stop_backup(). That way Postgres will
>> flush its buffers, so that they are present on storage when you
>> make the
>> backup.
>>
>> Is the procedure for Oracle or any other transactional RDBMS any
>> different?
>>
>> --
>> Simon Riggs
>> EnterpriseDB http://www.enterprisedb.com
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Wasn't it select pg_start_backup('backuplabel');?

Andreas

Kurt Overberg wrote:
> You can use the psql command line to run:
>
> "select pg_start_backup();"
>
> ...then when you're done,
>
> "select pg_stop_backup();"
>
> if you want an example from the unix command line:
>
> psql -c "select pg_start_backup();" database_name
>
> then
>
> psql -c "select pg_stop_backup();" database_name
>
> /kurt
>
>
> On Jun 22, 2007, at 7:51 AM, Dan Gorman wrote:
>
>> Ah okay. I understand now. So how can I signal postgres I'm about to
>> take a backup ? (read doc from previous email ? )
>>
>> Regards,
>> Dan Gorman
>>
>> On Jun 22, 2007, at 4:38 AM, Simon Riggs wrote:
>>
>>> On Fri, 2007-06-22 at 04:10 -0700, Dan Gorman wrote:
>>>> This snapshot is done at the LUN (filer) level, postgres is un-aware
>>>> we're creating a backup, so I'm not sure how pg_start_backup() plays
>>>> into this ...
>>>
>>> Postgres *is* completely unaware that you intend to take a backup, that
>>> is *exactly* why you must tell the server you intend to make a backup,
>>> using pg_start_backup() and pg_stop_backup(). That way Postgres will
>>> flush its buffers, so that they are present on storage when you make the
>>> backup.
>>>
>>> Is the procedure for Oracle or any other transactional RDBMS any
>>> different?
>>>
>>> -- Simon Riggs
>>> EnterpriseDB http://www.enterprisedb.com
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>>> choose an index scan if your joining column's datatypes do not
>>> match
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGe7zyHJdudm4KnO0RAgyaAJ9Vz52izICKYkep/wZpJMFPkfAiuQCfZcjB
yUYM6rYu18HmTAs3F4VaGJo=
=n3vX
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 2007-06-22 at 17:23 +0900, Toru SHIMOGAKI wrote:
> Dan Gorman wrote:
> > Here is an example. Most of the snap shots worked fine, but I did get
> > this once:
>
> Thank you for your example. I'd appreciate it if I'd get any responses; whether
> we should tackle the problem for 8.4?

If you see a problem, please explain what it is, after careful review of
the manual.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Toru SHIMOGAKI wrote:
> Joshua D. Drake wrote:
>
>>> - If we don't use hardware level snapshot operation, it takes long time to take
>>> a large backup data, and a lot of full-page-written WAL files are made.
>> Does it? I have done it with fairly large databases without issue.
>
> You mean hardware snapshot?

Oh goodness no.

> I know taking a backup using rsync(or tar, cp?) as a
> n online backup method is not so a big problem as documented. But it just take a

I use rsync with pg_start/stop_backup and it works very well. Even on
databases that are TB in size.

> long time if we handle a terabyte database. We have to VACUUM and other batch
> processes to the large database as well, so we don't want to take a long time
> to take a backup...

Ahh o.k. that makes sense. The difference here is probably how often we
take the snapshot. We take them very often to insure we don't have a ton
of logs we have to pull over.

Joshua D. Drake

>
> Regards,
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Toru SHIMOGAKI wrote:
>
> Steve Atkins wrote:
>
>>> - When we take a PITR base backup with hardware level snapshot operation
>>> (not filesystem level) which a lot of storage vender provide, the
>>> backup data
>>> can be corrupted as Dan said. During recovery we can't even read it,
>>> especially if meta-data was corrupted.
>>
>> I can't see any explanation for how this could happen, other
>> than your hardware vendor is lying about snapshot ability.
>
> All of the hardware vendors I asked always said:
>
> "The hardware level snapshot has nothing to do with filesystem
> condition and of course with what data has been written from operating
> system chache to the hard disk platter. It just copies byte data on
> storage to the other volume.

Right that has been my understanding as well.

Joshua D. Drake

>
> So, if any data is written during taking snapshot, we can't assurance
> data correctness *strictly* .
>
> In Oracle, no table data is written between BEGIN BACKUP and END BACKUP,
> and it is not a problem REDO is written..."
>
> I'd like to know the correct information if the explanation has any
> mistakes, or a good way to avoid the probrem.
>
> I think there are users who want to migrate Oracle to PostgreSQL but
> can't because of the problem as above.
>
>
> Best regards,
>


--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

>> So, if any data is written during taking snapshot, we can't assurance data
>> correctness *strictly* .

That sounds nothing like what I've heard called a "snapshot" before. Some
"filesystems" which aren't really filesystems but are also storage layer
drivers like Veritas (and ZFS?) allow you to take a snapshot which they
guarantee is atomic. You can do them while you have concurrent i/o and be sure
to get a single consistent view of the filesystem.

If you're just copying blocks from a device without any atomic snapshot
guarantee then you're going to get garbage. Even in Postgres wasn't writing
anything the OS might still choose to flush blocks during that time, possibly
not even Postgres data blocks but filesystem meta-information blocks.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Dan Gorman <dgorman@hi5.com> writes:
> This snapshot is done at the LUN (filer) level, postgres is un-aware
> we're creating a backup, so I'm not sure how pg_start_backup() plays
> into this ...

That method works too, as long as you snapshot both the data files and
WAL files --- when you start PG from the backup, it will think it
crashed and recover by replaying WAL. So, assuming that the snapshot
technology really works, it should be exactly as reliable as crash
recovery is. If you saw a problem I'd be inclined to question whether
there is some upstream component (OS or disk controller) that's
reordering writes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match