PostgreSQL is a powerful, open source object-relational database with over two decades of continuous development. It is one of my favorite databases because of it’s community, functionality, and extendability. FreeBSD with built in ZFS support, jails, and several other feature is a great platfom to host your PostgreSQL instance.
At the time of this write up the latest version of PostgreSQL is 9.6 but you are not required to install the latest version. FreeBSD support several version of PostgreSQL. Simply search for all the latest versions and choose the one that your project supports.
# pkg search postgresql
Install your desired version of PostgreSQL.
# pkg install postgresql96-server
To have PostgreSQL startup when the server does you need to add postgresql_enable=“YES” to the /etc/rc.conf file:
# sysrc postgresql_enable="YES"
Before you start using postgres we need to initialize the cluster. By default the cluster will be placed in a data folder in /var/db/postgres directory.
# service postgresql initdb
If you wish to place your data in a differant location, such as a ZFS Datasets or a NFS (Network File Share), specify the -D flag then the path.
Now you can start PostgreSQL
# service postgresql start
Create users and databases
Log into the pgsql account created for you when you installed PostgreSQL.
# su postgres
Create a PostgreSQL user account. Supply the -s flag if you want the user to be a superuser. The -P flag will prompt you for a password..
$ createuser -P user_test
Create a database suppling the owner of the database.
$ createdb db_test -O user_test
Allowing Remote Connections
By default you will not be able to log into the database server unless you are on the local server. If your application is hosted on the same server as the database, you can skip this section.
Configure PostgreSQL to listen for database connections on all system IP addresses by adding the following line to postgresql.conf file. This file is located in the directory where the database is initialized. For FreeBSD 11.0-RELEASE and PostgreSQL 9.6 this location will be /var/db/postgres/data96/postgresql.conf unless you specified another location.
listen_addresses = '*'
Next you need to edit pg_hba.conf file in the same directory. Append the following configuration lines to give access to 10.1.1.0/32 network:
host all all 10.1.1.0/32 trust
Restart the service for the changes to take effect.
# /usr/local/etc/rc.d/postgresql restart
Be sure to install the postgres client on the host computer. Now test your connection from your other host computer.
$ psql -h -U user_test db_test
If you are capable you should be running your PostgreSQL instance on OpenZFS. If you are unfamiliar with ZFS please refer to the FreeBSD Handbook in the section The Z File System (ZFS). In short ZFS is one of the best if not the best File systems out. I has several features to protect and backup your files, such as copy-on-write, snapshots and block level checksum validation. You also have the ability to use a SSD as a secondary cache (L2ARC) to improve random reads and improve the performance of your database.
PostgreSQL In Jail
You also have the option to run your instance of postgres in a jail. A jail is a operating system-level virtualization the allows you to seperate your services from each other to protect your server. However this is outside the scope of this post.