Thread: 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 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 another node 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 missing something here?
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