postgresql Cheat Sheet.md

change to postgres user and open psql prompt

sudo -u postgres psql postgres

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>

jonfk/postgresql Cheat Sheet.md