Re: Postgres questions

On 24/05/2011 6:10 AM, Trenta sis wrote:

> - I need to connect from postgres to other database (linked server) no
> postgres (for example with jdbc or odbc). I have tried to work with
> dbi-link with sql, it seems to work but with poor performance and whit
> other database different mssql is not working. What possible options
> exsits with 8.3? and with other versions?

DBI-link is probably your best bet. Another possibility is to use an
in-database procedural language to talk to the other database - for
example, PL/perl via DBI::DBD or PL/Python via a PEP-249
(http://www.python.org/dev/peps/pep-0249/) database driver like pymssql.

Otherwise you can do the data sharing/sync/whatever via a client
application that has connections to PostgreSQL and to the other database
of interest. That's often a better choice for more complex jobs.

Perhaps it'd help if you explained why you need this and what you want
to accomplish with it?

> - This server has some critical applications and I need high
> availability, but I'm not sure about possible options for this versions
> or similar. I have thought about active/active, active/passive or
> active/read-only but I'm not sure what are real options, and what could
> be a possible environment for this situation

It depends a LOT on what your needs are, and what your budget is. You
have some basic questions to ask yourself, like:

- Do I need true HA with failover, or just to protect against data loss?

- Can I modify my apps to be aware of failover, or does failover have to
   be transparent?

- Do I need multi-site failover or is all access of interest within
   one site?

- 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?

Once you've worked out the answers to those kinds of questions, THEN you
can look at bucardo, slony-I, PostgreSQL 9.0 native replication, etc etc
as well as failover-control options like heartbeat and decide what might
be suitable for you.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/