To install postgres on Mac

brew update
brew install postgres

To have postgres to start with Mac:

# to have launchd start postgres now and restart at login
brew services start postgresql
# to start postgres on demand:
pg_ctl -D /usr/local/var/postgres start

To configure postgres:

# if you have trouble logging into psql try this:
sudo -u postgres psql
# if that doesn't work:
/usr/local/opt/postgres/bin/createuser -s postgres
# first enter the posgres console:
psql postgres
# once inside psql postgres
# to list users:
\\du
# to exit postgres console
\\q
# to create users using SQL commands
# first login into console:
psql postgres
# you will need to have the default password for the mains setup:
\\password <username>
# you will be prompted to enter your password
# then create a new user:
CREATE ROLE <rolename> WITH LOGIN PASSWORD 'password';
# then modify permissions:
ALTER ROLE <username> CREATEDB;
# to create a db: first login as the user you have created:
psql postgres -U <username>;
# then create the db:
CREATE DATABASE <databasename>;
# grant all privileges to the user for this db:
GRANT ALL PRIVILEGES ON DATABASE <databasename> TO <username>;
ALTER DATABASE sample_app_test OWNER TO demo_app;
ALTER USER username CREATEDB;
# list all databases in postgres:
\\list
# connect to a database:
\\connect <databasename>
# list tables in the current database
\\dt
# change general user password:
ALTER USER <username> WITH PASSWORD 'new_password';
# to allow user to login into postgres
ALTER ROLE deployer WITH LOGIN;
# to grant same privileges of postgres to another do:
GRANT postgres TO deployer;
# to grant user ability to create db:
ALTER USER deployer CREATEDB;

# to delete a user
drop user username;

# to reassign a table that belongs to a user
reassign owned by username to postgres;

To backup and restore database:

# first backup the database:
# optional step: su - postgres
pg_dump <databasename> > filename.bak

# you may want to drop the db first:
dropdb <databasename>
# if you do drop, you may want to recreate:
createdb <databasename>

# to restore the db:
psql <databasename> < <backup_filename>

To configure postgres server to receive external connections:

# edit the file /etc/postgresql/10/main/postgresql.conf
# find the lins where it reads 'listen_addresses' and change it so:
listen_addresses='*'
# this will allow Postgres to listen to calls from any IP address
# alternatively you may want to restrict to some ips like so:
listen_addresses='95.148.235.140, 80.85.87.41'
# then open the file /etc/postgresql/10/main/pg_hba.conf
# and add the line to it:
host all all 0.0.0.0/0 md5
# this will allow Postgres to listen to all connections requests and authenticate via password using md5 encryption

Queries

Postgres performance

Commands

# to connect to a db:
\\connect TABLENAME
# to see list of tables:
\\du
# to see contents of table:
TABLE <tablename>;