[FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)

apoc9009 wrote:

Hi Hackers,

In my Project i have to handle a Database with 600 GByte Text only,
distributed on 4 Tablespaces
on multiple Harddisks and Remote SAN's connected via Gigaethernet to the
Remote SAN-Storage.

I need more flexibility by doing Backups of my big Database, but the
built in Online Backupsystem
dont work for my Setup good enought for me. I Can not accept 16 MByte
big WAL's Files
for securing it on Tape. 16 MByte Data loss on a Crash Situation is
Fatal and no helpfully
(1 MByte to). I wish to have a continoues Backup without any data losses.

http://www.postgresql.org/docs/8.2/static/warm-standby.html
Particularly section 23.4.4

That can get you to 1 second or less.

--
Richard Huxton
Archonet Ltd

http://www.postgresql.org/docs/8.2/static/warm-standby.html
Particularly section 23.4.4

Thadt is Replication NOT Backup

http://www.postgresql.org/docs/8.2/static/warm-standby.html
Particularly section 23.4.4

23.4.4 is thadt what iam using just im Time but this ist not eneought
for me!

No Versioning, no chances to prevent data losses

You have to wait until a WAL File ist written (Default Value for WAL
Files is 16 MBytes).

I need an EXCAT Copy from the Database and only the last changes in it
to for
updating my Backupresitory. If the System Crash, you have Dataloss of
over 16 MBytes
thadts Fatal and not acceptable! 1MByte Dataloss of ASCII Data is also
not acceptable!

apoc9009 wrote:

http://www.postgresql.org/docs/8.2/static/warm-standby.html
Particularly section 23.4.4

23.4.4 is thadt what iam using just im Time but this ist not eneought
for me!

No Versioning, no chances to prevent data losses

You have to wait until a WAL File ist written (Default Value for WAL
Files is 16 MBytes).

You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.

--
Richard Huxton
Archonet Ltd

You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.

I have read this:

PostgreSQL directly supports file-based log shipping as described above.
It is also possible to implement record-based log shipping, though this
requires custom development.

But thadt is not thadt what iam looking for!

Filebased Logship backups having a big Problem for doing continous
Backups. You have to wait until
the Postmaster has written the WAL File, after this you can save it to
the Backupserver. But 1 WAL
has a size of 16 MByte ny default! (thadt is a big Datahole in your
"Online Backup"!)

It makes no sense to reduce the Filesize. If the Filesize is smaller
then 16 MBytes for WAL Files
you have still the same Problem, there are Data losses and thadt the
Center of the Problem.

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses, without locking Tables, without Shutdown
and without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

apoc9009 wrote:

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses, without locking Tables, without Shutdown
and without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

"Without datalosses" is utopy. For that, you'd need something like
synchronous replication, otherwise you're always going to have a window
where you have something committed in the server, but not yet in the
backup. So it's just a question of how wide that window is, how much
data loss can you live with.

With file-based log shipping, you can get down to 1 second, by using the
archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

The record-based log shipping will give you a very narrow window, down
to < 1 second or even less if you're willing to poll the server that
often, but requires some custom development.

I wonder, do you really need such a super real time backup solution,
when you have the remote SAN? Don't you trust that the SAN hardware?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Hi,

On 9/6/07, apoc9009 <apoc9009@yahoo.de> wrote:

Filebased Logship backups having a big Problem for doing continous
Backups. You have to wait until
the Postmaster has written the WAL File, after this you can save it to
the Backupserver. But 1 WAL
has a size of 16 MByte ny default! (thadt is a big Datahole in your
"Online Backup"!)

You should read the documentation more carefully:
"archive_timeout (integer)

The archive_command is only invoked on completed WAL segments.
Hence, if your server generates little WAL traffic (or has slack
periods where it does so), there could be a long delay between the
completion of a transaction and its safe recording in archive storage.
To put a limit on how old unarchived data can be, you can set
archive_timeout to force the server to switch to a new WAL segment
file periodically. When this parameter is greater than zero, the
server will switch to a new segment file whenever this many seconds
have elapsed since the last segment file switch. Note that archived
files that are closed early due to a forced switch are still the same
length as completely full files. Therefore, it is unwise to use a very
short archive_timeout — it will bloat your archive storage.
archive_timeout settings of a minute or so are usually reasonable.
This parameter can only be set in the postgresql.conf file or on the
server command line. "

(Taken from http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html#GUC-ARCHIVE-TIMEOUT)

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses, without locking Tables, without Shutdown
and without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

Well, there's what you want and there's what is possible. Warm standby
is definitely the best solution for your problem, even if not perfect.

--
Guillaume

apoc9009 wrote:

You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.

I have read this:

PostgreSQL directly supports file-based log shipping as described above.
It is also possible to implement record-based log shipping, though this
requires custom development.

But thadt is not thadt what iam looking for!

Filebased Logship backups having a big Problem for doing continous
Backups. You have to wait until
the Postmaster has written the WAL File, after this you can save it to
the Backupserver. But 1 WAL
has a size of 16 MByte ny default! (thadt is a big Datahole in your
"Online Backup"!)

Which is why that entire section is about copying just the changed parts
of WAL files.

It makes no sense to reduce the Filesize. If the Filesize is smaller
then 16 MBytes for WAL Files
you have still the same Problem, there are Data losses and thadt the
Center of the Problem.

But in your original email you said:

All Users of Hugh Databases (Missioncritical and allways Online) can
bring up its
Databases with the same information with differences 1-5 Sec. before
the Crash occurs!

That suggested to me that you didn't want per-transaction backup, just
one backup every second. OK, what you actually want is a continuous
backup with one copy made per transaction.

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses, without locking Tables, without Shutdown
and without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

So, if I understand, you want on of:
1. External RAID array. If main machine dies, turn backup machine on.
Both share the same disks.

2. Something like DRBD to copy individual disk blocks between machines.
You could do this just for WAL.

3. Replication.

--
Richard Huxton
Archonet Ltd

Ühel kenal päeval, N, 2007-09-06 kell 12:53, kirjutas apoc9009:

You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.

I have read this:

PostgreSQL directly supports file-based log shipping as described above.
It is also possible to implement record-based log shipping, though this
requires custom development.

check out SkyTools, it seems to make use of 23.4.4 to have sub-second
failure window

-----
Hannu

On Thu, 2007-09-06 at 12:21 +0200, apoc9009 wrote:

If the System Crash, you have Dataloss of
over 16 MBytes thats Fatal and not acceptable! 1MByte Dataloss of ASCII Data is also
not acceptable!

Is any data loss acceptable in the case of a disaster? How much?

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Ühel kenal päeval, N, 2007-09-06 kell 12:53, kirjutas apoc9009:

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses,

Then you need the backup record to be completed on the backup machine
before the transaction commit returns on master.

This is quaranteed to be really slow for any significant transaction
rate but can be done using DRBD.

without locking Tables,

No backup I know of needs locking tables

without Shutdown

This one I just don't understand. Shtdown what ?

without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

So you want the user to still be connected to the failed machine, but at
the same time be connected to the new live failover machine ?

-----
Hannu

Heikki Linnakangas schrieb:

apoc9009 wrote:

"Without datalosses" is utopy. For that, you'd need something like
synchronous replication, otherwise you're always going to have a window
where you have something committed in the server, but not yet in the
backup. So it's just a question of how wide that window is, how much
data loss can you live with.

With file-based log shipping, you can get down to 1 second, by using the
archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

Yes, it is possible but not recommended . My Backup Servers Filesystem
will explode :D

The record-based log shipping will give you a very narrow window, down
to < 1 second or even less if you're willing to poll the server that
often, but requires some custom development.

Correct, but this is not good enought and i think there are a lot of
Peoples having the same Problem.
It was wishfull, having a Online Streaming Backupsubsystem, thadt can
produce portable Backupdatabases,
to prevent users for Millions of Archive Files on the Backup FTP-Server.

My Ideaa is the following Folder Structure:

/pg/backup/

Backup 12/24/2007 Version 1
/pg/backup/12_24_2007/base/rcvry.rcv <--- Basebackup
/pg/backup/12_24_2007/changes/0001.chg <--- Changed Data
/changes/0002.chg <--- Changed Data
/changes/0003.chg <--- Changed Data

Backup 12/24/2008 Version 2
/pg/backup/12_24_2008/base/rcvry.rcv <--- Basebackup
/pg/backup/12_24_2008/changes/0001.chg <--- Changed Data
/changes/0002.chg <--- Changed Data
/changes/0003.chg <--- Changed Data
....
/changes/0010.chg <--- Changed Data

/changes/0001.rsf <--- Recovery
Stripeset File (10 MByte) addon of Basebackup
delete *.chg

if a Stripeset of 10 *.chg Files exist, they should be converted or merged
to one greater Recovery Stripe File (*.RSF)

Things to do:

1.A Integradted FTP-Client to the Postmaster Process
2.Online Streamingbackup Logic inside the Postmaster

Apoc

So you want the user to still be connected to the failed machine, but at
the same time be connected to the new live failover machine ?

-----
Hannu

No.
The User should be connected to the running db without restrictions
while backup is in progress

Apoc

Ühel kenal päeval, N, 2007-09-06 kell 16:15, kirjutas apoc9009:

So you want the user to still be connected to the failed machine, but at
the same time be connected to the new live failover machine ?

-----
Hannu

No.
The User should be connected to the running db without restrictions
while backup is in progress

And what's wrong with WAL-based backup then ?

Especially the variant described in 23.4.4 wich keeps the WAL copied to
backup machine up to last second ?

---------------
Hannu

On Thu, 2007-09-06 at 12:53 +0200, apoc9009 wrote:

You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.

I have read this:

PostgreSQL directly supports file-based log shipping as described above.
It is also possible to implement record-based log shipping, though this
requires custom development.

We may expect that feature in later releases, but definitely not in 8.3

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses, without locking Tables, without Shutdown
and without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

I'm not clear whether you are looking for Backup, or High Availability
Replication.

There is no data loss with the online backup technique described in the
manual.

If you require HA replication with zero data-loss then you are currently
restricted to non-database techniques, which you already mentioned, so
you have your answer already.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Simon Riggs schrieb:

On Thu, 2007-09-06 at 12:53 +0200, apoc9009 wrote:

You've either not read 23.4.4 or haven't understood it. If the text is
unclear, documentation additions/changes are always welcome.

I have read this:

PostgreSQL directly supports file-based log shipping as described above.
It is also possible to implement record-based log shipping, though this
requires custom development.

We may expect that feature in later releases, but definitely not in 8.3

I wish to have an Solution, thadt backup my Database DB wihout
Datalosses, without locking Tables, without Shutdown
and without any User must be forced for logging out (Backup in
Production State Online without Datalosses).

I'm not clear whether you are looking for Backup, or High Availability
Replication.

There is no data loss with the online backup technique described in the
manual.

No, there is a lost of Data.

The WAL File musst bei generated by the Postmaster and this File must be
copied to the
safe Remote Backupserver. If the Machine, where the Database is running
crashed, then the Last
WAL is lost and the Backup isnt complet,e this is the Center of the Problem.

If you require HA replication with zero data-loss then you are currently
restricted to non-database techniques, which you already mentioned, so
you have your answer already.

we talking about Backup this isnt the same as Replication.

On 9/6/07, apoc9009 <apoc9009@yahoo.de> wrote:

Backup 12/24/2008 Version 2
/pg/backup/12_24_2008/base/rcvry.rcv <--- Basebackup
/pg/backup/12_24_2008/changes/0001.chg <--- Changed Data
/changes/0002.chg <--- Changed Data
/changes/0003.chg <--- Changed Data
....
/changes/0010.chg <--- Changed Data

/changes/0001.rsf <--- Recovery
Stripeset File (10 MByte) addon of Basebackup
delete *.chg

if a Stripeset of 10 *.chg Files exist, they should be converted or merged
to one greater Recovery Stripe File (*.RSF)

Why? What does this actually do?

On Thu, Sep 06, 2007 at 04:08:10PM +0200, apoc9009 wrote:

archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

Yes, it is possible but not recommended . My Backup Servers Filesystem
will explode :D

. . .

Correct, but this is not good enought and i think there are a lot of
Peoples having the same Problem.
It was wishfull, having a Online Streaming Backupsubsystem, thadt can
produce portable Backupdatabases,
to prevent users for Millions of Archive Files on the Backup FTP-Server.

It seems that what you want is near-real-time online backups with _no
cost_, which is not a feature that I think anyone will ever work on.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
--Damien Katz

On Thu, 2007-09-06 at 17:53 +0300, Hannu Krosing wrote:

Ühel kenal päeval, N, 2007-09-06 kell 16:15, kirjutas apoc9009:

So you want the user to still be connected to the failed machine, but at
the same time be connected to the new live failover machine ?

-----
Hannu

No.
The User should be connected to the running db without restrictions
while backup is in progress

And what's wrong with WAL-based backup then ?

Especially the variant described in 23.4.4 wich keeps the WAL copied to
backup machine up to last second ?

I think the OP means
- synchronous replication
- ability to access the standby node for queries

Yes, both requests are planned for later releases.

Bruce, can we edit the TODO to include a section called "High
Availability"?

We already have a few scattered references to such things, but in Admin
and WAL. We need a specific section.

We currently have these items, that can be moved to the new section:
- Allow a warm standby system to also allow read-only statements
- Allow WAL traffic to be streamed to another server for stand-by
replication

new section would look like this:

High Availability
-----------------
- Allow a warm standby system to also allow read-only statements
- Allow WAL traffic to be streamed to another server for stand-by
replication (synchronous/asynchronous options)
- Improve scalability of recovery for large I/O bound databases

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote:

With file-based log shipping, you can get down to 1 second, by using the
archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

I tried doing a couple pg_switch_xlog() in quick succession, and the WAL
files that represent less than a second of actual data don't seem much
more compressible than the rest.

I think WAL compression has been talked about before, with ideas such as
removing unneeded full page images. However, it seems like it would be
easy to get a lot of the benefit by just having a utility that could
discard useless contents that are left over from a previously-recycled
file, and then could just reconstruct it later with zeros.

Regards,
Jeff Davis

Ühel kenal päeval, N, 2007-09-06 kell 19:33, kirjutas apoc9009:

Simon Riggs schrieb:

I'm not clear whether you are looking for Backup, or High Availability
Replication.

There is no data loss with the online backup technique described in the
manual.

No, there is a lost of Data.

The WAL File musst bei generated by the Postmaster and this File must be
copied to the
safe Remote Backupserver. If the Machine, where the Database is running
crashed, then the Last
WAL is lost and the Backup isnt complet,e this is the Center of the Problem.

read and re-read 23.4.4

you DO NOT have to wait for the whole file to be complete to copy it
over

you can query the position where postgres has currently completed
writing and then copy over that part. doing so you can have wal-based
backup that is good up to last second (if you poll the function and copy
over the newly written part of the file every second)

Look at WalMgr.py in SkyTools package how this can be done in practice.

Skytools is available at http://pgfoundry.org/projects/skytools/

If used for setting up WAL-based backup on pg 8.2, it runs automatically
in "syncdaemon" mode, which means that parts of WAL file are copied over
as soon as they are written by postgres.

--------------
Hannu

"Simon Riggs" <simon@2ndquadrant.com> writes:

High Availability
-----------------
- Allow a warm standby system to also allow read-only statements
- Allow WAL traffic to be streamed to another server for stand-by
replication (synchronous/asynchronous options)

Asynchronous streaming of WAL would be a very good feature.

Synchronous streaming where a commit doesn't return until the backup server
(or a quorum of backup servers if you have more than one) acknowledges receipt
of the logs past the commit record would be an *extremely* good feature.

Those could be broken out into two separate points. Streaming WAL is one
thing, feedback and pitr-synchronous commits would be a second point.

Knowing what WAL record the backup server had received so far and what WAL
record it had processed so far would be useful for plenty of other purposes as
well.

- Improve scalability of recovery for large I/O bound databases

That seems too vague for the TODO. Did you have specific items in mind?

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

On Thu, 2007-09-06 at 21:50 +0100, Gregory Stark wrote:

- Improve scalability of recovery for large I/O bound databases

That seems too vague for the TODO. Did you have specific items in mind?

I think we should parallelise recovery. Heikki wanted to do this another
way, so I worded it vaguely (i.e. as a requirement) to cover multiple
approaches. Some of that was discussed on -hackers already (where?).

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Thu, Sep 6, 2007 at 3:25 PM, in message

<1189110327.9243.17.camel@dogma.ljc.laika.com>, Jeff Davis <pgsql@j-davis.com>
wrote:

On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote:

With file-based log shipping, you can get down to 1 second, by using the
archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

I tried doing a couple pg_switch_xlog() in quick succession, and the WAL
files that represent less than a second of actual data don't seem much
more compressible than the rest.

Agreed. We kick out a WAL file once per hour (if they don't fill first)
and have found that a compressed WAL file during normal activity averages
a little over 4 MB. During the nightly VACUUM ANALYZE we get a few over
10 MB. The interesting thing is that about the time that these WAL files
would get reused, even when the system is idle, they are at the VACUUM
ANALYZE size again. Note how the first three 18:00 file sizes echo at the
next morning, before the users are in and working.

5293110 2007-08-22 17:14 000000010000001800000044.gz
5205720 2007-08-22 17:14 000000010000001800000045.gz
5104595 2007-08-22 17:14 000000010000001800000046.gz
3747524 2007-08-22 17:14 000000010000001800000047.gz
3118762 2007-08-22 17:14 000000010000001800000048.gz
3342918 2007-08-22 17:14 000000010000001800000049.gz
4600438 2007-08-22 17:14 00000001000000180000004A.gz
2715708 2007-08-22 17:15 00000001000000180000004B.gz
2865803 2007-08-22 17:15 00000001000000180000004C.gz
10111700 2007-08-22 18:00 00000001000000180000004D.gz
11561630 2007-08-22 18:00 00000001000000180000004E.gz
11619590 2007-08-22 18:00 00000001000000180000004F.gz
7165231 2007-08-22 18:00 000000010000001800000050.gz
4012164 2007-08-22 18:00 000000010000001800000051.gz
4590502 2007-08-22 18:00 000000010000001800000052.gz
3617203 2007-08-22 18:01 000000010000001800000053.gz
3552210 2007-08-22 18:01 000000010000001800000054.gz
4006261 2007-08-22 18:01 000000010000001800000055.gz
3538293 2007-08-22 18:01 000000010000001800000056.gz
4291776 2007-08-22 18:02 000000010000001800000057.gz
4837268 2007-08-22 18:02 000000010000001800000058.gz
3948408 2007-08-22 19:02 000000010000001800000059.gz
2714635 2007-08-22 20:02 00000001000000180000005A.gz
4989698 2007-08-22 21:02 00000001000000180000005B.gz
5290729 2007-08-22 22:02 00000001000000180000005C.gz
5203306 2007-08-22 23:02 00000001000000180000005D.gz
5101976 2007-08-23 00:02 00000001000000180000005E.gz
3745125 2007-08-23 01:02 00000001000000180000005F.gz
3119218 2007-08-23 02:02 000000010000001800000060.gz
3340691 2007-08-23 03:02 000000010000001800000061.gz
4599279 2007-08-23 04:02 000000010000001800000062.gz
10110899 2007-08-23 05:02 000000010000001800000063.gz
11555895 2007-08-23 06:02 000000010000001800000064.gz
11587646 2007-08-23 07:02 000000010000001800000065.gz

Jeff Davis <pgsql@j-davis.com> writes:

On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote:

With file-based log shipping, you can get down to 1 second, by using the
archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

I tried doing a couple pg_switch_xlog() in quick succession, and the WAL
files that represent less than a second of actual data don't seem much
more compressible than the rest.

That's because we do not try to zero out a WAL file when recycling it,
so the part after what's been used is not any more compressible than the
valid part.

pg_switch_xlog is not, and was never intended to be, a solution for a
low-latency log-shipping scheme. The right solution for that is to make
a smarter log-shipping daemon that transmits only part of a WAL segment
at a time. (As Hannu is getting tired of repeating, you can find a
working example in SkyTools.)

regards, tom lane

On Thu, 2007-09-06 at 19:23 -0400, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

On Thu, 2007-09-06 at 12:08 +0100, Heikki Linnakangas wrote:

With file-based log shipping, you can get down to 1 second, by using the
archive_timeout setting. It will produce a lot of log files with very
little content in them, but they will compress well.

I tried doing a couple pg_switch_xlog() in quick succession, and the WAL
files that represent less than a second of actual data don't seem much
more compressible than the rest.

That's because we do not try to zero out a WAL file when recycling it,
so the part after what's been used is not any more compressible than the
valid part.

That was my point.

pg_switch_xlog is not, and was never intended to be, a solution for a
low-latency log-shipping scheme. The right solution for that is to make
a smarter log-shipping daemon that transmits only part of a WAL segment
at a time. (As Hannu is getting tired of repeating, you can find a
working example in SkyTools.)

I think that in addition to WalMgr (which is the tool I assume you're
referring to), there's still room for a simple tool that can zero out
the meaningless data in a partially-used WAL segment before compression.
It seems reasonable to me, so long as you keep archive_timeout at
something reasonably high.

If nothing else, people that already have a collection of archived WAL
segments would then be able to compact them.

I agree that something like WalMgr is better for low-latency, however.

Regards,
Jeff Davis

On Thu, Sep 6, 2007 at 7:03 PM, in message

<1189123422.9243.29.camel@dogma.ljc.laika.com>, Jeff Davis <pgsql@j-davis.com>
wrote:

I think ... there's still room for a simple tool that can zero out
the meaningless data in a partially-used WAL segment before compression.
It seems reasonable to me, so long as you keep archive_timeout at
something reasonably high.

If nothing else, people that already have a collection of archived WAL
segments would then be able to compact them.

That would be a *very* useful tool for us, particularly if it could work
against our existing collection of old WAL files.

-Kevin

Andrew Sullivan schrieb:

It seems that what you want is near-real-time online backups with _no
cost_, which is not a feature that I think anyone will ever work on.
A

100% Correct!

I think anyone commit the Statement, thadt a Databases is a very
imported Part of Software
for a wide range of Peoples. It is absoluty normaly thadt thoose Peoples
who using Databases having the Wish to have
there Data save and up to Date until to the Last Record of a Customer
(for Example: A Order form a Onlineshop).

Very Large Databases with sizes more then over 1 Terrabyte cannot be
backed up continously using Dumps
or the common Backupmethods

The Abstract need is:

1.An exact Copy (1:1) of the Productiondatabase should be mirrored at a
diffrent Machine, connected via (LAN/WAN) (as Backup)
2.The Backup Operations should be done normaly while the Database is
Online with very small Bandwich, I/O and CPU Trafficcosts.
3.Per Month or Week (or choosable) at a New Backup-Timeline/Version
should be started (depend on the Basebackup)
4.A New Base and its Streaming Backup Configuration should be
"Hot-Addable" to an running Productiondatabase (without Shutdown or Lock)
5.A Remote Operationmanager Interface (GUI) should be there for
Monitoring and Maintaining the Backups (maybe in PGAdmin)
6.If the Production and Mirror replicated Database is crashed (Hardware
Failure or Provider Problems) the Recovery should done verry fast.

(An Idea for 5. / Clientside)...
It would be a great Feature, if the PSQL-Client having a Local Deamon,
thadt can Download the Backup Catalogues from
the WAN-Backup FTP-Server continously down to the local Adminmachine.
Then the Admin is able to burn the DB-Backups on
DVD or write it on Tape on its local Machine at his Company (if the
Backupreplicationserver fails two or the Internetprovider has
Problems, the DB-Admin can apply its local Backups from DVD to the New
DB on a New Machine anytime)

Apoc

Trevor Talbot schrieb:

Backup 12/24/2008 Version 2
/pg/backup/12_24_2008/base/rcvry.rcv <--- Basebackup
/pg/backup/12_24_2008/changes/0001.chg <--- Changed Data
/changes/0002.chg <--- Changed Data
/changes/0003.chg <--- Changed Data
....
/changes/0010.chg <--- Changed Data
/changes/0001.rsf <--- Recovery

Stripeset File (10 MByte) addon of Basebackup
delete *.chg

if a Stripeset of 10 *.chg Files exist, they should be converted or merged
to one bigger *.rsf Stripesetfile (to prevent for having Millions *.chg)

Why? What does this actually do?

This is the Structure on FTP-Site of the Backupserver! Not locally where
the Postmaster runs on!

Locally the Postmaster can create a basebackup (rcvry.rcv) then the
FTP-Deamon logs on to the
Remote FTP-Backupserver and Uploads this File to keep it save:

/pg/backup/12_24_2008/
/pg/backup/12_24_2008/base/rcvry.rcv <--- Basebackup

At the Next:
If a New Record at the Database was written, then the Postmaster locally
creates

A change File named: "0001.chg"

After thits the locally FTP-Deamon transfers this File to

/pg/backup/12_24_2008/changes/0001.chg <--- Changed Data

Then

/pg/backup/12_24_2008/changes/0002.chg <--- Changed Data
/pg/backup/12_24_2008/changes/0003.chg <--- Changed Data
....
/pg/backup/12_24_2008/changes/0010.chg <--- Changed Data

Then the FTP-Backupdeamon merged the last 10.x *.chg Files to one greater
*.RSF File "Online while logged into the FTP-Backupserver" and Delete Online
the Last 10 *.chg Files (after this the Last 10.x *.chg Files, created
by the Postmaster
can be deleted localy where the Postmaster runs).

The Benefit:
If the Machine where the Database run on having a Mailfunction, then the
Last Record
of the Databasecatalouge is backed up to an *.chg or *. RSF File.

Thadt my Idea of this

Apoc

On Fri, Sep 07, 2007 at 12:03:31PM +0200, apoc9009 wrote:

Andrew Sullivan schrieb:

It seems that what you want is near-real-time online backups with _no
cost_, which is not a feature that I think anyone will ever work on.
A

100% Correct!

I think anyone commit the Statement, thadt a Databases is a very
imported Part of Software
for a wide range of Peoples. It is absoluty normaly thadt thoose Peoples
who using Databases having the Wish to have
there Data save and up to Date until to the Last Record of a Customer
(for Example: A Order form a Onlineshop).

Ah, but that's not what you asked (at least not as I interpreted it).
You see, postgres does what you want if there's a simple power failure.
Everything committed stays committed. You can protect against disk
failure with RAID arrays also. What it sounds like is that you want to
protect from someone blowing up your storage system.

I think you need to step back and work out exactly what you are
protecting against. Because it looks like your suggestion won't protect
against a meteor stike on your data centre and 100km surrounding.

Tell us what you are protecting against, then you can get some more
useful answers.

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

Ühel kenal päeval, R, 2007-09-07 kell 12:20, kirjutas apoc9009:

Trevor Talbot schrieb:

Backup 12/24/2008 Version 2
/pg/backup/12_24_2008/base/rcvry.rcv <--- Basebackup
/pg/backup/12_24_2008/changes/0001.chg <--- Changed Data
/changes/0002.chg <--- Changed Data
/changes/0003.chg <--- Changed Data
....
/changes/0010.chg <--- Changed Data
/changes/0001.rsf <--- Recovery

Stripeset File (10 MByte) addon of Basebackup
delete *.chg

if a Stripeset of 10 *.chg Files exist, they should be converted or merged
to one bigger *.rsf Stripesetfile (to prevent for having Millions *.chg)

Why? What does this actually do?

This is the Structure on FTP-Site of the Backupserver! Not locally where
the Postmaster runs on!

This can currently be done with a simple script, either with ftp or
ssh/scp

-------------
Hannu

Ühel kenal päeval, R, 2007-09-07 kell 12:03, kirjutas apoc9009:

Andrew Sullivan schrieb:

It seems that what you want is near-real-time online backups with _no
cost_, which is not a feature that I think anyone will ever work on.
A

100% Correct!

I think anyone commit the Statement, thadt a Databases is a very
imported Part of Software
for a wide range of Peoples. It is absoluty normaly thadt thoose Peoples
who using Databases having the Wish to have
there Data save and up to Date until to the Last Record of a Customer
(for Example: A Order form a Onlineshop).

Very Large Databases with sizes more then over 1 Terrabyte cannot be
backed up continously using Dumps
or the common Backupmethods

The Abstract need is:

1.An exact Copy (1:1) of the Productiondatabase should be mirrored at a
diffrent Machine, connected via (LAN/WAN) (as Backup)
2.The Backup Operations should be done normaly while the Database is
Online with very small Bandwich, I/O and CPU Trafficcosts.
3.Per Month or Week (or choosable) at a New Backup-Timeline/Version
should be started (depend on the Basebackup)

Probably you don't want your backup as a base backup + a bunch of
logfiles.
If you need to rally move to backup, then even a weeks worth of logfiles
will take several hours or possibly days to apply.

What you want is a standby machine, which applies all WAL files as they
are copied over.

4.A New Base and its Streaming Backup Configuration should be
"Hot-Addable" to an running Productiondatabase (without Shutdown or Lock)

It currently is

5.A Remote Operationmanager Interface (GUI) should be there for
Monitoring and Maintaining the Backups (maybe in PGAdmin)

Huh ? I don't think this has anything to do with postgres-hackers list.

Actually your other questions should also go to some users/newbies list,
as this is a question of using existing features, and not something that
needs to be added to backend.

6.If the Production and Mirror replicated Database is crashed (Hardware
Failure or Provider Problems) the Recovery should done verry fast.

Yep. That is what Hot-standby as described in postgres manual is meant
to do.

(An Idea for 5. / Clientside)...
It would be a great Feature, if the PSQL-Client having a Local Deamon,
thadt can Download the Backup Catalogues from
the WAN-Backup FTP-Server continously down to the local Adminmachine.
Then the Admin is able to burn the DB-Backups on
DVD or write it on Tape on its local Machine at his Company (if the
Backupreplicationserver fails two or the Internetprovider has
Problems, the DB-Admin can apply its local Backups from DVD to the New
DB on a New Machine anytime)

All this keeping the WAL logs on CD's/tapes has nothing to do with fast
recovery after failure.

This is something that would be useful, but for entirely other purposes.
Namely for a case, when you need to _GO_BACK_ to an earlier state. For
example you discover that an careless operator or software bug has
deleted important data last Wednesday and you need a way to get it back.
In that case you take your last Sundays base backup and apply WAL up to
few moments before the error happened. But it will not be fast.

--------------------
Hannu

Hi,

apoc9009 wrote:

Thadt is Replication NOT Backup

I've now read all of your messages in this thread, but I simply fail to
understand why you are that much opposed to the term 'replication'. I
think the only thing which comes any close to what you're looking for is
replication (in particular eager multi-master replication).

I'd recommend you familiarize yourself with the world of database
replication. You already know the important chapter from our manual,
learn that by heart. Then read [2]Terms and Definitions of Database Replication http://www.postgres-r.org/documentation/terms and [3]. :-)

Regards

Markus

[1]: Postgres advocacy wiki: http://developer.postgresql.org/index.php/Replication%2C_Clustering%2C_and_Connection_Pooling
http://developer.postgresql.org/index.php/Replication%2C_Clustering%2C_and_Connection_Pooling

[2]: Terms and Definitions of Database Replication http://www.postgres-r.org/documentation/terms
http://www.postgres-r.org/documentation/terms

Markus Schiltknecht schrieb:

Hi,

apoc9009 wrote:

Thadt is Replication NOT Backup

I've now read all of your messages in this thread, but I simply fail
to understand why you are that much opposed to the term 'replication'.
I think the only thing which comes any close to what you're looking
for is replication (in particular eager multi-master replication).

What is your Problem in understanding the Word "Backup"?

Translation for you:
A Backup is a File or Set of Files thadt contains the Data of your
Business critical Informations.
It should not be Archived on the same place, the same House or the same
Room.

A Replication Database has nothing to do with a Backup, it works only
for Failover if the Primary
Database has a Mailfunction.

A good Backuptool is needed if you have Databases with sizes over 1
Terrabyte. The common
Backup methods wont Work with Online Productiondatabases and without the
Problem of Datalosses,
this is only a Way for small and Mediumsize Databases, not for Hugh
Databases.

Keep in Mind: Backup is NOT Replication!

Write it down 100 times and maybe you understand

apoc9009 wrote:

Write it down 100 times and maybe you understand

If you are going to be rude nobody will bother to respond to you.
Acknowledged experts have been very patient with you so far in this
thread. You should be appreciative, not truculent.

cheers

andrew

Markus Schiltknecht wrote:

[2]: Terms and Definitions of Database Replication
http://www.postgres-r.org/documentation/terms

Markus, the links in the left side menu are broken on the "about" and
"documentation" page. They point to <http://www.postgres-r.org/overview&gt;
instead of <http://www.postgres-r.org/documentation/overview&gt;, etc.

Rainer

backup is not replication.
but replicated database can be treated as good source of backup.

please take following remarks:

1) in English you don't capitalize nouns
2) read what other people write to you and try to understand that.
3) this is open source, try to be more cooperative not just cry for a
ready made solution

2007/9/7, apoc9009 <apoc9009@yahoo.de>:

Markus Schiltknecht schrieb:

Hi,

apoc9009 wrote:

Thadt is Replication NOT Backup

I've now read all of your messages in this thread, but I simply fail
to understand why you are that much opposed to the term 'replication'.
I think the only thing which comes any close to what you're looking
for is replication (in particular eager multi-master replication).

What is your Problem in understanding the Word "Backup"?

Translation for you:
A Backup is a File or Set of Files thadt contains the Data of your
Business critical Informations.
It should not be Archived on the same place, the same House or the same
Room.

A Replication Database has nothing to do with a Backup, it works only
for Failover if the Primary
Database has a Mailfunction.

A good Backuptool is needed if you have Databases with sizes over 1
Terrabyte. The common
Backup methods wont Work with Online Productiondatabases and without the
Problem of Datalosses,
this is only a Way for small and Mediumsize Databases, not for Hugh
Databases.

Keep in Mind: Backup is NOT Replication!

Write it down 100 times and maybe you understand

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

http://archives.postgresql.org

--
Filip Rembiałkowski

Filip Rembiałkowski schrieb:

please take following remarks:

thx, but if i need some advice form a scandinavian dickhead then i will
let you know this

Hi,

apoc9009 wrote:

Translation for you:
A Backup is a File or Set of Files thadt contains the Data of your
Business critical Informations.
It should not be Archived on the same place, the same House or the same
Room.

I disagree, a backup does not necessarily have to be a single file or a
set of files. Wikipedia has this definition:

"backup refers to making copies of data so that these additional
copies may be used to restore the original after a data loss event."

While for "replica", it states:

"replica is a copy that is relatively indistinguishable from the
original"

Thus a backup can very well be thought of as replica, and vice versa.

A Replication Database has nothing to do with a Backup, it works only
for Failover if the Primary
Database has a Mailfunction.

That's certainly plain wrong, see multi-master replication where
failover doesn't make any sense. Wikipedia again (although that's
unfair, as I've contributed to that definition myself) [1]http://en.wikipedia.org/wiki/Replication_%28computer_science%29:

"Replication is the process of sharing information so as to ensure
consistency between redundant resources"

..for example a master database and a backup.

Keep in Mind: Backup is NOT Replication!
Write it down 100 times and maybe you understand

A backup IS a replica. A backup IS a replica. A backup IS a replica. A
backup IS a replica...

Regards

Markus

[1]: http://en.wikipedia.org/wiki/Replication_%28computer_science%29

apoc9009 wrote:

Filip Rembiałkowski schrieb:

