Thread: Problem with Online-Backup

roopa perumalraja wrote:
> Hi all,
>
> While there are inserts & updates happening into the database, is it
> possible to make the base backup without losing any of the updates in
> the database?

Yes, that's the whole point of PITR. The filesystem backup + WAL files
gives you a working database when restored.

> What does select pg_start_backup('label'); & pg_stop_backup(); do
> actually?

Tell the server that you are taking a backup, start a new WAL segment
for you too.

http://www.postgresql.org/docs/8.2/static/continuous-archiving.html

> I am worried if the file system backup tool will not store the data
> files in the /pgsql/data/base which is still getting populated. Or
> does the segment file store that?

Not sure I understand you here.

--
   Richard Huxton
   Archonet Ltd

roopa perumalraja wrote:
> Hi all,
>
> While there are inserts & updates happening into the database, is it
> possible to make the base backup without losing any of the updates in
> the database?

Yes, that's the whole point of PITR. The filesystem backup + WAL files
gives you a working database when restored.

> What does select pg_start_backup('label'); & pg_stop_backup(); do
> actually?

Tell the server that you are taking a backup, start a new WAL segment
for you too.

http://www.postgresql.org/docs/8.2/static/continuous-archiving.html

> I am worried if the file system backup tool will not store the data
> files in the /pgsql/data/base which is still getting populated. Or
> does the segment file store that?

Not sure I understand you here.

--
Richard Huxton
Archonet Ltd

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

On 02/01/07 03:02, roopa perumalraja wrote:
> Hi all,
>
> While there are inserts & updates happening into the database, is
> it possible to make the base backup without losing any of the
> updates in the database?

pg_dump does transactionaly-consistent hot backups.

PITR allows you to roll forward to the point-in-time at which the
system crashed.

> What does select pg_start_backup('label'); & pg_stop_backup(); do
> actually?
>
> I am worried if the file system backup tool will not store the
> data files in the /pgsql/data/base which is still getting
> populated. Or does the segment file store that?

Open-database file-level backups might work with PITR, but I
wouldn't trust it.

Closed-database file-level backups definitely work.

> Will the Continuous backup or Hot backup (if any) solve the
> problem?

See above.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwmfgS9HxQb37XmcRAjrcAKDB751ZWH/aKV17URY4OKwVN4dosgCfasCD
dF21fHEpPpFQ/TII0OijV6Q=
=EbJe
-----END PGP SIGNATURE-----

> Open-database file-level backups might work with PITR, but I
> wouldn't trust it.

IME, it does work, and very well.  Inconsistencies in the heap files are
trumped by the WAL archive during recovery.

-Glen

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

On 02/01/07 16:27, Glen Parker wrote:
>> Open-database file-level backups might work with PITR, but I
>> wouldn't trust it.
>
> IME, it does work, and very well.  Inconsistencies in the heap files are
> trumped by the WAL archive during recovery.

Tarring hot database files still gives me the willies.  But then, I
wear belt and suspenders.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwnVGS9HxQb37XmcRAqZCAKDNCuAD1ZKnXNCFDrdC+86rxkKINgCg7syt
QpSZmzLAKlJFdoWThULV/1o=
=Ociw
-----END PGP SIGNATURE-----

> Tarring hot database files still gives me the willies.  But then, I
> wear belt and suspenders.

I understand.  A list of "file changed while we read it" errors is just
a little unnerving at first!

I did quite a few end to end backup/PITR tests, and no matter what I did
to the DB during backup, no matter how many errors tar or cpio produced,
I have been unable to find any problems with the end result.

OTOH, I still take a full base backup every night and keep ten days
worth of WAL files on our backup server, so I guess maybe I don't
*completely* trust it :-)

-Glen

Glen Parker <glenebob@nwlink.com> writes:
>> Tarring hot database files still gives me the willies.  But then, I
>> wear belt and suspenders.

> I understand.  A list of "file changed while we read it" errors is just
> a little unnerving at first!

> I did quite a few end to end backup/PITR tests, and no matter what I did
> to the DB during backup, no matter how many errors tar or cpio produced,
> I have been unable to find any problems with the end result.

In theory, at least, this is exactly as reliable as Postgres' crash
recovery: it's essentially the same mechanism that gets us back to a
consistent state on-disk after a crash.  So don't worry too much about
all those gripes from tar.  (The only real problem with 'em is they
might distract you from a more serious problem, like out-of-space.)

            regards, tom lane

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

