PostgreSQL

Installation

Ubuntu/Debian

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

You will likely need an additional package if using PostgreSQL as the Django backend:

sudo pip3 install psycopg2
# or:
sudo pip3 install psycopg2-binary

If you don't already have pip3 installed, there is some footwork you'll need to follow as documented on the Linux Tools page.

Default location of the data files:

/var/lib/postgresql/9.5/main

Uninstall:

# check the directories first:
dpkg -l | grep postgres

# now remove all components:
sudo apt-get --purge remove postgresql # etc, for each component

On Ubuntu 17.04 and up version 9.6 comes as a default, but for lower versions of Ubuntu PostgreSQL needs to be installed separately if version 9.6 is desired:

sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

CentOS

sudo yum install postgresql-server postgresql-contrib
sudo pip3 install psycopg2

Red Hat

sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm # reference to repo
# or:
sudo rpm -Uvh http://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
# then
sudo yum install postgresql96-server postgresql96

Find Version

SELECT version();

Running

Ubuntu/Debian

If a restart of the DB engine is required:

sudo service postgresql restart

Configuration will be defined in files contained in /etc/postgresql

CentOS

Before first use:

sudo postgresql-setup initdb

Start the service:

sudo systemctl start postgresql

To enable automatic start on reboot:

sudo systemctl enable postgresql

Restarting can be a little tricky

sudo -u  postgres pg_ctl restart -D /var/lib/pgsql/data

Red Hat

/usr/pgsql-9.6/bin/postgresql96-setup initdb

https://tecadmin.net/install-postgresql-on-centos-rhel-and-fedora/

Strict Service Name

Some installations require the exact service name to be specified when restarting etc. Example:

$ systemctl list-units|grep postgresql
postgresql-9.6.service

So in this case one needs to run:

sudo service postgresql-9.6.service restart

PSQL

Log in/out on localhost

The "psql" client requires a semicolon after each string you enter on the command line. It won't report if it's missing and it's easy to forget. This doesn't include the "backslash commands" (see below).

Switch over to the postgres account on your server by typing:

sudo -i -u postgres
# or, if sudo configuration is restrictive
sudo su - postgres
# then
psql
#
###### do same as above without switching accounts (just switching for one session):
#
sudo -u postgres psql
# whether this is allowed by sudo will depend on the system

After having created a user and making sure authentication method is set correctly in the configuration file (path may be system and version dependent and named something like /etc/postgresql/9.5/main/pg_hba.conf), one can connect to PostgreSQL not as the default "postgres" user but for example as "p3s" or any other userID of choice:

psql -U p3s -d tst
# or, as in the example above when the accounts are switched just for one command:
sudo -u postgres psql -d tstOrOtherDatabaseOfChoice

The "-d" option may be important because otherwise psql may assume a default database name which may not in fact exist. In the above example, the "tst" database was created beforehand by the user "postgres" to enable testing.

Alternatively, one can connect to an existing database after logging in by using the "\c" shortcut i.e.

\c tst

...or any other existing database within the system.

Execution of a SQL script contained in a file is achieved by utilizing the "-f" option e.g.

psql -U p3s -d tst -f myCoolSqlScript.sql

Example of getting help:

testdb=# \h create table

Exit out of the PostgreSQL prompt by typing: \q

Pager

Many PostgreSQL installation will use a paging program when displaying the output of "select" in the user's terminal window, and it may be something like "vi". This is not helpful at times e.g. when you run a macro. This can be avoided by deactivating the pager, and optionally setting the "extended" option on:

\x on
\pset pager off
select * from myTable

Users

List users:

\du

Create a user/role:

createuser --interactive

Another example:

create user FOO with SUPERUSER

Password:

alter user FOO password 'BAR';

Databases, Schema and Tables

Creation of DB

From the OS prompt:

sudo -u postgres createdb foo

Also can be done from within psql.

postgres=# create database testdb;

Schemas

These are akin to namespaces. The tables will be looked up only in those whose name is in the "search_path", so if you are puzzled that some of legit tables aren't visible to you do something like

# for a user:
ALTER ROLE <your_login_role> SET search_path TO a,b,c;
# for the whole database:
ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

Tables

testdb=# create table people (
testdb(# name char(50) primary key not null,
testdb(# age int not null
testdb(# );

Another example:

CREATE TABLE account(
 user_id serial PRIMARY KEY,
 username VARCHAR (50) UNIQUE NOT NULL,
 password VARCHAR (50) NOT NULL,
 email VARCHAR (355) UNIQUE NOT NULL,
 created_on TIMESTAMP NOT NULL,
 last_login TIMESTAMP
);

Changing a table:

ALTER TABLE foo ADD last_maint date;

Info

List of DBs:

\l

List of schemas:

\d

Info about a table:

\d table_name

More detail:

\d+ table_name

Locks

SELECT * FROM pg_locks 

Grant

Create a read-only user in PostgreSQL
First, grant connect access:

GRANT CONNECT ON DATABASE table_name TO username;
Then grant usage on schema

GRANT USAGE ON SCHEMA public TO username;
For a specific table

GRANT SELECT ON table_name TO username;
For multiple tables

GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
If you want to grant access to the new table in the future automatically, you have to alter default:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO username;

Restoring from a dump file

pg_restore -d myDB -U user DUMP_FILE_NAME

The user needs to have right privileges to the database.

Remote Access and Authorization Mode

The firewall settings need to be checked. For example, on a recent Ubuntu distribution one may do the following in order to open the PostgreSQL port:

sudo ufw enable
ufw allow 5432/tcp

Two configurations files are of relevance here:

• postgresql.conf
• pg_hba.conf

Listen Addresses

Add or edit the following line in your postgresql.conf, in order to enable access from any host (edit accordingly for more selective access rights):

listen_addresses = '*'
port = 5432

The original lines are these:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

Remote Authentication

To enable authentication from remote hosts for user "foo", edit pg_hba.conf to add

host    all             foo             0.0.0.0/0               md5

This file can be found in locations like

/etc/postgresql/9.5/main/pg_hba.conf # Ubuntu
/var/lib/pgsql/data/pg_hba.conf # CentOS
/var/lib/pgsql/9.6/data/pg_hba.conf # Red Hat

One can also query the database itself for the location of the file:

psql -U postgres -c 'SHOW config_file' # may or may not work depending on local restrictions

Working example of the authorization section:

local   all             postgres                                peer
local   all             p3s                                     md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5