Postgres

Basic commands

PGPASSWORD=<password> psql -h <host> -U <user> -d <database>`
 
\l -- list databases
\c <db> -- switch database
\dn -- show schemas
SET search_path TO <schema>;
\dt -- show tables
\du -- show users
 
SELECT current_database();
SELECT current_schema();
DROP SCHEMA public CASCADE;

Privileges

https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html

\dp -- shows privileges
\ddp -- shows default privileges
 
CREATE ROLE my_user NOSUPERUSER NOCREATEDB
    NOCREATEROLE LOGIN PASSWORD '<PASSWORD>' VALID UNTIL 'infinity';
 
grant connect on database "db_name" to my_user;
 
-- shows connect
select db.datname, r.rolname, has_database_privilege(r.rolname, db.datname, 'connect')  
from pg_roles r 
cross join pg_database db  
where r.rolcanlogin and db.datallowconn
order by db.datname, r.rolname;
 
 

Export / Import

pg_dump -U admin DBNAME > /tmp/file.sql
pg_restore -U admin -C /tmp/file.sql 

Docker-Compose

  container_name: postgres
  image: postgres:10.6-alpine
  environment:
    - POSTGRES_DB=db
    - POSTGRES_USER=postgres
    - POSTGRES_PASSWORD=password
  volumes:
  - ${PWD}/postgres-data:/var/lib/postgresql/data
  ports:
    - "5432:5432"
# sql -h localhost -U postgres -d db