Introduction
PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and on standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.
PostgreSQL implements the majority of the SQL:2011 standard, is ACID-compliant and transactional (including most DDL statements) avoiding locking issues using multiversion concurrency control (MVCC), provides immunity to dirty reads and full serializability; handles complex SQL queries using many indexing methods that are not available in other databases; has updateable views and materialized views, triggers, foreign keys; supports functions and stored procedures, and other expandability, and has a large number of extensions written by third parties. In addition to the possibility of working with the major proprietary and open source databases, PostgreSQL supports migration from them, by its extensive standard SQL support and available migration tools. Proprietary extensions in databases such as Oracle can be emulated by built-in and third-party open source compatibility extensions. Recent versions also provide replication of the database itself for availability and scalability.
PostgreSQL is cross-platform and runs on many operating systems including Linux, FreeBSD, Solaris, and Microsoft Windows. Mac OS X, starting with OS X 10.7 Lion, has the server as its standard default database in the server edition, and PostgreSQL client tools in the desktop edition. The vast majority of Linux distributions have it available in supplied packages.
PostgreSQL is developed by the PostgreSQL Global Development Group, a diverse group of many companies and individual contributors. It is free and open-source software, released under the terms of the PostgreSQL License, a permissive free-software license. (Source: Wikipedia)
Requirements
Installation
sudo pkg install postgresql94-server
Configuration
It is very important that you change the UID for the pgsql user (replace ip with the three digits after the last dot in your jail IP address, prepend zeros if necessary so that the final UID is always 5 digits. Ex. 192.168.1.1 would be 70001, 192.168.1.12 would be 70012, 192.168.1.111 would be 70111)
sudo pw usermod pgsql -u 70ip sudo chown -R pgsql /usr/local/pgsql
Then enable PostgreSQL, initialize the database, and start it
sudo sysrc postgresql_enable=YES sudo service postgresql initdb sudo service postgresql start
In order to login with pgAdmin under the pgsql username (so that you can administer the database) you have to set a password for that account and allow password based logins from anywhere (assuming you are connecting from multiple places). First, change to the pgsql account:
sudo su - pgsql
then connect to the postgres database
psql postgres
change the password
ALTER USER pgsql WITH PASSWORD 'replace_with_new_password';
quit psql
\q
and return to your user account
exit
Allowing logins from anywhere requires adding a line to pg_hba.conf. Open the file with
sudo ee /usr/local/pgsql/data/pg_hba.conf
and add the following line toward the bottom
host all all 0.0.0.0/0 md5
and save and exit the file. This change requires a restart:
sudo service postgresql restart
Now you should be able to connect with pgAdmin or otherwise.
Running PostgreSQL in a jail
Note: you can only change these settings if you have access to the host system.
First, change a few parameters in sysctl.conf and loader.conf (this requires a reboot):
sudo vi /etc/sysctl.conf kern.ipc.shmall=32768 kern.ipc.shmmax=134217728 sudo vi /boot/loader.conf kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256
Finally, we still have to allow sysvipc in the jail. With qjail, this is achieved by running the following command (when the jail is stopped):
sudo qjail config -y jailname