PostgreSQL Commands

Install and setup in macOS

brew install postgresql

To have launchd start postgresql now and restart at login:

brew services start postgresql

Or, if you don’t want/need a background service you can just run:

pg_ctl -D /usr/local/var/postgres start

Run psql console

psql postgres

Backup Database

pg_dump -h <host> -U <user> -W -Ft <database name> > ./backup.tar

Restore Database

pg_restore -h <host> -U <user> -W -Ft <database name> ./backup.tar

Create User & Database

CREATE DATABASE <name>;
CREATE USER <name> WITH PASSWORD '<password>';
GRANT ALL PRIVILEGES ON DATABASE <db> TO <user>;