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?

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?

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

Thanks a lot in advance


---------------------------------
Everyone is raving about the all-new Yahoo! Mail beta.

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/s...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

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

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?

--
Roopa

Richard Huxton <dev@archonet.com> wrote:
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/s...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



---------------------------------
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.

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

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

> 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


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

-----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+8 6rxkKINgCg7syt
QpSZmzLAKlJFdoWThULV/1o=
=Ociw
-----END PGP SIGNATURE-----

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

http://archives.postgresql.org/

> 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


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

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

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

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

iD8DBQFFwpWNS9HxQb37XmcRAtUhAKDavPVEGP4UiqNh8Wn4yM 87+8YwGgCeJ0XM
QMLq3wgjA1LiVTi+a8xwkI0=
=xVi1
-----END PGP SIGNATURE-----

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

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

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

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