After using MySQL for quite a while I decided I missed a lot of features from other relational databases that I use at work, namely MS SQL Server. I had used PostgreSQL 7.x a long time ago and it had some quirks that annoyed me, but I figured I would give 8.x a shot. After a quick google search I discovered that almost every guide to installing PostgreSQL on FreeBSD was extremely outdated, and having muddled through it using Linux guides and the official documentation I figured I would put what I learned here so hopefully other people don't have to spend an hour digging through documents just to set up a quick development server.
Any FreeBSD box with some spare disk space will work. I installed it on my FreeBSD desktop machine running CURRENT.
This is really the same as installing any other port, with one exception: I would highly recommend excluding "clean" from your make command at first, especially if you want to use pgadmin3. The reason for this is that the port will not automaticall build all the modules from the contrib directory, and there is no option knob to tell it to do so. As far as port options I accepted the defaults, they seemed sane for my purposes.
The pgadmin3 utility is a GUI app for administering PostgreSQL. I am running the tool from my Windows machine, but I installed it on my FreeBSD machine from ports easily enough. The port is located in databases/pgadmin3.
To install the admin pack you need gmake installed. I already had it from a previous port installation, but if you don't it is in devel/gmake. Now, do the following:
# cd work/postgresql-8.3.6/contrib/adminpack/ # gmake # gmake install
There is one more part to this but we need the server to be running before we can do the last step.
Before we can configure or start the server we need to initialize the data store. You will need to su to the pgsql user that was added during the install of the port. I used the default location, but you could put the files anywhere you want. In my opinion somewhere in /var/db would make more sense.
# su pgsql $ initdb -D /usr/local/pgsql/data
Once that command finishes we can edit the config file, located at /usr/local/pgsql/data/postgresql.conf. Below is the only line I changed to allow remote connections on every interface.
listen_addresses = '*' # defaults to listen_addresses = 'localhost'
If you want to remotely connect to the postgre server you will also need to edit the pg_hba.conf file in the same directory. Just add the following line at the end of the file, replacing the subnet with your own.
host all all 192.168.1.0/24 trust
There are probably a lot of other things you may want to configure, but this got it up and running for me.
Now we need to start up the server before we can continue. I wanted the server to start every time I boot the machine, so I went ahead and added a line to my rc.conf.
echo 'postgresql_enable="YES"' >> /etc/rc.conf
Now just run the rc script to start the server.
/usr/local/etc/rc.d/postgresql start
I was kind of annoyed it didn't give any feedback that it had started the server, but sure enough it was running.
# su pgsql $ psql -d postgres postgres=# \q
Now that we have the server up and running we can finish installing the admin pack.
# su pgsql $ psql -U pgsql -d postgres -f /usr/local/share/postgresql/contrib/adminpack.sql
I love how so many DB packages come with blank administrative passwords by default.
# su pgsql $ psql -d postgres postgres=# ALTER USER pgsql WITH PASSWORD 'password'; postgres=# \q
It wasn't overly hard to get running, but when I want to set something up quickly to get it running I don't want to muddle through 20 pages of documentation. Hopefully this quick guide will save others some time in the future.