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
