Re: Postgres questions - Mailing list pgsql-general
| From | Trenta sis |
|---|---|
| Subject | Re: Postgres questions |
| Date | |
| Msg-id | BANLkTimtpceKHxE+JBu9ACiCO73ETaCygw@mail.gmail.com Whole thread Raw |
| In response to | Re: Postgres questions (Craig Ringer <craig@postnewspapers.com.au>) |
| Responses |
Re: Postgres questions
(Craig Ringer <craig@postnewspapers.com.au>)
|
| List | pgsql-general |
Hi Craig,
First of all, thanks for you answer.
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.
About second question I'll try to answer your questions:
- Do I need true HA with failover, or just to protect against data loss?
It's a critical environment and if it is possible we need failover...
- Can I modify my apps to be aware of failover, or does failover have to
be transparent?
Failover must be transparent for applications.
- Do I need multi-site failover or is all access of interest within
one site?
With 2 site It could be a valid option, if one is master (RW), and secondary could be (RO) to use as datawarehouse (allow only selects, except in failover scenario: allow all)
- 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.
I have scheduled to create a second server but I'm not sure what options to choose to implement with our requirements...
Thanks
First of all, thanks for you answer.
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.
About second question I'll try to answer your questions:
- Do I need true HA with failover, or just to protect against data loss?
It's a critical environment and if it is possible we need failover...
- Can I modify my apps to be aware of failover, or does failover have to
be transparent?
Failover must be transparent for applications.
- Do I need multi-site failover or is all access of interest within
one site?
With 2 site It could be a valid option, if one is master (RW), and secondary could be (RO) to use as datawarehouse (allow only selects, except in failover scenario: allow all)
- 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.
I have scheduled to create a second server but I'm not sure what options to choose to implement with our requirements...
Thanks
2011/5/24 Craig Ringer <craig@postnewspapers.com.au>
On 24/05/2011 6:10 AM, Trenta sis wrote: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.- 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?
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?It depends a LOT on what your needs are, and what your budget is. You have some basic questions to ask yourself, like:- 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
- 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/
pgsql-general by date: