PostgreSQL Administration

postgres.sh

# Login to database 
sudo -u postgres psql

# Creating user
sudo -u postgres createuser <username>

# Creating Database
sudo -u postgres createdb <dbname>

# Giving the user a password
sudo -u postgres psql
psql=# alter user <username> with encrypted password '<password>';

# Granting privileges on database
psql=# grant all privileges on database <dbname> to <username> ;

# Same in psql
CREATE DATABASE yourdbname;
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;

# Create database backup
pg_dump -U <user> -h localhost <db name> > dump.sql
# promt password here

# Restoring database
psql -U <user> -d <dbname> -h localhost < dump.sql
# or
pg_restore -U <username> -d <dbname> -1 dump.sql