postgresql at Tracy Phillips

Installing PostgreSQL on FreeBSD is the topic of the day. If you haven’t been following this little series, make sure you have portupgrade installed.

What we want to accomplish today are the following.

Install PostgreSQL
Initialize PostgreSQL with initdb
Setup the postgres db with UNICODE
Only allow logins with MD5 authentication
Setup PostgreSQL to start when the server is started.
Setup a db user that will connect remotely via pgAdmin III
Setup PostgreSQL so that we can connect remotely from our workstation.
Setup PostgreSQL to Autovacuum.

I would like to thank AndrewSN from #postgresql on irc.freenode.com. His knowledge of PostgreSQL is what made possible the tiny details in configuring a PostgreSQL server correctly.

Digest all of that and hurry up so you will not be late for the party.

This is not going to take that long at all to setup if you are familiar with vim and how to search for things in files.

First lets just install Postgresql. I chose postgresql 8.1 and just used the default configure settings that came up.

[server][root][~]# portinstall postgresql-server

Don’t you just love FreeBSD Ports and how much easier it makes life? Ports are much better than RPM Hell any day of the week, although YUM makes things tolerable.

Next lets use initdb to initialize the postgresq db and get things setup the way we want them. This one line will take care of our second, third and fourth items on the list.

[server][root][~]# su -l pgsql -c "initdb -D /usr/local/pgsql/data -E UNICODE -A 'ident sameuser'"

Now what we need to do is setup /etc/rc.conf so that PostgreSQL gets started when the server is started. Issue the following at the command line and you will be all set:

[server][root][~]# echo 'postgresql_enable="YES"' >> /etc/rc.conf

Start PostgreSQL so that we can create a new role.

[server][root][~]# /usr/local/etc/rc.d/010.pgsql.sh start

Now let’s create the new user/role that we will use to login remotely with. The -P option of createuser prompts you for a password that will authenticate the new user, -U is the username you’re connecting as (NOT the user you’re creating), and then monty is the name of the new user. My comments are in ( ).

[server][root][~]# su pgsql -c 'createuser -P monty'

Enter password for new role: montypass (monty's password)
Enter it again: montypass (confirm monty's password)
Shall the new role be a superuser? (y/n) y (let monty be a superuser)
CREATE ROLE

Now we can get PostgrSQL setup to allow us to connect from our IP address. To do that we need to edit two files, /usr/local/pgsql/data/postgresql.conf and /usr/local/pgsql/data/pg_hba.conf.

[server][root][~]# vi /usr/local/pgsql/data/postgresql.conf

Search for listen_addresses and change it to look like:

listen_addresses = '*'

Search for password_encryption and uncomment it (delete the #)

password_encryption = on

Search for port and uncomment it and make sure the port number is 5432

port = 5432

To enable pg_autovacuum search for the following items and make sure they are uncommented and set to on

stats_start_collector = on
stats_row_level = on
autovacuum = on

Now lets fire up vi with the second file we need to edit

[server][root][~]# vi /usr/local/pgsql/data/pg_hba.conf

Search for # "local" is for Unix domain socket (should be about line 66) and make the local section identical to what we have here. Beneath host all 127.0.0.1/32 md5 add your IP address or netblock. I am doing this on a local network so I just allow a connection from any of my internal IP Addresses.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         pgsql                             ident sameuser
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         192.168.1.0/24        md5
#IPv6 local connections:
host    all         all         ::1/128               md5

Open up (or create) /etc/periodic.conf to automatically perform daily backups (and tell it where and how to back them up) and turn off daily vacuum since we turned on autovacuum in the postgresql.conf file.

daily_pgsql_vacuum_enable="NO"
daily_pgsql_backup_enable="YES"
daily_pgsql_pgdump_args="-F c"
daily_pgsql_backupdir="~pgsql/backups"
daily_pgsql_savedays="7"

Now start/restart PostgreSQL

[server][root][~]# /usr/local/etc/rc.d/010.pgsql.sh restart

AndrewSN from #postgresql on Freenode said that there is a bug in the FreeBSD port:

You can fix the bug by copying install-sh and mkinstalldirs from the config dir of the distribution into /usr/local/lib/postgresql/pgxs/config if they don’t already exist there.

If you don’t do that, then you can’t build extensions that use pgxs.

Ok, that is all there is to getting PostgreSQL installed and configured for a remote connection. It wasn’t all that painful was it? I sure hope not :)

Next we will go about installing and setting up Lighttpd.

Rails Server Setup:

Part 1: New Server Setup
Part 2: Setup FreeBSD 6.x
Part 3: FreeBSD Usability
Part 4: Setup FreeBSD To Use Blowfish
Part 5: Install and Setup PostgreSQL
Part 6: Install Lighttpd on FreeBSD
Part 7: Install MySQL 5.x on FreeBSD
Part 8: Install Ruby On Rails with FreeBSD

Tracy Phillips

Archive for the ‘postgresql’ tag

Install and Setup PostgreSQL on FreeBSD

New Server Setup

Tag Cloud