Type of application that use PostgreSQL

[My Internet]

Hello,

I am in the process to define if our product can use PostgreSQL.

Do you know what type of application use PostgreSQL, and also what is the
size of the database for these projects?

Our application has a table with more than 300000 rows. There are complexe
query with many joins. And we must respect some time constraints. Also the
application is running 24 hours per day, so the product must be stable, with
good recovery in case of problems.

Thank you,
Jean-Marc

__________________________________________________ _______________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Richard Huxton]

On Thursday 02 October 2003 09:10, My Internet wrote:

Hello,

I am in the process to define if our product can use PostgreSQL.

Do you know what type of application use PostgreSQL, and also what is the
size of the database for these projects?
Everything from small-business apps (which I use it for), web-based apps,
large-scale scientific data processing, GIS support, substantial parts of the
DNS system...
Our application has a table with more than 300000 rows. There are complexe
query with many joins. And we must respect some time constraints. Also the
application is running 24 hours per day, so the product must be stable,
with good recovery in case of problems.

People have been saying it's inconvenient to upgrade between versions when
dealing with 100GB+ databases, so if 3million rows is your largest table,
that might put things in perspective.

The query planner generally makes a good job optimising. Three things you
probably want to be aware of though:
- PG is very strict on types - if you have an int2 field compared to an int4
value, you will want to cast the int4 to make sure an index is used.
- Use of the JOIN keyword forces joins in the order you specify them - I
believe this is configurable in 7.4
- Use of IN (...) with many values can be slow. Recommended procedure is to
rewrite using EXISTS, although again I believe the situation is much improved
in 7.4
- count(*) isn't optimised - it has to scan the table.
- you will need to tune the values in the configuration file, they are set
quite small to start with.

Stability is good. If you're feeling cautious, you could use 7.3.4 rather than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to get
a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options.

Some of its high points I would say are:
- good proedural language support
- many client interfaces (C,C++,Perl,Python,PHP,JDBC,ODBC...)
- good compliance with SQL standards
- very helpful mailing lists

In short, I'd recommend spending a couple of days installing and running some
tests - the beauty of open-source is that it costs you nothing to try it out.
--
Richard Huxton
Archonet Ltd

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

[Network Administrator]

Quoting Richard Huxton <de*@archonet.com>:

[stuff deleted] Stability is good. If you're feeling cautious, you could use 7.3.4 rather
than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to get

a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options. [stuff deleted] --
Richard Huxton
Archonet Ltd

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

Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the all
of the beta3 docs yet.
--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

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

[Bruno Wolff III]

On Thu, Oct 02, 2003 at 14:55:04 -0400,
Network Administrator <ne******@vcsn.com> wrote:

Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the all
of the beta3 docs yet.

No. pg_dump still gives you a consistant dump.

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

On Thursday 02 October 2003 19:55, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:

[stuff deleted]

Stability is good. If you're feeling cautious, you could use 7.3.4 rather
than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to
get

a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options.

Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the
all of the beta3 docs yet.

