13

I am not a DBA; I'm the primary developer of an enterprise database based application.

I'm currently specing out some new machines to upgrade our existing enterprise database. Currently we're running Postgres 8.4 with a database at our DR site that receives updates at intervals via some custom rsync work a former employee performed.

One of the major issues we're trying to tackle is latency between two global offices. We have staff in NY and staff in London. Currently, the London staffers suffer the fate of our VPN pipe. I'm not really in a position to change that infrastructure.

What I'd like to do is move to Postgres 9 and setup streaming replication. The slave would be in the London office, which would alleviate read problems for users. The problem I foresee is writes to the slave (not sure how PG handles this, my understanding is the slave is in read-only mode). Ideally, writes would be sent to the master in NY (writes are very infrequent from London, but necessary) by the database itself. Furthermore, I can setup fallover to have it act as a hot swappable backup (replacing the DR). Again, all contained within Postgres configuration with no additional code.

That's my idealized solution. How far off am I? Is this at all possible?

I'm a little overwhelmed by the breadth of this topic and Google is not really helping me. I would appreciate any advice from some experienced DBAs, with anecdotes, relevant documentation or examples.

Currently we're using SQLAlchemy as the primary interface to the database, if that's relevant. It does mean we're not tied to Postgres.

Thanks everyone.

TrevorB
  • 257
  • 1
  • 3
  • 10
  • 6
    If anyone gets to this: Postgres eventually added Master/Master replication which is what we ended up using. Prior to that I used some custom code with timestamps on key tables to create delta sets and update the DB. Nightly (Asia hours) the DB's were fully synced using the prior rsync methedology. The pgpool suggestion, while good, suffered latency on both sides of the pond. I may have misconfigured it, but it was not an ideal solution. – TrevorB Dec 14 '16 at 14:56

2 Answers2

11

pgpool-II can handle this for your: enter image description here

Check this example.

user
  • 5,335
  • 7
  • 47
  • 63
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • Cool. And it's in pkgsrc too. I'll try this out. Thanks very much. – TrevorB Jun 01 '11 at 22:02
  • 1
    Note 1 caveat though, that if you are doing any writes via functions, then this solution will not work because PgPool will send reads to just 1 host and [functions are executed with a select statement](http://www.postgresql.org/docs/9.1/static/sql-syntax-calling-funcs.html), which means a function which does any writes will not be synced to all hosts. – Michael M Nov 07 '13 at 23:36
2

The PostgreSQL 9 documentation gives a nice overview of the options available.

http://www.postgresql.org/docs/9.0/static/different-replication-solutions.html

You might want to look at Slony if you do not mind having to write to the master and not the slave, or if you want, check out any of the multi-master replication technologies available.

Imraan
  • 657
  • 1
  • 5
  • 10