Обсуждение: [FEATURE REQUEST] Streaming Onlinebackup (Maybe OFFTOPIC)
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. My Idea:- 1 A Complete Database Backup from Scratch (its implemented right now)- 2 Online streaming Backup thadt, updatesmy Basebackup continously every Time Changes was made (the half functionality is allready implemented)- 3 Able to redirect the Online StreamingbackupFiles to an Remote Servermachine (FTP) (the ARCHIVE Param in postgresql.conf can do thadt allready but the Method is not 100% Continously, big Holes of Datalosses can occur)- 4 Version Management of Multiple Backuplines by Timestamp (is not yet implemented)- 5 Recovery Option inside the PSQL-Client, for doing the Desaster Recovery. (is not yet implemented) Benefitts: 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! ps: At EMCSoftware there is an Tool thadt has can do thadt for ORACLE and MSSQL but there is not Option for Postgres avaiable ); Sry for my bad english and i hope there is some one thadt can understand the Problem. Apoc
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
> 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 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
Ü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
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 <---
ChangedData /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 <---
ChangedData /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
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-2217:14 000000010000001800000045.gz 5104595 2007-08-22 17:14 000000010000001800000046.gz 3747524 2007-08-22 17:14000000010000001800000047.gz 3118762 2007-08-22 17:14 000000010000001800000048.gz 3342918 2007-08-22 17:14 000000010000001800000049.gz4600438 2007-08-22 17:14 00000001000000180000004A.gz 2715708 2007-08-22 17:15 00000001000000180000004B.gz2865803 2007-08-22 17:15 00000001000000180000004C.gz10111700 2007-08-22 18:00 00000001000000180000004D.gz115616302007-08-22 18:00 00000001000000180000004E.gz11619590 2007-08-22 18:00 00000001000000180000004F.gz7165231 2007-08-22 18:00 000000010000001800000050.gz 4012164 2007-08-22 18:00 000000010000001800000051.gz4590502 2007-08-22 18:00 000000010000001800000052.gz 3617203 2007-08-22 18:01 000000010000001800000053.gz3552210 2007-08-22 18:01 000000010000001800000054.gz 4006261 2007-08-22 18:01 000000010000001800000055.gz3538293 2007-08-22 18:01 000000010000001800000056.gz 4291776 2007-08-22 18:02 000000010000001800000057.gz4837268 2007-08-22 18:02 000000010000001800000058.gz 3948408 2007-08-22 19:02 000000010000001800000059.gz2714635 2007-08-22 20:02 00000001000000180000005A.gz 4989698 2007-08-22 21:02 00000001000000180000005B.gz5290729 2007-08-22 22:02 00000001000000180000005C.gz 5203306 2007-08-22 23:02 00000001000000180000005D.gz5101976 2007-08-23 00:02 00000001000000180000005E.gz 3745125 2007-08-23 01:02 00000001000000180000005F.gz3119218 2007-08-23 02:02 000000010000001800000060.gz 3340691 2007-08-23 03:02 000000010000001800000061.gz4599279 2007-08-23 04:02 000000010000001800000062.gz10110899 2007-08-23 05:02 000000010000001800000063.gz115558952007-08-23 06:02 000000010000001800000064.gz11587646 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/ > From each according to his ability. To each according to his ability to litigate.
Ü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] and [3]. :-) Regards Markus [1]: Postgres advocacy wiki: 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
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
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> instead of <http://www.postgres-r.org/documentation/overview>, etc. Rainer
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
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 dataloss 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]: "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:
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
hmm, a little bit Para?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
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