Database - Postgres (PostgreSQL)

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

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

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';