Обсуждение: Replication Across Two Servers?

We had a 8.4.8 production server of PostgreSQL on a Dell blade server
which ran for 3 years fine. The server housed all our database needs
perfectly but sadly the entire machine died. The drives were dead and
the motherboard was fried but we did have daily full backups of the
entire machine. Today I received our new blade servers which will run
VMware & I get to create two new PostgreSQL servers. I wanted to make
a master database server and a slave in case the master dies. My
question is does PostgreSQL 8.4 or 9.1support synchronization between
two physical machines over Ethernet? I've never replicated any kind of
database before so I don't know if that's possible and the more I
search this on my own, the more confused I am. It appears in
PostgreSQL, the word "replication" has several different meanings.

If you had to stand up two individual Debian Linux servers running a
specific version of PostgreSQL, could / would you be able to have the
master also synchronize all data to a slave server?

Thanks for any info!

Carlos,

Streaming replication was introduced in PostgreSQL 9.0 and should do
what you want.

http://wiki.postgresql.org/wiki/Streaming_Replication


On 11/04/2011 11:47 AM, Carlos Mennens wrote:
> We had a 8.4.8 production server of PostgreSQL on a Dell blade server
> which ran for 3 years fine. The server housed all our database needs
> perfectly but sadly the entire machine died. The drives were dead and
> the motherboard was fried but we did have daily full backups of the
> entire machine. Today I received our new blade servers which will run
> VMware&  I get to create two new PostgreSQL servers. I wanted to make
> a master database server and a slave in case the master dies. My
> question is does PostgreSQL 8.4 or 9.1support synchronization between
> two physical machines over Ethernet? I've never replicated any kind of
> database before so I don't know if that's possible and the more I
> search this on my own, the more confused I am. It appears in
> PostgreSQL, the word "replication" has several different meanings.
>
> If you had to stand up two individual Debian Linux servers running a
> specific version of PostgreSQL, could / would you be able to have the
> master also synchronize all data to a slave server?
>
> Thanks for any info!
>

On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps <bphelps@gls.com> wrote:
> Carlos,
>
> Streaming replication was introduced in PostgreSQL 9.0 and should do what
> you want.
>
> http://wiki.postgresql.org/wiki/Streaming_Replication

Oh great! I didn't see that in the 8.4 manual since that is what
Debian 6 has as the most stable version in it's package manager.
Anyone know of a stable Linux distribution that offers 9.0+? I know
Debian Wheezy (testing) has 9.1 but sadly it's testing and not
recommended for production utilization. RHEL is years behind as far as
packages go which makes them stable to an annoying degree.

On Fri, Nov 4, 2011 at 9:59 AM, Carlos Mennens <carlos.mennens@gmail.com> wrote:
>
> On Fri, Nov 4, 2011 at 11:56 AM, Prashant Bharucha <prashantbharucha@yahoo.ca> wrote:
>>
>> Hi Carlos
>>
>> Use Slony "master to multiple slaves" replication system for PostgreSQL supporting cascading (e.g. - a node can feed
anothernode which feeds another node...) and failover. 
>> http://slony.info/
>>
>
> I'm not sure I see the point of using a third party application to do something PostgreSQL can do natively. Am I
missingsomething here? 

Whether it's "third party" is immaterial really, the real issue is
what are your requirements and which method best meets those
requirements.  For certain more complex replication setups, slony is a
better method.  For instance you can create interesting indexes on a
slony slave that are independent of the master, or create views,
materialized or otherwise on a reporting server and so on.  While
streaming replication is easier to setup and maintain, and generally a
bit  more efficient, it's also got a more limited scope of operation.

Also, if you want to run 8.4 for now, which you've tested against, and
move from 8.4 to 9.1 or 9.2 at a later date, slony is built to do just
that, with running from one major version to another being one of the
things it's really good at.

The real answer then is that it comes down to which meets your
requirements the best.  Both are well tested and supported.

Carlos,

I would recommend you simply stick with Debian 6 and add the debian
backports repository.  Add the following to your /etc/apt/sources.list:

deb http://backports.debian.org/debian-backports squeeze-backports main

After adding that just do an 'apt-get update' and you will be able to
install the postgresql-9.1 package.

If you would prefer to use Aptitude to install your packages, then do a
search for ^postgres, then when you find the postgresql-8.4 package just
hit the V key to view other available versions, 9.1 should be one of the
additional versions available.

-Brandon

On 11/04/2011 11:58 AM, Carlos Mennens wrote:
> On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps<bphelps@gls.com>  wrote:
>> Carlos,
>>
>> Streaming replication was introduced in PostgreSQL 9.0 and should do what
>> you want.
>>
>> http://wiki.postgresql.org/wiki/Streaming_Replication
>
> Oh great! I didn't see that in the 8.4 manual since that is what
> Debian 6 has as the most stable version in it's package manager.
> Anyone know of a stable Linux distribution that offers 9.0+? I know
> Debian Wheezy (testing) has 9.1 but sadly it's testing and not
> recommended for production utilization. RHEL is years behind as far as
> packages go which makes them stable to an annoying degree.
>

Use Debian backports. We have several debian squeeze with backports
running PostgreSQL 9.1.

2011/11/4 Carlos Mennens <carlos.mennens@gmail.com>:
> On Fri, Nov 4, 2011 at 11:52 AM, Brandon Phelps <bphelps@gls.com> wrote:
>> Carlos,
>>
>> Streaming replication was introduced in PostgreSQL 9.0 and should do what
>> you want.
>>
>> http://wiki.postgresql.org/wiki/Streaming_Replication
>
> Oh great! I didn't see that in the 8.4 manual since that is what
> Debian 6 has as the most stable version in it's package manager.
> Anyone know of a stable Linux distribution that offers 9.0+? I know
> Debian Wheezy (testing) has 9.1 but sadly it's testing and not
> recommended for production utilization. RHEL is years behind as far as
> packages go which makes them stable to an annoying degree.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Martín Marqués
select 'martin.marques' || '@' || 'gmail.com'
DBA, Programador, Administrador