Check here for notes about SQL itself. I’ve never used PostgreSQL but I admire it as a 20 year old free software project that seems to be quite stable and feature rich.

PostgreSQL Official Site

Installation

Debian

Debian Guide

  • apt - postgresql, postgresql-client

CentOS, Red Hat

  • yum - postgresql, postgresql-devel, postgresql-libs

Maybe this form too: postgresql96-server

Gentoo

  • dev-db/postgresql

I needed to do both of these steps.

emerge -avuD dev-db/postgresql dev-python/psycopg
emerge --config dev-db/postgresql:9.5
/etc/init.d/postgresql-9.5 start

The second command makes a bunch of files in the data directory (mine was /var/lib/postgresql/9.5/data as well as some configuration files in /etc/postgresql-9.5/

Types

PostgreSQL features many useful types not always present in other database systems.

  • IPv4, IPv6, MAC addresses, UUID

  • Geometry, geographical location

  • boolean bits, bit strings

  • JSON and XML

  • Money

  • Arbitrary precision numbers

  • User defined

Geometry

One of the interesting distinguishing feature of PostgreSQL is its native types for geometrical entities.

Here’s where more information can be found.

Getting Started

Configuration

If you are planning to use the server from other hosts, you must set that up. The final line here is to open an external connection to a specific host. Note that you need the netmask (/32, /24, etc) or it won’t work. This file is also important to configure for replication purposes. The default file is quite well-documented; read it.

/var/lib/postgresql/9.5/data/pg_hba.conf
local   all    all                            trust
host    all    all        127.0.0.1/32        trust
host    all    all        ::1/128             trust
host    xtest  xedtester  128.128.128.152/32  md5

To get network connections working you also need to adjust things here.

/var/lib/postgresql/9.5/data/postgresql.conf

Most importantly is to set the listening address. The default of "localhost" should be changed to this or something else sensible.

listen_addresses = '*'

You can check that things are as they should be with something like this.

$ nmap example.univ.edu -p 5432 | grep 5432
5432/tcp open  postgresq
# lsof -n -u postgres | grep LISTEN
postgres 1780 postgres    3u  inet   802699  0t0    TCP *:postgresql (LISTEN)

After making these changes, restart (SIGHUP) the server.

/etc/init.d/postgresql-9.5 restart

Or the direct way which I never needed to use is something like this.

pg_ctl reload

Note that you have to be the postgres user for that to work.

Basics

:->[~]$ psql -U postgres -d postgres
psql (9.5.4)
Type "help" for help.

postgres=# CREATE ROLE xedtester WITH LOGIN;
CREATE ROLE
postgres=# \password xedtester
Enter new password:
Enter it again:
postgres=# CREATE DATABASE xtest WITH OWNER xedtester;
CREATE DATABASE
postgres=# \connect xtest
You are now connected to database "xtest" as user "postgres".
xtest-# \l xtest
                            List of databases
 Name  |   Owner   | Encoding | Collate |    Ctype    | Access privileges
-------+-----------+----------+---------+-------------+-------------------
 xtest | xedtester | UTF8     | C       | en_US.UTF-8 |

xtest-# \?
                ...
xtest-# \quit
:->[~]$ psql -U xedtester -d xtest -W
xtest=> \conninfo
You are connected to database "xtest" as user "xedtester" via socket in "/run/postgresql" at port "5432".

The last line is for help which is actually helpful.

Python

Python has many different ways interact with to PostgreSQL databases. The one that seems most popular (in 2016) is Psycopg2.

Normal install with these packages.

  • yum - python-psycopg2.x86_64

  • apt - python-psycopg2, python3-psycopg2

  • emerge - dev-python/psycopg

Basic Usage of the module.