Login to postgres

psql postgres

Create Database

CREATE DATABASE <database_name>;

Drop Database

DROP DATABASE [IF EXISTS) name;

Check for Global Users

SELECT pg_user.usename FROM pg_catalog.pg_user;

Create user

CREATE USER tom WITH PASSWORD 'myPassword';

Grant user permission (be careful when providing all privileges to users)

GRANT CONNECT ON DATABASE <database_name> TO <username>;

or

GRANT ALL PRIVILEGES ON DATABASE <db-name> to <username>;

Alter table increment sequence

alter sequence table_id_seq restart with <id you want next>;

Upgrade brew postgres database

brew postgresql-upgrade-database

Backup

Backup a database

pg_dump dbname > dbname.bak

Backup all databases

pg_dumpall > pg_backup.bak

Restore a database

psql test < dbname.bak

Restore all databases

psql -f pg_backup.bak postgres

Backup a remote database

pg_dump -h 198.51.100.0 -p 5432 dbname > dbname.bak

Backup a database on docker

docker exec -i pg_container_name /bin/bash -c "PGPASSWORD=pg_password pg_dump --username pg_username database_name" > /desired/path/on/your/machine/dump.sql

Restore a database on docker

docker exec -i pg_container_name /bin/bash -c "PGPASSWORD=pg_password psql --username pg_username database_name" < /path/on/your/machine/dump.sql