Рубрика: postgresql

Опубликовано

PostgreSQL 11: Мастерство разработки

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

CREATE UNIQUE INDEX some_name ON person USING btree (id) INCLUDE (name);

Если вы хотите получить «настоящее время», то пользуйтесь функцией clock_timestamp(), а не now().

Читать далее «PostgreSQL 11: Мастерство разработки»
Опубликовано

Установка настройка pgAgent

Оригинал инструкции размещен на сайте https://gist.github.com/peterneave/83cefce2a081add244ad7dc1c53bc0c3

  • Устанавливаем приложение
sudo apt update
sudo apt install pgagent
  • Далее создаем файл .pgpass в рабочей директории postgresql (/var/lib/postgresql)
sudo su - postgres
echo localhost:5432:*:pgagent:securepassword >> ~/.pgpass
chmod 600 ~/.pgpass
chown postgres:postgres /var/lib/postgresql/.pgpass
Читать далее «Установка настройка pgAgent»
Опубликовано

PostgreSQL размер таблиц и индексов

SELECT
    TABLE_NAME,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        TABLE_NAME,
        pg_table_size(TABLE_NAME) AS table_size,
        pg_indexes_size(TABLE_NAME) AS indexes_size,
        pg_total_relation_size(TABLE_NAME) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

Оригинал: Naik’s blog PostgreSQL размер таблиц и индексов — Naik’s blog (kesh.kz)

Опубликовано

Сбор основных показателей PostgreSQL

Настраиваем периодический сбор данных при помощи psql:

sudo su - postgres
crontab -e
# добавляем следующий код
*/15 * * * * psql -U postgres -d postgres -c "SELECT datname, xact_commit, now(), numbackends FROM pg_stat_database where datname = 'cic-release-db' or datname = 'hangfire'" >> /var/lib/postgresql/pg_stat_database.log

Примечание: имена таблиц в pg_stat_database указывать свои.

Читать далее «Сбор основных показателей PostgreSQL»
Опубликовано

PLV8 для Postgres 12

Если пытаться установить на Ubuntu 20.04 postgresql-plv8, то будет ошибка, что пакет не найден.

Для установки данного расширения на указанную выше ОС нужно собрать этот пакет вручную.

$ wget https://github.com/plv8/plv8/archive/v3.0.0.tar.gz
$ tar -xvzf v3.0.0.tar.gz
$ cd plv8-3.0.0
$ make

Возможные проблемы:

Нужно проверить если все эти программы, если нет, то установить:

Git
g++ or clang++
Python 2 (for v8)
pkg-config (linux only for v8)
libc++-dev (linux only)
libc++abi-dev (linux only)
libglib2.0-dev (ubuntu 20.04)
libtinfo5 (ubuntu 20.04)
ninja-build (ubuntu arm64)
  • Может отсутствовать make (apt install make)
  • Не будет ссылки на python
/usr/bin/env: ‘python’: No such file or directory

Устанавливаем python второй версии:

sudo apt install python2

И настраиваем ссылку:

sudo ln -s /usr/bin/python2 /usr/bin/python
sudo apt install postgresql-server-dev-12

Официальный сайт: PLV8 Documentation

Опубликовано

Очистка логов journalctl в Ubuntu

Для очистки логов по условиям: до даты или обрезать до такого-то размера (в кол-ве записей или в Мб) можно использовать встроенные команды:

journalctl --vacuum-size=128M 
journalctl --vacuum-time=1d

Опубликовано

Оптимизация запросов из базы данных для мобильного приложения

  • Основным механизмом этого процесса является trigger в PostgreSql.Создается функция которая обновляет информацию о том, в каком состоянии находиться таблица. Состояние — это переменная в которой храниться время с типом данных double precision.
INSERT INTO core.sd_table_change (c_table_name, n_change)
VALUES (_c_table_name, (SELECT EXTRACT(EPOCH FROM now())))
ON CONFLICT (c_table_name) DO UPDATE
SET c_table_name = _c_table_name,
n_change = (SELECT EXTRACT(EPOCH FROM now()));
  • Далее создается триггер, который изменяет «состояние таблицы»
CREATE OR REPLACE FUNCTION core.cft_change_version() RETURNS trigger
   LANGUAGE plpgsql
   AS $$
BEGIN
   IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      select core.sf_table_change_update(t.c_table_name_ref) from  (select TG_TABLE_NAME as c_table_name_ref UNION select  c_table_name_ref from core.sd_table_change_ref where c_table_name = TG_TABLE_NAME) as t; END IF; RETURN NEW;
END
$$;
ALTER FUNCTION core.cft_change_version() OWNER TO mobnius;
COMMENT ON FUNCTION core.cft_change_version() IS 'Триггер. Обновление справочной версии';
  • Для хранения информации о состоянии, создается 2 таблицы:
  1. Состояние таблиц — имя таблицы и её числовое состояние
  2. Таблицы зависимых состояний
CREATE TABLE core.sd_table_change (
c_table_name text NOT NULL,
n_change double precision NOT NULL
);
ALTER TABLE core.sd_table_change OWNER TO mobnius;
COMMENT ON TABLE core.sd_table_change IS 'Изменение состояния таблицы';
COMMENT ON COLUMN core.sd_table_change.c_table_name IS 'Имя таблицы';
COMMENT ON COLUMN core.sd_table_change.n_change IS 'Версия изменения';

ALTER TABLE core.sd_table_change
ADD CONSTRAINT sd_table_change_pkey PRIMARY KEY (c_table_name);
CREATE TABLE core.sd_table_change_ref (
id smallint DEFAULT nextval('core.sd_table_change_ref_id_seq'::regclass) NOT NULL,
c_table_name text NOT NULL,
c_table_name_ref text NOT NULL
);
ALTER TABLE core.sd_table_change_ref OWNER TO mobnius;
COMMENT ON TABLE core.sd_table_change_ref IS 'Зависимость таблиц состояний';
COMMENT ON COLUMN core.sd_table_change_ref.c_table_name IS 'Таблица';
COMMENT ON COLUMN core.sd_table_change_ref.c_table_name_ref IS 'Зависимая таблица';

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT sd_table_change_ref_pkey PRIMARY KEY (id);

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT sd_table_change_ref_c_table_name_fkey FOREIGN KEY (c_table_name) REFERENCES core.sd_table_change(c_table_name) NOT VALID;

ALTER TABLE core.sd_table_change_ref
ADD CONSTRAINT "sd_table_change_ref_cTable_name_ref_fkey" FOREIGN KEY (c_table_name_ref) REFERENCES core.sd_table_change(c_table_name) NOT VALID;
Сайт работает на WordPress