Guide - PostgreSQL 11.1 and pgAdmin 4.3 in iocage

dknm

Dabbler
Joined
Aug 27, 2018
Messages
25
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:



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:

clueluzz

Dabbler
Joined
May 17, 2019
Messages
24
Hi, I've tried following your guide and I'm lost. I've already created the cage and installed postgresSQL. In the step, "service postgresql initdb" I get the following from shell:

root@resolvedb:~ # sudo service postgresql initdb
sudo: Command not found.

root@resolvedb:~ # service postgresql initdb
The files belonging to this database system will be owned by user "pgsql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /mnt/posttank/iocage/jails/resolvedb ... initdb: could not create directory "/mnt/posttank": Permission denied

What am I doing wrong?
 

dknm

Dabbler
Joined
Aug 27, 2018
Messages
25
1st thing I would check is if you've got the sudo package installed in the jail - pkg install sudo.

You might need to run postgres as super user. See the 3rd post down in this thread.

Hope it helps.
 

clueluzz

Dabbler
Joined
May 17, 2019
Messages
24
Thanks @dknm. I just needed to add the user. Everything done. Now trying to connect to the postgres from another computer. I’ve set the IP, but no fish. FYI, I’m trying to connect using DaVinci Resolve.
 

dknm

Dabbler
Joined
Aug 27, 2018
Messages
25
Glad it worked.

Check point 5.1 again , issues around that area are likely to be preventing intranet access.

If you plan to connect remotely (DR software is not hosted on the same network) it won’t work until you forward the port in your router. Disclaimer: it’s bad for security
 

clueluzz

Dabbler
Joined
May 17, 2019
Messages
24
IT'S ALIVE! Yes, had to redo 5.1 because my file location was far more obscure and 5.2 because I forgot to include the ; when making the user permissions. DR connected to the database no problems and I'm already collaborating with multiple clients on-site. No plans to do remote sharing and am reluctant to becoz of security. Maybe someday if a client wants to have remote access while we work, I'll have a completely separate server for that.
 

RKMStudios

Dabbler
Joined
Feb 28, 2019
Messages
14
Hello, I am having a hard time with this step:

iocage fstab -a postgresql /mnt/Data/apps/postgresql /mnt/postgres/data nullfs rw 0 0

I understand that the paths need to change, but I must be doing something wrong here, because I cannot get the destination path to be correct. This what I am currently trying:

iocage fstab -a postgresql /mnt/Athena_Pool/apps/postgresql /mnt/postgres/data nullfs rw 0 0

And I get this error from shell:

Destination: /mnt/Athena_Pool/iocage/jails/postgresql/root/mnt/postgres/data does not exist or is not a directory.

What am I missing here? Thank you!
 

dknm

Dabbler
Joined
Aug 27, 2018
Messages
25
Hey

you can replicate this step from the web-ui, adding storage to the jail - pay attention to the path.

1. are you running your iocage commands as root?
2. run pkg info to confirm that Postgres has indeed installed (I suspect it hasn't).
3. I strongly recommend you run the guide with a 11.3 jail template and the latest version of the software (pkg search postgresql11)
 

RKMStudios

Dabbler
Joined
Feb 28, 2019
Messages
14
Okay I am going back through carefully here. I deleted the previous jail and made sure that root has sudo privileges. I am on 11.3 U1.

Starting from the beginning, I made a dataset called "apps/postgreql" in my pool: Athena_Pool. Next I successfully ran:

Code:
echo '{"pkgs":["postgresql11-contrib-11.1","postgresql11-server-11.1","postgresql11-client-11.1"]}' > /tmp/pkg.json


Then If I run this command to create the jail...

Code:
iocage create --name "postgresql" -p /tmp/pkg.json -r 11.3-RELEASE ip4_addr="vnet0|10.0.1.15/24" defaultrouter="10.0.1.1" boot="on" host_hostname="postgresql" vnet="on"


...after the installation completes, I get a set of DNS related errors. I can confirm that the jail cannot properly connect to the internet by attempting pings. I can modify the settings of the jail and if I enable DHCP and raw sockets, the jail can ping from that same IP. Can I now force that Postgres PKG install after fixing the network settings, or do I need to get it right in the iocage creation? Sorry I am a real noob at this and I appreciate the help!
 

dknm

Dabbler
Joined
Aug 27, 2018
Messages
25
yes, you can install packages at any time inside a jail.

creating a jail & declaring a 'shopping list' json file of things to install, all in one step, is purely for convenience.
 

RKMStudios

Dabbler
Joined
Feb 28, 2019
Messages
14
yes, you can install packages at any time inside a jail.

creating a jail & declaring a 'shopping list' json file of things to install, all in one step, is purely for convenience.

Awesome, yes I just successfully installed the pkg management and sudo. I used your command,
Code:
pkg search postgresql11
to get a list of all the files in the repository. I found contrib-11.6. Installing now.
 

RKMStudios

Dabbler
Joined
Feb 28, 2019
Messages
14
Alright, so now I'm still getting that destination path issue. Trying to set it up through the UI. Is this correct:

Screen Shot 2020-03-30 at 2.44.40 PM.png
 

RKMStudios

Dabbler
Joined
Feb 28, 2019
Messages
14
Just following up here. I was able to get this all to work and have been successfully using pgsql for months without issue. Today I had a power outage and my battery backup allowed me to safely shut down the server, but when I restarted the server, I was unable to get pgadmin running again.

Postgres seems to run in the cage fine , but I am getting this error when attempting to restart pgadmin:

Code:
root@postgresql:~ # source ~/py3-venv-pgadmin/bin/activate.csh
(py3-venv-pgadmin) root@postgresql:~ # python3 ~/py3-venv-pgadmin/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py &
[1] 13743
(py3-venv-pgadmin) root@postgresql:~ # Starting pgAdmin 4. Please navigate to http://10.0.1.15:5050 in your browser.
 * Serving Flask app "pgadmin" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: off
2020-08-12 12:23:05,598: ERROR  flask.app:      Error starting the app server: (<class 'OSError'>, OSError(49, "Can't assign requested address"), <traceback object at 0x80e5c5f08>)


I've noticed that my iocage ip is 10.0.1.17, idk if that's my issue... It won't let me set it to 10.0.1.15 as the network interface it is using is set to that.
 
Top