Postgresql

-- show object in all schemes https://stackoverflow.com/questions/15644152/list-tables-in-a-postgresql-schema
 \dt *.*   
-- clear console
\! clear
--show info
\conninfo
-- show timing
\timing ON
-- ------------Files --------------------
-- field separator
\f ','
-- toggle between unaligned and aligned output mode
\a
-- save in file 
\o '/tmp/output.csv'
----------------------------- 
 
-- vertical  show data
\x

-- выставить схему по умолчанию
SET search_path TO adm,public;
 
-- ----- Собрать таблицу из списка ----
SELECT  NUMBER
FROM    (VALUES ('555123'), ('555321'), ('555000') ) prefixes (prefix)
JOIN    phonenumbers ON  NUMBER >= prefix
        AND NUMBER < prefix || 'a'
 

-- системная колонка типа CTID с физическим размещением на диске - типа TID
SELECT ctid, * FROM adm.sqlcommands 

-- Generate data
INSERT INTO temperature (ts, city, temperature)
SELECT ts + (INTERVAL '60 minutes' * random()), city, 30*random()
FROM generate_series('2010-01-01' :: TIMESTAMP,
                     '2020-12-31', '1 hour') AS ts,
     unnest(array['Moscow', 'Berlin']) AS city;

-- Иерархия  hierarchy запрос
/*create table T1(id int, F1 varchar(100));
insert into T1 values(1, '10,56,657,34,767,71');
insert into T1 values(3, '1,5487,27,9');
insert into T1 values(2, '12,6,65,8,67,22,70,5');*/
 
WITH recursive t1r AS 
(SELECT 
     0 AS i
    ,ROW_NUMBER () OVER (ORDER BY id)  AS ir
    ,id
    ,f1
    ,'' f
FROM T1
	UNION
SELECT   
	 i+1
	, ROW_NUMBER () OVER (ORDER BY i)  AS ir
    , id
    , f1
    , split_part(f1, ',', i+1)
 FROM t1r
 WHERE i<10
 AND split_part(f1, ',', i+1)!=''
)
SELECT id,i AS num, f AS val
FROM t1r
WHERE i>0
ORDER BY id,i;

// postgres размер
SELECT version();
SELECT pg_size_pretty(pg_database_size('UIS_MV'));
 
SELECT oid,table_schema,TABLE_NAME
, to_char(row_estimate::INT , 'FM999 999 999 999 999 999') AS rows_estimate
, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a ORDER BY  total_bytes DESC
LIMIT 10;
 
SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
               , to_char(c.reltuples , 'FM999 999 999 999 999 999')  AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a ORDER BY  total_bytes DESC;

-- find fields
SELECT
pg_namespace.nspname,
pg_class.relname,
    pg_attribute.attname AS column_name,
    pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) AS data_type
FROM
    pg_catalog.pg_attribute
INNER JOIN
    pg_catalog.pg_class ON pg_class.oid = pg_attribute.attrelid
INNER JOIN
    pg_catalog.pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE
    pg_attribute.attnum > 0
    AND NOT pg_attribute.attisdropped
    AND pg_namespace.nspname = 'public'
    AND pg_class.relname = 'ЭтапИсходящегоЗапроса'
	AND ( 1=0 OR pg_catalog.format_type(pg_attribute.atttypid, pg_attribute.atttypmod) = 'text')
ORDER BY
    attnum ASC;
 
 
-- find fields	
SELECT tablename,attname, avg_width FROM pg_stats
WHERE 1=1
 AND tablename IN('СообщениеВИС', 'СообщениеВИСИЗ', 'ЗначениеПараметраИЗ', 'ЭтапИсходящегоЗапроса')
 AND ( 1=1 OR
(tablename, attname) IN 
	( VALUES 
	  ('СообщениеВИС', 'ТипСообщения'), 
	  ('СообщениеВИС','Сообщение')
	)
)
ORDER BY 1 DESC; 

#!/bin/bash
# Проверить содержимое на целостность 
 
if ! su - postgres -c 'pg_dumpall > /dev/null 2>/tmp/dumpall_log'
then
    echo 'pg_dumpall failed'
    exit 125
fi

-- list index
SELECT c.oid, i.indisunique
        ,n.nspname 
        ,c.relname
        ,c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree' 
AND (1=1 OR n.nspname = 'pg_catalog') -- # set 1=0 if you need only system
AND c.relpersistence != 't'
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC LIMIT 10;

-- verification tbl Tabular b-tree Indexes   IN PUBLIC SCHEME
CREATE extension IF NOT EXISTS amcheck;
SET 
  statement_timeout TO 0;
do $$ DECLARE r record;
SQL text;
ts_pre timestamptz;
e_message text;
e_detail text;
e_context text;
e_hint text;
errcount INT := 0;
BEGIN raise info 'begin!...';
FOR r IN 
SELECT 
  ROW_NUMBER() OVER(
    ORDER BY 
      tc.reltuples
  ) AS i, 
  COUNT(*) OVER() AS cnt, 
  c.oid, 
  i.indisunique, 
  c.relname, 
  c.relpages :: int8, 
  tc.reltuples :: int8 AS tuples 
FROM 
  pg_index i 
  JOIN pg_opclass op ON i.indclass[0] = op.oid 
  JOIN pg_am am ON op.opcmethod = am.oid 
  JOIN pg_class c ON i.indexrelid = c.oid 
  JOIN pg_class tc ON i.indrelid = tc.oid 
  JOIN pg_namespace n ON c.relnamespace = n.oid 
WHERE 
  am.amname = 'btree' --and n.nspname = 'public'
  AND c.relpersistence <> 't' -- don't check temp tables
  AND c.relkind = 'i' 
  AND i.indisready 
  AND i.indisvalid 
  --and tc.relname = 'tbl' -- comment this out to check the whole DB
  --and c.relname in ('index_projects_on_name_and_id', 'index_projects_on_lower_name', 'index_projects_api_name_id_desc') 
ORDER BY 
  tc.reltuples loop ts_pre := clock_timestamp();
raise info '[%] Processing %/%: index: % (index relpages: %; heap tuples: ~%)...', 
ts_pre :: timestamptz(3), 
r.i, 
r.cnt, 
r.relname, 
r.relpages, 
r.tuples;
BEGIN perform bt_index_parent_check(
  INDEX => r.oid, heapallindexed => TRUE
);
raise info '[%] SUCCESS %/% – index: %. Time taken: %', 
clock_timestamp():: timestamptz(3), 
r.i, 
r.cnt, 
r.relname, 
(
  clock_timestamp() - ts_pre
);
exception WHEN others THEN GET stacked diagnostics e_message = message_text, 
e_detail = pg_exception_detail, 
e_context = pg_exception_context, 
e_hint = pg_exception_hint;
errcount := errcount + 1;
raise warning $err$[ % ] FAILED %/% – INDEX : %.ERROR : % CONTEXT : % DETAIL : % HINT : % $err$, 
clock_timestamp():: timestamptz(3), 
r.i, 
r.cnt, 
r.relname, 
e_message, 
e_detail, 
e_context, 
e_hint;
END;
END loop;
IF errcount = 0 THEN raise info 'Btree index scan with amcheck successfully finished. 0 errors.';
ELSE raise exception 'Index corruption detected by amcheck, % errors, see details in the log.', 
errcount;
END IF;
END $$;

-- https://www.tonic.ai/blog/how-to-create-realistic-test-data-for-postgresql
-- generate data
 
SELECT now();
 
 
 SET search_path TO adm,public;
 SHOW SEARCH_PATH;
 
 
CREATE TABLE users(
  id    SERIAL PRIMARY KEY,
  email VARCHAR(40) NOT NULL UNIQUE
);
CREATE TABLE posts(
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title   VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE comments(
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  post_id INTEGER NOT NULL REFERENCES posts(id),
  body    VARCHAR(500) NOT NULL
);
 
 
INSERT INTO adm.users(email)
SELECT
  'user_' || seq || '@' || (
    CASE (RANDOM() * 2)::INT
      WHEN 0 THEN 'gmail'
      WHEN 1 THEN 'hotmail'
      WHEN 2 THEN 'yahoo'
    END
  ) || '.com' AS email
FROM GENERATE_SERIES(1, 10) seq;
 
SELECT * FROM adm.users;
 
INSERT INTO adm.posts(user_id, title)
WITH expanded AS (
  SELECT RANDOM(), seq, u.id AS user_id
  FROM GENERATE_SERIES(1, 50) seq, users u
), shuffled AS (
  SELECT e.*
  FROM expanded e
  INNER JOIN (
    SELECT ei.seq, MIN(ei.random) FROM expanded ei GROUP BY ei.seq
  ) em ON (e.seq = em.seq AND e.random = em.min)
  ORDER BY e.seq
)
SELECT
  s.user_id,
  'It is ' || s.seq || ' ' || (
    CASE (RANDOM() * 2)::INT
      WHEN 0 THEN 'sql'
      WHEN 1 THEN 'elixir'
      WHEN 2 THEN 'ruby'
    END
  ) AS title
FROM shuffled s;
 
SELECT * FROM adm.posts LIMIT 10;
 
 
 
 
INSERT INTO comments(user_id, post_id, body)
WITH expanded AS (
  SELECT RANDOM(), seq, u.id AS user_id, p.id AS post_id
  FROM GENERATE_SERIES(1, 2000) seq, users u, posts p
), shuffled AS (
  SELECT e.*
  FROM expanded e
  INNER JOIN (
    SELECT ei.seq, MIN(ei.random) FROM expanded ei GROUP BY ei.seq
  ) em ON (e.seq = em.seq AND e.random = em.min)
  ORDER BY e.seq
)
SELECT
  s.user_id,
  s.post_id,
  'no this test ' || s.seq AS body
FROM shuffled s;
 
SELECT * FROM comments WHERE body LIKE '%Test comment 927%' LIMIT 10;
 
SELECT * FROM comments WHERE body LIKE '%no this test %' LIMIT 10;
 
/*
drop TABLE comments;
drop TABLE posts;
drop TABLE users;
*/
 
 
SELECT now()

# jsonb_populate
SELECT x.*
FROM "test"."jsonb_example_table" t
JOIN lateral jsonb_to_recordset( t.data ) AS 
                     x( "id" INT,
			"name" text,
			"email" text,
			"roles" jsonb,
			"esia_tag" text,
			"position" text,
			"created_at" text, 
			"department" text,
			"updated_at" text,
			"subdepartment" text,
			"fo_and_to_organ" jsonb,
			"email_verified_at" text,
			"fo_and_to_organs_id" jsonb,
			"digital_certificates" text,
			"place_of_work_filled" bool
 
    ) ON TRUE
WHERE t.id=16;
 
-- example TO_TIMESTAMP('2021-10-26T01:53:12.000000Z','YYYY-MM-DD"T"HH24:MI:SS"Z"')

COPY country TO STDOUT (DELIMITER '|');
 
COPY (SELECT ir, "Район", "HouseNumber", "Name", "Полный_адрес" FROM dist)
TO '/tmp/DistrictMap.csv'
DELIMITER ','
CSV HEADER;
 
COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

SET search_path TO dist,public;
 
-- создать таблицу
CREATE TABLE dist (
    ir BIGINT,
    "Район" CHARACTER VARYING(450),
    "HouseNumber" CHARACTER VARYING(10),
    "Name" CHARACTER VARYING(450),
    "Полный_адрес" CHARACTER VARYING(1000)
);
 
-- загрузить данные
COPY dist(ir, "Район", "HouseNumber", "Name", "Полный_адрес")
FROM '/tmp/DistrictMap.csv'
DELIMITER ','
CSV HEADER;
 
SELECT UPPER("Name"),ds.* 
FROM dist ds WHERE UPPER("Name") LIKE '%ИГНАТОВ%'
AND "HouseNumber"='116'
ORDER BY ir DESC
 
 
SELECT "HouseNumber","Name",COUNT(*)
FROM dist
GROUP BY "HouseNumber","Name","Район"
HAVING COUNT(*)>1
ORDER BY 3 DESC
;

https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/
-- select attributes
percona=# SELECT attname, format_type (atttypid, atttypmod)
FROM pg_attribute
WHERE attrelid::regclass::text='scott.employee'
ORDER BY attnum;
 
percona=# SELECT txid_current();
 txid_current 
--------------
          646
(1 ROW)
 
percona=# INSERT INTO scott.employee VALUES (9,'avi',9);
INSERT 0 1
percona=# SELECT xmin,xmax,cmin,cmax,* FROM scott.employee WHERE emp_id = 9;
 xmin | xmax | cmin | cmax | emp_id | emp_name | dept_id 
------+------+------+------+--------+----------+---------
  647 |    0 |    0 |    0 |      9 | avi      |       9
(1 ROW)
 
 
 
--show bloat table
 
WITH constants AS (
    -- define some constants for sizes of things
    -- for reference down the query and easy maintenance
    SELECT current_setting('block_size')::NUMERIC AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
    -- screen out table who have attributes
    -- which dont have stats, such as JSON
    SELECT table_schema, TABLE_NAME, 
        n_live_tup::NUMERIC AS est_rows,
        pg_table_size(relid)::NUMERIC AS table_size
    FROM information_schema.columns
        JOIN pg_stat_user_tables AS psut
           ON table_schema = psut.schemaname
           AND TABLE_NAME = psut.relname
        LEFT OUTER JOIN pg_stats
        ON table_schema = pg_stats.schemaname
            AND TABLE_NAME = pg_stats.tablename
            AND column_name = attname 
    WHERE attname IS NULL
        AND table_schema NOT IN ('pg_catalog', 'information_schema')
    GROUP BY table_schema, TABLE_NAME, relid, n_live_tup
),
null_headers AS (
    -- calculate null header sizes
    -- omitting tables which dont have complete stats
    -- and attributes which aren't visible
    SELECT
        hdr+1+(SUM(CASE WHEN null_frac <> 0 THEN 1 ELSE 0 END)/8) AS nullhdr,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        schemaname,
        tablename,
        hdr, ma, bs
    FROM pg_stats CROSS JOIN constants
        LEFT OUTER JOIN no_stats
            ON schemaname = no_stats.table_schema
            AND tablename = no_stats.table_name
    WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
        AND no_stats.table_name IS NULL
        AND EXISTS ( SELECT 1
            FROM information_schema.columns
                WHERE schemaname = COLUMNS.table_schema
                    AND tablename = COLUMNS.table_name )
    GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
    -- estimate header and row size
    SELECT
        ma, bs, hdr, schemaname, tablename,
        (datawidth+(hdr+ma-(CASE WHEN hdr%ma=0 THEN ma ELSE hdr%ma END)))::NUMERIC AS datahdr,
        (maxfracsum*(nullhdr+ma-(CASE WHEN nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM null_headers
),
table_estimates AS (
    -- make estimates of how large the table should be
    -- based on row and page size
    SELECT schemaname, tablename, bs,
        reltuples::NUMERIC AS est_rows, relpages * bs AS table_bytes,
    CEIL((reltuples*
            (datahdr + nullhdr2 + 4 + ma -
                (CASE WHEN datahdr%ma=0
                    THEN ma ELSE datahdr%ma END)
                )/(bs-20))) * bs AS expected_bytes,
        reltoastrelid
    FROM data_headers
        JOIN pg_class ON tablename = relname
        JOIN pg_namespace ON relnamespace = pg_namespace.oid
            AND schemaname = nspname
    WHERE pg_class.relkind = 'r'
),
estimates_with_toast AS (
    -- add in estimated TOAST table sizes
    -- estimate based on 4 toast tuples per page because we dont have 
    -- anything better.  also append the no_data tables
    SELECT schemaname, tablename, 
        TRUE AS can_estimate,
        est_rows,
        table_bytes + ( COALESCE(toast.relpages, 0) * bs ) AS table_bytes,
        expected_bytes + ( CEIL( COALESCE(toast.reltuples, 0) / 4 ) * bs ) AS expected_bytes
    FROM table_estimates LEFT OUTER JOIN pg_class AS toast
        ON table_estimates.reltoastrelid = toast.oid
            AND toast.relkind = 't'
),
table_estimates_plus AS (
-- add some extra metadata to the table data
-- and calculations to be reused
-- including whether we cant estimate it
-- or whether we think it might be compressed
    SELECT current_database() AS databasename,
            schemaname, tablename, can_estimate, 
            est_rows,
            CASE WHEN table_bytes > 0
                THEN table_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                AS table_bytes,
            CASE WHEN expected_bytes > 0 
                THEN expected_bytes::NUMERIC
                ELSE NULL::NUMERIC END
                    AS expected_bytes,
            CASE WHEN expected_bytes > 0 AND table_bytes > 0
                AND expected_bytes <= table_bytes
                THEN (table_bytes - expected_bytes)::NUMERIC
                ELSE 0::NUMERIC END AS bloat_bytes
    FROM estimates_with_toast
    UNION ALL
    SELECT current_database() AS databasename, 
        table_schema, TABLE_NAME, FALSE, 
        est_rows, table_size,
        NULL::NUMERIC, NULL::NUMERIC
    FROM no_stats
),
bloat_data AS (
    -- do final math calculations and formatting
    SELECT current_database() AS databasename,
        schemaname, tablename, can_estimate, 
        table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) AS table_mb,
        expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) AS expected_mb,
        round(bloat_bytes*100/table_bytes) AS pct_bloat,
        round(bloat_bytes/(1024::NUMERIC^2),2) AS mb_bloat,
        table_bytes, expected_bytes, est_rows
    FROM table_estimates_plus
)
-- filter output for bloated tables
SELECT databasename, schemaname, tablename,
    can_estimate,
    est_rows,
    pct_bloat, mb_bloat,
    table_mb
FROM bloat_data
-- this where clause defines which tables actually appear
-- in the bloat chart
-- example below filters for tables which are either 50%
-- bloated and more than 20mb in size, or more than 25%
-- bloated and more than 1GB in size
WHERE ( pct_bloat >= 50 AND mb_bloat >= 20 )
    OR ( pct_bloat >= 25 AND mb_bloat >= 1000 )
ORDER BY pct_bloat DESC;

# facts IN log
pg_dump UIS_MV -t 'public.stage' --schema-only 

# facts IN log
WARNING: 2022/07/02 02:55:35.641221 Invalid page header encountered: blockNo 0, path /var/lib/postgresql/DATA/base/16384/923678
WARNING: 2022/07/02 02:55:35.646335 Invalid page header encountered: blockNo 0, path /var/lib/postgresql/DATA/base/16384/923753
WARNING: 2022/07/02 02:55:35.646392 Invalid page header encountered: blockNo 1, path /var/lib/postgresql/DATA/base/16384/923753
WARNING: 2022/07/02 02:55:35.648309 Invalid page header encountered: blockNo 0, path /var/lib/postgresql/DATA/base/16384/923628
WARNING: 2022/07/02 02:55:35.648593 Invalid page header encountered: blockNo 0, path /var/lib/postgresql/DATA/base/16384/923840
WARNING: 2022/07/02 02:55:35.648655 Invalid page header encountered: blockNo 1, path /var/lib/postgresql/DATA/base/16384/923628
WARNING: 2022/07/02 02:55:35.648722 Invalid page header encountered: blockNo 1, path /var/lib/postgresql/DATA/base/16384/923840
# Find 
UIS_MV=# SELECT oid,relname,relnamespace,reltype,relfilenode FROM pg_class WHERE relfilenode IN (923678, 923753, 923753, 923628, 923840, 923628, 923840);
  oid   |           relname           | relnamespace | reltype | relfilenode
--------+-----------------------------+--------------+---------+-------------
 418561 | sqlcommands                 |       418347 |  418563 |      923628
 418566 | pg_toast_418561_index       |           99 |       0 |      923678
 923753 | idx_sqlcommands_currentstep |       418347 |       0 |      923753
 923840 | sqlcommands_pkey            |       418347 |       0 |      923840
(4 ROWS)
TIME: 1.112 ms
 
UIS_MV=#show data_directory;
/var/lib/postgresql/DATA
 
$ find /var/lib/postgresql/DATA/ -TYPE f -name 923628
/var/lib/postgresql/DATA/base/16384/923628
 
 
# FIX
UIS_MV=#SET zero_damaged_pages = ON;
UIS_MV=#VACUUM FULL adm.sqlcommands;
UIS_MV=#REINDEX TABLE adm.sqlcommands;

-- https://tableplus.com/blog/2018/04/postgresql-how-to-grant-access-to-users.html
--1. Grant CONNECT to the database:
GRANT CONNECT ON DATABASE database_name TO username;
 
--2. Grant USAGE on schema:
GRANT USAGE ON SCHEMA schema_name TO username;
 
--3. Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username;
 
--4. Grant all privileges on all tables in the schema:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username;
 
--5. Grant all privileges on all sequences in the schema:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA schema_name TO username;
 
--6. Grant all privileges on the database:
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
 
--7. Grant permission to create database:
ALTER USER username CREATEDB;
 
--8. Make a user superuser
ALTER USER myuser WITH SUPERUSER;
 
--9. Remove superuser status:
ALTER USER username WITH NOSUPERUSER;
 
--Those statements above only affect the current existing tables. To apply to newly created tables, you need to use alter default. For example:
ALTER DEFAULT PRIVILEGES
FOR USER username
IN SCHEMA schema_name
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO username;

-- https://www.cybertec-postgresql.com/en/tracking-view-dependencies-in-postgresql/
 
SELECT
 classid::regclass AS "depender object class",
    CASE classid
        WHEN 'pg_class'::regclass THEN objid::regclass::text
        WHEN 'pg_type'::regclass THEN objid::regtype::text
        WHEN 'pg_proc'::regclass THEN objid::regprocedure::text
        ELSE objid::text
    END AS "depender object identity",
    objsubid,
    refclassid::regclass AS "referenced object class",
    CASE refclassid
        WHEN 'pg_class'::regclass THEN refobjid::regclass::text
        WHEN 'pg_type'::regclass THEN refobjid::regtype::text
        WHEN 'pg_proc'::regclass THEN refobjid::regprocedure::text
        ELSE refobjid::text
    END AS "referenced object identity",
    refobjsubid,
    CASE deptype
        WHEN 'p' THEN 'pinned'
        WHEN 'i' THEN 'internal'
        WHEN 'a' THEN 'automatic'
        WHEN 'n' THEN 'normal'
    END AS "dependency type"
, pc.conname
, CASE pc.contype::text
WHEN 'c' THEN 'check constraint'
WHEN 'f' THEN 'foreign key constraint'
WHEN 'p' THEN 'primary key constraint'
WHEN 'u' THEN 'unique constraint'
ELSE pc.contype::text
END AS constrainttype
,conrelid::regclass AS "conn_rel_from"
,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid)  
,confrelid::regclass AS "conn_foreign_on"
,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
 
FROM pg_catalog.pg_depend d
LEFT JOIN pg_constraint pc ON 
pc.oid=d.objid
--pc.confrelid = d.refobjid 
AND classid::regclass='pg_constraint'::regclass
WHERE (objid >= 16384 OR refobjid >= 16384)
AND refobjid = 'public."Запрос"'::regclass
AND classid::regclass='pg_constraint'::regclass
;
;

-- Hierarchies from object - объекты зависящие от объекта - ищи "ВложениеЗапроса"
WITH recursive temp1  AS (
SELECT refobjid AS label
	,1 AS i
	,ROW_NUMBER () OVER ()  AS ir
	,array[array[1,ROW_NUMBER () OVER () ]] AS order_pth
	,array[array[
			conrelid::regclass ||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid LIMIT 1)  
			  ,
			confrelid::regclass||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
		]]  AS path
	,conrelid AS cconrelid
	,confrelid AS cconfrelid
	,/*case pc.contype::text
	when 'c' then 'check constraint'
	when 'f' then 'foreign key constraint'
	when 'p' then 'primary key constraint'
	when 'u' then 'unique constraint'
	else pc.contype::text
	end */
	pc.contype::text AS cconstrainttype
	,conrelid::regclass AS "cconn_rel_from"
	,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid)  AS  cattcol
	,confrelid::regclass AS "cconn_foreign_on"
	,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid) AS cforcol
FROM pg_catalog.pg_depend d
LEFT JOIN pg_constraint pc ON 
pc.oid=d.objid
--pc.confrelid = d.refobjid 
AND classid::regclass='pg_constraint'::regclass
WHERE (objid >= 16384 OR refobjid >= 16384)
AND refobjid = 'public."ВложениеЗапроса"'::regclass
AND classid::regclass='pg_constraint'::regclass
AND pc.contype::text='f'
AND conrelid=refobjid
	UNION
SELECT  
	label 
	, i+1
	, ROW_NUMBER () OVER (ORDER BY i)  AS ir
	, order_pth || array [i+1,ROW_NUMBER () OVER (ORDER BY i) ]
	, path ||array[
			conrelid::regclass ||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid LIMIT 1)  
			  ,
			confrelid::regclass||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
		]
	, conrelid 
	, confrelid 
	, pc.contype::text 
	, conrelid::regclass 
	, (SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid)  
	, confrelid::regclass
	, (SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
	FROM temp1 t,
	  pg_catalog.pg_depend d 
LEFT JOIN pg_constraint pc ON pc.oid=d.objid
--pc.confrelid = d.refobjid 
AND classid::regclass='pg_constraint'::regclass
WHERE (objid >= 16384 OR refobjid >= 16384)
AND refobjid = t.cconn_foreign_on
AND confrelid <> t.cconfrelid
AND classid::regclass='pg_constraint'::regclass
AND pc.contype::text='f'
AND t.i<5 AND t.label!=conrelid
)
SELECT 
  label
	,  i
	, ir
	--, order_pth
	,
	format(
    '+%s %s -> %s',
    repeat('-', round((i-1) * 4)::INT),
    path[i][1],path[i][2]
   )   
	--, cconrelid
	--, cconfrelid 
