jonfk/postgresql Cheat Sheet.md

## postgresql Cheat Sheet.md

      
    Raw
  

              postgresql Cheat Sheet.md
            
          
    change to postgres user and open psql prompt
sudo -u postgres psql postgres

or
psql -U postgres

list databases
postgres=# \l

list roles
postgres=# \du

run sql from file
psql -d myDataBase -a -f myInsertFile

create role
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD 'password1' CREATEDB;

create role with multiple privileges
postgres=# CREATE ROLE demorole1 WITH LOGIN ENCRYPTED PASSWORD
postgres=# 'password1' CREATEDB CREATEROLE REPLICATION SUPERUSER;

alter role
postgres=# ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

drop role
postgres=# DROP ROLE demorole1;

allow md5 authentication
modify pg_hba.conf to
# local   all         postgres                          md5
sudo /etc/init.d/postgresql restart #on ubuntu

update password
ALTER USER Postgres WITH PASSWORD '<newpassword>'

create database
postgres=# CREATE DATABASE demodb1 WITH OWNER demorole1 ENCODING 'UTF8';

grant privileges to new user
postgres=# GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

drop database
postgres=# DROP DATABASE demodb1;

connect to database
postgres=# \c <databasename>

list tables in connected database
postgres=# \dt

list columns on table
postgres=# \d <tablename>

backup database
$ pg_dump <databasename> > <outfile>