Setting up Postgresql in FreeNAS using iocage

In this guide, I will show you how to set up postgresql 10 database on FreeNAS using iocage. Before we begin, let's create a dataset under FreeNAS, this is where our postgresql data will reside. If you are not sure how to do that, please google it online. Launch the shell of your FreeNAS or log into your FreeNAS via SSH using the root account. Using the command below to create a jail with an image of FreeBSD 11.1-RELEASE. --name is the name of the jail and boot sets the jail to autostart

iocage create -r 11.1-RELEASE --name database boot=on

Next, we will set up the network interface of the jail. my network interface is igb0, while yours may be something else. And you will need to change the IP and network mask according to your own network setup.

iocage set ip4_addr="igb0|192.168.1.100/24" database

The downside of using the above command is that the IP assigned will not be shown in your router. For those of you who wants a static mapping of IP, you may want to consider using vnet and dhcp enabled. With the DHCP enabled, set a static IP on your router.

iocage set vnet=on database
iocage set dhcp=on database
iocage set bpf=yes database

Enable the raw socket of the jail so that you can use certain network commands(e.g. ping) for debugging purpose.

iocage set allow_raw_sockets=1 database

Log into your newly created jail

iocage console database

In the jail's console, let's install the postgresql packages When you run the pkg command for the first time, you will be prompted to install the FreeBSD package manager, select y to install it.

pkg install sudo postgresql10-contrib-10.3 postgresql10-server-10.3_1

After we have done installing postgresql, we will need to configure it.

sysrc postgresql_enable=YES #Autostart
sysrc postgresql_data=/mnt/postgres/data #path to be used by the initdb 

Still in the jail's console, create the postgres directory in /mnt. Set the owner:group to postgres

mkdir /mnt/postgres
sudo chown postgres:postgres /mnt/postgres

Now we need to exit the jail's console to return to the console of FreeNAS. Next, we need to mount the dataset that we have created in FreeNAS onto the jail that we have created. The following command will create an entry in the fstab for this jail. You will not be able to find this fstab in the jail itself, this fstab is kept by iocage.

iocage fstab -a database "/path/to/dataset /mnt/postgres nullfs rw 0 0"

Restart the jail so that the dataset is mounted to the mounting point that we have just created.

iocage restart database

We need to go back into the jail's console to initialize the postgresql db

iocage console database

We now initialize the database and start the service

sudo service postgresql initdb
sudo service postgresql start

if you were to run

sudo service postgresql status

you will be able to see that your postgresql is running

root@database:~ # service postgresql status
pg_ctl: server is running (PID: 25178)
/usr/local/bin/postgres "-D" "/mnt/postgres/data"

Let's check if we can log into the DB

su postgres
psql

you will be able to see

root@database:/mnt/postgres # su postgres
$ psql
psql (10.3)
Type "help" for help.

postgres=# 

In order for our postgresql database to be available outside of localhost, we will need to change some configurations. Change the listening address in postgresql.conf to * so that it will listen for connections from all addresses.

listen_addresses = '*'

Add the following to pg_hba.conf

host all all 192.168.1.0/24 md5 #all the clients in the same network will be able to connect to the database using password authentication. 

Both postgresql.conf and pg_hba.conf can be found in /mnt/postgres/data Congratulation! now you have postgresql 10 running on you FreeNAS. You may proceed to create a new DB to be used for your projects.