Рубрика: postgresql

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

Autodoc для postgresql

Ставим приложение autodoc

$ sudo apt install libdbi-perl libhtml-template-perl libterm-readkey-perl libdbd-pg-perl
$ git clone https://github.com/cbbrowne/autodoc.git
$ cd autodoc
$ sudo make install

Дальше запускаем скрипт

postgresql_autodoc -d cic-dev-db -h 192.168.17.111 -u postgres --password -t html -s core -f cic-dev-db-core

Примечание: схемы можно передать как

-s "core|dbo"

Исходный код: https://github.com/cbbrowne/autodoc

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

Обновление строки в postgresql

UPDATE dummy 
SET customer=subquery.customer, 
address=subquery.address, 
partn=subquery.partn 
FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ ...) AS subquery 
WHERE dummy.address_id=subquery.address_id;
UPDATE table T1 
SET column1 = T2.column1 
FROM 
table T2 
INNER JOIN table T3 USING (column2) 
WHERE T1.column2 = T2.column2;

https://stackoverflow.com/questions/6256610/updating-table-rows-in-postgres-using-subquery

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

Заметка: работа с JSON в postgresql

Работая с JSON в postgresql заметил следующие момент:

select t.value::uuid
from json_array_elements_text('["e7ded0cd-12dd-47f5-a75b-192376291e83"]'::json) as t;

select (t.value #>> '{id}')::uuid 
from json_array_elements('[{"id": "e7ded0cd-12dd-47f5-a75b-192376291e83"}]'::json) as t;

Две команды возвращают одинаковый результат, но разными способами.

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

Postgresql вывод информации в «консоль»

IF (select count(*) from json_array_elements(_users)) > 0 THEN
-- select * from dbo.cf_arm_cd_points(1, 5, 1, null, 4);
raise notice '%', _users;
-- обходчики выбраны и находим все маршруты за текущую дату
RETURN QUERY select * from dbo.cf_arm_cd_points((select array_to_json(array_agg(row_to_json(t))) #>> '{id}'
from (select r.id
from core.cd_userinroutes as uir
INNER JOIN core.cd_routes as r ON r.id = uir.f_route
where uir.b_main = true and uir.f_user IN (
select (t.value #>> '{f_user}')::integer from json_array_elements(_users) as t
) and _d_date >= r.d_date_start and (core.cf_old_date(r.d_date_end) or core.cf_old_date(r.d_extended))) as t)::json, now()::date);
END IF;
Опубликовано

Foreign Data Wrapper

https://riptutorial.com/postgresql/example/29417/foreign-data-wrapper

To access complete schema of server db instead of single table. Follow below steps:

  1. Create EXTENSION :
    CREATE EXTENSION postgres_fdw;
  1. Create SERVER :
    CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'host_ip', 
    dbname 'db_name', port 'port_number');
  1. Create USER MAPPING:
    CREATE USER MAPPING FOR CURRENT_USER
    SERVER server_name
    OPTIONS (user 'user_name', password 'password');
  1. Create new schema to access schema of server DB:
   CREATE SCHEMA schema_name;
  1. Import server schema:
     IMPORT FOREIGN SCHEMA schema_name_to_import_from_remote_db
     FROM SERVER server_name
     INTO schema_name;
  1. Access any table of server schema:
    SELECT * FROM schema_name.table_name;

This can be used to access multiple schema of remote DB.

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

Автоматическое создание backup

Ниже написан скрипт для автоматического создания копии БД с последующим восстановлением на тестовой среде. 

#!/bin/bash
echo "Архивация БД vote-dev"
dir_folder=/var/lib/postgresql/vote-dev
mkdir -p $dir_folder
cd $dir_folder
# создание backup
pg_dump vote-dev > $dir_folder/vote-dev-day_$(date +\%Y\%m\%d).bak
# disconnect БД
psql -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'vote-test';"
# удаление и создание БД
dropdb vote-test
psql -c 'create database "vote-test";'
psql -c 'ALTER DATABASE "vote-test" OWNER TO mobnius;'
# восстановление резервной копии
psql -U postgres vote-test < $dir_folder/vote-dev-day_$(date +\%Y\%m\%d).bak
ls -1tr | head -n -7 | xargs -d '\n' rm -f --
echo "Завершено"

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

Повышение производительности PostgreSQL после настройки параметров

Многие думают, что изменение параметров в postgresql.conf — это реальный путь к успеху. Однако это не всегда так. Конечно, чаще всего хорошие параметры конфигурации базы данных очень полезны. Тем не менее, во многих случаях реальные проблемы будут возникать из-за странного запроса, скрытого глубоко в некоторой логике приложения. Даже вполне вероятно, что запросы, вызывающие реальные проблемы, не являются теми, на которые вы обратили внимание. Возникает естественный вопрос: как мы можем отследить эти запросы и выяснить, что на самом деле происходит? Мой любимый инструмент для этого — pg_stat_statements, который всегда должен быть включен по моему мнению, если вы используете PostgreSQL 9.2 или выше (пожалуйста, не используйте его в более старых версиях).
 

Читать далее «Повышение производительности PostgreSQL после настройки параметров»
Сайт работает на WordPress