Update postgresql sequences in preparation for asynchronous replication. Ensure sequence numbers by multiple masters never conflict.

update_sequences

#!/usr/bin/python
# translated to python from http://fahodzi.wordpress.com/2010/11/11/generating-even-and-odd-postgresql-databases-for-replication/
import argparse
import psycopg2

parser = argparse.ArgumentParser(description='Update sequences for replication')
parser.add_argument('--host', dest='host', help='Postgresql host')
parser.add_argument('--user', dest='user', help='Postgresql password')
parser.add_argument('--password', dest='password', help='Postgresql password')
parser.add_argument('--port', dest='port', type=int, help='Postgresql port')
parser.add_argument('--database', dest='database', required=True, help='Postgresql password')
parser.add_argument('--mod', dest='mod', type=int, required=True, help='Number of servers')
parser.add_argument('--n', dest='n', type=int, required=True, help='This server')
args = parser.parse_args()

if args.mod < 1:
        parser.error("n must be 1 or above")

if args.n < 0:
        parser.error("n must be 0 or above")

if args.n >= args.mod:
        parser.error("n must be less then mod")

db_args = {}
for key in ['host', 'user', 'password', 'port', 'database']: 
        value = getattr(args, key)
        if value:
                db_args[key] = value

conn = psycopg2.connect(**db_args)

cursor = conn.cursor()
cursor.execute("SELECT sequence_schema, sequence_name FROM information_schema.sequences")

for row in cursor:
        sequence = "%s.%s" % (row[0], row[1])

        tmp_cursor = conn.cursor()
        tmp_cursor.execute("SELECT last_value from "+sequence)
        last_value = tmp_cursor.fetchone()[0]

        cur_n = last_value % args.mod
        print sequence, last_value, cur_n

        # if n is too high for this cycle, go to next cyle
        if cur_n > args.n:
                last_value += (args.mod - cur_n)
                cur_n = 0
                print "NC", sequence, last_value, cur_n

        last_value += args.n - cur_n
        cur_n += args.n - cur_n

        print sequence, last_value, cur_n
        tmp_cursor.execute("ALTER SEQUENCE " + sequence + " INCREMENT BY %s", (args.mod,))
        tmp_cursor.execute("SELECT setval(%s, %s)", (sequence, last_value))

        print

exit(0)