Рубрика: postgresql

sudo apt-get install -y \
    build-essential \
    postgresql-server-dev-16 \
    cmake \
    ninja-build \
    python3 \
    pkg-config \
    libtinfo5 \
    libc++-dev \
    libc++abi-dev \
    libv8-dev \
    curl \
    git \
    wget

# Скачайте и соберите plv8
git clone https://github.com/plv8/plv8.git
cd plv8
make
sudo make install

Если будет ошибка при запуске: https://github.com/plv8/plv8/issues/571

ERROR:  загрузить библиотеку "/usr/lib/postgresql/16/lib/plv8-3.2.4.so" не удалось: /usr/lib/postgresql/16/lib/plv8-3.2.4.so: undefined symbol: _ZTIN2v84base6ThreadE 

ОШИБКА:  загрузить библиотеку "/usr/lib/postgresql/16/lib/plv8-3.2.4.so" не удалось: /usr/lib/postgresql/16/lib/plv8-3.2.4.so: undefined symbol: _ZTIN2v84base6ThreadE
SQL state: 58P01

-- Показывает размеры таблиц, участвующих в запросах
WITH table_sizes AS (
    SELECT 
        schemaname,
        tablename,
        pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
    FROM pg_tables 
    WHERE schemaname NOT LIKE 'pg_%' AND schemaname != 'information_schema'
)
SELECT 
    a.pid,
    a.usename,
    a.datname,
    a.query,
    a.state,
    -- Можно добавить JOIN с table_sizes если знаете какие таблики используются
    now() - a.query_start as duration
FROM pg_stat_activity a
WHERE a.state = 'active' 
  AND a.query NOT LIKE '%pg_stat_activity%';

psql example_db
psql (12.3)
Type "help" for help.

example_db=#

example_db=# COPY
example_db-# (select * from users)
example_db-# TO '/Users/tracehelms/code/examples/users.csv'
example_db-# WITH (format csv, header true);
COPY 4

example_db=# \q

~/code/examples/
❯ ls
users.csv

pgcodekeeper cli — эта утилита по поиску разницы между одной БД PostgreSQL и другой БД PostgreSQL. Позволяет выполнять операции сравнения через terminal.

Для начала необходимо установить Java SDK версии 17+ для вашей платформы.

Переходим на сайт GitHub и скачиваем последний релиз. На момент написания это 10.8.0

После скачивания файла pgCodeKeeper-cli-10.8.0.zip распаковываем результат в /opt/pgcodekeeper-cli (предварительно создаём её через mkdir -p pgcodekeeper-cli).

Читать далее «pgcodekeeper cli»

Суть проблемы в том, что после того как функция будет создана, первые пять раз запрос выполняется быстро, но потом производительность резко падает.

Ниже описано решение.

I have this plpgsql function (running PG 14.5 on UBUNTU 18.04 with 8GB Ram) which returns 200 rows just fine:

db=# explain (analyze,buffers) Select * from f_client_getlistasshown('{"limit":"200","startdate":"2014-01-01","enddate":"2100-01-01","showRequiresActionFromTaxadvisor":false}');

                    
--------------------------------------------------------------------------------------------------------------------------------
 Function Scan on f_client_getlistasshown  (cost=0.25..10.25 rows=1000 width=400) (actual time=69.515..69.529 rows=200 loops=1)
   Buffers: shared hit=8939 dirtied=1
 Planning Time: 0.066 ms
 Execution Time: 70.282 ms
(4 rows)

Читать далее «PostgreSQL: Performance of function call degrades after calling 5 calls in a row»

# postgresql.conf
session_preload_libraries = 'auto_explain'

auto_explain.log_min_duration = '3s'

Или

postgres=# LOAD 'auto_explain';
postgres=# SET auto_explain.log_min_duration = 0;
postgres=# SET auto_explain.log_analyze = true;
postgres=# SELECT count(*)
           FROM pg_class, pg_index
           WHERE oid = indrelid AND indisunique;

В итоге в /var/log/postgresql будет план запроса

Реиндекс:

psql -U postgres -d "database_name"
REINDEX DATABASE database_name;

Иногда при попытке запуска Datalens могут возникать странные проблемы отсутствия тех или иных объектов в БД (хотя они там есть).

Ошибка может быть связана в особенности PostgreSQL, когда по умолчанию схема public настроена, как «схема по умолчанию для вызова объектов».

Читать далее «Особенность работы Datalens в PostgreSQL. часть 2»

Данный способ позволяет создать скрипт INSERT для наполнения таблицы PostgreSQL.

pg_dump -a -U postgres -t table_name -f output_name.sql --inserts --column-inserts --on-conflict-do-nothing database_name

Описание параметров:

• a — Выгружаются только данные, а не схема (определения данных). Выгружаются табличные данные, большие объекты и значения последовательности. Этот параметр аналогичен, но по историческим причинам не идентичен параметру, определяющему --section=data.
• U — Имя пользователя для подключения как.
• t — Дамп только таблиц с совпадающими именами
• f — Отправляйте выходные данные в указанный файл. Этот параметр можно опустить для файловых форматов вывода, в этом случае используется стандартный вывод. Однако он должен быть указан для формата вывода каталога, где указывается целевой каталог, а не файл. В этом случае каталог создан pg_dump и не должен существовать ранее.
• inserts — Дамп данных в виде INSERT команд (а не COPY). Это сильно замедлит восстановление; в основном это полезно для создания дампов, которые можно загружать в базы данных, отличные от PostgreSQL. Любая ошибка во время восстановления приведет к потере только строк, которые являются частью проблемной таблицы, INSERT а не всего содержимого таблицы. Обратите внимание, что восстановление может завершиться полным сбоем, если вы изменили порядок столбцов. Опция --column-inserts защищает от изменений порядка столбцов, хотя и работает еще медленнее.
• column-inserts — Дамп данных в виде INSERT команд с явными именами столбцов (INSERT INTO table (column, ...) VALUES ...). Это сильно замедлит восстановление; в основном это полезно для создания дампов, которые можно загружать в базы данных, отличные от PostgreSQL. Любая ошибка во время восстановления приведет к потере только строк, которые являются частью проблемной таблицы INSERT, а не всего содержимого таблицы.
• on-conflict-do-nothing — Добавить команды ON CONFLICT DO NOTHING в INSERT. Этот параметр недопустим, если также не указано --inserts, --column-inserts или --rows-per-insert.

Пример:

pg_dump -a -U postgres -t dbo.cs_point_types -f cs_point_types.sql --inserts --column-inserts --on-conflict-do-nothing skr-dev-db

https://www.postgresql.org/docs/current/app-pgdump.html

Monitoring PostgreSQL Disk I/O Performance