As Bruno says - no (and I can't imagine why anyone would).

I was merely suggesting the fact that the 7.3 series is at it's fourth minor
revision means it's received a lot more testing, whereas 7.4.0 still has the
paint wet on it.

--
Richard Huxton
Archonet Ltd

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

[Peter Childs]

On Fri, 3 Oct 2003, Richard Huxton wrote:

On Thursday 02 October 2003 19:55, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:

[stuff deleted]

Stability is good. If you're feeling cautious, you could use 7.3.4 rather
than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to
get

a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options.

Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the
all of the beta3 docs yet.

As Bruno says - no (and I can't imagine why anyone would).

I was merely suggesting the fact that the 7.3 series is at it's fourth minor
revision means it's received a lot more testing, whereas 7.4.0 still has the
paint wet on it.

Hot backup? Postgres 7.3 can't really do hot-backup! The only way
to backup the database is to dump the entire thing. Two minites later this
backup is only news and not exactly hot. Having to shut your database down
to back it up would be down right nasty! But Since we don't yet have an
update log. Even a dump will not get you back to the good point before
your disk died.
Mind you the truth is the downdate log should work for most cases.
So take regualar backups... and use raid. I mean how often does an entire
raid array fail at once?
I would love to see an Update Log on postgres. That and true
replications (dual master) are the two biggest missing features in
Postgres. If we had them we could run circles round mysql and oracle as
well.
Its not as if an update log is a new idea. Our old system (that we
are replacing with postgres) has an update log. So you can always get from
the last backup to now using it. (Its a TPS Bull System) The update logs
last about 2 weeks if we forget to backup! (Since we have to shutdown to
backup we only do a full backup once a week) but apparently we have never
had to use the update logs in 9 years......
Maybe it would be a good idea to start a mailinglist marked
pgsql-backup since this subject comes up so often.

Peter Childs
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Shridhar Daithankar]

Peter Childs wrote:

Hot backup? Postgres 7.3 can't really do hot-backup! The only way
to backup the database is to dump the entire thing. Two minites later this
backup is only news and not exactly hot. Having to shut your database down
to back it up would be down right nasty! But Since we don't yet have an
update log. Even a dump will not get you back to the good point before
your disk died.
Mind you the truth is the downdate log should work for most cases.
So take regualar backups... and use raid. I mean how often does an entire
raid array fail at once?
There was a clever solution posted here for replicating database in near real
time. Use RAID for data directory. For backup break the RAID and pick up the
data image from broken part. That was clever one.
I would love to see an Update Log on postgres. That and true
replications (dual master) are the two biggest missing features in
Postgres. If we had them we could run circles round mysql and oracle as
well.

Postgresql do have update logs in form of WAL. However it can not do PITR with
log archiving if a node in cluster goes down for extended period. WAL does not
handle these situations in current design. Precisely, PITR is the feature that
implements this functonality.

Furthermore, mysql has replication is completely unrelated issue. Mysql didn't
have transactions so implementing replication didn't have so many constraints.
Postgresql has transactions for ages and it's replication has to support them
fully. That's what causing delays in true postgresql replication.

Design a mysql replicated cluster in a heavy concurrent update database and have
one of the node fail it. Let's see how mysql rolls back. ( FWIW, mysql rollback
time is proprotional to data in a transaction. So rolling back a 100MB
transaction isn't exactly so fast)
Shridhar

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

[Mike Mascari]

Peter Childs wrote:

Hot backup? Postgres 7.3 can't really do hot-backup! The only way
to backup the database is to dump the entire thing. Two minites later this
backup is only news and not exactly hot. Having to shut your database down
to back it up would be down right nasty! But Since we don't yet have an
update log. Even a dump will not get you back to the good point before
your disk died.
Isn't the definition of "hot-backup" the ability to perform a backup
while the database is still on-line engaging in transactions? I think
you're substituting "hot-backup" for point-in-time recovery...
Mind you the truth is the downdate log should work for most cases.
So take regualar backups... and use raid. I mean how often does an entire
raid array fail at once?
True...and assuming you're not running just RAID 0. But, as you say,
PITR could recover from such nasty things as an unqualified DELETE or
UPDATE. Disaster recovery from PITR logs being archived over a WAN or
a replicated slave would also be nice, in the event hurricane "Foo"
wipes out the data center. I haven't played around with the
replication options yet, so that might be less of a problem. But
without PITR, there's a window of risk in which a programming error or
a keying error could set you back to the last backup, whenever that was...
I would love to see an Update Log on postgres. That and true
replications (dual master) are the two biggest missing features in
Postgres. If we had them we could run circles round mysql and oracle as
well.

Amen.

Mike Mascari
ma*****@mascari.com

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Ron Johnson]

On Fri, 2003-10-03 at 04:26, Shridhar Daithankar wrote:

Peter Childs wrote: [snip] Postgresql do have update logs in form of WAL. However it can not do PITR with
log archiving if a node in cluster goes down for extended period. WAL does not
handle these situations in current design. Precisely, PITR is the feature that
implements this functonality.

For those not in the know, PITR is Point In Time Recovery.

And yes, that capability has been around for 20+ years on mainframes
and minicomputers, but they were/are funded a lot higher than the
PostgreSQL volunteers are!

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Python is executable pseudocode; Perl is executable line noise"
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Peter Childs]

On Fri, 3 Oct 2003, Ron Johnson wrote:

On Fri, 2003-10-03 at 04:26, Shridhar Daithankar wrote:

Peter Childs wrote: [snip]

Postgresql do have update logs in form of WAL. However it can not do PITR with
log archiving if a node in cluster goes down for extended period. WAL does not
handle these situations in current design. Precisely, PITR is the feature that
implements this functonality.

No it does not. WAL is Down-Date Logs not update logs. WAL will
enable you to rewind to the beginning of all currently running
transactions after a crash. Ie roll-back not roll-forward.

For those not in the know, PITR is Point In Time Recovery.

