Questions[replication](server)

I've found myself needing redundant storage at a block level. File-level replication (Gluster, GFS, etc) does not work for my use case.

It looks like DRBD is the go-to solution for block replication. There don't seem to be too many other sane options. Have I failed in my research, or is DRBD the only game in town?

1. I tried Postgres-XC and it doesn't implement full SQL yet (like SERIAL)

2. Postgres-R looks interesting but it's "not production ready" according to developers.

So I used pgpool-II 3.0.1. Yes, it works nicely. But as far as I can see it's only for 2 PG nodes.

Is there anything out there that is actually production ready AND capable of working with multiple PG nodes?

Running PostgreSQL 9.0.1, with streaming replication keeping one read-only mirror instance up to date. Auto-vaccuum is on on the primary, except for a few tables which are not vacuumed by the auto-vacuum daemon, in an effort to reduce business-hour IO. These tables are "materialised views".

Each night at midnight, we run a vacuum across the database in order to clean up those tables that are excluded from the auto-vacuum. I'm wondering if that process replicates across to the mirror, or if I need to set up vacuum on the mirror as well?

I'm considering setting up replication of our mysql db to be able to have local slaves in each of our branch offices, while having the master in the main office to improve application performance (significantly) at our branch offices.

The db itself isn't that large (<1gb) but I'm wondering; considering 200-300 record updates/min tops: how fast is replication? (assuming, first, a 5mb generic dsl connection, faster if necessary - trying to keep costs as low as possible but the money is there for more)

Are whole tables replicated in batches? Is the replication done, on demand, as each record in a table is updated (from the docs, I think I'm seeing that it's configurable)?

Notes:

• I'm thinking 1 master, 2 slaves (2 branch offices for now) setup as in the docs here except that it's an app, not a web client
• Any update done on the master needs to replicate to the other slaves in <10 mins.
• All of this assumes that I can get our ORM (DevExpress XPO) happy with the concept of reading from the slave and writing to the master.

We constantly bat this around the office, and the question continues to come up. How do you deal with PostgreSQL replication? I'm not even necessarily talking about advanced clusters, just keeping it simple with Master-Slave, Master-MultiSlave, and Master-Master. I find that setting it up for MySQL is typically pretty simple. Failover is straightforward if not perfect, especially for how easy it is to configure. We've played with Slony, but it's a bit too hands on (schema changes require intervention, new databases require intervention, etc). PGPool2 was pretty nice, until a node went down and we couldn't find a graceful way (other than bringing everything down and reseeding the fallen node) to get replication back in sync. Basically here's what I'm typically looking for:

• Easy setup (I'll settle for difficult setup, but easy to expand)
• Simplistic failover
• Bringing a fallen node back in just requires time (i.e. like mysql. Server goes down, you bring it up, and wait for replication to catch up)
• Schema changes don't break replication
• Adding a new database to the server is seamless (i.e. like mysql, you can replicate a whole DB server, so a new database is created on the master, it automatically propagates to the slave)

MySQL handles most of these fairly well, but I hold a certain fondness for PostgreSQL. Besides, we have some situations where it's our only option, and we'd like to add replication to the mix. What are you using currently, and how do you feel about your solution? This isn't a MySQL versus PostgreSQL post, I promise, because that's not what I'm trying to start. :)