postgres-tips

postgres-tips

apt-get update -y
apt-get install -y wget gnupg2 || apt-get install -y gnupg
wget -O - http://repo.postgrespro.ru/keys/GPG-KEY-POSTGRESPRO | apt-key add -
echo deb http://repo.postgrespro.ru/pg1c-archive/pg1c-12.1/debian/ buster main > /etc/apt/sources.list.d/postgrespro-1c.list
apt-get update -y
apt-get install -y postgrespro-1c-12-server postgrespro-1c-12-contrib
/opt/pgpro/1c-12/bin/pg-setup initdb --encoding=UTF8 --locale=ru_RU.UTF-8 --data-checksums
/opt/pgpro/1c-12/bin/pg-setup service enable
service postgrespro-1c-12 start
===
apt-get update -y
apt-get install -y wget gnupg2 || apt-get install -y gnupg
wget -O - http://repo.postgrespro.ru/keys/GPG-KEY-POSTGRESPRO | apt-key add -
echo deb http://repo.postgrespro.ru/pg1c-archive/pg1c-11.5/debian/ buster main > /etc/apt/sources.list.d/postgrespro-1c.list
apt-get update -y
apt-get install -y postgrespro-1c-11-server postgrespro-1c-11-contrib
/opt/pgpro/1c-11/bin/pg-setup initdb --encoding=UTF8 --locale=ru_RU.UTF-8 --data-checksums
/opt/pgpro/1c-11/bin/pg-setup service enable
service postgrespro-1c-11 start
===

Бэкап:
pg_dump -d dbname -F d -f dumpname -j 2 -Z 6

Восстановление:
createdb dbname
pg_restore -d dbname -F d -j 2 dumpname

Восстановление из бэкапа, созданного с помощью autopostgresqlbackup (включает создание базы и установку владельца):
gunzip < backupname.sql.gz | sed 's/source-db-name/dest-db-name/' | psql

Копирование базы на другой сервер:
~/.pgpass: *:*:*:postgres:password
createdb -h host2 -U postgres dbname
pg_dump -C -h host1 -U postgres dbname | psql -h host2 -U postgres dbname

Размер базы данных
SELECT pg_size_pretty( pg_database_size( 'sample_db' ) );

Размер всех баз данных на сервере
select datname, pg_size_pretty(pg_database_size(datname)) 
from pg_database;

Бенчилки:
sudo -u postgres pgbench -i
sudo -u postgres pgbench -T 5

pg_test_fsync


======================
Долгие запросы:
select current_timestamp - query_start as runtime,
pid,
datname,
usename,
query,
state
from pg_stat_activity
where state != 'idle'
order by 1 desc

================
Отключить индекс:
update pg_index set indisvalid = false where indexrelid = 'indexname'::regclass

==============
/etc/fstab:
tmpfs /var/lib/pgpro/1c-11/data/pg_stat_tmp/ tmpfs noatime,nodiratime,defaults,size=512M

=============
Утилиты
https://pgcodekeeper.org/pgsqlblocks/

====
bloat:
используем pgcompacettable, он существенно лучше и проще pg_repack

=====
Использования кэша, shared_buffers

SELECT usagecount, round(count(*) * 8192 / 1024 / 1024) as size
FROM pg_buffercache
GROUP BY usagecount
ORDER BY usagecount;

====
Чем занят кэш
SELECT c.relname,
  count(*) blocks,
  round( 100.0 * 8192 * count(*) / pg_table_size(c.oid) ) "% of rel",
  round( 100.0 * 8192 * count(*) FILTER (WHERE b.usagecount > 3) / pg_table_size(c.oid) ) "% hot"
FROM pg_buffercache b
  JOIN pg_class c ON pg_relation_filenode(c.oid) = b.relfilenode
WHERE  b.reldatabase IN (
         0, (SELECT oid FROM pg_database WHERE datname = current_database())
       )
AND    b.usagecount is not null
GROUP BY c.relname, c.oid
ORDER BY 2 DESC
LIMIT 10;

=================================
Оценка количества мертвых туплов
SELECT
    psut.relname,
    to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
    to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
    pg_class.reltuples::bigint AS n_tup,
    psut.n_dead_tup::bigint AS dead_tup,
    CASE WHEN pg_class.reltuples > 0 THEN
        (psut.n_dead_tup / pg_class.reltuples * 100)::int
    ELSE 0
    END AS perc_dead,
    CAST(current_setting('autovacuum_vacuum_threshold') AS bigint) + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric) * pg_class.reltuples) AS av_threshold,
    CASE WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint) + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric) * pg_class.reltuples) < psut.n_dead_tup THEN
        '*'
    ELSE ''
    END AS expect_av
FROM pg_stat_user_tables psut
    JOIN pg_class on psut.relid = pg_class.oid
--WHERE psut.relname = '_accrgat230572'
ORDER BY 5 desc, 4 desc;

=================================