And yes, that capability has been around for 20+ years on mainframes
and minicomputers, but they were/a`re funded a lot higher than the
PostgreSQL volunteers are!

It was planned for 7.4 so when is it going to happern now it has
missed 7.4?

Peter Childs
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Shridhar Daithankar]

Peter Childs wrote:

On Fri, 3 Oct 2003, Ron Johnson wrote:

On Fri, 2003-10-03 at 04:26, Shridhar Daithankar wrote:

Peter Childs wrote:

[snip]

Postgresql do have update logs in form of WAL. However it can not do PITR with
log archiving if a node in cluster goes down for extended period. WAL does not
handle these situations in current design. Precisely, PITR is the feature that
implements this functonality. No it does not. WAL is Down-Date Logs not update logs. WAL will
enable you to rewind to the beginning of all currently running
transactions after a crash. Ie roll-back not roll-forward.

Right. But if WAL files from one host are dropped to WAL log dir. of another
host, it will play them provided they have identical structure. IIRC there are
some replication solution which works on this basis.For those not in the know, PITR is Point In Time Recovery.

And yes, that capability has been around for 20+ years on mainframes
and minicomputers, but they were/a`re funded a lot higher than the
PostgreSQL volunteers are!

It was planned for 7.4 so when is it going to happern now it has
missed 7.4?

7.5. Lots of things have missed 7.4 boat. PITR, nested transactions, native
windows ports etc. So hopefully 7.5 would have very small development cycle as
most of the features already have work going on.

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

[Tom Lane]

Shridhar Daithankar <sh*****************@persistent.co.in> writes:

Peter Childs wrote:

Postgresql do have update logs in form of WAL.

No it does not. WAL is Down-Date Logs not update logs. WAL will
enable you to rewind to the beginning of all currently running
transactions after a crash. Ie roll-back not roll-forward.

Right.

Wrong. Peter, don't state something so authoritatively when you
obviously haven't looked at the code. Postgres does not do roll-back,
ever. (We don't need it because of MVCC.) We use WAL for roll
*forward* from the last checkpoint after a crash. Any updates that
didn't make it to disk before the crash are restored from WAL.

All that we basically need for PITR is to provide management code that
lets old WAL segments get archived off to tape (or wherever) rather than
deleted, plus some kind of control that lets the roll-forward process be
stopped at the desired point-in-time rather than necessarily running to
the end of the available WAL data. This isn't a trivial amount of code,
but there's no great conceptual difficulty either.

regards, tom lane

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Network Administrator]

Quoting Richard Huxton <de*@archonet.com>:

On Thursday 02 October 2003 19:55, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:

[stuff deleted]

Stability is good. If you're feeling cautious, you could use 7.3.4 rather than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to
get

a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options.

Is the ability to do "hot-backups" removed from 7.4.x? I haven't read the
all of the beta3 docs yet.

As Bruno says - no (and I can't imagine why anyone would).

I was merely suggesting the fact that the 7.3 series is at it's fourth minor

revision means it's received a lot more testing, whereas 7.4.0 still has the

paint wet on it.

--
Richard Huxton
Archonet Ltd

I agree and I figured that was the case ("hot backups" is one of the reasons I
choose Pg over some other DBs) but I wanted to be clear- more so for the archives.

The other thing is that I suspect there might be some confusion as to the
difference between "hot backups" (dump of data does not require DB to be "down")
and "online backups" (dump of data is not needed because on the disk state of
the DB is current to some "recent" time). To be perfectly honest, I've only had
to use a pg_dump file once in well over 5 years (and that was recent- it was
actually posted here) in conditions where the system with NOT proper shutdown
(at the OS or application layer). PG simply does its "housekeeping" when the
backend started up. Of course, running the VACUUM ANALYSE help confirm more
that was indeed true. I would think that is some sort of luck because as far as
I know Pg is not able to do online backups.

In any event, "hot backups" is very important to have and use for more and more
people these days. I had a conversation with a Progress guy yesterday who I
know was grimacing when I told him I didn't need to take Pg down for backup...

Oh what a feeling :)
--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

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

[Ron Johnson]

On Fri, 2003-10-03 at 13:32, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:

On Thursday 02 October 2003 19:55, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:
> [stuff deleted]

[snip] The other thing is that I suspect there might be some confusion as to the
difference between "hot backups" (dump of data does not require DB to be "down")
and "online backups" (dump of data is not needed because on the disk state of
the DB is current to some "recent" time). To be perfectly honest, I've only had

Huh? This is the 1st time that I've ever seen mention that there
is a distinction between "hot" and "on-line" backup.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Fair is where you take your cows to be judged."
Unknown
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Ron Johnson]

