PostgreSQL, also known as Postgres, is a databases, derived from the Ingres project.
Characteristics:
docker run ^
--name postgres ^
-e POSTGRES_PASSWORD=welcome ^
-p 5432:5432 ^
-d ^
postgres
where:
Postgresql
Example on how to Dockerize PostgreSQL (create a Dockerfile)
sudo apt install postgresql postgresql-contrib
sudo passwd postgres
Service:
sudo service postgresql status
sudo service postgresql start
sudo service postgresql stop
sudo -u postgres psql
psql -c "\du"
\q
# or use the shortcut key: Ctrl+D
Example ansible playbook
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 |
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';
The below query based on the pg_depend lists all objects:
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';