How to install PostgreSQL on FreeBSD/PCBSD

In this tutorial I want to share to you on how to install PostgreSQL v8.4.3 on FreeBSD/PC-BSD. This tutorial should be applicable on other BSD families (NetBSD, OpenBSD) and Linux distros (Slackware, Centos, Ubuntu, Redhat, Debian, etc) because we will install PostgreSQL from source distribution file.

1. Download the source file
You need to download the PostgreSQL file from its website here before following this tutorial.  The current stable version is v8.4.3. Save it in your home directory.

2. Extract the file and start configuring
We will install the PostgreSQL in /usr/local/pgsql folder so we set the –prefix to /usr/local/pgsql like below:

$ tar jxf postgresql-8.4.3.tar.bz2
$ cd postgresql-8.4.3
$ ./configure --prefix=/usr/local/pgsql --mandir=/usr/local/man
------
[snip]
------
checking if gcc supports -Wl,--as-needed... yes
configure: using CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
configure: using CPPFLAGS=
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking ./src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking ./src/backend/port/dynloader/freebsd.c to src/backend/port/dynloader.c
config.status: linking ./src/backend/port/sysv_sema.c to src/backend/port/pg_sema.c
config.status: linking ./src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking ./src/backend/port/dynloader/freebsd.h to src/include/dynloader.h
config.status: linking ./src/include/port/freebsd.h to src/include/pg_config_os.h
config.status: linking ./src/makefiles/Makefile.freebsd to src/Makefile.port
$ gmake
$ sudo gmake install

PostgreSQL can not be run as root so we have to create a user that will run the postgresql database. Simply it will be run by ‘postgres’ user. Also we have to specify the folder where the data will be stored, we will use /usr/local/pgsql/data. So we need to run below command as root:

adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data

After that we need to create a new PostgreSQL database cluster. As mentioned before, the database will be stored in /usr/local/pgsql/data. To create the new cluster, it must be run as ‘postgres’ user.

$ su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.                                  

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".

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers ... 28MB
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

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.

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
or
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

Now everything is ready. To start the postgresql database, run the command below:

$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data &
[1] 21196
$ LOG:  database system was shut down at 2010-03-19 11:07:02 WIT
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections

Try to create a ‘test’ database:

$ /usr/local/pgsql/bin/createdb test
$ /usr/local/pgsql/bin/psql test
psql (8.4.3)
Type "help" for help.

test=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
test#