On Fri, 2003-10-03 at 09:32, Tom Lane wrote:

Shridhar Daithankar <sh*****************@persistent.co.in> writes:

Peter Childs wrote:

[snip] All that we basically need for PITR is to provide management code that
lets old WAL segments get archived off to tape (or wherever) rather than
deleted, plus some kind of control that lets the roll-forward process be
stopped at the desired point-in-time rather than necessarily running to
the end of the available WAL data. This isn't a trivial amount of code,
but there's no great conceptual difficulty either.

Hope everybody realizes that the amount of WALs will get very big
on active-update systems...

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

Great Inventors of our time:
Al Gore -> Internet
Sun Microsystems -> Clusters
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Network Administrator]

Quoting Ron Johnson <ro***********@cox.net>:

On Fri, 2003-10-03 at 13:32, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:

On Thursday 02 October 2003 19:55, Network Administrator wrote:
> Quoting Richard Huxton <de*@archonet.com>:
> > [stuff deleted]

[snip]

The other thing is that I suspect there might be some confusion as to the
difference between "hot backups" (dump of data does not require DB to be

"down")

and "online backups" (dump of data is not needed because on the disk state

of

the DB is current to some "recent" time). To be perfectly honest, I've

only had

Huh? This is the 1st time that I've ever seen mention that there
is a distinction between "hot" and "on-line" backup.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Fair is where you take your cows to be judged."
Unknown
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

I might be splitting hairs but I have had this conversation elsewhere. It was
quiet awhile ago and I might not be using the right term for "online". Using
my terminalogy though, doing a "hot backup" gives you data current to the time
of that backup. Doing an "online backup" would give you data current to the
last time everthing was sync'd to disk.

I think I had this discussion when I was on a developers list of BeOS during
discussion of what are commonly called journaling filesystems (e.g. Ext3, JFS,
ReiserFS, etc in the *nix world). Databases were talked about since at the time
that was really the only reference for how to even begin implementing.

Actually one of the tests I'm going to be doing when the new Linux 2.6 distos
start coming out is running Pg with an ext3 and reiser filesystems and literally
pulling the plug to see what happens.

--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Ron Johnson]

On Fri, 2003-10-03 at 14:37, Network Administrator wrote:

Quoting Ron Johnson <ro***********@cox.net>:

On Fri, 2003-10-03 at 13:32, Network Administrator wrote:

Quoting Richard Huxton <de*@archonet.com>:

> On Thursday 02 October 2003 19:55, Network Administrator wrote:
> > Quoting Richard Huxton <de*@archonet.com>:
> > > [stuff deleted] [snip]

The other thing is that I suspect there might be some confusion as to the
difference between "hot backups" (dump of data does not require DB to be

"down")

and "online backups" (dump of data is not needed because on the disk state

of

the DB is current to some "recent" time). To be perfectly honest, I've

only had

Huh? This is the 1st time that I've ever seen mention that there
is a distinction between "hot" and "on-line" backup.

[snip garbage]
I might be splitting hairs but I have had this conversation elsewhere. It was
quiet awhile ago and I might not be using the right term for "online". Using
my terminalogy though, doing a "hot backup" gives you data current to the time
of that backup. Doing an "online backup" would give you data current to the
last time everthing was sync'd to disk.

What the heck kind of DBMS doesn't make sure that the on-disk data
is current? In PG, that's "fsync=on", and turning it off is always
strongly discouraged.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Whatever may be the moral ambiguities of the so-called demoratic
nations and however serious may be their failure to conform
perfectly to their democratic ideals, it is sheer moral
perversity to equate the inconsistencies of a democratic
civilization with the brutalities which modern tyrannical states
practice."
Reinhold Nieburhr, ca. 1940
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Network Administrator]

Quoting Ron Johnson <ro***********@cox.net>:

On Fri, 2003-10-03 at 14:37, Network Administrator wrote:

Quoting Ron Johnson <ro***********@cox.net>:

On Fri, 2003-10-03 at 13:32, Network Administrator wrote:
> Quoting Richard Huxton <de*@archonet.com>:
>
> > On Thursday 02 October 2003 19:55, Network Administrator wrote:
> > > Quoting Richard Huxton <de*@archonet.com>:
> > > > [stuff deleted]
[snip]
> The other thing is that I suspect there might be some confusion as to the > difference between "hot backups" (dump of data does not require DB to be "down")
> and "online backups" (dump of data is not needed because on the disk state of
> the DB is current to some "recent" time). To be perfectly honest, I've only had

