Re: A few questions to real pgsql gurus

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:
> 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 ?
> b) what would be the performance impact of taking frequent backups
> ( Lets say every 2-3 hours ).
> c) Is it possible to restore tables selectively from a backup.
> d) Can we do a point in time restore from the backups.
>
> 2) Failover :
>
> a) is it possible to create a cluster of 2 (primary & secondary)
> databases.
> b) is it possible to configure an auto-failover to the secondary
> database in case primary dies.
> c) how reliable the replication is in postgresql, can a) & b) be
> implemented using replication.
>
> 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.
>
> 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 ).
>
> Thanks
> Mike
>
>
>
>
> _________________________________________________________________
> Protect your PC - get McAfee.com VirusScan Online
> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster