The detail of my work is as under:
As I mentioned earlier that platform is windows xp and using postgres 8.2 in which Slony- I is included. And all databases are at localhost.
After reading the document entitled “Replicating Your First Database” which is for Linux I optimized the instructions for windows and performed the following tasks:
- I didn’t find any line like tcpip_socket=true in my postgresql.conf. Because there is another option and i.e. listen_addresses = '*'. I changed it to listen_addresses = 'localhost'.
- In pg_hba.conf, I changed the line
host all all 127.0.0.1/32 md5
To
host all all 127.0.0.1/32 trust
- Then created 2 databases named: master and slave
- Executed the command
pgbench -i -s 1 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
After optimizing it i.e.:
pgbench -i -s 1 -U postgres -h localhost master
- Slony-I does not automatically copy table definitions from a master when a slave subscribes to it, so we need to import this data. I did this with pg_dump.
pg_dump -s -U postgres -h localhost master > schema.sql
psql -U postgres -h localhost slave < schema.sql
- Executed the command:
pgbench -s 1 -c 5 -t 1000 -U postgres -h localhost master
- Optimized the given script (and used slonik to run this) as follows:
cluster name = pgbench;
node 1 admin conninfo = 'dbname=master host=localhost user=postgres';
node 2 admin conninfo = 'dbname=slave host=localhost user=postgres';
init cluster ( id=1, comment = 'Master Node');
table add key (node id = 1, fully qualified name = 'public.history');
create set (id=1, origin=1, comment='All pgbench tables');
set add table (set id=1, origin=1, id=1, fully qualified name = 'public.accounts', comment='accounts table');
set add table (set id=1, origin=1, id=2, fully qualified name = 'public.branches', comment='branches table');
set add table (set id=1, origin=1, id=3, fully qualified name = 'public.tellers', comment='tellers table');
set add table (set id=1, origin=1, id=4, fully qualified name = 'public.history', comment='history table', key = serial);
store node (id=2, comment = 'Slave node');
store path (server = 1, client = 2, conninfo='dbname=master host=localhost user=postgres');
store path (server = 2, client = 1, conninfo='dbname=slave host=localhost user=postgres');
store listen (origin=1, provider = 1, receiver =2);
store listen (origin=2, provider = 2, receiver =1);
- On MASTERHOST (localhost) the command to start the replication executed the command:
slon pgbench "dbname=master user=postgres host=localhost"
- On SLAVEHOST (localhost) the command to start the replication executed the command:
slon pgbench "dbname=slave user=postgres host=localhost"
- To start replicating the 4 pgbench tables (set 1) from the master (node id 1) the the slave (node id 2), executed the following script:
cluster name = pgbench;
node 1 admin conninfo = 'dbname=master host=localhost user=postgres';
node 2 admin conninfo = 'dbname=slave host=localhost user=postgres';
subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
- Got SUCCESS up to this point. Now I think this is pgbench which is responsible for replicating tables.
Now my question is that how to include the tables which are already present in an existing database
