CREATE INDEX IF NOT EXISTS cd_results_jb_data_f_act_number_btree_idx ON core.cd_results USING btree ((jb_data #>> '{f_act_number}'::text[]) COLLATE pg_catalog."default" ASC NULLS LAST) TABLESPACE pg_default;
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
- Сборка закончилась с ошибкой: тут проще удалить каталог plv8-3.0.0 и заново запустить установку.
- How to fix ‘postgres.h’ file not found problem?
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 таблицы:
- Состояние таблиц — имя таблицы и её числовое состояние
- Таблицы зависимых состояний
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;
Установка pgAdmin4 in Ubuntu
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Установка pgAdmin4
sudo apt install pgadmin4Читать далее «Установка pgAdmin4 in Ubuntu»
PostgreSQL: преобразование строки в число
В базе данных доступны следующие строки:
Требуется в колонке c_house_number, которая является типом text, найти число и сохранить преобразованное значение в новой колонке:
Читать далее «PostgreSQL: преобразование строки в число»Bash-скрипт для переименования БД в PostgreSQL
#!/bin/bash psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'vote-dev';" psql -c 'ALTER DATABASE "vote-dev" RENAME TO "vote-dev-db";'
Используется ли функция в базе данных PostgreSQL
select proname, prosrc
from pg_proc
where prosrc like '%search text%';