How to install PostgreSQL in FreeBSD

As my first article, I will start with a brief introduction of myself:
I am Gonzalo, I live in Salta, a city located in the NW of Argentina near the border with Bolivia and Chile (in South America). I’m 39, and I’ve been working as a SysAdmin in an University for the last 12 years. I also have a 10-month son.

Why I choose PostgreSQL

In a word: easier.

When I begin working in this University, I’ve started with 5 or 6 servers running in regular PC; now I have a farm of more than 100 virtual machines running on real servers with the ESXi hypervisor of VMware. Also we had an LMS (Learning Managment System) running with the FAMP stack (freebsd, apache, mysql, php) which was sloooooow.

Some day the director said, and we all agree: “stop of losing your time trying to tune mysql and tuning the queries. Find another LMS“. Then we changed to moodle, and someone suggest to give PostgreSQL as the database and opportunity. Best choice ever; and you’ll see why soon.

Let’s get start

For those who doesn’t have the time to read, here are the commands need to run, and skip the next section:

# su -
# cd /usr/ports/databases/postgresql14-server
make config install clean

follow the screen instructions…

# cat >> /etc/login.conf
postgres:\
         :lang=en_US-UTF8:\
         :setenv=LC_COLLATE=C:\
         :tc=default:  
# cap_mkdb /etc/login.conf
# echo 'postgresql_class="postgres"' >> /etc/rc.conf
# /usr/local/etc/rc.d/postgresql enable
# /usr/local/etc/rc.d/postgresl start
# passwd postgres
# su - postgres
$ psql

Skip the next section 😉

The previous section, explained

There is two ways you can install software on FreeBSD: the port system where you compile the things you need; or the package system, where you install binary packages as commonly done on Linux distributions. Actually, there are another two ways: one is mix both systems, compile some packages and install some other precompiled ones, that I don’t recommend; and the other is to go search the software homepage, download and install manually, for some programs is the only option available.

I prefer to compile and install the software from the port tree because of the flexibility: I can choose different options that precompiled packages doesn’t include or they include but I don’t want it. But when you are short on time, of course with precompiled packages you will finish sooner.

As I need to do several task as the superadmin, I just will turn to root with su (otherwise, you can run sudo <command> every time):

Here’s the commands to copy and paste:

su -

with that, you change your user to root, and also it’s like login as root.

Then change to the ports tree, to the database directory and to the postgresql14-server and start the magic:

cd /usr/ports/databases/postgresql14-server
make config install clean

If you don’t have those directories you are probably missing the ports tree, you can download (as root) with: portsnap fetch extract. Also, if you installed FreeBSD some time ago, it’s a good idea to update the ports tree: portsnap fetch update. I’m not doing because I’ve started with a fresh new install.

Then the system will show the available options for do the compilation:

Move with the arroy keys and select or unselect the desired option with space. When you finish move to Ok and hit enter.

building documentation

In my case, I keep the default choices, but deselect the “Docs” options because when I don’t know something I search the web for answers. You can choose “optimized_cflags”, but in my particular case didn’t notice any performance improvement.

Depending on the software you installed earlier the system will install more or less dependencies, so the options dialog may popup asking for you to choose some other options, for example:

And then you’ll see the actual compiling of packages. The system will start with the packages postgres depend on and finally will compile Postgres itself:

You can go for a coffee now, but another options screen could popup, so get back from time to time. At the end of the process you will get this screen:

Configure, initialize and start the service

But there are some other previous messages you could miss if you went for that coffee, so scroll up to this part:

For those that aren’t familiar with vim (like me), we are using the easy editor (ee) of FreeBSD to add those lines to login.conf:

# ee /etc/login.conf

move to the end of the file and add:

postgres:\
         :lang=en_US-UTF8:\
         :setenv=LC_COLLATE=C:\
         :tc=default:  

Exit with escape, a, a (the first ‘a’ is for exit, the second one is for save the file). Run:

# cap_mkdb /etc/login.conf
# echo 'postgresql_class="postgres"' >> /etc/rc.conf

The following commands are for enable the service, initialize the database and start the service:

# /usr/local/etc/rc.d/postresql enable

Alternatively you can add “postgresql_enable=YES” to /etc/rc.conf using your favorite text editor.

# /usr/local/etc/rc.d/postgresql initdb

And you are ready to start the PostgreSQL service and using your new database:

# /usr/local/etc/rc.d/postgresql start

Using and basic configuration

The basic command line client is psql, but initially there is only the postgresql role in the database, if you try to run as any user (like root or your own user) you will get an error, so first su to postgres and then launch psql:

And this is (almost) it. The are a few tasks missing

You can’t su to postgres from your regular user

It’s because the system postgres user doesn’t have a password yet, run as root:

passwd postgres

You need remote access, and a fancy PostgreSQL client

psql it’s not the prettiest PostgreSQL client; also you probably need to connect from other (say, some web application) server. In the default installation, PostgreSQL only listen for local connections.

First you need to change the postgresql.conf file to listen for remote connections, the main configuration file is stored in the data directory (default in FreeBSD is /var/db/postgres/data14), so open with my favorite text editor, ee /var/db/postgres/data14/postgresql.conf and uncomment and change the line

#listen_address = 'localhost'

to

listen_address = '*'

Also, you need to edit another file that controls client authentication, pg_hba.conf, and add a line in the following format:

type       database  user  IP-address/IP-mask  auth-method 

In my example, I’m gonna permit my user ‘gonz’ to connect only from my host ip 192.168.1.3 (I can permit my user from all my LAN by changing the mask to /24), to the database ‘pepe’ with an md5 hash of my password, this is the line I’m adding:

host       pepe  gonz  192.168.1.3/32  md5 

Now I restart the PostgreSQL service and I’m ready to start working with the DB

/usr/local/etc/rc.d/postgres restart

Where to go from here

First of all, there is excellent documentation on the PostgreSQL website, covering everything you should know about this database:

https://www.postgresql.org/docs/14/index.html

For some tunning to improve performance, there is a good wizard called pgtune: https://pgtune.leopard.in.ua/#/

But doesn’t blindly trust, my advice is first to conduct a benchmark to establish a baseline with the default parameters, you can use pgbench, already included with the postgres port. Then follow the wizard, change the parameters to the suggested values, run another benchmark repeat playing with different values until you are satisfied with the performance.