jan2003.tar

Usogres -- The PostgreSQL Replication Tool

Tim Epkes

Data redundancy is important in any network requiring zero downtime. Load balancing software works great, however, data replication sometimes becomes a challenge. How do we get the data currently being entered, replicated to a standby database in case of a primary database failure? This can be a very big challenge. You could shut down the database every night and rsync the data, which would work, but this method doesn't provide a very good solution for keeping the data up to date. A failure during mid-day transactions could result in loss of the data entered for that morning.

The Usogres tool solves this problem by replicating the data in real time. As updates are entered to the master database, the slave gets updated. Usogres runs on port 5432 (standard Postgres port). The two Postgres servers run on an alternate port (i.e., port 5434). Usogres actually listens for the updates and then distributes the updates to both databases at the same time. If you add a content services switch that will load balance the two databases and remove a failed system from the services ring, you get very high availability. Usogres was written by Sebastian Paviot and is currently in release 0.8.1.

Configuration

To begin, download Usogres from:

http://www.usogres.good-day.net

Configuration of this tool assumes knowledge of PostgreSQL.

The following steps describe how to install, configure, and run Usogres. Prerequisites for this process are two fully installed and operational PostgreSQL systems. Note that you must ensure the slave system does not have important data on it, as it will be wiped by the master. See Figure 1.

1. Log into the systems as the Postgres user and make a temporary directory in Postgres' home directory called temp:

# mkdir temp
# cd temp

2. Stop PostgreSQL on each server:

# /usr/local/pgsql/bin/pg_ctl stop

3. Untar Usogres in your sources directory (I use /opt):

# tar -xzvf usogres-0.8.1.tar.gz

4. Change directory to usogres and configure:

# cd usogres-0.8.1
# ./configure

5. Compile and install Usogres:

# make
# su -c make install

Note: By default Usogres is installed to /usr/local/bin/usogres and the resource file is installed to /usr/local/etc/usogresrc.

6. Vi /usr/local/etc/usogresrc and make it look as follows:

SYSTEM A -- Master

#
# usogres run command file for systemA
#
# host address that usogres performed
host = "10.1.1.20"
# port address that usogres performed
port = 5432
# host address that postgres original server performed
hostorg = "10.1.1.20"
# port address that postgres original server performed
portorg = 5434
# host address that postgres backup server performed
hostcopy = "10.1.1.21"
# port address that postgres backup server performed
portcopy = 5434
# directory for log files
logdir = ~/.usogres-log
# max number of usogres process
max_process = 32

SYSTEM B -- Slave

#
# usogres run command file for systemB
#
# host address that usogres performed
host = "10.1.1.21"
# port address that usogres performed
port = 5432
# host address that postgres original server performed
hostorg = "10.1.1.20"
# port address that postgres original server performed
portorg = 5434
# host address that postgres backup server performed
hostcopy = "10.1.1.21"
# port address that postgres backup server performed
portcopy = 5434
# directory for log files
logdir = ~/.usogres-log
# max number of usogres process
max_process = 32

7. As the user Postgres, add the following variables to the .profile file:

For system A:

PGHOST=systemA
PGPORT=5432
export PGHOST PGPORT

For system B:

PGHOST=systemB
PGPORT=5432
export PGHOST PGPORT

8. Restart PostgreSQL using port 5434 (on both systems):

# su pgsql -c "/usr/local/pgsql/bin/postmaster -S -i -p
   5434 -D /usr/local/pgsql/data"

9. Start Usogres to begin replication (on both systems):

# /usr/local/bin/usogres

10. Test your connection to Usogres:

# psql template1

It should come up to the psql prompt for template1. If it does not, recheck the steps to ensure you followed them correctly.

Optional: Usogres looks for the resource file in /usr/local/etc first; if it doesn't find it there, it will look in the Postgres home directory as a hidden file .usogres. I move my usogresrc (located in /usr/local/etc) to $PGHOME/.usogresrc.

Conclusion

I am currently running PostgreSQL/Usogres to support internal OSS Web applications. I will be moving to the Cisco CSS 11050's to provide load-balancing services, but first I have to write the PostgreSQL keep-alive script for the CSS. To accomplish the load balancing, you can create a service ring with a content switch. Then create a virtual IP for everybody to be your virtual server. If one of your database servers or Postgres processes goes down, it will be removed from the service ring and the online database will only be written to. The combination of Postgres and Usogres provides a powerful tool to ensure maximum availability of the database to your users.

I thank Sebastian Paviot for writing this application and hope he continues further development.

Tim Epkes is a consultant and provides professional services for small to medium businesses and service providers. He is a CCIE, CCDA, CCNA, Solaris Admin/Network certified and is currently working toward OCP certification. His Web site can be found at: http://www.epkes.net.