Re: Postgres questions

On 05/24/2011 05:15 PM, Trenta sis wrote:

> That I need is to connect to another databse (Cache Intersystems) to use
> select from Postgres. I have tried to configure dbilink but is not
> working with this database, with sql server seems to work but with poor
> peroformance.

You really need to be specific about things like "not working" and "poor
performance". Nobody can help you if that's all you say, you need to
give error messages, versions, commands, configurations, etc. You
haven't shown any queries, any EXPLAIN ANALYZE output, any schema, any
timing data, or pretty much anything else that'd allow anyone to help
you. It might be worth remedying that. See:

In addition to that, some obvious things to test and report back on include:

Can you connect to Cache from a stand-alone Perl script using Perl DBI?

If so, can you connect to Cache from a PL/Perlu script using Perl DBI?

Is MS-SQL performance OK when connecting from a stand-alone Perl script
outside the database using Perl DBI?

Is MS-SQL performance OK when connecting from a custom test plperlu
script using Perl DBI?

What do you expect performance to be like? Why? What kind of queries are
you executing? Which ones perform badly?

> /- What kind of guarantees do I need about data loss windows at failover
> time? Can I afford to lose the last <x> transactions / seconds worth
> of transactions? Or must absolutely every transaction be retained
> at all costs?/
> A windows data loss could be some secodn/minutes but if is not very
> complex no data loss will be excellent.

It sounds like your requirements can probably be satisfied by PostgreSQL
9.0's built-in replication combined with the use of repmgr and heartbeat.

For anything like this, though, I STRONGLY suggest that you hire a
consultant who has done what you need before and has learned the traps
and pitfalls. See:

http://www.postgresql.org/support/professional_support

I don't do PostgreSQL support commercially and am not experienced with
failover/HA setups, so I can't help you much there and suggest you find
someone who _really_ knows what they are doing. Getting HA right is *hard*.

--
Craig Ringer