please take following remarks:

thx, but if i need some advice form a scandinavian dickhead then i will
let you know this

That kind of remark is not acceptable on the PostgreSQL mailing lists.
Please do not post here again unless you can speak to people with an
appropriate amount of respect.

Regards, Dave.

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

apoc9009 wrote:

Filip Rembiałkowski schrieb:

please take following remarks:

thx, but if i need some advice form a scandinavian dickhead then i will
let you know this

This is not acceptable on our lists. Do not post in such a way again.

Sincerely,

Joshua D. Drake

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

- --

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFG4W9BATb/zqfZUUQRAgglAJ9Le5Yxu796/tfJfVTXUfRSecGnlACfe+iB
KNK7jelJo30lh8ymw1Ppfqo=
=CSbC
-----END PGP SIGNATURE-----

Ühel kenal päeval, R, 2007-09-07 kell 16:41, kirjutas apoc9009:

Filip Rembiałkowski schrieb:

please take following remarks:

thx, but if i need some advice form a scandinavian dickhead then i will
let you know this

Is this "apoc9009" guy real ?

For some time I honestly believed (based in part on the english-like
language used in postings) that he just cant understand what is written
in our documentation.

My other suspicion was that he has never actually tried to do what he
claims, but has just click-checked some checkboxes on some third-party
backup software and is actually lamenting about the lack of box labeled
"Absolutely Reliable PostgreSQL backup".

But now i suspect that someone is just pulling our collective leg and
just trolling under false name to test how long we stand it ?

---------------
Hannu

P.S.: any ideas, whom he suspect think to be "scandinavian" in this
thread ? or is "advice form a scandinavian dickhead" an idiom in some
language ?

On Sat, Sep 08, 2007 at 01:02:04AM +0300, Hannu Krosing wrote:

??hel kenal p??eval, R, 2007-09-07 kell 16:41, kirjutas apoc9009:

Filip Rembia??kowski schrieb:

please take following remarks:

thx, but if i need some advice form a scandinavian dickhead then i will
let you know this

Is this "apoc9009" guy real ?

Pretty much as soon as I saw that comment I just nuked the whole thread
and moved on. I suggest everyone else just do the same.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Joshua D. Drake schrieb:

This is not acceptable on our lists. Do not post in such a way again.

Sincerely,

Joshua D. Drake

Hi Josh,
Your're right, but this special Guy has just boring me a lot with
Replication Things but
my [Featurerequest] on the Topic was dedicated to "Streaming
Onlinebackup" and this is not
just the same as a simply Replication.

Sometimes i think, its an Scandinavian Tradition, boring and talking
without link to the Topic
and giving primitive, useless advices like: "look in google.com OR use:
"www.postgresql.org search Button"
tadt doesnt match with the Subject of Discussion.

Apoc

Ühel kenal päeval, L, 2007-09-08 kell 10:39, kirjutas apoc9009:

Joshua D. Drake schrieb:

This is not acceptable on our lists. Do not post in such a way again.

Sincerely,

Joshua D. Drake

Hi Josh,
Your're right, but this special Guy has just boring me a lot with
Replication Things but
my [Featurerequest] on the Topic was dedicated to "Streaming
Onlinebackup" and this is not
just the same as a simply Replication.

Sometimes i think, its an Scandinavian Tradition, boring and talking
without link to the Topic
and giving primitive, useless advices like: "look in google.com OR use:
"www.postgresql.org search Button"
tadt doesnt match with the Subject of Discussion.

While his second advice:

2) read what other people write to you and try to understand that.

may look like something "primitive" in general, I think it was to the
point in this case.

Try thinking of it some more, and you may eventually zen it, recognizing
that

A. you can easily roll your own "Streaming Onlinebackup" in any
scripting language and with exactly the ftp directory structure using
the info provided, and without needing any more skills than making
queries to database and reading a portion of file from position N to
position M.

B. it probably won't take more than an hour to set up including testing.

C. 'backup _is_ replication' is also true

----------
Hannu

C. 'backup _is_ replication' is also true

----------
Hannu

It is useless to speak with a person like you about the diffrence between
Backup and Replications.Both Things having diffrent Concepts and
Approaches,
but for you it is all the same.

What should i say? Thadts the typically scandinavian Fishheadnature. A
Fish is a Fish.
(anyway if one is a Wale and next is a Shark).

I guess, the next Thing you will say is: Nobody was on the Moon and the
9/11 Incident
was the Work of George W. Bush Junior itself and Santa Claus will be the
next President
of the United States.

Apoc

"Hannu Krosing" <hannu@skype.net> writes:

Is this "apoc9009" guy real ?

Please, just don't respond.

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

Ühel kenal päeval, L, 2007-09-08 kell 21:15, kirjutas Apoc Sagdiyev:

C. 'backup _is_ replication' is also true

----------
Hannu

It is useless to speak with a person like you

Oh, you think that _I_ am "scandinavian" ?? Never thought about that
possibility ;P

If speaking with me is useless to you, then don't.

about the diffrence between Backup and Replications.
Both Things having diffrent Concepts and Approaches, but for you it is all the same.

by Capitalising these Nouns you really make them look like some Big
Concepts that are Beyond Understanding Of Mere Mortals.

What should i say? Thadts the typically scandinavian Fishheadnature. A
Fish is a Fish. (anyway if one is a Wale and next is a Shark).

The reason I have been so patient with you is that your question and
writing style suggests that you are still quite young, probably no more
that 17 to 19, and also I thought that you were looking for a solution
to your problem, not just trying to look smart or pushing your latest
great idea.

<meat>

What I was trying to tell you, is that in order to have a backup that
can actually be used quickly in case of failure on master db, you need
your backup to be in form of replica (that is one meaning of "backup
_is_ replication").

If you had your backup as a week-old base backup + a set of WAL files,
it can take days to bring the replacement machine up, as both unpacking
the base backup and especially replaying the WAL files take time.

</meat>

I guess, the next Thing you will say is: Nobody was on the Moon and the
9/11 Incident was the Work of George W. Bush Junior itself and Santa
Claus will be the next President of the United States.

No! Actually I'm wearing my tin hat right now and I Never say Anything
about My Suspicions about 9/11 on Internet in fear of Echelon catching
and filing me.

---------------
Hannu

No! Actually I'm wearing my tin hat right now and I Never say Anything
about My Suspicions about 9/11 on Internet in fear of Echelon catching
and filing me.

---------------
Hannu

hmm, a little bit Para?

http://www.myvideo.de/watch/1776449

Ok, now your point of View its more clearly...

On Thu, Sep 6, 2007 at 7:31 PM, in message

<46E055A7.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:

On Thu, Sep 6, 2007 at 7:03 PM, in message

<1189123422.9243.29.camel@dogma.ljc.laika.com>, Jeff Davis <pgsql@j-davis.com>
wrote:

I think ... there's still room for a simple tool that can zero out
the meaningless data in a partially-used WAL segment before compression.
It seems reasonable to me, so long as you keep archive_timeout at
something reasonably high.

If nothing else, people that already have a collection of archived WAL
segments would then be able to compact them.

That would be a *very* useful tool for us, particularly if it could work
against our existing collection of old WAL files.

Management here has decided that it would be such a useful tool for our
organization that, if nobody else is working on it yet, it is something I
should be working on this week. Obviously, I would much prefer to do it
in a way which would be useful to the rest of the PostgreSQL community,
so I'm looking for advice, direction, and suggestions before I get started.

I was planning on a stand-alone executable which could be run against a
list of files to update them in-place, or to handle as single file as a
stream. The former would be useful for dealing with the accumulation of
files we've already got, the latter would be used in our archive script,
just ahead of gzip in the pipe.

Any suggestions on an existing executable to use as a model for "best
practices" are welcome, as are suggestions for the safest and most robust
techniques for identifying the portion of the WAL file which should be set
to zero.

Finally, I assume that I should put this on pgfoundry?

-Kevin

On Mon, Sep 24, 2007 at 4:17 PM, in message

<46F7E335.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:

On Thu, Sep 6, 2007 at 7:03 PM, in message

<1189123422.9243.29.camel@dogma.ljc.laika.com>, Jeff Davis <pgsql@j-davis.com>
wrote:

I think ... there's still room for a simple tool that can zero out
the meaningless data in a partially-used WAL segment before compression.

so I'm looking for advice, direction, and suggestions before I get started.

Lacking any suggestions, I plowed ahead with something which satisfies
our needs. First, rough, version attached. It'll save us buying another
drawer of drives, so it was worth a few hours of research to figure out
how to do it.

If anyone spots any obvious defects please let me know. We'll be running
about 50,000 WAL files through it today or tomorrow; if any problems turn
up in that process I'll repost with a fix.

Given the lack of response to my previous post, I'll assume it's not worth
the effort to do more in terms of polishing it up; but if others are
interested in using it, I'll make some time for that.

Adding this to the pipe in our archive script not only saves disk space,
but reduces the CPU time overall, since gzip usually has less work to do.
When WAL files switch because they are full, the CPU time goes from about
0.8s to about 1.0s.

-Kevin

On Wed, 2007-09-26 at 11:05 -0500, Kevin Grittner wrote:

Lacking any suggestions, I plowed ahead with something which satisfies
our needs. First, rough, version attached. It'll save us buying another
drawer of drives, so it was worth a few hours of research to figure out
how to do it.

If anyone spots any obvious defects please let me know. We'll be running
about 50,000 WAL files through it today or tomorrow; if any problems turn
up in that process I'll repost with a fix.

Given the lack of response to my previous post, I'll assume it's not worth
the effort to do more in terms of polishing it up; but if others are
interested in using it, I'll make some time for that.

Adding this to the pipe in our archive script not only saves disk space,
but reduces the CPU time overall, since gzip usually has less work to do.
When WAL files switch because they are full, the CPU time goes from about
0.8s to about 1.0s.

It's nicely written and looks like it would perform well.

The logic for zeroing the blocks makes me nervous. It doesn't locate the
block from which to start, it treats all blocks equally, so might zero
some blocks and not others. What you have should work, but I'd be
inclined to put a test in there to check that doesn't happen: once we
begin to zero pages, all of them should be zeroed to end of file. If we
find one that shouldn't be zeroed, throw an error.

We should also document that this is designed to help compress files
that aren't full because we switched early because of archive_timeout.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Wed, Sep 26, 2007 at 3:14 PM, in message

<1190837675.4181.716.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

It's nicely written

Thanks. I spent some time looking at Tom Lane's pg_resetxlog and the
source code for cat to model my code. I'm rather rusty on C, so I wanted
to minimize the chance of doing anything outrageously stupid. Should I
be including anything in the comments to give credit for that? (I'm
never sure where the line is on that.)

and looks like it would perform well.

In my tests so far, it is faster to pipe through this and then gzip than
to just gzip, except when the WAL file is full or nearly so. In tests
with small counties (which rarely fill a file except at peak periods),
I'm seeing archival WAL space reduced to 27% of the original. I expect
that to climb to 35% to 40% when we do all counties, but that's just a
guess. I've seen some clues that it will get a bit better in 8.3 because
of HOT updates. (We force WAL files to be written hourly, by the way.)

For us, this reduces overall CPU time used in archiving, reduces disk
space needed for backups, reduces network traffic (including over a
relatively slow WAN). The one downside I've found is that it adds 0.2
seconds of CPU time per WAL file archive during our heaviest update
periods. It's in the archiver process, not a backend process that's
running a query, and we're not generally CPU bound, so this is not a
problem for us.

The logic for zeroing the blocks makes me nervous. It doesn't locate the
block from which to start, it treats all blocks equally, so might zero
some blocks and not others. What you have should work, but I'd be
inclined to put a test in there to check that doesn't happen: once we
begin to zero pages, all of them should be zeroed to end of file. If we
find one that shouldn't be zeroed, throw an error.

Agreed. That is one of the reasons I referred to this as a first, rough
version. I wanted to prove the technique in general before that
refinement.

Another reason is that it is rather light on error checking in general.
While I was loath limit it to an exact match on the magic number, since it
works unmodified on multiple versions, it seems dangerous not to enforce
any limits there. I wasn't sure how best to approach that. Suggestions?
I think I should also error if stdin has more data when I think I'm done.
Agreed?

I omitted the code I was originally considering to have it work against
files "in place" rather than as a filter. It seemed much simpler this
way, we didn't actually have a use case for the additional functionality,
and it seemed safer as a filter. Thoughts?

We should also document that this is designed to help compress files
that aren't full because we switched early because of archive_timeout.

Sure. Again, this is more at a "proof of concept" stage. It's enough to
get us out of a tight spot on drive space, even as it stands, but I know
that it needs polishing and documentation if it is to be accepted by the
community. I just wasn't sure the interest was actually there.

I'm still not sure whether this might be considered for inclusion in the
base release or contrib, or whether I should open a pgfoundry project.

Thanks for the feedback.

-Kevin

On Wed, Sep 26, 2007 at 3:14 PM, in message

<1190837675.4181.716.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

The logic for zeroing the blocks makes me nervous. It doesn't locate the
block from which to start, it treats all blocks equally, so might zero
some blocks and not others. What you have should work, but I'd be
inclined to put a test in there to check that doesn't happen: once we
begin to zero pages, all of them should be zeroed to end of file. If we
find one that shouldn't be zeroed, throw an error.

We should also document that this is designed to help compress files
that aren't full because we switched early because of archive_timeout.

Attached is a modified version to implement both of these. I also bailed
out if there was surplus input. I tried an optimization of allocating a
separate buffer for outputting the zeros, to avoid repeated memset calls.
It didn't seem to make a very big difference; do you think it's worth
cluttering the code with that?

-Kevin

Kevin Grittner wrote:

I omitted the code I was originally considering to have it work against
files "in place" rather than as a filter. It seemed much simpler this
way, we didn't actually have a use case for the additional functionality,
and it seemed safer as a filter. Thoughts?

A special "non-filter" mode could save some IO and diskspace by not actually
writing all those zeros, but instead just seek to SizeOfWal-1 after writing the
last valid byte, and writing one more zero. Of course, if you're gonna
compress the WAL anyway, there is no point...

greetings, Florian Pflug

Kevin Grittner wrote:

<1190837675.4181.716.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

We should also document that this is designed to help compress files
that aren't full because we switched early because of archive_timeout.

Attached is a modified version to implement both of these. I also bailed
out if there was surplus input. I tried an optimization of allocating a
separate buffer for outputting the zeros, to avoid repeated memset calls.
It didn't seem to make a very big difference; do you think it's worth
cluttering the code with that?

Would it work to just ftruncate the file?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Thu, 2007-09-27 at 08:31 +0100, Heikki Linnakangas wrote:

Kevin Grittner wrote:

<1190837675.4181.716.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

We should also document that this is designed to help compress files
that aren't full because we switched early because of archive_timeout.

Attached is a modified version to implement both of these. I also bailed
out if there was surplus input. I tried an optimization of allocating a
separate buffer for outputting the zeros, to avoid repeated memset calls.
It didn't seem to make a very big difference; do you think it's worth
cluttering the code with that?

Would it work to just ftruncate the file?

At the source end, yes. At the destination one of the tests we perform
for a fully and correctly copied file is the file size; a smaller file
size we take to mean that the copy is still in progress. We check that
before we actually read the file, so it would complicate things
considerably to allow for variable file sizes.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Attached is a modified version to implement both of these. I also

bailed

out if there was surplus input. I tried an optimization of

allocating a

separate buffer for outputting the zeros, to avoid repeated memset

calls.

It didn't seem to make a very big difference; do you think it's

worth

cluttering the code with that?

Would it work to just ftruncate the file?

We would need to teach recovery to accept a short file if the last
record is a
valid switch log XLOG record. RestoreArchivedFile currently bails out if
the file
size is not XLogSegSize.

We need to make exact checks though, or this would reduce reliability.
(e.g. a short file must have records up to the very end)

The probably useful next step would be to pass the current length to the
archive_command,
so it can write the filled part of the file without the need for a
filter.

Andreas

On Thu, 2007-09-27 at 10:17 +0200, Zeugswetter Andreas ADI SD wrote:

Attached is a modified version to implement both of these. I also

bailed

out if there was surplus input. I tried an optimization of

allocating a

separate buffer for outputting the zeros, to avoid repeated memset

calls.

It didn't seem to make a very big difference; do you think it's

worth

cluttering the code with that?

Would it work to just ftruncate the file?

We would need to teach recovery to accept a short file if the last
record is a
valid switch log XLOG record. RestoreArchivedFile currently bails out if
the file
size is not XLogSegSize.

We also need to check in pg_standby to see whether the file is still
being copied or is complete. Currently, we check the filesize and wait
for it to be 16M. There would need to be another way for pg_standby to
assess whether the file has completed transfer before copying into the
data directory of the standby node.

We need to make exact checks though, or this would reduce reliability.
(e.g. a short file must have records up to the very end)

The probably useful next step would be to pass the current length to the
archive_command,
so it can write the filled part of the file without the need for a
filter.

It's certainly possible to pass file metadata as well as the file.

I'd be worried that would significantly complicate the mechanism, which
at the moment is clean and simple; so this isn't gonna happen for 8.3
AFAICS. Changing that would require people to redesign their HA configs,
which doesn't sound that great to me.

The next step for me is to stream the records, not to fuss too much with
the existing file level copying. Streaming provides what we really want:
a shorter delay in data transfer between primary and standby, without
additional overhead.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Wed, 2007-09-26 at 16:31 -0500, Kevin Grittner wrote:

The one downside I've found is that it adds 0.2
seconds of CPU time per WAL file archive during our heaviest update
periods. It's in the archiver process, not a backend process that's
running a query, and we're not generally CPU bound, so this is not a
problem for us.

OK, first time anybody's measured a significant cost to process creation
during execution of the archive_command. Still fairly low though.

Increasing the size of the WAL files would cure that. :-(

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

On Thu, Sep 27, 2007 at 3:17 AM, in message

<E1539E0ED7043848906A8FF995BDA57902685E09@m0143.s-mxs.net>, "Zeugswetter
Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> wrote:

The probably useful next step would be to pass the current length to the
archive_command,
so it can write the filled part of the file without the need for a
filter.

I can see that helping a lot, but not by writing onto the file on disk.
If the file is nearly empty, that would be a lot of disk I/O which doesn't
need to happen. One place it could help is allowing the archive script to
skip the filter if the file is full. On files with unused space, we could
skip the rest of the input and just blast out zeros to the desired file
length.

-Kevin

On Thu, Sep 27, 2007 at 6:56 AM, in message

<1190894211.4194.64.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

On Wed, 2007-09-26 at 16:31 -0500, Kevin Grittner wrote:

The one downside I've found is that it adds 0.2
seconds of CPU time per WAL file archive during our heaviest update
periods.

OK, first time anybody's measured a significant cost to process creation
during execution of the archive_command. Still fairly low though.

Since it's that unusual, I'll check it closely during more "normal"
testing. The timings so far are against old WAL files on the box running
72 warm standby instances and actively running rsync against all of the
sources. Perhaps the unusual load somehow distorted the measurement. I
based this on running a gzip of various WAL files versus the filter piped
to gzip; writing to a file in both cases, and using "time" to get the
metrics. Any suggestions for different or better ways to measure the
impact are welcome.

-Kevin

On Wed, Sep 26, 2007 at 7:29 PM, in message <46FAF95D.6070003@phlo.org>,

"Florian G. Pflug" <fgp@phlo.org> wrote:

Kevin Grittner wrote:

I omitted the code I was originally considering to have it work against
files "in place" rather than as a filter. It seemed much simpler this
way, we didn't actually have a use case for the additional functionality,
and it seemed safer as a filter. Thoughts?

A special "non-filter" mode could save some IO and diskspace by not actually
writing all those zeros, but instead just seek to SizeOfWal-1 after writing
the
last valid byte, and writing one more zero. Of course, if you're gonna
compress the WAL anyway, there is no point...

Right. And if you're not, why bother setting to zero? I couldn't invent
a plausible scenario where we would want to do the update in place, and
I'm afraid someone might be tempted to run it against "live" WAL files.
So I decided it was best to let it lie unless someone else had a real-
life situation where it was useful. Even then, I could write a bash
script to do it using the filter a lot faster than I could modify the C
code to safely deal with the files in-place, so I'm pretty skeptical.

-Kevin

On Thu, Sep 27, 2007 at 6:56 AM, in message

<1190894211.4194.64.camel@ebony.site>, Simon Riggs <simon@2ndquadrant.com>
wrote:

On Wed, 2007-09-26 at 16:31 -0500, Kevin Grittner wrote:

The one downside I've found is that it adds 0.2
seconds of CPU time per WAL file archive during our heaviest update
periods. It's in the archiver process, not a backend process that's
running a query, and we're not generally CPU bound, so this is not a
problem for us.

OK, first time anybody's measured a significant cost to process creation
during execution of the archive_command. Still fairly low though.

Confirmed in further tests on a normal production environment. Starting
from a set of OS cached, 16 MB WAL files representing several days of
activity, the overall time to compress through gzip to disk went down
when piped through this filter, but the time for a full file went up.

Best case:

gzip:
real 0m0.769s
user 0m0.759s
sys 0m0.009s
gz size: 4562441

pg_cleanxlogtail | gzip:
real 0m0.132s
user 0m0.119s
sys 0m0.024s
gz size: 16406

Worst case:

gzip:
real 0m0.781s
user 0m0.770s
sys 0m0.010s
gz size: 4554307

pg_cleanxlogtail | gzip:
real 0m1.073s
user 0m1.018s
sys 0m0.063s
gz size: 4554307

Is it necessary to try to improve that worst case?

By the way, I realize that the error messages are still lame.
I'm going to do something about that. I particularly don't like this
as a failure message:

echo 7777777777777777777 `cat 0000000100000003000000EF` | pg_clearxlogtail > /dev/null

pg_clearxlogtail: Warning, unexpected magic number
pg_clearxlogtail: stdin: Success

Is the filter-only approach acceptable, after the discussion here?
Is the magic number hanlding OK; if not, what would be?
Any other issues that I should address?

-Kevin

The probably useful next step would be to pass the current length to

the

archive_command,
so it can write the filled part of the file without the need for a
filter.

I can see that helping a lot, but not by writing onto the file on

disk.

If the file is nearly empty, that would be a lot of disk I/O which

doesn't

need to happen.

I think you misunderstood what I meant.
The actual archive command is constructed by expanding certain
placeholders.
I am suggesting to add such a placeholder for the size of the filled
part of the log.

A hypothetical example (note suggested %b placeholder for size in
bytes):
archive_command=dd if=%p of=/backup/WAL/%f bs=1 count=%b

This allows to avoid unnecessary io for the backup of partially filled
logs.

Andreas

Zeugswetter Andreas ADI SD wrote:

The probably useful next step would be to pass the current length to

the

archive_command,
so it can write the filled part of the file without the need for a
filter.

I can see that helping a lot, but not by writing onto the file on

disk.

If the file is nearly empty, that would be a lot of disk I/O which

doesn't

need to happen.

I think you misunderstood what I meant.
The actual archive command is constructed by expanding certain
placeholders.
I am suggesting to add such a placeholder for the size of the filled
part of the log.

A hypothetical example (note suggested %b placeholder for size in
bytes):
archive_command=dd if=%p of=/backup/WAL/%f bs=1 count=%b

This allows to avoid unnecessary io for the backup of partially filled
logs.

A nice improvement on that would be to have a "rearchive_command" to
allow to sync the new bytes written since a previous archive_command (so
it needs a new placeholder "start from this byte"). This allows writing
dd seek=%s skip=%s count=%b bs=1

(I had suggested something like this when PITR was just invented, but it
was disregarded because it was too complex for the first cut or the
feature).

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Sallah, I said NO camels! That's FIVE camels; can't you count?"
(Indiana Jones)

A nice improvement on that would be to have a "rearchive_command" to
allow to sync the new bytes written since a previous archive_command

(so

it needs a new placeholder "start from this byte"). This allows

writing

dd seek=%s skip=%s count=%b bs=1

But after a log switch nothing is filling that rest anymore.
Maybe this goes too much in the direction of a "streaming the log"
implementation,
which is imho better suited to ship transactions somewhere else as soon
as possible.

Andreas

"Zeugswetter Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> writes:

I am suggesting to add such a placeholder for the size of the filled
part of the log.

The archiver has not got that information, and can't compute it any
faster than the called command could.

regards, tom lane

On Fri, Sep 28, 2007 at 5:53 AM, in message

<E1539E0ED7043848906A8FF995BDA57902685F43@m0143.s-mxs.net>, "Zeugswetter
Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> wrote:

I think you misunderstood what I meant.
The actual archive command is constructed by expanding certain
placeholders.
I am suggesting to add such a placeholder for the size of the filled
part of the log.

A hypothetical example (note suggested %b placeholder for size in
bytes):
archive_command=dd if=%p of=/backup/WAL/%f bs=1 count=%b

This allows to avoid unnecessary io for the backup of partially filled
logs.

I did understand what you were suggesting regarding the size placeholder.
What didn't click is that the filter might not be necessary at all if we
had that. Thanks for clarifying that with an example.

Are you also suggesting that any code which depends on the log segment
files being at the full size should be changed, too? If not, I think
your example would need to cat the dd you showed with one which drew
from /dev/zero. I'll run a few tests with full and nearly empty files
using hand-generated values and see how the performance of this in
front of gzip compares to the filter.

-Kevin

On Fri, Sep 28, 2007 at 9:38 AM, in message

<46FCCB89.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:

On Fri, Sep 28, 2007 at 5:53 AM, in message

<E1539E0ED7043848906A8FF995BDA57902685F43@m0143.s-mxs.net>, "Zeugswetter
Andreas ADI SD" <Andreas.Zeugswetter@s-itsolutions.at> wrote:

archive_command=dd if=%p of=/backup/WAL/%f bs=1 count=%b

I think
your example would need to cat the dd you showed with one which drew
from /dev/zero. I'll run a few tests with full and nearly empty files
using hand-generated values and see how the performance of this in
front of gzip compares to the filter.

After Tom's email, this is pretty academic; but here are the results
for our "best case" example:

pg_clearxlogtail | gzip:
real 0m0.132s
user 0m0.119s
sys 0m0.024s

(dd if=00000001000000040000001A bs=1 count=132 ; dd if=/dev/zero bs=1 count=16777084) | gzip > ../kjgtest2/00000001000000040000001A.2.gz
132+0 records in
132+0 records out
16777084+0 records in
16777084+0 records out

real 0m19.243s
user 0m3.211s
sys 0m27.135s

That's a lot worse. I switched the bs and count:

(dd if=00000001000000040000001A bs=132 count=1 ; dd if=/dev/zero bs=16777084 count=1) | gzip > ../kjgtest2/00000001000000040000001A.3.gz
1+0 records in
1+0 records out
1+0 records in
1+0 records out

real 0m0.196s
user 0m0.173s
sys 0m0.025s

The filter code still wins.

The "worst case" example:

pg_clearxlogtail | gzip:
real 0m1.073s
user 0m1.018s
sys 0m0.063s
gz size: 4554307

ADAMS-PG:/var/pgsql/data/kjgtest # time dd if=0000000100000003000000F0 bs=16777216 count=1 | gzip > ../kjgtest2/0000000100000003000000F0.3.gz
1+0 records in
1+0 records out

Marginal improvement.

real 0m1.001s
user 0m0.923s
sys 0m0.081s

ADAMS-PG:/var/pgsql/data/kjgtest # time cat 0000000100000003000000F0 | gzip > ../kjgtest2/0000000100000003000000F0.4.gz

real 0m1.109s
user 0m1.055s
sys 0m0.062s

Not quite as good. Since the archiver process can't actually deliver
this number in a lightweight manner, all it goes to show is that the
filter code compares reasonably well in performance with dd and cat.

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Not quite as good. Since the archiver process can't actually deliver
this number in a lightweight manner, all it goes to show is that the
filter code compares reasonably well in performance with dd and cat.

I'd definitely vote for leaving it as a filter, given that there's
not a large performance penalty for that. It just seems a lot safer
and cleaner in that form.

regards, tom lane

On Thu, Sep 27, 2007 at 4:59 PM, in message

<46FBE172.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:

By the way, I realize that the error messages are still lame.
I'm going to do something about that.

Attached is a version as good as I know how to get it.
It works for us, so barring any problems as we use it, I'm done.

I confirmed with management that this code can be contributed to the
PostgreSQL community at large. It was written by myself as an employee
of the Wisconsin Supreme Court, Consolidated Court Automation Programs.
It is is distributed under the terms of the license of the University of
California as currently referenced here:

http://www.postgresql.org/docs/8.2/interactive/LEGALNOTICE.html

The only other code I looked at to derive technique was also distributed
under that license. I gratefully acknowledge the examples provided by the
authors of the code I examined: Tom Lane and Kevin Fall; although any
errors are my own.

I hope that others may find this filter useful.

-Kevin J. Grittner

It seems there is already a project on pgfoundry but there are no files:

http://pgfoundry.org/projects/clearxlogtail/

Should this be on pgfoundry or in the Postgres distribution. It seems
it might be tied enough to the WAL format to be in the Postgres
distribution.

---------------------------------------------------------------------------

Kevin Grittner wrote:

On Thu, Sep 6, 2007 at 7:31 PM, in message

<46E055A7.EE98.0025.0@wicourts.gov>, "Kevin Grittner"
<Kevin.Grittner@wicourts.gov> wrote:

On Thu, Sep 6, 2007 at 7:03 PM, in message

<1189123422.9243.29.camel@dogma.ljc.laika.com>, Jeff Davis <pgsql@j-davis.com>
wrote:

I think ... there's still room for a simple tool that can zero out
the meaningless data in a partially-used WAL segment before compression.
It seems reasonable to me, so long as you keep archive_timeout at
something reasonably high.

If nothing else, people that already have a collection of archived WAL
segments would then be able to compact them.

That would be a *very* useful tool for us, particularly if it could work
against our existing collection of old WAL files.

Management here has decided that it would be such a useful tool for our
organization that, if nobody else is working on it yet, it is something I
should be working on this week. Obviously, I would much prefer to do it
in a way which would be useful to the rest of the PostgreSQL community,
so I'm looking for advice, direction, and suggestions before I get started.

I was planning on a stand-alone executable which could be run against a
list of files to update them in-place, or to handle as single file as a
stream. The former would be useful for dealing with the accumulation of
files we've already got, the latter would be used in our archive script,
just ahead of gzip in the pipe.

Any suggestions on an existing executable to use as a model for "best
practices" are welcome, as are suggestions for the safest and most robust
techniques for identifying the portion of the WAL file which should be set
to zero.

Finally, I assume that I should put this on pgfoundry?

-Kevin

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

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Attached is a version as good as I know how to get it.
It works for us, so barring any problems as we use it, I'm done.

I finally got around to looking at this. Neither <err.h> nor err()
are portable (they're not in the Single Unix Spec, and they don't
work here...). The intent seems moderately clear, but please replace
those calls with something portable.

Otherwise, I think the only thing standing in the way of committing this
as a contrib module is that we'll need some user-facing documentation,
preferably in the form of an SGML file. (We'd also need a Makefile
of course, but that's pretty dang trivial. See pg_standby's files
if you need a sample to work from.)

regards, tom lane

On Sun, Sep 9, 2007 at 4:16 AM, apoc9009 <apoc9009@yahoo.de> wrote:

No! Actually I'm wearing my tin hat right now and I Never say Anything

about My Suspicions about 9/11 on Internet in fear of Echelon catching
and filing me.

---------------
Hannu

hmm, a little bit Para?

http://www.myvideo.de/watch/1776449

Ok, now your point of View its more clearly...

This is a total aside....

Seriously... the kid in the vid has _issues_!!! Is it the OP himself? I'd
love to see the subtitled edition of this, or if someone can translate it!!!

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
EnterpriseDB http://www.enterprisedb.com

singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device