This guide is for an iocage install of PostgreSQL 11.1 and pgAdmin 4.3 under Freenas 11.1-U7.
It will take about 45 minutes to complete.
Credit goes to linuxito (https://www.linuxito.com/nix/1062-como-instalar-pgadmin-4-en-freebsd), whose pgAdmin4 setup I have adapted.
I've left some comments starting with a # to describe what the commands are doing.
KNOWN ISSUES: the pgadmin webui is not setup as a service. You will have full functionality however. I used a workaround for this.
Let's start with setting up PostgreSQL
1. create a dataset in the FreeNAS WebUI where you'll store the app
#for example, mine resides at /mnt/Data/apps/postgresql
#storage segregation (from the iocage jail itself) gives you the flexibility to store the databases on an SSD, separate from the main pool. It will greatly increase performance. And should you ever decide to nuke the jail, your db's will not be affected.
2. In your terminal, create a iocage jail called "postgresql" by running:
#change 11.1-RELEASE with 11.2-RELEASE if you are running the latest FreeNAS build
#change {IP Address} and {Default Gateway} with your environment specifics
#wait for all the plugins to install (can take a minute or two) and then run:
3. let's attach that dataset we created in step 1.
4. accessing the jail
#check the jail exists and that it is up. it should look something like
#if your jail isn't active, do
#otherwise
5. setup
inside the jail, we'll do the following
#autostart service with jail
#initial postgres setup
#this should then return
Success. You can now start the database server using: /usr/local/bin/pg_ctl -D /mnt/postgres/data -l logfile start
#check that the service is running
#Output should be
5.1 make the service reachable
#right now, all connections to the database will be refused. to change that - we need to edit postgresql.conf and pg_hba.conf
#search (using ctrl+w) for "listen_addresses" and change it as follows
#save and exit (ctr+x, y, enter)
#next
#add this line to the end of the document
#save and exit
5.2 creating a database user
#terminal should now change to $
#create database
#configure new user permissions
#terminal should show postgres=#
#exit setup
#restart service
You should now be able to connect with your client of choice, pointing it to JAIL_IP:5432 , logging in as bob to the database new_db
SETTING UP PgAdmin 4.3
This can be done in the same jail as PostgreSQL
#Write down the Jail's IP, you will need this later
6. let's install some dependencies first
7. create the following folders + permissions to be used by pgAdmin
8. setup a virtual environment for pgAdmin4
#create a virtual environment called "py3-venv-pgadmin"
#activate environment
#terminal should now change to [py3-venv-pgadmin] root@postgresql:~ #
9. install some more dependencies
10. install pgAdmin 4.3
#check for the latest version at https://ftp.postgresql.org/pub/pgadmin/pgadmin4/ (currently 4.3)
#wait for the download to complete and then
#wait for the install to finish
10.1 launch pgAdmin4 setup
#if all went well you should see a prompt to configure a user + password like so:
#the setup will be successful if you see the following
#press ctrl+c to navigate away
#We need to edit the above address from "127.0.0.1" to the jail's IP
#Search for
#and change it to your jail's IP
#save and exit
#and we're almost done !
10.2 to ensure pgadmin4 runs on jail startup
create a new script
copy the following:
#save and exit
11. testing the config
#note the squiggly thing "&". This will prevent the script from terminating when you detach the terminal.
If all goes well this should return:
#press ctrl+c
Open the pgAdmin4 web UI in your favourite browser, then authenticate with credentials from step 10.1
# add a new server
# give it a name under the general tab
# point it to the jail ip and enter the database credentials from step 5.2 under the connections tab
You should now be connected. Happy days.
Notes: you can later stop pgadmin by running ps , making a note of the process id and running kill PID.
To restart pgadmin, kill any running instances and then
#wait for the ip confirmation and press ctrl +c
#Alternatively, you can manually activate the environment (step 8) and run the app (step 11).
#This is because the script at step 10.2 which configures pgadmin as a service is not complete, as mentioned at the start of the guide. Any help with this would be appreciated!
You've reached the end of this guide. Grab yourself a drink!
It will take about 45 minutes to complete.
Credit goes to linuxito (https://www.linuxito.com/nix/1062-como-instalar-pgadmin-4-en-freebsd), whose pgAdmin4 setup I have adapted.
I've left some comments starting with a # to describe what the commands are doing.
KNOWN ISSUES: the pgadmin webui is not setup as a service. You will have full functionality however. I used a workaround for this.
Let's start with setting up PostgreSQL
1. create a dataset in the FreeNAS WebUI where you'll store the app
#for example, mine resides at /mnt/Data/apps/postgresql
#storage segregation (from the iocage jail itself) gives you the flexibility to store the databases on an SSD, separate from the main pool. It will greatly increase performance. And should you ever decide to nuke the jail, your db's will not be affected.
2. In your terminal, create a iocage jail called "postgresql" by running:
Code:
echo '{"pkgs":["postgresql11-contrib-11.1","postgresql11-server-11.1","postgresql11-client-11.1"]}' > /tmp/pkg.json iocage create --name "postgresql" -p /tmp/pkg.json -r 11.1-RELEASE ip4_addr="vnet0|{IP Addres}" defaultrouter="{Default Gateway}" boot="on" host_hostname="postgresql" vnet="on"
#change 11.1-RELEASE with 11.2-RELEASE if you are running the latest FreeNAS build
#change {IP Address} and {Default Gateway} with your environment specifics
#wait for all the plugins to install (can take a minute or two) and then run:
rm /tmp/pkg.json
3. let's attach that dataset we created in step 1.
iocage fstab -a postgresql /mnt/Data/apps/postgresql /mnt/postgres/data nullfs rw 0 0
4. accessing the jail
#check the jail exists and that it is up. it should look something like
Code:
iocage list +-----+------------+-------+--------------+---------------+ | JID | NAME | STATE | RELEASE | IP4 | +=====+============+=======+==============+===============+ +-----+------------+-------+--------------+---------------+ | 20 | postgresql | up | 11.1-RELEASE | 192.168.1.200 | +-----+------------+-------+--------------+---------------+
#if your jail isn't active, do
iocage start postgresql
#otherwise
iocage console postgresql
5. setup
inside the jail, we'll do the following
#autostart service with jail
sysrc postgresql_enable=YES
#initial postgres setup
sysrc postgresql_data=/mnt/postgres/data
sudo service postgresql initdb
#this should then return
Success. You can now start the database server using: /usr/local/bin/pg_ctl -D /mnt/postgres/data -l logfile start
sudo service postgresql start
#check that the service is running
sudo service postgresql status
#Output should be
Code:
pg_ctl: server is running (PID: 22055) /usr/local/bin/postgres "-D" "/mnt/postgres/data"
5.1 make the service reachable
#right now, all connections to the database will be refused. to change that - we need to edit postgresql.conf and pg_hba.conf
nano /mnt/postgres/data/postgresql.conf
#search (using ctrl+w) for "listen_addresses" and change it as follows
Code:
listen_addresses = '*'
#save and exit (ctr+x, y, enter)
#next
nano /mnt/postgres/data/pg_hba.conf
#add this line to the end of the document
Code:
host all all 192.168.1.0/24 md5
#save and exit
5.2 creating a database user
su postgres
#terminal should now change to $
createuser --interactive
Code:
Enter name of role to add: bob Shall the new role be a superuser? (y/n) y
#create database
createdb new_db
#configure new user permissions
psql
#terminal should show postgres=#
Code:
ALTER USER bob WITH ENCRYPTED PASSWORD 'INSERT_YOUR_PASSWORD_HERE'; GRANT ALL PRIVILEGES ON DATABASE new_db TO bob;
#exit setup
\q
exit
#restart service
service postgresql restart
You should now be able to connect with your client of choice, pointing it to JAIL_IP:5432 , logging in as bob to the database new_db
SETTING UP PgAdmin 4.3
This can be done in the same jail as PostgreSQL
#Write down the Jail's IP, you will need this later
6. let's install some dependencies first
pkg install sudo python36-3.6.7 py36-sqlite3-3.6.7_7 py36-gmpy py36-gmpy2 gmp
python3.6 -m ensurepip
pip3.6 install --upgrade pip
7. create the following folders + permissions to be used by pgAdmin
Code:
mkdir /var/lib/pgadmin chmod 770 /var/lib/pgadmin mkdir /var/log/pgadmin chmod 770 /var/log/pgadmin
8. setup a virtual environment for pgAdmin4
sudo pip install virtualenv
#create a virtual environment called "py3-venv-pgadmin"
Code:
virtualenv --system-site-packages --no-setuptools --python=python3.6 ~/py3-venv-pgadmin
#activate environment
source ~/py3-venv-pgadmin/bin/activate.csh
#terminal should now change to [py3-venv-pgadmin] root@postgresql:~ #
9. install some more dependencies
Code:
sudo pip install cryptography pyopenssl ndg-httpsclient pyasn1
10. install pgAdmin 4.3
#check for the latest version at https://ftp.postgresql.org/pub/pgadmin/pgadmin4/ (currently 4.3)
Code:
wget https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v4.3/pip/pgadmin4-4.3-py2.py3-none-any.whl
#wait for the download to complete and then
Code:
pip3 install pgadmin4-4.3-py2.py3-none-any.whl
#wait for the install to finish
10.1 launch pgAdmin4 setup
Code:
python ./py3-venv-pgadmin/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py
#if all went well you should see a prompt to configure a user + password like so:
Code:
NOTE: Configuring authentication for SERVER mode. Enter the email address and password to use for the initial pgAdmin user account:
#the setup will be successful if you see the following
Code:
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
#press ctrl+c to navigate away
#We need to edit the above address from "127.0.0.1" to the jail's IP
Code:
nano ./py3-venv-pgadmin/lib/python3.6/site-packages/pgadmin4/config.py
#Search for
Code:
DEFAULT_SERVER = '127.0.0.1'
#and change it to your jail's IP
#save and exit
#and we're almost done !
10.2 to ensure pgadmin4 runs on jail startup
Code:
echo 'pgadmin_enable="YES"' >> /etc/rc.conf
create a new script
nano /usr/local/etc/rc.d/pgadmin
copy the following:
Code:
#!/bin/sh # . /etc/rc.subr name="pgadmin" rcvar=${name}_enable load_rc_config ${name} : ${pgadmin_enable:="NO"} : ${pgadmin_user:="root"} : ${pgadmin_dir:="/root/py3-venv-pgadmin/lib/python3.6/site-packages/pgadmin4/"} . /root/py3-venv-pgadmin/bin/activate python3 /root/py3-venv-pgadmin/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py &
#save and exit
11. testing the config
Code:
python3 ~/py3-venv-pgadmin/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py &
#note the squiggly thing "&". This will prevent the script from terminating when you detach the terminal.
If all goes well this should return:
Code:
Starting pgAdmin 4. Please navigate to http://{JAIL_IP}:5050 in your browser.
#press ctrl+c
Open the pgAdmin4 web UI in your favourite browser, then authenticate with credentials from step 10.1
# add a new server
# give it a name under the general tab
# point it to the jail ip and enter the database credentials from step 5.2 under the connections tab
You should now be connected. Happy days.
Notes: you can later stop pgadmin by running ps , making a note of the process id and running kill PID.
To restart pgadmin, kill any running instances and then
Code:
service pgadmin start
#wait for the ip confirmation and press ctrl +c
#Alternatively, you can manually activate the environment (step 8) and run the app (step 11).
#This is because the script at step 10.2 which configures pgadmin as a service is not complete, as mentioned at the start of the guide. Any help with this would be appreciated!
You've reached the end of this guide. Grab yourself a drink!
Last edited: