PostgreSQL configuration

1. References

  • FreeNAS 9.2.1.6+
  • Permanent storage (preferably a ZFS volume)

2. References

3. Create PostgreSQL jail and install PostgreSQL server

Create a new jail (e.g. name sql) and add storage to the jail so we can keep our data in a secure place. I'll mount my storage in '/mnt/sql' inside the jail.

SSH to your FreeNAS, enter the jail (jexec sql tcsh) and install PostgreSQL package using pkg manager.

pkg update && pkg upgrade
pkg autoremove
pkg install postgresql94-server postgresql94-contrib
  • Note: The first time it will have to upgrade repositories, so don't worry if it downloads a lot of data.
  • Replace version of the server with latest/preferred version

Set listen_addresses and port (default is localhost:5432) in the file '/mnt/sql/pgsql/data/postgresql.conf' if you prefer different values. And edit '/etc/rc.conf' so PostgreSQL starts automatically during start of the jail.

echo 'postgresql_enable="YES"' >> /etc/rc.conf
echo 'postgresql_data="/mnt/sql/pgsql/data"' >> /etc/rc.conf
echo 'postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C"' >> /etc/rc.conf

4. NAS server configuration

To run PostgreSQL server inside jail requires some additional configurations updates from main (web) configuration page of the NAS server.

System->Sysctls - add variable security.jail.sysvipc_allowed with value 1
System->Tunables - add variable kern.ipc.semmni with value 256
System->Tunables - add variable kern.ipc.semmns with value 512
System->Tunables - add variable kern.ipc.semmnu with value 256
System->Tunables - add variable kern.ipc.seumr with value 40
Jail->Sysctls - add variable allow.sysvipc=true

5. Prepare initial database setup

Create directory for database storage and set owner as pgsql, after initialize of the database.

mkdir -p /mnt/sql/pgsql/data
chown -R pgsql:pgsql /mnt/sql/pgsql/data
service postgresql initdb
  • Note: Directory have to be the same as in the configuration file 'rc.conf' (see step 1)

6. Set up authentication

Set authentication for allowed connection for custom databases and users. This should be updated every time when databases or users are added or removed in the file '/mnt/sql/pgsql/data/pg_hba.conf'.

#      TYPE  DATABASE  USER  ADDRESS      METHOD
# e.g. host  all       all   10.0.0.0/24  md5
  • Note: Don't forget to reload PostgreSQL configuration after the changes - 'service postgresql reload'.

7. Configure users and databases

Command to create postgresql user

su pgsql
createuser --pwprompt --encrypted --no-createrole --no-createdb username

Command to create database

su pgsql
createdb --encoding=UTF8 --owner=username databasename

8. Start and check PostgreSQL service

Start PosgreSQL server

service postgresql start
  • Note:* Error 'FATAL: could not create shared memory segment: Function not implemented' means, that jail was not properly configured in the step 2).

Check connection for created user

psql -h listen_addresses -p port -U username databasename

Comments