On 02/01/07 18:04, Glen Parker wrote:
>> Tarring hot database files still gives me the willies.  But then, I
>> wear belt and suspenders.
>
> I understand.  A list of "file changed while we read it" errors is just
> a little unnerving at first!
>
> I did quite a few end to end backup/PITR tests, and no matter what I did
> to the DB during backup, no matter how many errors tar or cpio produced,
> I have been unable to find any problems with the end result.
>
> OTOH, I still take a full base backup every night and keep ten days
> worth of WAL files on our backup server, so I guess maybe I don't
> *completely* trust it :-)

Or you don't trust tape to be 100% reliable.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFwpWNS9HxQb37XmcRAtUhAKDavPVEGP4UiqNh8Wn4yM87+8YwGgCeJ0XM
QMLq3wgjA1LiVTi+a8xwkI0=
=xVi1
-----END PGP SIGNATURE-----

roopa perumalraja wrote:
> Thanks a lot for your reply. To make it more clear will the be no
> loss of data or data corruption when taking a base backup while there
> is inserts & updates happening in the database?

Updates to the database continue uninterrupted.

The base backup alone is *not* enough to be safe.

The base backup  + WAL segments *are* enough to be safe.

--
   Richard Huxton
   Archonet Ltd

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

On 02/02/07 12:07, Lincoln Yeoh wrote:
> At 09:36 AM 2/2/2007, Ron Johnson wrote:
>> >
>> > OTOH, I still take a full base backup every night and keep ten days
>> > worth of WAL files on our backup server, so I guess maybe I don't
>> > *completely* trust it :-)
>>
>> Or you don't trust tape to be 100% reliable.
>
> Well so far tapes get chewed up by drives at intervals that are not far
> apart enough for me. And I've heard horror stories of tapes not being
> restorable using a different drive but same model etc (just not the same
> physical drive used for the backup).
>
> I suppose these problems are fixed by now in the latest tape drives, or
> were just "urban legends"? Right? *looks about nervously*...

Depends on the tape system.  We've been using DLT (and SuperDLT) for
years and have never had any problems.

> Nowadays I also wonder about the restoration times of say 200GB or even
> TBs of data from backups. More fun if there are Very Important and
> Influential People popping in every 15 minutes to ask whether it's done
> yet.

That's a problem with pg.  pg_dump is single-threaded and can only
write out to one file/device.

Now that PITR-from-WAL is in place, there are people who swear that
tarring up data directories, and then WAL-log rolling them forward
works perfectly.  If your database uses tablespaces and is spread
across multiple disk devices, then you could probably speed the
backup/restore by parallel tarring each device data tree to it's own
 tape drive.  6 LTO tape drives and your TB database gets backed up
up right quickly.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFw4J2S9HxQb37XmcRAkcvAKDCyMOkc2iRd8S6tW66su3pcRIAhQCgyc/0
CSrDgO5lnW+2KZpduyVgFJM=
=c4Lx
-----END PGP SIGNATURE-----

>>> Nowadays I also wonder about the restoration times of say 200GB or even
>>> TBs of data from backups. More fun if there are Very Important and
>>> Influential People popping in every 15 minutes to ask whether it's done
>>> yet.
>
> That's a problem with pg.  pg_dump is single-threaded and can only
> write out to one file/device.
>
> Now that PITR-from-WAL is in place, there are people who swear that
> tarring up data directories, and then WAL-log rolling them forward
> works perfectly.  If your database uses tablespaces and is spread

Perfectly? Hardly ;) but it does indeed work.

Joshua D. Drake

> across multiple disk devices, then you could probably speed the
> backup/restore by parallel tarring each device data tree to it's own
>  tape drive.  6 LTO tape drives and your TB database gets backed up
> up right quickly.
>
>

---------------------------(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



--

      === 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/

At 09:36 AM 2/2/2007, Ron Johnson wrote:
> >
> > OTOH, I still take a full base backup every night and keep ten days
> > worth of WAL files on our backup server, so I guess maybe I don't
> > *completely* trust it :-)
>
>Or you don't trust tape to be 100% reliable.

Well so far tapes get chewed up by drives at intervals that are not
far apart enough for me. And I've heard horror stories of tapes not
being restorable using a different drive but same model etc (just not
the same physical drive used for the backup).

I suppose these problems are fixed by now in the latest tape drives,
or were just "urban legends"? Right? *looks about nervously*...

Nowadays I also wonder about the restoration times of say 200GB or
even TBs of data from backups. More fun if there are Very Important
and Influential People popping in every 15 minutes to ask whether
it's done yet.

:p

Link.