10:00

WordPress to Django+PostgreSQL: Part 2, Configuring PostgreSQL

17 October 2009

In part 1, we installed PostgreSQL on the VPS. In this part, it’s time to get the database server up and configured. First, though, now that we have something to lose on the server, backups are now enabled at Slicehost. $5/month is cheap insurance. This also means we can take a snapshot of the slice, which we’ll do at the end of each major checkpoint, so we can roll back to it if something gets screwed up. (As they say in videogaming, “Hope you saved.”)

Since we built PostgreSQL from the tarball, rather than using an RPM, we didn’t get some of the conveniences that the RPM provides, like creating the user that the server will run as. So let’s do that!

[xof@blog ~]$ sudo /usr/sbin/adduser postgres
[xof@blog ~]$ sudo passwd postgres
Changing password for user postgres.
New UNIX password: 
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.

Logging in as our shiny new user, we add the PostgreSQL binaries to our $PATH in the .bash_profile:

PATH=$PATH:/usr/local/pgsql/bin:$HOME/bin

Well, no time like the present… let’s create a cluster:

[postgres@blog ~]$ initdb db

We want the locale and encoding to be UTF8, and that happens to be the default:

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

Nota bene that the default authentication is “trust”:

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

That’s fine for now (we’ll change it later). As a smoke test, let’s start up the server and make sure all is well:

[postgres@blog ~]$ pg_ctl -D ~postgres/db -l logfile start

We’re just writing the log to a file ‘logfile’ in the postgres user’s home director, which is not particularly sophisticated log file management. (That’s also something we’ll fix later.)

So, did it work?

[postgres@blog ~]$ psql postgres
psql (8.4.1)
Type "help" for help.

postgres=#

Looks good!

Now, before we start creating new databases, let’s set up a couple of useful defaults.

[postgres@blog ~]$ psql template1
psql (8.4.1)
Type "help" for help.

template1=# 

We’ll be using PL/pgSQL functions in the future, so let’s add that language to template1. The template1 database is the template from which new databases are created, so changes here will be propagated to all new databses we create in the future.

template1=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE

We can also add the UUID-OSSP functions:

template1=# \i /usr/local/pgsql/share/contrib/uuid-ossp.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

Great. PostgreSQL has a ton more configuration options, of course, but this will get us started.

One of the other things that you don’t get when you build PostgreSQL from a tarball is an automatic init.d script so that PostgreSQL starts on system boot. Fortunately, the source tarball comes packaged with a suitable startup script:

[xof@blog ~]$ cd builds/postgresql-8.4.1/contrib/start-scripts
[xof@blog start-scripts]$ sudo cp linux /etc/rc.d/init.d/postgresql
[xof@blog start-scripts]$ sudo chmod +x /etc/rc.d/init.d/postgresql

The standard script doesn’t have the right location for the database directory, so we have to edit the appropriate line:

# Data directory
PGDATA="/home/postgres/db"

and use chkconfig to add the appropriate symlinks:

[xof@blog start-scripts]$ sudo /sbin/chkconfig --add postgresql

(We went back to the xof account because the postgres account isn’t a member of wheel, and can’t use sudo, which is just the way it should be.)

So, did that work? Let’s find out!

[xof@blog ~]$ sudo reboot

(wait wait wait.) Back up!

[postgres@blog ~]$ psql postgres
psql (8.4.1)
Type "help" for help.

postgres=# 

Great. Note that the server logs are now being written to the default for the init.d file, which is $PGDATA/serverlogs rather than ~/logfile as before.

Next, in part 3, we’ll install Python 2.6, Apache, mod_wsgi and psycopg2.

There is one comment.