PostgreSQL

What to return for trigger (work with BEFORE)

trigger invocation	NEW is set	OLD is set
ON INSERT	✔️
ON UPDATE	✔️	✔️
ON DELETE		✔️

Interpretation of privilege symbols

symbol	description
r	SELECT (“read”)
w	UPDATE (“write”)
a	INSERT (“append”)
d	DELETE
D	TRUNCATE
x	REFERENCES
t	TRIGGER
X	EXECUTE
U	USAGE
C	CREATE
c	CONNECT
T	TEMPORARY
arwdDxt	ALL PRIVILEGES (for tables, varies for other objects)
*	grant option for preceding privilege

Bind to address /var/lib/pgsql/data/postgresql.conf

listen_addresses = 'localhost, ip_address'

/var/lib/pgsql/data/pg_hba.conf

host    all             all             0.0.0.0/0          md5

Added to SELinux

setsebool -P httpd_can_network_connect_db 1

Shared buffers

shared_buffers=<25% of RAM>

Commands

su - postgres
sudo -u postgres psql

Connenct to console

psql

Exit

\q

Set parameters in session

set <parameter name> to '<value>';
select set_config(<parameter name>, <value>,false)

Reload configuration file

select pg_reload_conf()

Start vacuum and show progress

vacuum verbose;
select * from pg_catalog.pg_stat_progress_vacuum;

Quotes

quote_ident() -- ""
quote_literal() -- ''

Start database

pg_ctl -D <data dir> start
postgres -D <data dir>

Promote replica

pg_ctl promote -D <data dir>

Move row in other table

with <cte name> as (delete from <only> <src table> where <condition> returning *) insert into <dst table> select * from <cte name>;

Show informations

Show parameters

show <parameter name>;
select current_setting('<parameter name>');
select * from pg_settings where name='<parameter name>'
select * from pg_file_settings where name='<parameter name>;

Show database size

select pg_size_pretty(pg_database_size(current_database()));

Show table size

SELECT pg_size_pretty(pg_relation_size('<table name>'));
SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Get path to configuration file

show config_file;

Check shared buffers

select * from pg_settings where name = 'shared_buffers';

Show search path

show search_path;

Show WAL`s

select * from pg_ls_waldir();

Show LSN

SELECT pg_current_wal_lsn();

Show locks

select * from pg_locks;
select pg_blocking_pids(<pid>);

Show current pid

select pg_backend_pid();

Get stats

select * from pg_stat_database;
select * from pg_class;
select * from pg_stats;
select * from pg_stat_activity;
select * from pg_stat_user_tables;
select * from pg_stat_user_indexes;
select * from pg_stat_statements;
select * from pg_catalog.pg_stat_replication;

Show databases and tables

SELECT datname FROM pg_database
WHERE datistemplate = false;

SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

Privileges

Show table privileges

\dp+ *.*

Show default table privileges

\ddp+ *.*

Show schema privileges

\dn+

Show database privileges

\l

Show roles privileges

\duS+

Show roles

select * from pg_catalog.pg_shadow;

Granting the permissions to transfer privileges

GRANT <statements> ON <table name> TO <role name> WITH GRANT OPTION;
GRANT <statements> ON <role name>.<table name> TO <role name>;

Granting default permissions

alter default privileges for role <role name> in schema <schema name> grant <statements> on <target object> on <role name>

Functions

Show functions

select * from information_schema.routines where routine_type = 'FUNCTION' and routine_schema = '<schema name>';

Return value

create or replace function <function name>(<variable name> <variable type>, out <variable name> <variable type>) as $$
begin
    <query>
end;
$$ language plpgsql

create or replace function <function name>(<variable name> <variable type>) returns <variable type> $$
declare <variable name> <variable type>;
begin
    <query>
    return <variable name>;
end;
$$ language plpgsql

Loop

create or replace function <function name>(variable name> <variable type>) returns table (<variable name> <variable type>, <variable name> <variable type>) as $$
declare <variable name> <variable type>;
begin
    <query>
    into <variable name>;
end;
$$ language plpgsql

create or replace function <function name>(<variable name> <variable type>, out <variable name> <variable type>, out <variable name> <variable type>) returns setof record as $$
begin
    <query>
    into <variable name>;
end;
$$ language plpgsql

Return table from query

create or replace function <function name>(<variable name> <variable type) 
    returns setof <table name> as $$
begin
    return query
    <query>;
end;
$$ language plpgsql

create or replace function <function name>(<variable name> <variable type) 
    returns <setof> <table name> as $$
    <query>;
$$ language sql

create or replace function <function name>(<variable name> <variable type, out <variable name> <variable type>) 
    returns setof record as $$
    <query>;
$$ language sql

Anonymous function

do $$
declare
    <variable name> = <query>;
begin
    execute '<query>' || <variable name>;
end;
$$ language plpgsql

PL/Python

create or replace function <function name>(<variable name> <variable type, out <variable type>) as $$
    <variable name> = plpy.execute('<query>')
    return <variable name>[<list index>][<dict key>]
$$ language plpython3u;

create or replace function <function name>(<variable name> <variable type) returns <variable type> as $$
    <variable name> = plpy.execute('<query>')
    return <variable name>[<list index>][<dict key>]
$$ language plpython3u;

Trigger

Show triggers

select * from information_schema.triggers;
select * from pg_event_trigger;

Data changes

create or replace function <function name>_tg() returns trigger as $$
begin
    new.<row name> = <query>;
    ---
    <insert|update|delete query>;
    return <null|new|old>;
end;
$$ language plpgsql


create trigger <trigger name> <before|after|instead of> 
<update|insert|delete|truncate> on <table name>
for each <row|statement> when (<condition>) execute <procedure|function> <function name>_tg();

Database event

create or replace function <function name>_tg() returns event_trigger as $$
begin
    for i in select * from pg_event_trigger_ddl_commands()
    loop
        <query>;
    end loop;
end;
$$ language plpgsql

create event <trigger name> on <ddl_command_start,ddl_command_end|sql_drop|table_rewrite> execute function <function name>_tg();

Procedure

Show functions

select * from information_schema.routines where routine_type = 'PROCEDURE' and routine_schema = '<schema name>';

create or replace procedure <procedure name>() as $$
begin
    <query>;
    commit;
end;
$$ language plpgsql;

call <procedure name>();

Views

Show views

select * from information_schema.views;

Create view

create view <view name> as
    <query>;

select * from <view name>;

Extensions

Show extensions

select * from pg_catalog.pg_available_extensions;

Create extension for view buffers

CREATE EXTENSION pg_buffercache;

Rules

Create rule

create or replace rule <rule name> as on <insert|update|delete> to <src table name> where <condition> do instead <insert|update|delete> into <dst table name>; -- <values (new.*)> for insert

Show rules

select * from pg_rules;

Table

Create table with same structure

create table <to table name> as table <from table name> with no data;

Create inherits

create table <table name> (check (<condition>)) inherits <src table name>;

create index <index name>_idx on <dst table name> (cast(<column name> <column type>));

Create temp table

create temporary table <table name>_temp as (select * from <table name>);

Replication

Stream

Create replication user

create role <role name> with replication login password '<password>';

Add access in pg_hba.conf

host replication <role name> <replica host> md5

Change parameters in postgresql.conf

wal_level = <logical|replica>
wal_log_hints = on
max_wal_senders = 8
max_wal_size = 1GB
hot_standby = on

Restart master

Backup

pg_basebackup -R -P -X stream -c fast -D <new data dir> -h <master host> -U <role name> -W

Check connection string postgresql.auto.conf

Start database

Partitioning

Horizontal

Create table

create table <table name> (check (<condition>)) inherits <src table name>;
create index <index name>_idx on <dst table name> (cast(<column name> <column type>));

Create rules or triggers

create or replace rule <rule name> as on insert to <src table name> where <condition> do instead insert into <dst table name> values (new.*);
create or replace rule <rule name> as on update to <src table name> 
where <condition>
do instead insert into <src table name> values (new.*);
    delete from <dst table name> where <condition id>;

create or replace function <function name>_tg() returns trigger as $$
begin
    if <condition> then
        insert into <dst table name> values (new.*);
    else raise exception '<>';
    end if;
    return null;
end;
$$ language plpgsql


create trigger <trigger name> before insert on <src table name>
for each row execute function <function name>_tg();

Move rows

with <cte name> as (delete from only <src table name> where <condition> returning *) insert into <dst table name> select * from <cte name>;

Sharding

Create databases

create database <database name>;

Create table on shard with table structure and create index

create table <table name>(
    <structure>,
    <row name> <row type> check(<condition>),
);

create index <index name>_idx on <table name> (<row name>);

Create extension for create remote server

create extension postgres_fdw;

Create server

create server <server name> foreign data wrapper postgres_fdw options (host '<>', port '<>', dbname '<>');

Create user mapping

create user mapping for <local user name> server <server name> options (user '<remote user name>', password '<>');

Create foreign table

create foreign table <table name> (
<structure>,
)
inherits (<table name>)
server <server name>
options (schema_name '<remote schema name>', table_name '<remote table name>');

Function and trigger

create or replace function <function name>_tg() returns trigger as $$
begin
    if <condition> then
        insert into <dst table name> values (new.*);
    else raise exception '<>';
    end if;
    return null;
end;
$$ language plpgsql


create trigger <trigger name> before insert on <src table name>
for each row execute function <function name>_tg();

Move rows

with <cte name> as (delete from only <src table name> where <condition> returning *) insert into <dst table name> select * from <cte name>;

Row Security Policies(Row Leve Security)

Enable for table

ALTER TABLE <table name> ENABLE ROW LEVEL SECURITY;

Create policy

CREATE POLICY <policy name> ON <table name> TO <role name> USING (<condition>);

Show polices

SELECT * FROM information_schema.policies;