Bruce is discussing various replication schemes for PostgreSQL database servers. These are nearly real-time notes, so they're a bit sketchy.

Uses

  • Fail Over (a replica on standby)
  • Load Balancing (use a number of smaller boxes)
  • Data Warehousing (replica used for insane read queries)
  • Remote Servers (firewall or slow line in between machines)
  • Mobile Servers (laptops, usually)

Each use has slightly different requirements. Fast or slow line, on-line 24/7 or only part time, etc.

Methods

  • Master/Slave Asyncrhonous
  • Master/Slave Synchronous
  • Multi-Master Asynchronous (conflict resolution issues: one master master, first one wins based on time sync, locality specific queries (use views + permissions for that, possibly))
  • Multi-Master Synchronous

Uses and Methods

Given a particular usage, which method do you use?

  • Fail Over: Master/Slave
  • Load Balancing: Mutli-Master with Sync or Async + Conflict Resolution
  • Data Warehousing: Aysnc Master/Slave
  • Rmote Servers: Async Multi-Master
  • Mobile Servers: Async Multi-Master + Conflict Resolution

Other Methods

  • Shared disks or SAN
  • Application-level replication
  • Data partitioning among servers

PostgreSQL Replication Soutions

  • Usogres (from Japan)
  • eRServer/Rserv/Dbmirror (in the PostgreSQL contrib directory, uses triggers for async replication)
  • PgReplicator (async with conflict resolution)
  • Postgres-R (semi-synchronous, code not quite complete, uses Spread Toolkit for group communication)

Talk is half done, but I'm off to a Ruby talk now...

Posted by jzawodn at July 10, 2003 11:31 AM

Reader Comments
# Chris said:

I think it's neat that you, a MySQL guy, are paying some attention to PostgreSQL, too. What's your general opinion of Postgres? Sometimes I hear that Real DB Experts prefer it to MySQL, but I'd like to hear an example from someone who's more involved in the practical side of things.

Thanks.

on July 11, 2003 07:01 AM
# Henry said:

This is cool. We're looking at a client-facing project that could probably make use of Postgres replication to scale up. Thanks!

on July 11, 2003 05:19 PM
Disclaimer: The opinions expressed here are mine and mine alone. My current, past, or previous employers are not responsible for what I write here, the comments left by others, or the photos I may share. If you have questions, please contact me. Also, I am not a journalist or reporter. Don't "pitch" me.

 

Privacy: I do not share or publish the email addresses or IP addresses of anyone posting a comment here without consent. However, I do reserve the right to remove comments that are spammy, off-topic, or otherwise unsuitable based on my comment policy. In a few cases, I may leave spammy comments but remove any URLs they contain.