A few questions to real pgsql gurus

On 24 Feb 2003 at 5:53, mike McGire wrote:

Hi

We are working on a project and our client wants to use PostgreSQL as the
backend, since it will be a very mission critical 24x7 live environment, I
have to know a few things about postgresql before we can consider it.

1) Backups : Going through the documents I can see that there are no
incremental backups available in postgres yet, I may have overlooked it,
therefore, I would like to know:

a) Am I right on the incremental backup ?

Not exactly. There are replication solutions which would sync a WAL file
between two hosts. That is good enough incremental backup in 16MB chunks.
However it is all or none solution. You can not backup a single database this
way. Your entire installation will be backed up.

Google/list archives for links.

b) what would be the performance impact of taking frequent backups
( Lets say every 2-3 hours ).

Depends upon data size if you are going to use pg_dump. most important aspect I
can see is backup process chewing disk bandwidth which can be a eral choker if
you are working on data parallely and your database is quite large.

c) Is it possible to restore tables selectively from a backup.

If you dump them selectively, yes. Afterall it is just ascii dump of insert
statements.

d) Can we do a point in time restore from the backups.

Depends. If you have WAL files safe and postgresql data is safe, yes. But that
is not true PITR for many guys.

2) Failover :

a) is it possible to create a cluster of 2 (primary & secondary)
databases.

I believe so. But no links handy.

b) is it possible to configure an auto-failover to the secondary
database in case primary dies.

If you have heartbeat service installed and using async replication, like I
mentioned above, you should have a database which is current till latest WAL
segment. Otherwise you have to user a sync. replication which is costly in
performance I believe...

c) how reliable the replication is in postgresql, can a) & b) be
implemented using replication.

It works for many guys. A beta/pilot at your own site is recommended.

3) Functions & triggers : Our project would be heavily dependent on
functions and triggers :

a) I see postgresql supports many procedural languages, so what
should be the preferred language to be used for functions/
procedures ( i.e. is PL/PGSQL as fast as C is etc ).

Writng triggers in C would be fast for performance but may not be as fast for
development. Rule of thumb is PL/PgSQL is usually good enough..

HTH

Bye
Shridhar

--
design, v.: What you regret not doing later on.

mike McGire wrote:

Hi

We are working on a project and our client wants to use PostgreSQL as
the backend, since it will be a very mission critical 24x7 live
environment, I have to know a few things about postgresql before we can
consider it.

1) Backups : Going through the documents I can see that there are no
incremental backups available in postgres yet, I may have overlooked it,
therefore, I would like to know:

a) Am I right on the incremental backup ?

As true "incremental" backups, you're right.

b) what would be the performance impact of taking frequent backups
( Lets say every 2-3 hours ).

Normally not that much, depending on the size of the database. Pretty
much it's like 1 extra client process connecting to the database, doing
a query for all the data at that point in time, and writing it out to a
file on the way. Not a huge load causing thing.

c) Is it possible to restore tables selectively from a backup.

Yep.

d) Can we do a point in time restore from the backups.

Not a true Point in Time restore, no. It will be accurate to every
commited transaction for when the backup process was started.

2) Failover :

a) is it possible to create a cluster of 2 (primary & secondary)
databases.

Definitely. eRServer is a commercial PostgreSQL replication solution
that does master to multi slave replication.

http://www.digitaldistribution.com/erserver
http://www.erserver.com

It's used in replicating the .org and .info domain name registries,
amongst others. It's it not a WAL log file method, but a separate
application that tracks data changes in the master database and ensures
they are propagated to the slaves.

It works very well.

b) is it possible to configure an auto-failover to the secondary
database in case primary dies.

Definitely. See the above URLs for further information.

c) how reliable the replication is in postgresql, can a) & b) be
implemented using replication.

Very, very reliable. It is specifically designed for high-volume 24x7
environments.

Auto-failover is very crucial for this project, so I would like
to get as many reliable solutions for that as possible including
3rd party applications ( like NetApp or whatever ) if there are
any.

The maximum failover time for the Lanux enabled eRServer solution has
consistently been less than 20 seconds, which is pretty decent.

3) Functions & triggers : Our project would be heavily dependent on
functions and triggers :

a) I see postgresql supports many procedural languages, so what
should be the preferred language to be used for functions/
procedures ( i.e. is PL/PGSQL as fast as C is etc ).

If you're going for pure speed, C is your best bet. If you happen to
have some skilled Assembly language programmers around, even better.

