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