PostgreSQL sync/replication master-slave
| shell howto frontpage |

English is not my main language, if you like to correct my text, please - do it.

PostgreSQL replication

www.rubyrep.org is my solution. You need Java Runtime.

Done using Ruby, install package include JRuby.

Your Java JRE maybe not include jre6/server/bin/jvm.dll, install it.

Rubyrep replication daemon is only one daemon in some machine - not even in the database servers. Support also MySQL.

You can create master-master sync or more master-slave sync. Here is my example: left is master and right is slave. Master is localhost and slave is in the ip SOME.IP.

1. Create config file

rubyrep generate some.conf

2. Edit config file (some.conf)

RR::Initializer::run do |config|
  config.left = {
    :adapter  => 'postgresql', # or 'mysql'
    :database => 'DB',
    :username => 'postgres',
    :password => 'someword',
    :host     => ''

  config.right = {
    :adapter  => 'postgresql',
    :database => 'DB',
    :username => 'postgres',
    :password => 'someword',
    :host     => 'SOME.IP'

  #config.include_tables 'sometable'  # use only table sometable
  #config.include_tables /^e/ # regexp matching all tables
  #                             starting with e
  # master on left ja slave on right,
  config.options[:sync_conflict_handling] = :left_wins
  config.options[:replication_conflict_handling] = :left_wins
  # example= table has not primary key = every table need primarykey
  config.include_tables 'mysometable' , :key => 'tableoid'
  config.include_tables /./  # = regexp = all tables

3. Run check

Test whole tableset or use only some tables. rubyrep scan -c some.conf rubyrep scan -c some.conf table1 table2

4. Sync =dump

rubyrep sync -c some.conf You can sync also using only some tables as section 3.

5. Replication

rubyrep replicate -c some.conf

If you need daemon start/stop scripts for *nix, then look example.

Even you slave is sometimes offline, no problem. Put it online. After that master will do the changes.

How to make empty db and dump db from host to other host

In this example DB user postgres has priviledges to create db and so on, real superuser.

Every command has done in master = localhost.

Slave (pf_hba.conf) must accept connection from this host.

Empty the slave db: (Win users remove " marks from echo). echo "drop database DB" | psql -U postgres -h SOME.IP template1 Create emty table using encoding WIN1252 in this example: echo "create database DB with owner = postgres encoding = 'LATIN1' " | psql -U postgres -h SOME.IP template1 Dump the localhost database to the slave: pg_dump -U postgres -h localhost DB | psql -U postgres -h SOME.IP DB

Thank you Arndt about great tool. We have been lucky about your Japan "trip".

Other SQL tool