POSTGRESQL

all informations are inside great documentation

psql
psql [option] -d [dbname] -h [hostname] -U [username]
psql -l	db list
psql syntax:
\timing	set show sql execution time
\q	quit
\password	set password
\pset	change psql settings
\l	show databases
\d [table]	table desc
\c [database]	connect to db
\da	agregation functions
\db+	tablespaces
\dc	conversions
\df+	functions
\dg+ \du+	roles
\di+	indexes
\ds+	sequences
\dt+	tables
\dv+	views
\dSvtis+	system views,tables,indexes,sequences
\dn+	schemas
\do	operators
\dp	privileges
\encoding	db encoding
\l+	db description
\z	objects with privileges
\o [file]	spool file
\![command]	run OS command
help commands:
\?	info about commands with backslash
\h	sql help
parameters:
show all;	db parameters
show [parametr];	show search_path
show search_path	current schama
set search_path to [other_schema]	now you can see objects from other schema

administrating:

variable PGDATA point to cluster catalog
pg_ctl status #db state
pg_ctl -D [directory] [action]
pg_ctl start | stop |restart #db start,stop you can add -l [logfile]
pg_ctl stop -m [smart | fast | immediate] #db stop smart(default), fast(with rolleback transactions, immediate(shutdown abort)
pg_controldata [cluser_dir] #cluster info
SELECT pg_database_size('[dbname]'); #show db size SELECT pg_size_pretty(pg_database_size('[dbname]')); #show db size SELECT pg_size_pretty(pg_total_relation_size('[table]')); #show table size with index SELECT pg_size_pretty(pg_relation_size('[table]')); #show table without index

DUMP:

-- export:
pg_dump [db_name] > [file] #dump in plaintext format
pg_dump -t '[table]' [db_name] > [file]
pg_dump -Fc #dump in pg_restore format
pg_dumpall > [file] #dump all databases
-- import:
psql [db] < [plik] #import from plaintext format
psql -f [file] postgres #import from plaintext format
pg_restore -d [baza] [plik] #import from pg_restore format
pg_restore -l [plik] #content of dumpfile

CREATE:

initdb --pgdata | -D [cluster_dir] [-E encoding] #cluster initialization,create template1 and postgres database
createdb [dbname] [-D tablespace] [-E encoding] [-O owner] [-T template to create new database]

change host database IP:

change in $PG_DATA/postgresql.conf
change in $PG_DATA/pg_hba.conf (according manual - chapter 20: Client Authentication)

misc:

ALTER TABLE [table] ALTER COLUMN [column] TYPE int USING [column]::int; #change column type from char to int

MANAGE ROLES:

--check
\dg #check system privs
SELECT * FROM pg_roles;
create role [role_name]; #create role
--change
ALTER ROLE [role_name] SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT [connlimit] | PASSWORD [password] | ENCRYPTED | UNENCRYPTED | VALID UNTIL [timestamp]
--change password
ALTER ROLE [role] PASSWORD '[pass]';

change object privs:

\z #check privs
=xxxx #privileges granted to PUBLIC
uname=xxxx #privileges granted to a user
group gname=xxxx #privileges granted to a group
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
R -- RULE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
T -- TEMPORARY
arwdRxt -- ALL PRIVILEGES (for tables)
* -- grant option for preceding privilege

GRANT [privs] ON [object] TO [role];