Huh? This is the 1st time that I've ever seen mention that there
is a distinction between "hot" and "on-line" backup.

[snip garbage]

I might be splitting hairs but I have had this conversation elsewhere. It

was

quiet awhile ago and I might not be using the right term for "online".

Using

my terminalogy though, doing a "hot backup" gives you data current to the

time

of that backup. Doing an "online backup" would give you data current to

the

last time everthing was sync'd to disk.

What the heck kind of DBMS doesn't make sure that the on-disk data
is current? In PG, that's "fsync=on", and turning it off is always
strongly discouraged.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

"Whatever may be the moral ambiguities of the so-called demoratic
nations and however serious may be their failure to conform
perfectly to their democratic ideals, it is sheer moral
perversity to equate the inconsistencies of a democratic
civilization with the brutalities which modern tyrannical states
practice."
Reinhold Nieburhr, ca. 1940
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Well, like I said this was years ago at this point so you really can't compare
now to then. I lived through that so I don't equate the terms. As for
"strongly discouraged", the docs don't read that way. The implications of both
fsync being "true" or "false". Either way, its academic now.
--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Shridhar Daithankar]

Ron Johnson wrote:

All that we basically need for PITR is to provide management code that
lets old WAL segments get archived off to tape (or wherever) rather than
deleted, plus some kind of control that lets the roll-forward process be
stopped at the desired point-in-time rather than necessarily running to
the end of the available WAL data. This isn't a trivial amount of code,
but there's no great conceptual difficulty either.

Hope everybody realizes that the amount of WALs will get very big
on active-update systems...

Of course they will be recycled in some point of time or other. And even if
postgresql would provide PITR abilities, that would be nearly useless if WAL is
recycled.. Its a space/time tradeoff issue..

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

[Ron Johnson]

On Mon, 2003-10-06 at 01:47, Shridhar Daithankar wrote:

Ron Johnson wrote:

All that we basically need for PITR is to provide management code that
lets old WAL segments get archived off to tape (or wherever) rather than
deleted, plus some kind of control that lets the roll-forward process be
stopped at the desired point-in-time rather than necessarily running to
the end of the available WAL data. This isn't a trivial amount of code,
but there's no great conceptual difficulty either.

Hope everybody realizes that the amount of WALs will get very big
on active-update systems...

Of course they will be recycled in some point of time or other. And even if

???? Of course they'll get recycled, after you dump them, prior
to the nightly pg_dump.
postgresql would provide PITR abilities, that would be nearly useless if WAL is
recycled.. Its a space/time tradeoff issue..

Again, ?????. Typically (i.e., on DBMSs that currently have PITR,
it's possible to do a "pg_dump" on Sunday, and *only* do "WAL dumps"
each subsequent night, and be able to restore the DB to the point
of failure by doing a "pg_restore" and then applying X number of
"WAL dumps" in sequence.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA

YODA: Code! Yes. A programmer's strength flows from code
maintainability. But beware of Perl. Terse syntax... more than
one way to do it...default variables. The dark side of code
maintainability are they. Easily they flow, quick to join you
when code you write. If once you start down the dark path,
forever will it dominate your destiny, consume you it will.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Shridhar Daithankar]

Ron Johnson wrote:

On Mon, 2003-10-06 at 01:47, Shridhar Daithankar wrote:

Ron Johnson wrote:

Hope everybody realizes that the amount of WALs will get very big
on active-update systems...

Of course they will be recycled in some point of time or other. And even if

???? Of course they'll get recycled, after you dump them, prior
to the nightly pg_dump.

The WAL under PITR will not swell till it fills the partition. In all
probability there will be(should be) a parameter to control how much space it
can occupy at the most. Otherwise it simply does not make sense.

postgresql would provide PITR abilities, that would be nearly useless if WAL is
recycled.. Its a space/time tradeoff issue..

Again, ?????. Typically (i.e., on DBMSs that currently have PITR,
it's possible to do a "pg_dump" on Sunday, and *only* do "WAL dumps"
each subsequent night, and be able to restore the DB to the point
of failure by doing a "pg_restore" and then applying X number of
"WAL dumps" in sequence.

Yes. But when PITR realises, there will be/should be a command/switch to pg_dump
to back up a WAL segment, so that the particular WAL segment could be recycled.

No matter what, WAL remains a resource that should be used sparingly.

PITR is not yet visible as yet. When the discussion starts for providing front
end to PITR, such facilities are going to be suggested and are essential IMO..

Just a thought..

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