Friday, August 27, 2010

PostgreSQL Database Server Farm Installation and Configuration with PgCluster on FreeBSD


I recently installed a PostgreSQL Database Server Farm on FreeBSD at work. This installation had 1 loadbalancer, 3 cluster servers and 2 replication servers. PostgreSQL works really fast under FreeBSD (especially if you apply the correct configuration). I had some hard time finding a documentation, so I’m publishing the one I wrote here. This guide includes FreeBSD installation (step by step) and configuration, PgCluster installation and configuration (with a little bit performance tuning, thanks to EnderUnix Team and Alper YALCINER) and also performance testing via Bristlecone.


This document outlines installing a PostgreSQL Database Server farm on FreeBSD System.
The installation in this document was tested on a Server Farm which includes 6 PC’s. Hardware specifications of these PC’s are: Pentium 4 CPU’s, 512 MB of RAM, 64 MB Graphics Card and 80 GB Disks. We used PostgreSQL 8.3 Latest on FreeBSD 7.0 and PgCluster 1.9 Latest.
Download FreeBSD from The following instructions are based on FreeBSD Version 7.0
Step by step FreeBSD Installation Guide:
1. Boot with FreeBSD 7.0 installation disk.
2. When menu loads hit 1 to enter the default option.
3. Afterwards choose your country.
4. Then choose to begin a standard installation, it’s the recommended option at this dialog.
5. Then enter OK at the next two messages.
6. When partitioning your drive select ‘A’ for “Use Entire Disk,” then ‘Q’ to finish.
7. Next we need to install a boot manager, so select “BootMgr”.
8. Then enter OK at the next message.
9. Hit ‘A’ for the auto defaults, then ‘Q’ for finish.
10. When choosing your distribution pick option 9 “X-User,” then click OK to the next message, then click OK.
11. Then to install choose the option to do so via FreeBSD CD/DVD, and then click Yes.
12. The installation will then begin.
13. After the installation hit “OK”
14. Hit “No” when configuring any Ethernet or slip/ppp network devices (this can be done later)
15. Hit “No” when prompted if the machine will function as a network gateway.
16. Hit “No” for the next 6 dialogs.
17. Then hit “Yes” to set your machines time zone.
18. Then hit “No” when asked if your CMOS clock is set to UTC.
19. Select your region, then your country, then timezone.
20. When asked to enable Linux binary compatibility, hit no.
21. Hit “Yes” to having a mouse, and then hit no when asked to browse the content for the FreeBSD package.
22. Hit Yes to adding user accounts.
23. Select User to add a new user.
24. Name your user, (i.e normaluser) and give a password (i.e user12345!), then hit okay, then exit user adding menu.
25. When asked to set “root’s password, set it. (i.e rootAxR5!).
26. Then hit no to last dialog, you can now exit installation.
27. You can now reboot and log on.


Repeat these steps on each machine:
1. Login as root, and create a directory called “pgcluster” under /root (mkdir /root/pgcluster)
2. Move the source package to pgcluster directory (mv /source/path/of/the/package /root/pgcluster)
3. Untar the package with “tar -zxvf pgcluster-1.9.0rc5.tar.gz”
4. Enter the directory with “cd pgcluster-1.9.0rc5″
5. Do a configure with “./configure”
6. Open with a text editor (use nano or pice, pico /root/pgcluster/pgcluster-1.9.0rc5/src/
7. Find the line starting with “LIBS” and change it to : “LIBS = -lz -lreadline -lcrypt -lm -lcompat -lpthread”
8. Go back to the root directory of the program (cd /root/pgcluster/pgcluster-1.9.0rc5)
9. Type “gmake”
10. If all goes well without any errors, type “gmake install”
11. As root, enter the following commands:
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data


In order to prevent unprepared access during starting / stop, a load balancer should start at the end. Stop from a load balancer conversely at the time of a stop.
To start/stop the system, you can do:
/usr/local/etc/rc.d/pgcluster start
/usr/local/etc/rc.d/pgcluster stop
on all computers. To start/stop them manually:
Replication Server
1. Start replication server
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc
2. Stop replication server
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc stop
3. Restart replication server
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc restart
4. Usage of a replication server’s operation command.
pgreplicate [-D path of a configuration file] [-W path of a log file][-v] [-n] [-l][-h] [stop | restart]
1. Starting of Cluster DB
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -o "-i" start
2. Stop of Cluster DB
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stop
Load Balancer
1. Start load balance server
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc
2. Stop load balance server
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc stop
3. Restart load balance server
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc restart
4. Usage of a load balance server’s operation command.
pglb [-D path of a configuration file] [-W path of log file][-v] [-n] [-l][-h] [stop | restart]


We used Bristlecone from Continuent Systems for performance testing.
Bristlecone provides tools for testing database performance. There are two main tools included in the package.
Evaluator generates mixed loads of inserts, updates, deletes, and selects. Output can be built-in graphics, HTML, XML, or CSV.
Benchmark runs performance test cases with systematically varying parameters. Output can be HTML or CSV.
The Bristlecone tools were designed for comparative evaluations of database clusters. They include built-in support for threading, ability to generate new tests quickly with simple configuration file changes, and the ability to do systematic tests across different database implementations.
Bristlecone is written in Java. It has mostly run on Linux but is easily adaptable to other platforms. Bristlecone has been tested against the following database types: PostgreSQL, MySQL, and HSQLDB (Hypersonic)
1. Download Bristlecone from
2. Unzip it in a directory
3. Go to bin directory
4. Create the following file:
type=PostgreSQL 8.3
# Command to reformat/analyze tables.
analyzeCmd=vacuum full analyze
5. Create the following file:
# Specify separate connection files for mcluster/mysql.|
# Specify thread combinations.
# Specify table combinations.
# Remaining values are fixed or irrelevant.
6. Create the following file:
type=uni/cluster for PG
# Command to reformat/analyze tables.
analyzeCmd=vacuum full analyze
7. Enter config/evaluator directory and create the following file: postgres.xml
< !DOCTYPE EvaluatorConfiguration SYSTEM >
<evaluatorconfiguration name="postgres" testDuration="120"
autoCommit="true" statusInterval="2" xmlFile="postgresResults.xml">
<database driver="org.postgresql.Driver"
<tablegroup name="tbl" size="200"></tablegroup>
<threadgroup name="A" threadCount="40" thinkTime="500"
updates="7" deletes="1" inserts="2" readSize="10"
rampUpInterval="10" rampUpIncrement="10"/>

8. Before starting a benchmark/evaluation, create a test database from loadbalancer:
# su postgres
$/usr/local/pgsql/bin/createuser -S -d -R -P benchmarkuser
[And enter the password "benchmark" when asked]
$ /usr/local/pgsql/bin/createdb -owner=benchmarkuser
This will create the database on all clusters. If not, check your cluster configuration settings and make sure your clusters/loadbalancers are up and connected to each other and running.
9. To benchmark, go to bin folder in bristlecone directory and type:
The benchmark should start
10. To evaluate, go to bin folder in bristlecone directory and type:
$./ ../config/evaluator/postgres.xml
Enjoy. Keep the light.
Bora Bilgin

No comments: