Re: A few questions to real pgsql gurus - Mailing list pgsql-general

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