[How-to] Install PostgreSQL in 9.3

Status
Not open for further replies.

Roger Wilco

Explorer
Joined
Jul 17, 2014
Messages
65
Hi,

this is a step by step tutorial on installing PostgreSQL in a jail.
It will use a jail for the server binaries and a dataset for the database cluster (the data stuff).

On the FreeNAS host:
* Create user 'pgsql' UID 5432, primary group pgsql, nologin, nonexistent, disable password login
* Create a dataset, UNIX, (Blocksize 8k), e.g. '/path/to/the/pool/postgres'
* Set dataset permissions: owner + group: pgsql, Perms:755
* Create loader tunables:
Code:
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256

* Create a Jail
* Edit the Jail (this probably would be fine during creation too) and set "Sysctls" to
Code:
allow.raw_sockets=true,allow.sysvipc=1

* Reboot FreeNAS


Inside the jail:
Set the root password:
Code:
passwd

Install packages. I took 9.4., should work for 9.3. too:
Code:
pkg update
pkg upgrade
pkg install postgresql94-server-9.4.1
pkg install postgresql94-contrib-9.4.1

FYI, the contrib package contains the adminpack extension.

Edit /etc/login.conf
Code:
#
# This is for PostgreSQL
#
postgres:\
	:lang=en_US.UTF-8:\
	:setenv=LC_COLLATE=C:\
	:tc=default:

If you know that this is wrong for you change it, otherwise leave it.

Update the capability database:
Code:
cap_mkdb /etc/login.conf


Now, the server-package installed a few steps ago, created a user and group 'pgsql' with some UID. I didn't like it and wanted the UID to be the listening port. Therefore I've deleted the user and group (the one in the jail!) and created a new one.
Code:
pw userdel pgsql
pw groupdel pgsql
pw groupadd -n pgsql -g 5432
pw useradd -n pgsql -u 5432 -g pgsql -s /bin/sh -c "PostgreSQL Server" -d /mnt/postgres


Set a password for user pgsql:
Code:
passwd pgsql


Create the mount point for the cluster and set owner (Perms 755):
Code:
mkdir /mnt/postgres
chown pgsql:pgsql /mnt/postgres



On the FreeNAS host:
Create storage for the jail (/path/to/the/pool/postgresql, /mnt/postgres)


Inside the jail:
Update /etc/rc.conf
Code:
echo 'postgresql_enable="YES"' >> /etc/rc.conf
echo 'postgresql_data="/mnt/postgres/data"' >> /etc/rc.conf
echo 'postgresql_flags="-w -s -m fast"' >> /etc/rc.conf
echo 'postgresql_class="postgres"' >> /etc/rc.conf
echo 'postgresql_profiles=""' >> /etc/rc.conf


Initialise the cluster:
Code:
/usr/local/etc/rc.d/postgresql initdb

This will hopefully finish without errors ... :)

Set a password for the database default user 'pgsql' (FYI, this is a different user than the UNIX 'pgsql'):
Code:
/usr/local/bin/psql -U pgsql -d postgres
ALTER USER pgsql WITH PASSWORD 'the password can be the same as the one of the UNIX user';
\q


Now, configure network settings as one probably wants to access the server from the subnet and not only locally from the jail:
Code:
vi /mnt/postgres/data/pg_hba.conf
# IPv4 LAN connections:
host    all             all             192.168.1.0/24          md5

vi /mnt/postgres/data/postgresql.conf
listen_addresses = '*'

This is of course an example only.

Now start the server:
Code:
service postgresql start
 

Roger Wilco

Explorer
Joined
Jul 17, 2014
Messages
65
Hi ops,

is there any reason, why this howto is not moved into the "How-To Guides/Jails" section?
 

memel.parduin

Dabbler
Joined
Feb 13, 2012
Messages
42
On my quest installing nginx/DAViCal/Postgresql I tried creating a jail with
Code:
allow.raw_sockets=true,allow.sysvipc=1

which rendered my system unresponsive. I didn't however create loader tunables. Changing them afterwards and restarting the jail caused a reboot of the entire system.
 

Roger Wilco

Explorer
Joined
Jul 17, 2014
Messages
65
Hm, the described configuration has been running here for several months without any problems. But this is a home installation - nothing is really under load...
 

STREBLO

Patron
Joined
Oct 23, 2015
Messages
245
What exactly are you doing in this step ?

Initialise the cluster:
Code:
/usr/local/etc/rc.d/postgresql initdb

Nvm, got it.
 
Last edited:

ChrisFor

Dabbler
Joined
Aug 30, 2013
Messages
18
I think its a really bad idea to set allow.sysvipc to 1, cause it compromises the idea of a jail. See here.
It was needed for allowing shared memory via System V according to here.
Apart from that its not needed anyway anymore since PostgreSQL 9.3. uses shared memory via POSIX according to here.

Current Version available via 'pkg search' is 95. So there should be no need to set that anymore.
Also if setting kern.ipc.semmn? is needed is unclear, anyone?

That should remind ourselves to check every sysctl we enable on the host, just to get something going in a jail.
Thankfully this one is solved.
 

ChrisFor

Dabbler
Joined
Aug 30, 2013
Messages
18
I am so not sure about the postgres docu, there is nothing about Posix in here for 9.3
 
Last edited:

Roger Wilco

Explorer
Joined
Jul 17, 2014
Messages
65
Hi,

I think its a really bad idea to set allow.sysvipc to 1, cause it compromises the idea of a jail. See here.

I'd say it depends on what you're trying to achieve, doesn't it?
If security is your biggest concern, then it might be a bad idea to use jails at all.

But if your scenario is that of a home user, then you probably would like the convenience of separating services via jails. The security issues are in this case rather theoretical, and therefore IMO acceptable.

It was needed for allowing shared memory via System V according to here.
Apart from that its not needed anyway anymore since PostgreSQL 9.3. uses shared memory via POSIX according to here.

And here.

Current Version available via 'pkg search' is 95. So there should be no need to set that anymore.
Also if setting kern.ipc.semmn? is needed is unclear, anyone?

They say it is: link
 

Roger Wilco

Explorer
Joined
Jul 17, 2014
Messages
65

giblesnot

Cadet
Joined
May 17, 2016
Messages
2
RESOLVED
Thank you for the detailed steps!

Since this is the most current guide I can find I've been trying to follow these steps on Freenas 9.10 with postgres 9.5.3.

I'm hitting this issue with initdb:

Code:
selecting dynamic shared memory implementation ... posix                     

creating configuration files ... initdb: could not change permissions of "/mnt/p
ostgres/data/postgresql.conf": Operation not permitted                       
initdb: removing data directory "/mnt/postgres/data" 


I've tried repeating
Code:
chown -R psql:psql /mnt/postgres/

I also tried deleting the directory, dataset, etc and re-creating (even deleted the jail and tried from the 'create jail' step down again.

I'm pretty new to BSD, so I anticipate this is user error. I've tried googling and the closest fit is this: https://github.com/sameersbn/docker-postgresql/issues/22. However, Darwin+Docker != Freebsd+Jail so I don't know if that is the issue.

Thank you in advance for any advice!

My freenas usergroup was names psql, not pgsql. Too many times typing that into command line I guess.
 
Last edited:

ChrisFor

Dabbler
Joined
Aug 30, 2013
Messages
18
Hi,
I'd say it depends on what you're trying to achieve, doesn't it?
If security is your biggest concern, then it might be a bad idea to use jails at all.
But if your scenario is that of a home user, then you probably would like the convenience of separating services via jails. The security issues are in this case rather theoretical, and therefore IMO acceptable.
And here.
They say it is: link

I agree, it definitly depends on what you're trying to achieve. Security does not have to be the biggest concern for a home user, but that does not mean a home user should throw it out of the window for 17% performance penalty on a single service when your system offers quite a lot of services. I for instance have emby, transmission, owncloud and boinc just to name a few. I am very happy about having them in a jail, so that if I ever expose a single service to the internet (e.g. via portmapping towards my home-router, using dyndns and the like to access it) what I definitly don't want is a jail that can compromise the whole FreeNAS-box, just because root in a Jail can do the same as root on the host. And this is the implication of setting allow.sysvipc=1. This basically brings jail-security down to docker security.
But apart from all these considerations, as a home-user do you even recognize 17% speed penalty?
At least with bhyve coming up soon, it should be no biggie to have postgresql running under linux in a vm.
 

Filter412

Cadet
Joined
Sep 15, 2011
Messages
3
Is there a reason Version 9.6 won't work as opposed to Version 9.4 as described in your how-to?

I followed your instructions but I utilized the most recent version and was unable to get it to initdb.

Started over with 9.4 and it worked just fine.
 

tngri

Dabbler
Joined
Jun 7, 2017
Messages
39
Hi and thanks for this tutorial.
I would appreciate an update to install postgresql10 on last Freenas 11.1, following actual indication give me an error : su: no direcotory on any command like service postgresql status or the initdb (I've made to changes to use now default user postgresql instead of pgsql
 

mow4cash

Contributor
Joined
Jan 20, 2017
Messages
132
Hi and thanks for this tutorial.
I would appreciate an update to install postgresql10 on last Freenas 11.1, following actual indication give me an error : su: no direcotory on any command like service postgresql status or the initdb (I've made to changes to use now default user postgresql instead of pgsql
Did you figure out how to get it running?
 

tngri

Dabbler
Joined
Jun 7, 2017
Messages
39
No, so I decided to move to VM/docker
 
Status
Not open for further replies.
Top