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.
Install your desired version of PostgreSQL.
To have PostgreSQL startup when the server does you need to add postgresql_enable=”YES” to the /etc/rc.conf file:
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.
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
Log into the pgsql account created for you when you installed PostgreSQL.
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..
Create a database suppling the owner of the database.
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.
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:
Restart the service for the changes to take effect.
Be sure to install the postgres client on the host computer. Now test your connection from your other host computer.
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.
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.