/ sql

[WIP] PostgreSQL Notes

PostgreSQL Database Objects

  • service
  • database
  • schema
  • catalog
  • variable
  • extension
  • table
  • foreign table and foreign data wrapper
  • tablespace
  • view
  • function
  • language
  • operator
  • type
  • cast
  • sequence
  • row or record
  • trigger
  • rule

Main Configuration Files

  • postgres.conf
  • pg_hb.conf
  • pg_ident.conf

An easy way to check the current settings is to query the pg_settings view

Restarting terminates active connections, whereas reloading does not.

Authentication Methods

  • trust
  • md5
  • password
  • ident
  • peer

init a database directory

pg_ctl initdb -D <data_dir>

start a postgres server

pg_ctl start -D <data_dir>

stop a postgres server

pg_ctl stop -m fast -D <data_dir>

restart a postgres server

pg_ctl restart -m fast -D <data_dir>

reload the conf file from inside psql

select pg_reload_conf();

To reload configuration changes from command line

pg_ctl reload -D <data_dir>

Retieve a list of recent connections and process ids

select * from pg_stat_activity;

Cancel all active queries on a connection

select pg_cancel_backend(procid)

Kill the connection

select pg_terminate_backend(procid)

Roles

  • login roles
  • group roles
  • group login roles

create login role

create role leo login password 'king' createdb valid until 'infinity';

create superuser role

create role regina login password 'queen' superuser valid until '2020-1-1 00:00';

create a group role

create role royalty inherit;

add roles to a group role

grant royalty to leo;
grant royalty to regina;

Create a database modelled after a template

create database my_db template my_template_db;

Schemas organise your database into logical groups. A common way to organise schemas is by roles

to find out the current loggin in user

select user;

As a best-practice, install extensions into a seperate schema

WITH GRANT OPTION - the grantee can grant onwards

grant all on all tables in schema public to mydb_admin with grant option;

Revoke privileges

revoke execute on all functions in schema my_schema from public;

People often forget to set GRANT USAGE ON SCHEMA or GRANT ALL ON SCHEMA. Even if your tables and functions have rights assigned to a role, these tables and functions will still not be accessible if the role has not USAGE rights to the schema.

To view all extension binaries already available on your server

select * from pg_available_extensions;

Remove an extension

drop extension

Install and extension

create extension <extn_name> schema my_extensions;

pg_dump and pg_dumpall