PostgreSQL Installation

VHCS, the control panel, doesn’t have support for postgres built in. This means that you need to manually setup the database from the command line.

Each client gets their own instance of postgresql running on a private port. This means that you can run it with remote access if you like, as there’s no security risk to other peoples instances.

Setup your Environment Variables

This process is much easier to manage if you first take a moment to modify your Server Environment Variables for PostgreSQL.

Initialize your database.

Assuming that you have modified your PGPATH and PGPORT in your Server Environment Variables, let’s now initialize your new PostgreSQL database.


$ initdb -D ~/pgdata --no-locale -A md5 -W

Note: If you decide to skip changing your EnvironmentVariables, see the bottom of this page for the paths to each version of PostgreSQL, which you will need to type out each time you run a postgresql command from the shell.

Modifing your postgresql.conf

In pgdata/postgresql.conf you need to change two settings.

Port number
Max connections

Port number should be set to your assigned port number. Server Port Allocation

For example, vu2069 = 5469.

Max connections should be set to 10.

Testing your new installation

Now that your instance is created it is started with the pg_ctl command.

$ pg_ctl -D pgdata/ start -l logfile

Restarting with the server?

As far as making it start on reboot vixie cron has a @reboot option, so you would add:


@reboot /usr/local/pgsql-8.1/bin/pg_ctl -D /home/vu####/pgdata start -l logfile

On ion, I had to set it up as follows:


@reboot /usr/bin/pg_ctl -D $HOME/pgdata start -l $HOME/logfile

Thats it!

Having problems restarting your instance?

If you just came back from a system crash, you might be having a problem restarting your instance. Try removing your postmaster.pid file and restarting again.


rm ~/pgdata/postmaster.pid

PostgreSQL Versions & Paths

Here are the different versions of PostgreSQL that we have running at PLANET ARGON.

PostgreSQL 8.2

/usr/local/pgsql-8.2/bin

PostgreSQL 8.1

/usr/local/pgsql-8.1/bin

PostgreSQL 8.0

/usr/local/pgsql-8.0/bin/

PostgreSQL 7.4

/usr/local/pgsql-7.4/bin/

Making it Fast

Although query optimization is a huge subject for another time, PostgreSQL offers a particularly handy tool for speeding up the performance of your application.

After you’ve added a significant amount of data to your database, or changed your schema, run the following SQL statement:

VACUUM FULL ANALYZE;

VACUUM FULL strips all of the deleted data from your database and optimizes the storage. We recommend doing this every week or two if you have an active database with frequently updated data; it improves performance and cuts down on your disk utilization.

ANALYZE examines the contents of tables in the database, and gives the internal query planner hints to help determine the most efficient execution of queries. Some of our apps have seen a 150x improvement in query performance after running this command, so it’s highly recommended to run it for apps that use complex queries, particularly those with more than one JOIN and subselects. Any Rails application that uses has_and_belongs_to_many relationships will certainly benefit!

More information can be found at:

VACUUM: http://www.postgresql.org/docs/8.1/static/sql-vacuum.html ANALYZE: http://www.postgresql.org/docs/8.1/static/sql-analyze.html

Procedural Languages

plRuby? – Build PostgreSQL Functions in Ruby!