Setting up RServ with PostgreSQL 7.0.3

This page contains historical information or deprecated articles.

Last updated 13th September 2001

by S.Gopi

This Document describes how to do Mirroring of Databases in PostgreSQL 7.0.3 using RServ

All the below processes were developed through a trial and error basis, you might find another easy way to achieve the same results. If so, please let me know.

Tested Under Slackware 7.0 (2.2.16), PostgreSQL Version 7.0.3, RServ Version 0.1

This Document is created with the assumption you have installed PostgreSQL under /usr/local/pgsql and successfully installed the RServ binaries and libraries. If not, first install them, then check out README.rserv in postgresql-7.0.3/share/contrib/rserv/ for installation instructions.

Creating the Master Database

(You can skip this if you already have one)

    createdb master

Then create whatever tables you want in this Master database (required), and any records you want (optional).

Let us go with pg_dump because Replication will happen only if table structures and record entries (if any) are similar for both master and slave databases at initial stage, otherwise it gives error while Replicating.

    pg_dump -d master -f master.dump

Creating the Slave Database

First we create the slave database then restore the table structures and records from the dump we created in the previous step.

    createdb slave
    psql slave

    slave=$ \i master.dump
    slave=$ \q (quit from psql)

After setting up both the Master and Slave Databases lets get into setting up the Replication process. Setting up Replication Process

There are Four Steps you must carry out before starting Replication

• Step 1: Initializing Master Database for Replication
• Step 2: Initializing Slave Database for Replication
• Step 3: Adding Tables and Columns that needs to be Replicated in Master Database
• Step 4: Adding Tables and Columns that needs to be Replicated in Slave Database. These must be same as Master Database.

Step 1: Initializing Master Database for Replication

From the Command line execute :

    /usr/local/pgsql/bin/MasterInit master

This Process will create necessary tables and sequences used by Replication process in Master database. These tables are used by the Replication process for keeping track of replication.

Step 2: Initializing Slave Database for Replication

From the Command line execute :

    /usr/local/pgsql/bin/SlaveInit slave

This process will create necessary tables in slave database, according to the author of RServ these are used for book keeping information (I don't know for what purpose these are used).

Step 3: Adding Tables and Columns that needs to be Replicated in Master Database

From the Command line execute :

    /usr/local/pgsql/bin/MasterAddTable master table_name column_name

This will add necessary entries in rserv tables in master database. Only tables which are added in this way will be replicated into mirror database.

You can give a specific column name in the table, there is no restriction on this in this version (0.1), athough the author's document says only this column will be replicated. Useful Bug !!! :-) Any column changed in the table will be properly updated in the master database.

Repeat the above step for all the tables in master for which you want mirroring (real painful job)

Step 4: Adding Tables and Columns that needs to be Replicated in Slave Database

From the Command line execute :

    /usr/local/pgsql/bin/SlaveAddTable slave table_name column_name

These tables and columns must be same as you did for master database (I don't know what will happen if you change something, you can test this functionality if you have time. Just remember this is version 0.1 ;-))

Repeat the above step for all the tables in slave for which you want mirroring (another painful work)

Then what?......

You can start doing the replication now,

    /usr/local/pgsql/bin/Replicate master slave

That's it! You have done it... As a test, update some records in the master database (add, modify, delete), then do the replication and check the Slave database, it must be a mirror of the Master. If not check all the above process again and still nothing works out just email me.

Whenever You want to replicate just execute the command :

    Replicate master slave

Note

After executing the Replicate command just ensure a "__Snapshot" file of 0 bytes doesn't exist either in /usr/local/pgsql/bin/ or in your current directory.

When I checked, this file gets created only if the replication process fails for some reason (even if the program didn't show up any error messages, remember this is version 0.1)

P.S.

RServ is very easy to use once you have created the appropriate structures (that is the big task). RServ is just in its infancy and it may be buggy, so use it at your own risk. For example, just try this : Without doing MasterAddTable and SlaveAddTable try doing Replicate it will return without any error messages with no actual mirroring.

Now, do the MasterAddTable and SlaveAddTable process then try with Replicate this time it does replication but original entries in slave database (before SlaveAddTable entries) will never get updated, or some other crap will happen!! Try updating some entries in master database and then replication you will go with surprises...

Enjoy...