Re: Multiple servers with Postgresql

Oops, my apologies. I replied to you and not the list. I'm copying the
list on this one, John.

I don't think that you will be able to mix systems like you want to.
Replication between systems is only natively supported in 9.x of
PostgreSQL, though replication could be done in earlier versions with
an add-on. In addition, I don't know if 32 and 64 bit systems can
replicate. You may be able to create a backup of a 9.1 system on 8.4
by dump and restore, provided you don't implement any facilities that
are in 9.1 and not in 8.4, but you would have to write scripts to do
it.

The issue with replication that exists in 9.x is that if you switch to
the backup system and make updates to it, then you are faced with a
dump and restore to switch back again (in the majority of cases, as I
understand it).

Sorry, tuning is a black art to me, but I'm sure that you could find a
suitable PostgreSQL person in your location! I'm not a PostgreSQL
expert, though I've used PostgreSQL for years.

Cheers,

Cliff

On Sun, Sep 23, 2012 at 11:54 PM, John Coryat <coryat@gmail.com> wrote:
> Cliff,
>
> Thanks for the clarification.
>
> I'm looking to implement hot spare on 9.1 (64 bit) and warm spare on 8.4 (32
> bit).
>
> What I would like to know is the recommended procedure in bringing up warm
> spare for 8.4. I have two new servers with 9.1 that I'd like to implement
> hot spare and master (?) so once they are running well, I'll move to 9.1
> from 8.4 and deactivate the older 32 bit machines.
>
> Is that better?
>
> I am willing to hire an expert who can advise us on performance and other
> postgres fine tuning as we use this for mission critical operations.
>
> Thanks in advance.
>
> -John Coryat
>
>
> On Sat, Sep 22, 2012 at 7:02 PM, Cliff Pratt <enkiduonthenet@gmail.com>
> wrote:
>>
>> On Sat, Sep 22, 2012 at 7:50 AM, John Coryat <coryat@gmail.com> wrote:
>> >
>> > I have a number of web servers that access a server largely dedicated to
>> > just
>> > Postgres. There are over a million queries a day to this server. It's
>> > working well
>> > and doesn't seem to be bogged down.
>> >
>> > I would like to improve my server system by using a mirror or alternate
>> > machine
>> > for both backup and serving queries. The problem is the data has to be
>> > up to date
>> > on both machines. I've been using Postgres for many years but I've never
>> > had to
>> > do anything like this before.
>> >
>> > What are the best practices for doing this type of data and query
>> > sharing among
>> > separate servers?
>> >
>> > Thanks in advance for any assistance.
>> >
>> Without being condescending, you need to determine what you really
>> want to do. That may be what you are really asking. Do you want
>> reliability or performance or maximum up time. These things are not
>> mutually incompatible, but if you have a specific goal in mind you are
>> likely to succeed whereas if you don't you may end up just messing
>> around.
>>
>> That said, I would suggest that you don't load balance on the DB
>> server but on the front ends web servers.
>>
>> You could set up replication between two servers so that you have a
>> solution to which you can switch if the primary server goes down. But
>> if the database is any size you may have issues switching back.
>>
>> You could automate a switchover so that you have maximum uptime, but
>> again there may be issues switching back.
>>
>> Really, as I said at the start, you need to decide what your
>> requirements are so that people can advise the best solution.
>>
>> Cheers,
>>
>> Cliff
>
>