PostgreSQL Cheatsheet

if initial setup

stop/start service

1. service postgresql-10 initdb

2. service postgresql-10 start

Users / Roles

create new user via cmd line
createuser newUser

create new user Joe and new DB (login to postgres using 'su - postgres' && psql)
CREATE USER david WITH PASSWORD 'jw8s0F4';

CREATE ROLE joe WITH LOGIN PASSWORD 'jellyfish' VALID UNTIL 'infinity'; 

CREATE DATABASE myApp WITH ENCODING='UTF8' OWNER=joe CONNECTION LIMIT=-1;

create superuser Role
postgres=# CREATE ROLE coverity_user WITH SUPERUSER CREATEROLE CREATEDB LOGIN PASSWORD 'coverity';

change user's password
alter user <username> with password '<password>';

show all users
postgres-# \du

remove user
DROP ROLE joe;

grant all privileges on DB to a user
GRANT ALL PRIVILEGES ON DATABASE myDB to joe;

PSQL

quit 

\q

Database

switch to a DB and begin work on it,
\c db_name

show all DBs for user acct
psql -h localhost --username=atlbitbucket --list

or  \l (L)

login into a specific DB
psql -d myDBname -U username

create a new DB
cmd line:  createdb -U username -h localhost myDBname

drop database

from cmd line

show physical size of DB
\l+  (L +)

backup database
root@host> runuser -l postgres -c 'pg_dump -F c

backup as Postgres user
root@server> runuser -l postgres -c 'pg_dump -F c -f /opt/backups/db.dat -p 5432 -h localhost -d myDBname'

restore from DB dump
stop service thats using the DB
pg_restore -v -c -d myDBname myDBname.backup

TABLE

drop table
DROP TABLE table_name;

drop all tables from DB (cmd line)
su -c "psql -d bitbucket -c \"\l\"" postgres

drop schema
DROP SCHEMA schame_name CASCADE;

show all tables in a database
\dt

change owner of table
alter table public.<table_name> owner to <username>