FROM temp1
 -- where i<5
ORDER BY order_pth

--   объекты ссылающиеся на объект исследования - обратная иерархия - ищи "Адрес"
WITH recursive temp1  AS (
SELECT  refobjid AS label
	,1 AS i
	,ROW_NUMBER () OVER ()  AS ir
	,array[array[1,ROW_NUMBER () OVER () ]] AS order_pth
	,array[array[
			conrelid::regclass ||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid LIMIT 1)  
			  ,
			confrelid::regclass||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
		]]  AS path
	,conrelid AS cconrelid
	,confrelid AS cconfrelid
	,/*case pc.contype::text
	when 'c' then 'check constraint'
	when 'f' then 'foreign key constraint'
	when 'p' then 'primary key constraint'
	when 'u' then 'unique constraint'
	else pc.contype::text
	end */
	pc.contype::text AS cconstrainttype
	,conrelid::regclass AS "cconn_rel_from"
	,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid)  AS  cattcol
	,confrelid::regclass AS "cconn_foreign_on"
	,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid) AS cforcol
FROM pg_catalog.pg_depend d
LEFT JOIN pg_constraint pc ON pc.oid=d.objid
--pc.confrelid = d.refobjid 
AND classid::regclass='pg_constraint'::regclass
WHERE (objid >= 16384 OR refobjid >= 16384)
AND refobjid = 'public."Адрес"'::regclass
AND classid::regclass='pg_constraint'::regclass
AND pc.contype::text='f'
AND confrelid=refobjid
AND confrelid!=conrelid
	UNION
SELECT  
	label 
	, i+1
	, ROW_NUMBER () OVER (ORDER BY i)  AS ir
	, order_pth || array [i+1,ROW_NUMBER () OVER (ORDER BY i) ]
	, path ||array[
			conrelid::regclass ||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid LIMIT 1)  
			  ,
			confrelid::regclass||'.'||(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
		]
	, conrelid 
	, confrelid 
	, pc.contype::text 
	, conrelid::regclass 
	, (SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid)  
	, confrelid::regclass
	, (SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
	FROM temp1 t,
	  pg_catalog.pg_depend d 
LEFT JOIN pg_constraint pc ON pc.oid=d.objid
--pc.confrelid = d.refobjid 
AND classid::regclass='pg_constraint'::regclass
WHERE (objid >= 16384 OR refobjid >= 16384)
AND refobjid = t.cconrelid
--AND confrelid <> t.cconfrelid
AND classid::regclass='pg_constraint'::regclass
AND pc.contype::text='f'
    AND t.i<5   -- 5 итерация
	AND t.label!=confrelid -- если вернулись к первоначальному субъекту
	AND confrelid =  t.cconrelid -- фильтруем только зависимые объекты!!!
	AND confrelid!=conrelid -- убираем зависимость объекта на себя
)
SELECT 
    label
 
	,  i
	, ir
	, order_pth
	,format(
    '+%s %s -> %s',
    repeat('-', round((5-i) * 4)::INT),
    path[i][1],path[i][2]
   )   
	, cconrelid
	, cconfrelid 
	, cconrelid::regclass
	, cconfrelid::regclass
	--,path
	--,t.*
FROM temp1 t
WHERE 1=1 
--and cconfrelid='"ЗаявлениеНаУслугу"'::regclass
--and cconfrelid=cconrelid
ORDER BY order_pth

SELECT ev_class::regclass,
r.*
FROM pg_rewrite r
WHERE oid=17182;
 
SELECT
CASE pc.contype::text
WHEN 'c' THEN 'check constraint'
WHEN 'f' THEN 'foreign key constraint'
WHEN 'p' THEN 'primary key constraint'
WHEN 'u' THEN 'unique constraint'
ELSE pc.contype::text
END AS constrainttype
,conrelid::regclass
,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.conkey[1] AND attrelid=conrelid)  
,confrelid::regclass
,(SELECT attname FROM  pg_attribute WHERE pg_attribute.attnum=pc.confkey[1] AND attrelid=confrelid)
FROM pg_constraint pc
WHERE pc.oid=4849966;

