# 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 )
docker pull dpage/pgadmin4
docker run -p 80:80 -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" -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';