Архив метки: Postgresql

PostgreSQL. Динамический LIMIT / Dynamic LIMIT

В качестве примера предлагаю варант использования динамического ограничения выводимых строк для функций PostgreSQL:

CREATE OR REPLACE FUNCTION <name of function>
(
 IN <Input param #1> <type>,
 IN <Input param #2> <type>,
 IN i_limit integer 
)
RETURNS TABLE 
(
 column1 <type>,
 column2 <type>,
 column3 <type>
)
AS
$$
SELECT 
 <column1>,
 <column2>,
 <column3>
FROM mm.gain_history a
...........
WHERE
...........
LIMIT
   COALESCE($3, NULL);
$$
LANGUAGE 'sql';

PostgreSQL. Скрипт для нахождения разрастающихся объектов базы данных

Недавно столкнулся с ситуацией, когда backup базы стал довольно внушительного размера, хотя на самом деле я прекрасно понимал, что даже самые большие таблицы не «весят» столько.

Как оказалось, выполнив следующий скрипт:

SELECT nspname || '.' || relname as name,
 pg_size_pretty(pg_total_relation_size(C.oid)) as totalsize, -- incl. TOASTed and indx
 pg_size_pretty(pg_relation_size(C.oid)) as relsize -- pure size (w/o TOAST and indexes)
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
 ORDER BY pg_total_relation_size(C.oid) DESC
 LIMIT 20;

я обнаружил, что есть некие объекты pg_toast_<некий порядковый номер>, имеющие громадный размер. При этом связанные с ними таблицы мой базы имели такой же размер.
Сделав VACUUM (автоматический вакуум у меня отключен на этой базе), все обернулось самым лучшим образом. 

Выполнив еще раз вышеуказанный запрос, pg_toast_ уже в результатах не оказался.

 

Postgresql. Backup to Amazon S3 through WAL-E on CentOS

В этой статье я расскажу про свой опыт разворачивания backup сервиса WAL-E для заливания бэкапов Postgresql баз на сервис Amazon S3.

Первым делом поведаю, что у меня было на входе:

1. ОС — Centos 6.2
2. DB — Postgresql 9.2
3. Желание хранить бэкапы базы данных на Amazon S3

Далее опишу пункты которые мною выполнялись(заведомо до этого я уже имел доступ к сервису Амазона):

Читать далее

Рассылка почты с PostgreSQL (Sending E-Mail from PostgreSQL)

Делая монитор процессов, происходящих в базе данных Postgresql, столкнулся с вопросом посылки некого аларма из базы данных на специфический адрес с уведомлением о происходящем.

Далее расскажу о своих шагах в этом направлении:

1. Учитывая то, что сам Postgresql этого делать не умеет, пришлось прибегнуть к сторонним библиотекам. Все во благо, когда СУБД позволяет интегрировать в себе «1000+1» язык разработки.

2. Первым делом надо проверить установлена ли у Вас библиотека Python для Postgres.

На моем сервере установлена CentOS + Postgresql 9.2

Путь папки с библиотеками — /usr/pgsql-9.2/lib/

В этой папке должно находится, что-то типа plpython2.so или plpython3.so (первой, более старой, вполне достаточно).

У меня этого не было, посему пришлось искать подходящий пакет. В качестве подходящего кандидата был выбран — postgresql92-plpython-9.2.2-1PGDG.rhel6.x86_64

2. Далее, следующим стейтментом создаем язык в Postgres:

CREATE LANGUAGE plpython2u;

3. Далее создаем функцию для отсылки сообщений:

CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT LANGUAGE plpython2u
AS $function$

import smtplib
sender = _from
receivers = receiver
message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"
% (_from,receiver,subject,send_message))

try:

smtpObj = smtplib.SMTP(smtp,port)
smtpObj.starttls()
smtpObj.login(_from, _password)
smtpObj.sendmail(sender, receivers,message)
print ('Successfully sent email')
except SMTPException:
print ('Error: unable to send email')

return message
$function$
;

4.  Пробуем послать первое сообщение (в качестве smtp сервера я использовал gmail):

select send_email
('<Sender address>',
'<Sender password>',
'smtp.gmail.com',
587,
'<email of recipient>',
'<Your subject>',
'<Message body>');

5. Результатом должен быть приход письма с <Sender address>.

Буду рад, если кому-то это объяснение поможет 🙂

Postgresql. Меняем создателя sequence / Change sequence owner

Вчера столкнулся с интересным синтаксисом для изменения создателя последовательности в Postgresql:

alter table <sequence name> owner to <user name>;

Так же попробуйте выполнить запрос:

select * from <sequence name>;

Там можно увидеть много интересного 🙂

Postgresql. Преобразовываем колонку в строку (сolumn to row)

Я не раз возвращался к подобной теме для других СУБД.

Сегодня покажу пример для Postgresql:

select array_to_string(ARRAY(select 'Тракторист Вася' union select 'Тракторист Петя'),',')

где

1) select ‘Тракторист Вася’ union select ‘Тракторист Петя’ — интерпретация табличного набора из одной колонки;

2) ARRAY — формирует массив данных из табличного набора.

3) array_to_string — преобразовывает массив в строку с разделителем (в моем случае это запятая).

На выходе получаем:
Тракторист Вася, Тракторист Петя