Test scripts for observing failover data consistency

Gemfile

source :rubygems

gem 'pg'

test.rb

#!/usr/bin/env ruby
require 'bundler'
Bundler.require(:default)

counter = 0
exiting = false

def connection
  PG.connect( host: 'localhost', port: 5432, user: 'iasta', dbname: 'test' )
end

def clean_table(c = connection)
  c.exec "DELETE FROM test"
  c.exec "SELECT setval('test_id_seq', 1);"
end

trap("INT") do
  exiting = true
end

conn = connection
conn.exec("CREATE TABLE IF NOT EXISTS test ( id SERIAL NOT NULL PRIMARY KEY, local_counter INTEGER NOT NULL DEFAULT 0, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);")
conn.exec("CREATE UNIQUE INDEX uniqe_counter ON test ( local_counter )") rescue nil
clean_table(conn)
loop do
  if exiting
    puts "STOPPED AT #{counter}"
    exit 0
  end
  begin
    conn ||= connection
    result = conn.exec("INSERT INTO test(local_counter) VALUES (#{counter})") 
    if result.cmd_tuples == 1
      counter += 1
    end
    if counter % 1000 == 0
      puts "INSERTED #{counter} values"
    end
  rescue Exception => e
    conn = nil
    puts "Got exception #{e.message}:#{e.inspect}  at counter value #{counter}, moving on..."
  end
end

This was tested on Postgres 9.3 w/ PGPool 3.3.1. Hot standby w/ restore_command from WAL archives as well. Max local_counter value == counter in this test, so declaring it a success.

It occurs to me that this is probably the easiest thing for PG to get right as they are all inserts, and I should really be testing UPDATEs instead.