PostgreSQL

World's most advanced open source database.

PostgreSQL

PostgreSQL is an advanced Object-Relational database management system (DBMS).

Fedora distribution provides feature-full package set for PostgreSQL client and server, server-compatible plug-ins and database connectors.

Quick start

PostgreSQL installation on Fedora box and basic command-line usage:

$ sudo dnf install postgresql postgresql-server # install client/server
$ sudo postgresql-setup initdb                  # initialize PG cluster
$ sudo systemctl start postgresql               # start cluster
$ sudo su - postgres                            # login as DB admin
$ createdb quick-start                          # create testing database
$ psql -d quick-start                           # connect to the new database
psql (9.3.9)
Type "help" for help.

quick-start=# create table test (id int);       -- create testing table
CREATE TABLE
quick-start=# insert into test values (1);      -- insert some data
INSERT 0 1
quick-start=# select * from test;               -- query data
 id
----
  1
(1 row)

quick-start=#

Controlling access to PG cluster (pg_hba.conf, users, passwords)

Sometimes it is useful to create a new user (with password) for specific database applications. There is set of utilities in the postgresql package (see rpm -ql postgresql | grep /usr/bin) that may simplify some of those tasks. Lets try to create additional user, password and database:

$ sudo su - postgres
$ createuser testuser -P
Enter password for new role:
Enter it again:
$ createdb testdb --owner testuser
$ # add 'local all testuser md5' line **before** 'local all all peer' line
$ # (typical mistake is "just" appending the line at the and of the file)
$ vim ~/data/pg_hba.conf
$ cat ~/data/pg_hba.conf | grep ^local
local   all             testuser                                md5
local   all             all                                     peer
$ ^D
$ sudo systemctl restart postgresql
$ psql -d testdb -U testuser
Password for user testuser:
psql (9.3.9)
Type "help" for help.

testdb=>

Documentation

Packaging documentation is available as README.rpm-dist in the postgresql package (client package). Anybody running PostgreSQL on Fedora should start here:

$ rpm -qd postgresql | grep README.rpm-dist
/usr/share/doc/postgresql/README.rpm-dist
$ less /usr/share/doc/postgresql/README.rpm-dist

Install/use official documentation with:

$ sudo dnf install postgresql-docs
$ firefox /usr/share/doc/postgresql-docs/html/index.html         # HTML version
$ rpm -ql postgresql-docs | grep pdf
/usr/share/doc/postgresql-docs/postgresql-9.3.9-US.pdf
$ okular /usr/share/doc/postgresql-docs/postgresql-9.3.9-US.pdf  # PDF version

This documentation is also available online, please choose the version installed on your box (rpm -q postgresql --qf "%{VERSION}\n").

Other documentation available on-line:

Graphical DB management tools

Extensions/Tools available in (Fedora/RHEL/EPEL packages)

  • postgresql-contrib (bunch of extensions, see rpm -ql postgresql-contrib | grep so$), postgresql-plperl, postgresql-plpython, postgresql-plpython3, postgresql-pltcl (language bindings extending SQL syntax)
  • postgis
  • postgresql-pgpool-II
  • pg-semver
  • postgresql-ip4r

How to use extensions:

PostgreSQL implements the CREATE EXTENSION syntax (example with hstore extension):

$ sudo dnf install postgresql-contrib           -- install extension (hstore)
$ sudo su - postgres                            -- switch user to DB admin
$ psql -d quick-start                           -- connect to an existing DB
psql (9.3.9)
Type "help" for help.

quick-start=# CREATE EXTENSION hstore;          -- install extension to actual db
CREATE EXTENSION
quick-start=# SELECT 'a=>1,b=>2'::hstore;       -- use the extension
  hstore
  ----------
   "a"=>"1"
   (1 row)

Language connectors/adapters (packages)

PostgreSQL in containers

Currently there is a prepared Docker container for Fedora 21. Documentation is available on the Fedora-Dockerfiles github page.

There is also a PostgreSQL image available for OpenShift.