postgresql cheat sheet

# install python postgresql interface libraries
python2:
$ sudo apt-get install python-psycopg2
python3:
$ sudo apt-get install python3-psycopg2

# login to psql
$ sudo -u postgres psql postgres

FILES

 pg_hba.conf
 = pg host based authentication => client auth
 https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

PSQL CONSOLE COMMANDS

# close current connection and open new connection to database db_name
=# \c db_name
or
=# \connecgt db_name

# switch user to (os) user postgres (not a root user, and owns postgres binaries)
$ sudo su postgres

# start server
pg_ctl start -D /path/to/database/data/directory

# restart server
pg_ctl restart -D /path/to/database/data/directory

# stop server
pg_ctl stop -D /path/to/database/data/directory

# ENGINE
get psql version                       select version()
print data directory location          show data_directory

# terminate all connections other than the psql one in use
SELECT pg_terminate_backend(pid)  FROM  pg_stat_activity WHERE (pid <> pg_backend_pid()) AND datname = 'database_name';

# DATABASE

change db ownership                    ALTER DATABASE database_name OWNER TO new_owner

# USER
list all database users                \du
change password for user postgres      \password postgres
make user userx a superuser            alter role userx superuser

# LOGGING
edit config file @ PSQL_DATA_DIR/postgresql.conf:
- uncomment the following lines: #log_directory, #log_filename
- change #log_statement = 'none' to log_statement = 'all'
- change #logging_collector = off to logging_collector = on
- change #log_connections = off
- restart daemon


# SCHEMA

list schemas with owners               \dn+
list all databases                     \list or \l
list all tables in current database    \dt
connect to database                    \connect db_name or \c db_name
change ownership of schema             ALTER SCHEMA schema_name OWNER TO new_schema_owner;

SQL

# list databases
#
SELECT datname FROM pg_database
WHERE datistemplate = false;

# list tables in the current database
#
SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;