PostgreSQL

→ postgresql.org

Getting Started

Local installation

Docker

GitHub

# Alpine
docker run --name postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:alpine
# Official
docker run --name postgres966 -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -d postgres:9.6.6

Tools

pgAdmin

pgAdmin 3

• With Docker (see hub.docker.com)

docker pull dpage/pgadmin4
docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=[e-mail]" -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" --name pgadmin4 -d dpage/pgadmin4
# open http://localhost and login

Recipes

Backup / restore

• Backup can be done with pgAdmin (personally I prefer plain format to have human readble sql content)

• Restore can be done with Docker: cat D:\Temp\dump.sql | docker exec -i postgres966 psql -U postgres (you may have to add the missing roles entries, which are not exported such as CREATE ROLE mycompany SUPERUSER;)

Queries

SQL

Update a user password

ALTER USER user WITH PASSWORD 'newpassword';

Get all databases

SELECT * FROM pg_catalog.pg_database;

Get all tables

SELECT
    table_schema || '.' || table_name
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'gracethd';

Get all columns of a table

SELECT *
FROM information_schema.columns
WHERE table_schema = 'gracethd'
  AND table_name   = 'adresse';