PostgreSQL

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

Sudo

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

References

PostgreSQL Documentation
PostgreSQL on Wikipedia

Leave A Reply

Your email address will not be published.