Re: Replication with slony-I - Mailing list pgsql-general

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:

1. 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'.

2. 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

3. Then created 2 databases named: master and slave
4. 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

5. 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

6. Executed the command:

      pgbench -s 1 -c 5 -t 1000 -U postgres -h localhost master

7. 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);

8. On MASTERHOST (localhost) the command to start the replication executed the command:

      slon pgbench "dbname=master user=postgres host=localhost"

9. On SLAVEHOST (localhost) the command to start the replication executed the command:

      slon pgbench "dbname=slave user=postgres host=localhost"

10. 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);

11.  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