Install

Install

Standalone

Linux

Latest version

Start here

LTS

Steps (from there):

• install it using package manager (with aptitude: sudo apt-get install postgresql postgresql-contrib libpq-dev
• check startup log: tail /var/log/postgresql/postgresql-*.log
• check postgreSQL is started by opening prompt sudo -u postgres psql, leave by \q
• create an linux user account sudo adduser ``<OS_USER_NAME>{=html}
• create a role sudo -u postgres createuser --interactive, supplying the newly created linux user account (<POSTGRESQL_ROLE_NAME>{=html} = ``<OS_USER_NAME>{=html})

Additionally, enable access from outside:

• locate your authentication file sudo find /etc/postgresql -name "pg_hba.conf"
• sudo vi ``<PATH_TO_PG-HBA>{=html}/pg_hba.conf
• locate block

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer

• change peer to md5
• reload pg service sudo /etc/init.d/postgresql reload

Container (Docker)

Links:

• Build your own image

Windows

Overview:

• create a user named postgres, add it to Administrator group;
• open a windows as postgres user: runas /user:postgres cmd.exe.

Steps:

• create data container : docker create -v /var/lib/postgresql/data --name PostgreSQLData alpine
• create executable container : docker run -p 5432:5432 --name PostgreSQL -e POSTGRES_PASSWORD=yourPassword -d --volumes-from PostgreSQLData postgres

Guide

Linux

Steps:

• create data container : docker create -v /var/lib/postgresql/data --name PostgreSQLData alpine
• create executable container : docker run -p 5432:5432 --name PostgreSQL -e POSTGRES_PASSWORD=yourPassword -d --volumes-from PostgreSQLData postgres
• connect to database: docker exec -it --user postgres PostgreSQL psql
• create database: create database sandbox;
• switch to database \connect sandbox;

Or, with a one-liner

    mkdir -p PATH_TO_DB_DATA_STORAGE
    docker run 
      --name    CONTAINER_NAME
      --publish LOCAL_PORT:5432 
      --env     POSTGRES_DB=DATABASE_NAME
      --env     POSTGRES_USER=USER_NAME
      --env     POSTGRES_PASSWORD=USER_PASSWORD
      --volume  PATH_TO_DB_DATA_STORAGE:/var/lib/postgresql/data 
      --detach
      postgres

Load a sample database

• get backup
• extract it, whould get a .tar file
• upload backup inside container: docker cp ./dvdrental.tar PostgreSQL:/tmp
• create database dvdrental
• import backup pg_restore -U postgres -d dvdrental /tmp/dvdrental.tar
• connect: docker exec -it --user postgres PostgreSQL psql -d dvdrental
• list tables \dt
• check content: SELECT COUNT(*) FROM city;

Configure

Disable implicit COMMIT

DB

Steps:

• get pg location pg_config --sysconfdir
• create a global initialization file sudo vi <PG_LOCATION>/psqlrc
• paste \set AUTOCOMMIT off
• restart OS and check

Session

Steps:

• open pg CLI
• type \set AUTOCOMMIT OFF
• commit COMMIT;
• check \echo :AUTOCOMMIT

Command prompt

Get a nice command prompt (originally here)

-- Command prompt config
-- on main prompt, display [local/domain] user@database
\set PROMPT1 '%[%033[1m%]%M %n@%/%R%[%033[0m%]%# '
-- on a multi-line prompt, displays "more"
\set PROMPT2 '[more] %R > '

Misc

-- By default, NULL displays as an empty space. 
-- Is it actually an empty string, or is it null? 
-- This makes that distinction visible displaying [NULL] instead
\pset null '[NULL]'
-- Use table format (with headers across the top) by default, but switch to
-- expanded table format when there's a lot of data, which makes it much
-- easier to read.
\x auto
-- Verbose error reports.
\set VERBOSITY verbose
-- Use a separate history file per-database.
\set HISTFILE ~/.psql_history- :DBNAME
-- If a command is run more than once in a row, only store it once in the
-- history.
\set HISTCONTROL ignoredups
-- Autocomplete keywords (like SELECT) in upper-case, even if you started
-- typing them in lower case.
\set COMP_KEYWORD_CASE upper

Authentication and autorisation

Reset admin password

Lost admin (postgres) password:

• add to pg_hba.conf : local all all trust
• restart : sudo service postgresql restart
• connect : sudo -u postgresql psql
• restart : ALTER USER postgres with password '``<PASSWORD>{=html}';
• comment in pg_hba.conf : local all all trust
• restart : sudo service postgresql restart
• connect : sudo -u postgresql psql