pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple servers with Postgresql
4+ messages / 3 participants
[nested] [flat]
* Multiple servers with Postgresql
@ 2012-09-21 19:50 John Coryat <coryat@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: John Coryat @ 2012-09-21 19:50 UTC (permalink / raw)
To: pgsql-admin
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.
-John Coryat
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Multiple servers with Postgresql
@ 2012-09-24 00:45 Cliff Pratt <enkiduonthenet@gmail.com>
parent: John Coryat <coryat@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Cliff Pratt @ 2012-09-24 00:45 UTC (permalink / raw)
To: John Coryat <coryat@gmail.com>; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Multiple servers with Postgresql
@ 2012-09-24 10:25 Pascal Heraud <heraud@tatm.com>
parent: Cliff Pratt <enkiduonthenet@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pascal Heraud @ 2012-09-24 10:25 UTC (permalink / raw)
To: pgsql-admin
Hello,
In this case, the use of slony1 is the recommended one.
Pascal.
On 09/24/12 02:45, Cliff Pratt wrote:
> 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
>>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Multiple servers with Postgresql
@ 2012-09-24 11:50 John Coryat <coryat@gmail.com>
parent: Cliff Pratt <enkiduonthenet@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: John Coryat @ 2012-09-24 11:50 UTC (permalink / raw)
To: pgsql-admin
I'm currently running 8.4 (32 bit) in my production environment and want to implement warm spare in that system. I have a test environment consisting of two 64bit CentOS6 servers running 9.1 that I want to use hot spare / master with and when that system is working well, migrate to those servers instead of the 32 bit 8.4 system.
I've been using Postgres for many years and have a basic understanding about tuning but I would like to hire (rent?) an expert to help fine tune the system, especially the test 9.1 environment so it can run more efficiently and deal with the spikes in demand we have.
Thanks.
-John Coryat
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-09-24 11:50 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-21 19:50 Multiple servers with Postgresql John Coryat <coryat@gmail.com>
2012-09-24 00:45 ` Cliff Pratt <enkiduonthenet@gmail.com>
2012-09-24 10:25 ` Pascal Heraud <heraud@tatm.com>
2012-09-24 11:50 ` John Coryat <coryat@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox