Postgresql – postgres 9.1 master-master setup using Bucardo

master-masterpostgresql

is anyone aware of any comprehensive tutorials for setting up 2 postgres 9.1 databases in master-master configuration?
What "add-ons" are required (for example is Bucardo still a necessity?). I have tried googling but a lot of posts that I find are dated back a couple of years so I am not sure if they are still relevant.Let me be more specific:

For example the following approach might be effective: – install bucardo on master1 – setup replication as follows: master1: master – master2: slave – verify that replication works If the above are correct then: – install bucardo on master2 – setup replication as follows: master2: master – master1: slave – verify replication works Will the previous be enough or I am missing a fundamental piece here?

Below is the advise I got from the bucardo mailing list – perhaps this will be useful to someone:

"Bucardo only needs to be installed on one host, although obviously, that won't provide HA capability. Typically, in a master-master scenario, it's installed on one of the masters. In the event either of the masters then goes down, the Bucardo daemon wouldn't have anything to do anyway, so HA for it is pretty moot."

Thanks in advance for any replies

Best Answer

Bucardo only needs to be installed on one host, although obviously, that won't provide HA capability. Typically, in a master-master scenario, it's installed on one of the masters. In the event either of the masters then goes down, the Bucardo daemon wouldn't have anything to do anyway, so HA for it is pretty moot.

Some useful links:

http://justatheory.com/computers/databases/postgresql/bootstrap-bucardo-mulitmaster.html

http://blog.endpoint.com/2011/06/bucardo-multi-master-for-postgresql.html

Related Solutions

Linux – PostgreSQL Replication

Short answer - there's no such solution yet for PostgreSQL if you need online readonly slaves.

There're two major development projects currently going on in this area which are included in PostgreSQL 9.0 (Spring/Summer 2010), namely:

Synchronous Replication:

http://wiki.postgresql.org/wiki/NTT's_Development_Projects

Read only hot standby slaves:

http://wiki.postgresql.org/wiki/Hot_Standby

which in combination aim to achieve the ease of use of MySQL-style replication minus the bugs/issues MySQL has plus the reliability users know from PostgreSQL.

All of this was kicked off by a manifest from the PostgreSQL Core Team in 2008:

http://archives.postgresql.org/pgsql-hackers/2008-05/msg00913.php

The PostgreSQL replication solutions to this day with the largest user base are Slony-I (more expensive for writes, makes schema changes fiddly), WAL shipping/walmgr (Slaves can't be used online) and pgQ/londiste from Skype/Skytools (more tools/building blocks than a finished solution).

I've written a few things on Log Shipping, walmgr and Slony-I, see

http://blogs.amd.co.at/mt/mt-search.cgi?blog_id=1&tag=pgrep&limit=20 for more information.

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Best Answer

Related Solutions

Linux – PostgreSQL Replication

PostgreSQL – Default Superuser Username/Password After New Install

Related Topic