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:
* Create a Jail
* Edit the Jail (this probably would be fine during creation too) and set "Sysctls" to
* Reboot FreeNAS
Inside the jail:
Set the root password:
Install packages. I took 9.4., should work for 9.3. too:
FYI, the contrib package contains the adminpack extension.
Edit /etc/login.conf
If you know that this is wrong for you change it, otherwise leave it.
Update the capability database:
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.
Set a password for user pgsql:
Create the mount point for the cluster and set owner (Perms 755):
On the FreeNAS host:
Create storage for the jail (/path/to/the/pool/postgresql, /mnt/postgres)
Inside the jail:
Update /etc/rc.conf
Initialise the cluster:
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'):
Now, configure network settings as one probably wants to access the server from the subnet and not only locally from the jail:
This is of course an example only.
Now start the server:
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