Hope this is helpful.

Regards and best wishes,

Justin Clift

Thanks
Mike

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Dmitry Tkach <dmitry@openratings.com> writes:

- The database files sit on a NetApp box (this is a very reliable disk array with parity control etc...
plus, it has a 'snapshot' capability, that allows you to take a live snapshot of any directory in real time - that creates
a read-only copy of the content of that directory within a second or so).
- The snapshots are taken hourly by a cron job on the whole database (older snapshots get removed after a while), and the most recent snapshot
gets backed up to the tape every night.

Have you ever actually restored one of those snapshots? I don't believe
this will work, unless you shut down the database while taking the
snapshot. Or unless the snapshot is instantaneous across all files in
the $PGDATA directory tree --- but your "within a second or so" doesn't
sound real promising. If there's any time skew between copying
different files, you've got problems.

regards, tom lane

Dmitry Tkach wrote:

Tom Lane wrote:

<snip>

Have you ever actually restored one of those snapshots? I don't believe
this will work, unless you shut down the database while taking the
snapshot. Or unless the snapshot is instantaneous across all files in
the $PGDATA directory tree --- but your "within a second or so" doesn't
sound real promising. If there's any time skew between copying
different files, you've got problems.

Yes, it *is* instanteneous, that's the point!
The idea is, that it does not really copy anything at all - just freezes
all the stuff under that directory exactly the way it is, and then, if
somebody (like postmaster) is writing to that directory, it replaces the
particular block that's is neing modified, so that the snapshot contiues
to look at the old block, and the 'real thing' referes to the new one...

If it's of any help, recent versions of Solaris 8 and higher come with
this built in too.

Regards and best wishes,

Justin Clift

That 'under a second' time is what it needs to setup its bookeeping
recors, and it freezes all writes to the directory during that time...

Dima

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

Dmitry Tkach wrote:

If it's of any help, recent versions of Solaris 8 and higher come with
this built in too.

I know, but we don't have too much money to spend on Solaris, and are
running on Linux :-(
Besides, the NetApp also has that redundancy stuff - if one (or, I
believe, even up to three) drive fails, it is still able to recover...

Um, I'm not a 100% total fan of Solaris anymore, but it's probably worth
mentioning that Solaris 8 INTEL does all of this too. It has DiskSuite
as a free add-on that does many types of software RAID.

Solaris 9 SPARC/INTEL is supposed to have even better software RAID
capabilities available (similar to Veritas Volume Manager), but I
haven't had a chance to use it yet (downloading it now).

Regards and best wishes,

Justin Clift

Dima

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

And what about all the stuff in memory, pending transactions, etc?

2/24/2003 9:33:55 AM, Dmitry Tkach <dmitry@openratings.com> wrote:

Tom Lane wrote:

Dmitry Tkach <dmitry@openratings.com> writes:

- The database files sit on a NetApp box (this is a very reliable disk array with parity

control etc...

plus, it has a 'snapshot' capability, that allows you to take a live snapshot of any directory

in real time - that creates

a read-only copy of the content of that directory within a second or so).
- The snapshots are taken hourly by a cron job on the whole database (older snapshots get

removed after a while), and the most recent snapshot

Got a link to this netapp stuff?

2/24/2003 9:48:29 AM, Dmitry Tkach <dmitry@openratings.com> wrote:

On Tue, Feb 25, 2003 at 01:03:38AM +1030, Justin Clift wrote:

b) is it possible to configure an auto-failover to the secondary
database in case primary dies.

Definitely. See the above URLs for further information.

I have to say that we do not use it that way, and I cannot recommend
such a strategy. (We're the ones using it for .org and .info.)
Failing over is something of a pain in the neck, and it is not
100% reliable in cases where you have flakey hardware (as I learned
to my chagrin recently).

That said, the replication itself has not failed for us.

PostgreSQL, Inc. sells a version which is somehow linked to a Linux
system; I don't know very much about it, so it may work perfectly
well. But that is _not_ the system we are using.

There are some other replication systems, as well, but I have no
experience with them. For more information on replication, see Bruce
Momjian's recent presentation on it

http://candle.pha.pa.us/main/writings/pgsql/replication.pdf

or visit the replication gborg site

http://gborg.postgresql.org/project/pgreplication/projdisplay.php

We are very happy with PostgreSQL, and have consistently good
experience with its reliability and speed. I would strongly
recommend it, with one of the various replication systems.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

We have looked into a bunch of backup/replication solutions when switching our production site from informix to postgres, and
none of the ones we looked at was particularly attractive :-(

So, we ended up sticking with the following configuration:

- The database files sit on a NetApp box (this is a very reliable disk array with parity control etc...
plus, it has a 'snapshot' capability, that allows you to take a live snapshot of any directory in real time - that creates
a read-only copy of the content of that directory within a second or so).
- The snapshots are taken hourly by a cron job on the whole database (older snapshots get removed after a while), and the most recent snapshot
gets backed up to the tape every night.
- Both primary and fail-over servers look at the same copy of the database on the NetApp (only one server is up at any given moment, of course), thus
eliminating the need in replication.

This gives us the ability to do the 'point-in-time' restore from a snapshot if necessary, and no, we cannot restore a particular table
(cannot imagine a situation when we'd ever want to do that tough)...

The only potential problem we might have with this setup is with what happens if the NetApp box itself dies (we still have the tape backups, but
it would take a while to restore one of them back to disk - so we won't be able to have the immediate fail over), but we decided that we can
live with it, because we only have one NetApp anyway, so, even if we did have replication, we'd be replicating on the same device, and still have
a problem if it goes south.... The box is pretty reliable anyway, with a lot of redundancy, so, it is unlikely that it crashes so badly, that there
is no way to recover...

As for functions and triggers. we are doing everything in C - I personally find plpgsql not only slow, but also having very cryptic syntax,
and not very flexible (there is lots of stuff that you can do in C, that just cannot be done in plpgsql)... besides, I was just not too
happy about having to learn yet another language :-)

Dima

mike McGire wrote:

Dennis Gearon <gearond@cvc.net> writes:

And what about all the stuff in memory, pending transactions, etc?

If he's got an accurate snapshot of the WAL files, everything the
database has claimed to be committed will be committed. It's the same
scenario as a crash.

The trouble with most "I'll just take a tar dump/rsync copy/whatever
of the $PGDATA directory" backup solutions is that you don't get a copy
of the WAL files that's consistent with what you copy out of the other
subdirectories --- and any time skew there will burn you. See recent
reports of duplicate rows for an example. But if NetApp can implement
an instantaneous copy (and offhand it seems that they should be able to,
since they sit between PG and the disk) then it should work.

I'd still recommend some tests before trusting it ;-)

regards, tom lane

Tom Lane wrote:

Dmitry Tkach <dmitry@openratings.com> writes:

- The database files sit on a NetApp box (this is a very reliable disk array with parity control etc...
plus, it has a 'snapshot' capability, that allows you to take a live snapshot of any directory in real time - that creates
a read-only copy of the content of that directory within a second or so).
- The snapshots are taken hourly by a cron job on the whole database (older snapshots get removed after a while), and the most recent snapshot
gets backed up to the tape every night.

Have you ever actually restored one of those snapshots? I don't believe
this will work, unless you shut down the database while taking the
snapshot. Or unless the snapshot is instantaneous across all files in
the $PGDATA directory tree --- but your "within a second or so" doesn't
sound real promising. If there's any time skew between copying
different files, you've got problems.

Yes, it *is* instanteneous, that's the point!
The idea is, that it does not really copy anything at all - just freezes
all the stuff under that directory exactly the way it is, and then, if
somebody (like postmaster) is writing to that directory, it replaces the
particular block that's is neing modified, so that the snapshot contiues
to look at the old block, and the 'real thing' referes to the new one...

That 'under a second' time is what it needs to setup its bookeeping
recors, and it freezes all writes to the directory during that time...

Dima

If it's of any help, recent versions of Solaris 8 and higher come with
this built in too.

I know, but we don't have too much money to spend on Solaris, and are
running on Linux :-(
Besides, the NetApp also has that redundancy stuff - if one (or, I
believe, even up to three) drive fails, it is still able to recover...

Dima

Dennis Gearon wrote:

And what about all the stuff in memory, pending transactions, etc?

Well... all of that will be gone, of course...
If you ever need to restore from that snapshot, it will look like the
crash happened a few milliseconds before it actually did :-)

Dima

Dennis Gearon wrote:

Got a link to this netapp stuff?

Sure.

http://www.netapp.com/

Dima

--On Monday, February 24, 2003 12:28:03 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

Dennis Gearon <gearond@cvc.net> writes:

And what about all the stuff in memory, pending transactions, etc?

If he's got an accurate snapshot of the WAL files, everything the
database has claimed to be committed will be committed. It's the same
scenario as a crash.

The trouble with most "I'll just take a tar dump/rsync copy/whatever
of the $PGDATA directory" backup solutions is that you don't get a copy
of the WAL files that's consistent with what you copy out of the other
subdirectories --- and any time skew there will burn you. See recent
reports of duplicate rows for an example. But if NetApp can implement
an instantaneous copy (and offhand it seems that they should be able to,
since they sit between PG and the disk) then it should work.

I'd still recommend some tests before trusting it ;-)

the netapp facility is called SnapShot.

What happens is that the original blocks of the file are write protected,
and any changes are put elsewhere. (I don't recall whether it's the new or
the old blocks that get saved, but it doesn't matter, the effect is the
same).

The snapshot stuff in Veritas' VxFS is very similar.

It makes a consistent view of the filesystem available for backup.

LER

regards, tom lane

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

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

On Mon, 24 Feb 2003, Larry Rosenman wrote:

--On Monday, February 24, 2003 12:28:03 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

Dennis Gearon <gearond@cvc.net> writes:

And what about all the stuff in memory, pending transactions, etc?

If he's got an accurate snapshot of the WAL files, everything the
database has claimed to be committed will be committed. It's the same
scenario as a crash.

The trouble with most "I'll just take a tar dump/rsync copy/whatever
of the $PGDATA directory" backup solutions is that you don't get a copy
of the WAL files that's consistent with what you copy out of the other
subdirectories --- and any time skew there will burn you. See recent
reports of duplicate rows for an example. But if NetApp can implement
an instantaneous copy (and offhand it seems that they should be able to,
since they sit between PG and the disk) then it should work.

I'd still recommend some tests before trusting it ;-)

the netapp facility is called SnapShot.

What happens is that the original blocks of the file are write protected,
and any changes are put elsewhere. (I don't recall whether it's the new or
the old blocks that get saved, but it doesn't matter, the effect is the
same).

The snapshot stuff in Veritas' VxFS is very similar.

It makes a consistent view of the filesystem available for backup.

Sounds like they're using MVCC for filesystems. :-)

On Mon, 24 Feb 2003, Andrew Sullivan wrote:

On Tue, Feb 25, 2003 at 01:03:38AM +1030, Justin Clift wrote:

b) is it possible to configure an auto-failover to the secondary
database in case primary dies.

Definitely. See the above URLs for further information.

I have to say that we do not use it that way, and I cannot recommend
such a strategy. (We're the ones using it for .org and .info.)
Failing over is something of a pain in the neck, and it is not
100% reliable in cases where you have flakey hardware (as I learned
to my chagrin recently).

That said, the replication itself has not failed for us.

PostgreSQL, Inc. sells a version which is somehow linked to a Linux
system; I don't know very much about it, so it may work perfectly
well. But that is _not_ the system we are using.

The newest version uses an Lanux (not Linux) layer ... its a set of
monitoring tools that are used to detect failure of the primary server,
and auto-switch over to the secondary ... we're workign right now on
extending that further to include fail-over mid-transaction, so that the
failover itself is more transparent to the end user ...

* Larry Rosenman (ler@lerctr.org) [030224 13:48]:

--On Monday, February 24, 2003 12:28:03 -0500 Tom Lane <tgl@sss.pgh.pa.us>
wrote:

If he's got an accurate snapshot of the WAL files, everything the
database has claimed to be committed will be committed. It's the same
scenario as a crash.

the netapp facility is called SnapShot.

What happens is that the original blocks of the file are write protected,
and any changes are put elsewhere. (I don't recall whether it's the new or
the old blocks that get saved, but it doesn't matter, the effect is the
same).

The snapshot stuff in Veritas' VxFS is very similar.

It makes a consistent view of the filesystem available for backup.

And just for completeness, this is also available in Linux via LVM
see http://tldp.org/HOWTO/LVM-HOWTO/index.html - it works great out of
the box on RH8 - we have a 2TB NFS/SMB server built on it.

-Brad

On Mon, Feb 24, 2003 at 03:41:34PM -0400, Marc G. Fournier wrote:

The newest version uses an Lanux (not Linux) layer ... its a set of

Sorry for the typo; "Linux" is just in my fingers, and "Lanux" not so
much.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110