2 October 2012 - San Antonio

change to postgres user and open psql prompt

sudo -u postgres psql postgres

list databases

postgres=# \l

list roles

postgres=# \du

create role

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

alter role

postgres=#ALTER ROLE demorole1 CREATEROLE CREATEDB REPLICATION SUPERUSER;

drop role

postgres=#DROP ROLE demorole1;

create database

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

grant privileges to new user

GRANT ALL PRIVILEGES ON DATABASE demodb1 TO demorole1;

drop database

postgres=#DROP DATABASE demodb1;

connect to database

\c <databasename>

list tables in connected database

\dt

list columns on table

\d <tablename>

backup database

pg_dump <databasename> > <outfile>