Database - Postgres (PostgreSQL)

Installation

Docker

Docker

docker run ^
    --name postgres ^
    -e POSTGRES_PASSWORD=welcome ^
    -p 5432:5432 ^
    -d ^
    postgres

where:

  • POSTGRES_USER is the database user (default postgres)
  • POSTGRES_PASSWORD is the database user password (mandatory for remote access)
  • POSTGRES_DB is the database name (default to POSTGRES_USER)

Postgresql

Example on how to Dockerize PostgreSQL (create a Dockerfile)

Linux

  • Install
sudo apt install postgresql postgresql-contrib
  • The default admin user, postgres, needs a password assigned in order to connect to a database. To set a password:
sudo passwd postgres

Service:

sudo service postgresql status
sudo service postgresql start 
sudo service postgresql stop

Client

psql

  • Connect to the postgres service and open the psql shell
sudo -u postgres psql
  • Example: What user accounts have been created on the PostgreSQL installation
psql -c "\du"
  • Exit
\q 
# or use the shortcut key: Ctrl+D

Restoration backup

  • pg_dump - extract a PostgreSQL database into a script file or other archive file. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).
  • pg_restore - restore a PostgreSQL database from an archive file created by pg_dump
  • pg_basebackup is used to take base backups of a running PostgreSQL database cluster.

Example ansible playbook

Metadata

Doc

Object class

To query an object, you need to give the oid (Object identifiers) in the good class table

select distinct classid::regclass 
from 
pg_catalog.pg_depend
order by 1 asc
classid
pg_type
pg_proc
pg_class
pg_constraint
pg_conversion
pg_language
pg_rewrite
pg_trigger
pg_ts_dict
pg_ts_config
pg_ts_template

List relational object

The class table that holds all tables, indexes, sequences, views (“relations”) is called pg_class.

select 
ns.nspname, -- namespace = schema
obj.relname, -- name
case 
when obj.relkind = 'S' then 'sequence'
when obj.relkind = 'i' then 'index'
when obj.relkind = 'r' then 'table'
when obj.relkind = 'v' then 'view'
when obj.relkind = 'm' then 'materialized view'
when obj.relkind = 'c' then 'composite'
when obj.relkind = 'f' then 'foreign table'
else cast(obj.relkind as VARCHAR)
end as objtype -- type
from
pg_class obj
join pg_namespace ns on obj.relnamespace = ns.oid
where
ns.nspname = 'public';

Relational Object Dependency

The below query based on the pg_depend lists all objects:

  • at column level
  • that depends on relational objects
  • that can be dropped only with cascade (dependency type = n)

List all dependency for a dimension table

SELECT 
distinct -- at relation level (no column - ie the column pg_depend.refobjsubid is not added)
classid::regclass, --dependency class
classid, -- dependency class id
objid, -- dependency id
case 
when dep.deptype = 'n' then 'normal dependency'
when dep.deptype = 'i' then 'internal dependency'
when dep.deptype = 'a' then 'auto dependency'
else cast(dep.deptype as VARCHAR)
end as deptype -- the type of dependence
FROM pg_depend dep
join pg_class relation on dep.refobjid = relation.oid
where 
relation.relname = 'd_date'
and deptype = 'n';

Documentation


Powered by ComboStrap