SELECT e.extname, ne.nspname AS extschema, p.proname, np.nspname AS proschema
,pg_catalog.pg_get_function_identity_arguments(p.oid)
FROM pg_catalog.pg_extension AS e
    INNER JOIN pg_catalog.pg_depend AS d ON (d.refobjid = e.oid)
    INNER JOIN pg_catalog.pg_proc AS p ON (p.oid = d.objid)
    INNER JOIN pg_catalog.pg_namespace AS ne ON (ne.oid = e.extnamespace)
    INNER JOIN pg_catalog.pg_namespace AS np ON (np.oid = p.pronamespace)
WHERE d.deptype = 'e'
ORDER BY 1, 3;

SELECT i.inhrelid::regclass
     , partition_bound
     , split_part(partition_bound, '''', 2) AS lower_bound
     , split_part(partition_bound, '''', 4) AS upper_bound
FROM   pg_inherits i
JOIN   pg_class    c ON c.oid = i.inhrelid
     , pg_get_expr(c.relpartbound, i.inhrelid) AS partition_bound
WHERE  inhparent = 'ЭтапАрхив'::regclass;

CREATE TABLE public."ЭтапАрхив" (
    primarykey uuid NOT NULL,
    "Статус" CHARACTER VARYING(24),
    "ТекстЗапроса" text,
    "ТекстОтвета" text,
    "ВремяОтправки" TIMESTAMP(3) WITHOUT TIME zone,
    "ВремяПолученияОтвета" TIMESTAMP(3) WITHOUT TIME zone,
    "ИмяЗапросаВоВложении" CHARACTER VARYING(50),
    createtime TIMESTAMP(3) WITHOUT TIME zone,
    creator CHARACTER VARYING(255),
    edittime TIMESTAMP(3) WITHOUT TIME zone,
    editor CHARACTER VARYING(255),
    "КонфигурацияЭтапа" uuid,
    "Запрос" uuid,
    "ИдСообщенияСМЭВ3" uuid,
    namespaceuri text,
    rootelement text,
    "НомерИтерации" INTEGER
 
) PARTITION BY RANGE ("ВремяОтправки")
(PARTITION "ЭтапАрхив_before_2017"  FOR VALUES FROM
('1990-01-01') TO ('2017-01-01')   );
 
CREATE TABLE IF NOT EXISTS  public."ЭтапАрхив_before_2017"
partition OF public."ЭтапАрхив" 
FOR VALUES FROM ('1990-01-01') TO (TIMESTAMP' 2017-01-01 00:00:00'); 
 
 
CREATE TABLE IF NOT EXISTS  public."ЭтапАрхив_2023"
partition OF public."ЭтапАрхив" FOR VALUES FROM
('2023-01-01') TO ('2024-01-01');

-- https://evilmartians.com/chronicles/a-slice-of-life-table-partitioning-in-postgresql-databases
 
    CREATE TABLE measurement (
        city_id         INT NOT NULL,
        logdate         DATE NOT NULL,
        peaktemp        INT,
        unitsales       INT
    );
 
    CREATE OR REPLACE FUNCTION new_partition_creator() RETURNS TRIGGER AS
  $BODY$
    DECLARE
      partition_date TEXT;
      partition TEXT;
      partition_day INT;
      startdate DATE;
      enddate DATE;
    BEGIN
      partition_day := to_char(NEW.logdate,'DD');
      partition_date := to_char(NEW.logdate,'YYYY_MM');
 
         IF partition_day < 15 THEN
      partition := TG_RELNAME || '_' || partition_date || '_p1';
      startdate := to_char(NEW.logdate,'YYYY-MM-01');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      ELSE 
      partition := TG_RELNAME || '_' || partition_date || '_p2';
      startdate := to_char(NEW.logdate,'YYYY-MM-15');
      enddate := date_trunc('MONTH', NEW.logdate) + INTERVAL '1 MONTH - 1 day';
      END IF;
 
      IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' ( CHECK ( logdate >= DATE ''' || startdate || '''  AND logdate <=  DATE ''' ||  enddate || ''' )) INHERITS (' || TG_RELNAME || ');';
        EXECUTE 'CREATE INDEX ' || partition || '_logdate ON '  || partition || '(logdate)';
        EXECUTE 'ALTER TABLE ' || partition || ' add primary key(city_id);';       
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING city_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100; 
 
 
    CREATE TRIGGER testing_partition_insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE new_partition_creator();
 
 
    postgres=# INSERT INTO measurement VALUES(1,'2017-10-11',10,10);
    NOTICE:  A partition has been created measurement_2017_10_p1
    INSERT 0 0

Prepare

# exec CMDS .1.196
    /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -T Db bash
    /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -u postgres Db bash
 
# Restore DB
    createdb UIS_MV
 
    psql UIS_MV    CREATE USER uis_mv WITH PASSWORD 'uis_mvuis_mv';

0. Dump DDL - only structure

pg_dump --host localhost --port 5432 -d ISUI --username postgres --format plain --verbose --file "/tmp/table.sql" --table dbo.tmp_dist

Резервная копия UISMV - запуск только в screen !!!
1. резервная копия в формате каталога в 10 потоков

$ time pg_dump -Z 2 -j 10 -Fd --clean --if-exists UIS_MV -f /tmp/backup/dmp/temp/

2. restore

$ time pg_restore -c -d  UIS_MV -j 12 /opt/dmp/temp
# if plain text 
$ zcat ./UIS_MV_20220713_192005.dump.gz | psql UIS_MV 2>&1 >./walg/log.txt

3. test

ash-5.0$ time pg_dumpall > /dev/null 2>/tmp/dump.log

1. оцениваем необходимое место смотри выше - SQL check version/size
2. запускаем SQL переключая wal SELECT pg_walfile_name(pg_switch_wal()), pg_walfile_name(pg_switch_wal());
3. запускаем backup wal-g - см. wal-g
4. проверяем корректность см. Sql.check - SQL check database data
5. проверка index`ов - SQL check database index - amcheck

manage DB

# WAL-G list repository
 /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -u postgres -T Db  wal-g backup-list --pretty
 /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -u postgres -T Db  wal-g wal-show
 /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -T Db /home/wal-g/cron_do_backup.sh
# PG run in compose
 /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -T Db bash
 /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -T Db -u postgres psql UIS_MV
# PG STOP
/usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml down

1. запускаем контейнер восстановления

docker container run --rm -it -u postgres --name pgrestore  \
  -v $(pwd)/wal-g/.walg.json:/var/lib/postgresql/.walg.json \
  -v $(pwd)/wal-g/wal-g:/usr/bin/wal-g \
  -v $(pwd)/wal-g/:/home/wal-g/ \
  -v /media/add300gb/postgres/data:/var/lib/postgresql/data/ \
  -v /etc/localtime:/etc/localtime \
  -v /etc/sysconfig/clock:/etc/sysconfig/clock \
  -v /media/synology-esb-smb/uismv-db-backups/full-backups:/opt/dmp \
  postgres:14-alpine /bin/bash

2. Очищаем $PGDATA

cd $PGDATA
rm -rf -- $PGDATA/*

3. проверяем репозиторий резервного копирования

 # WAL-G list repostory 
 wal-g wal-show && wal-g backup-list --pretty
 # WAL-G restore data
 wal-g backup-fetch $PGDATA base_0000000200000069000000F1_D_0000000200000069000000EC  --reverse-unpack --skip-redundant-tars 
 
 # после  восстановления переводим базу в RECOVERY MODE
 touch $PGDATA/recovery.signal
 # если необходимо PITR [Set PITR] 
 echo " " >> $PGDATA/postgresql.conf
 echo "recovery_target_time = '2022-07-18 09:29:07.907202+05'" >> $PGDATA/postgresql.conf
 # готовы к запуску базы 

6. Запускаем recovery postgresql

# запуск внутри контейнера 
 /usr/local/bin/docker-entrypoint.sh postgres -c cluster_name='UIS_MV'
# или запустить целевой контейнер на host
 /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml up Db 
 
 
-- check data in DB - example
[mea@root-esb-pg ~]$ /usr/local/bin/docker-compose -f /opt/docker-pg-wal-g/docker-compose.yml exec -u postgres  Db   psql UIS_MV
 
UIS_MV=#  SET search_path TO adm,public;
UIS_MV=#  SHOW SEARCH_PATH;
 search_path
-------------
 adm, public
(1 row)
 
UIS_MV=# SELECT * FROM comments where body like '%no this test %' LIMIT 10;
 id | user_id | post_id | body
----+---------+---------+------
(0 rows)
 
UIS_MV=# SELECT * FROM comments where body like '%Test comment 927%' LIMIT 10;
  id   | user_id | post_id |        body
-------+---------+---------+---------------------
 18001 |       6 |      16 | Test comment 927 1
...
 18010 |      10 |      46 | Test comment 927 10
(10 rows)
 
 
-- # finish check 
select pg_wal_replay_resume();

7. am_check - see in wiki - RECONNECT

-- in public do  
CREATE extension IF NOT EXISTS amcheck;
 
 
-- list index
SELECT c.oid, i.indisunique
        ,n.nspname 
        ,c.relname
        ,c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree' 
AND (1=1 OR n.nspname = 'pg_catalog') -- # set 1=0 if you need only system
AND c.relpersistence != 't'
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC LIMIT 10;
 
INFO:  Btree INDEX scan WITH amcheck successfully finished. 0 errors.
DO

-- example restore
9:36 -> 
bash-5.1$ wal-g  backup-fetch $PGDATA base_0000000200000069000000F1_D_0000000200000069000000EC --reverse-unpack --skip-redundant-tars
INFO: 2022/07/18 09:36:51.195271 Selecting the backup with name base_0000000200000069000000F1_D_0000000200000069000000EC...
INFO: 2022/07/18 09:36:51.227569 Delta base_0000000200000069000000F1_D_0000000200000069000000EC at LSN 69/F1000028
INFO: 2022/07/18 09:36:51.236294 AO files metadata was not found. Skipping the AO segments unpacking.
 
INFO: 2022/07/18 09:48:25.596068 Finished extraction of part_065.tar.br
INFO: 2022/07/18 09:48:25.625988 Finished extraction of pg_control.tar.br
INFO: 2022/07/18 09:48:25.630771
Backup extraction complete.
 
 
220718 09:35:19 - 18 09:48:33 73G /media/add300gb/postgres