Re: A few questions to real pgsql gurus

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