Jump to navigation Jump to search



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


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



If a restart of the DB engine is required:

sudo service postgresql restart

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


Before first use:

sudo postgresql-setup initdb

Start the service:

sudo systemctl start postgresql
sudo systemctl enable postgresql


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.

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

sudo -i -u postgres

Same without switching accounts (just switching for one session):

sudo -u postgres psql

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

The "-d" option is important because otherwise psql will 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.

Example of getting help:

testdb=# \h create table

Exit out of the PostgreSQL prompt by typing: \q


Create a user/role:

createuser --interactive

Another example:

create user FOO with SUPERUSER

Databases 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;


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

Changing a table:

ALTER TABLE foo ADD last_maint date;


List of DBs:


List of schemas:


Remote Access

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 = '*'

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

host    all             foo                  md5