I recently was setting up a new VPS on Linode and I got the following error when trying to create a new database:

ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII) 
HINT: Use the same encoding as in the template database, or use template0 as template.

This error is related to the locale of the system when Postgres was installed. If you run the commands in my previous post before installing Postgres, you should avoid these errors altogether.

If Postgres has already been installed, however, you can run the following commands (assuming Postgres version 8.4) to reinitialise the database with the correct encoding:

pg_dropcluster --stop 8.4 main
pg_createcluster --start -e UTF-8 8.4 main

Note: this will erase all databases and reset your postgres configuration so it is only really useful when you are first setting up your database, or have taken appropriate measures to be able to restore your data.

Dependencies

Install the GEOS and PROJ frameworks from here

Postgres

Download and Compile

http://ftp2.au.postgresql.org/pub/postgresql/source/v8.4.1/postgresql-8.4.1.tar.bz2 | tar xjf -
cd postgresql-8.4.1
./configure
make && sudo make install

The files are now all installed in the right places, onwards to making them usable!

Create a postgres User and Group

This is adjusted from the instructions in the comments here).

# Find unused Group ID and User ID:
export GROUPID=`sudo dscl . -list /Groups PrimaryGroupID | ruby -e 'puts STDIN.read.scan(/\d+/m).map{|i|i.to_i}.uniq.sort.last.succ'`
export USERID=`sudo dscl . -list /Users UniqueID | ruby -e 'puts STDIN.read.scan(/\d+/m).map{|i|i.to_i}.uniq.sort.last.succ'`

# Create group:
sudo dscl . -create /Groups/_postgres
sudo dscl . -create /Groups/_postgres PrimaryGroupID $GROUPID
sudo dscl . -append /Groups/_postgres RecordName postgres

# Create user:
sudo dscl . -create /Users/_postgres
sudo dscl . -create /Users/_postgres UniqueID $USERID
sudo dscl . -create /Users/_postgres PrimaryGroupID $GROUPID
sudo dscl . -create /Users/_postgres UserShell /bin/bash
sudo dscl . -create /Users/_postgres RealName "PostgreSQL Server"
sudo dscl . -create /Users/_postgres NFSHomeDirectory /usr/local/pgsql
sudo dscl . -append /Users/_postgres RecordName postgres

That’s the clean way of making a user (so it doesn’t show up in your Accounts preference pane, etc.

Clean up

sudo touch /var/log/psql.log
sudo mkdir /usr/local/pgsql/data
sudo chown -R postgres:postgres /usr/local/pgsql/data /var/log/psql.log

# Put Postgres in your path
export $PATH="/usr/local/pgsql/bin:$PATH"
sudo sh -c 'echo /usr/local/pgsql/bin > /etc/paths.d/pgsql'

Thanks to Apple we have a nice way of setting the PATH across the entire system so every app should now know how to find the Postgres binaries and the data directory is now set up to store the database files — now, we just need to create them.

Initialise the Database

# Become the postgres user
sudo su - postgres

# If this throws next commant throws an error about shared memory, 
# try putting "these lines":http://gist.github.com/224815 into 
# /etc/sysctl.conf, rebooting, and trying again:
initdb -D /usr/local/pgsql/data -E UTF8

# Start the database
postgres -D /usr/local/pgsql/data >/var/log/psql.log 2>&1 &

# Create a test database and check you can connect to it
createdb test
psql test

# Just create a superuser for general dev tasks
createuser postgres

# we don't want to be the postgres user anymore
exit

Your done with the Postgres setup now!

PostGIS

curl http://postgis.refractions.net/download/postgis-1.3.7SVN.tar.gz | tar xzf -
cd postgis-1.3.7SVN
./configure --with-geosconfig=/Library/Frameworks/GEOS.framework/unix/bin/geos-config --with-projdir=/Library/Frameworks/PROJ.framework/unix
make && sudo make install

Phew! That one was easy.

Postgres Ruby Gem

This is the part that everyone cares about the most. If this works your Rails apps should start working like a charm.

sudo env ARCHFLAGS='-arch x86_64' gem install pg

This post is mostly a reminder for me in the future. However, others may find it useful also.

I love Postgres but more often than not I get frustrated and confused trying to get authentication working with a new database. By looking through the Deprec source code, I pulled out these two lines of code and so far they haven’t failed me:

su - postgres -c "createuser -P -D -A -E $DBUSER"
su - postgres -c "createdb -O $DBUSER $DBNAME"

Of course, replace $DBUSER and $DBNAME with your desired username and database name, or define them as shell variables. The $DBUSER creates that user as the owner of the database (so it has permission to read/write etc).