Re: 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.