Difference between revisions of "PostgreSQL"

Jump to navigation Jump to search
Line 92: Line 92:
create user FOO with SUPERUSER
create user FOO with SUPERUSER
alter user FOO password 'BAR';

Revision as of 22:36, 27 June 2017



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

Restarting can be a little tricky

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


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


alter user FOO password 'BAR';

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:



SELECT * FROM pg_locks 

Remote Access and Authorization Mode

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

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

Working example:

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               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5