{"id":262,"date":"2018-06-23T22:52:19","date_gmt":"2018-06-23T14:52:19","guid":{"rendered":"https:\/\/codestrian.com\/?p=262"},"modified":"2020-05-24T11:20:55","modified_gmt":"2020-05-24T03:20:55","slug":"setting-up-postgresql-in-freenas-using-iocage","status":"publish","type":"post","link":"https:\/\/codestrian.com\/index.php\/2018\/06\/23\/setting-up-postgresql-in-freenas-using-iocage\/","title":{"rendered":"Setting up Postgresql in FreeNAS using iocage"},"content":{"rendered":"

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.<\/strong> 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<\/strong> is the name of the jail and boot<\/strong> sets the jail to autostart<\/p>\n

iocage create -r 11.1-RELEASE --name database boot=on\n<\/code><\/pre>\n
Next, we will set up the network interface of the jail. my network interface is igb0<\/strong>, while yours may be something else. And you will need to change the IP and network mask according to your own network setup.<\/p>\n
iocage set ip4_addr=\"igb0|192.168.1.100\/24\" database\n<\/code><\/pre>\n
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.<\/p>\n
iocage set vnet=on database\niocage set dhcp=on database\niocage set bpf=yes database\n<\/code><\/pre>\n
Enable the raw socket of the jail so that you can use certain network commands(e.g. ping) for debugging purpose.<\/p>\n
iocage set allow_raw_sockets=1 database\n<\/code><\/pre>\n
Log into your newly created jail<\/p>\n
iocage console database\n<\/code><\/pre>\n
In the jail's console, let's install the postgresql packages When you run the pkg<\/strong> command for the first time, you will be prompted to install the FreeBSD package manager, select y<\/strong> to install it.<\/p>\n
pkg install sudo postgresql10-contrib-10.3 postgresql10-server-10.3_1\n<\/code><\/pre>\n
After we have done installing postgresql, we will need to configure it.<\/p>\n
sysrc postgresql_enable=YES #Autostart\nsysrc postgresql_data=\/mnt\/postgres\/data #path to be used by the initdb \n<\/code><\/pre>\n
Still in the jail's console, create the postgres<\/strong> directory in \/mnt<\/strong>. Set the owner:group to postgres<\/p>\n
mkdir \/mnt\/postgres\nsudo chown postgres:postgres \/mnt\/postgres\n<\/code><\/pre>\n
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<\/strong> for this jail. You will not be able to find this fstab in the jail itself, this fstab is kept by iocage.<\/p>\n
iocage fstab -a database \"\/path\/to\/dataset \/mnt\/postgres nullfs rw 0 0\"\n<\/code><\/pre>\n
Restart the jail so that the dataset is mounted to the mounting point that we have just created.<\/p>\n
iocage restart database\n<\/code><\/pre>\n
We need to go back into the jail's console to initialize the postgresql db<\/p>\n
iocage console database\n<\/code><\/pre>\n
We now initialize the database and start the service<\/p>\n
sudo service postgresql initdb\nsudo service postgresql start\n<\/code><\/pre>\n
if you were to run<\/p>\n
sudo service postgresql status\n<\/code><\/pre>\n
you will be able to see that your postgresql is running<\/p>\n
root@database:~ # service postgresql status\npg_ctl: server is running (PID: 25178)\n\/usr\/local\/bin\/postgres \"-D\" \"\/mnt\/postgres\/data\"\n<\/code><\/pre>\n
Let's check if we can log into the DB<\/p>\n
su postgres\npsql\n<\/code><\/pre>\n
you will be able to see<\/p>\n
root@database:\/mnt\/postgres # su postgres\n$ psql\npsql (10.3)\nType \"help\" for help.\n\npostgres=# \n<\/code><\/pre>\n
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<\/strong> to * so that it will listen for connections from all addresses.<\/p>\n
listen_addresses = '*'\n<\/code><\/pre>\n
Add the following to pg_hba.conf<\/strong><\/p>\n
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. \n<\/code><\/pre>\n
Both postgresql.conf<\/strong> and pg_hba.conf<\/strong> can be found in \/mnt\/postgres\/data<\/strong> Congratulation! now you have postgresql 10 running on you FreeNAS. You may proceed to create a new DB to be used for your projects.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[39,38,40,11],"_links":{"self":[{"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/posts\/262"}],"collection":[{"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/comments?post=262"}],"version-history":[{"count":20,"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/posts\/262\/revisions"}],"predecessor-version":[{"id":483,"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/posts\/262\/revisions\/483"}],"wp:attachment":[{"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/media?parent=262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/categories?post=262"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codestrian.com\/index.php\/wp-json\/wp\/v2\/tags?post=262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}