Слава нації
#StandWithUkraine

Роман Теличко

Блог з програмування, оптимізації та адміністрування систем

Тег postgresql

PostgreSQL: Создание индекса для uuid[]

// 25 Березня, 2018 | 1953 переглядів | коментарів (0)

Для начала что такое uuid:

UUID (universally unique identifier) — это стандарт идентификации, используемый в создании программного обеспечения, стандартизированный Open Software Foundation (OSF) как часть DCE — среды распределённых вычислений (Distributed Computing Environment (англ.)). Основное назначение UUID — это позволить распределённым системам уникально идентифицировать информацию без центра координации. Таким образом, любой может создать UUID и использовать его для идентификации чего-либо с приемлемым уровнем уверенности, что данный идентификатор непреднамеренно никогда не будет использован для чего-то ещё. UUID представляет собой 16-байтный (128-битный) номер.

В PostgreSQL, по умолчанию, нет индекса для столбцов массива uuid (uuid[]).
Для примера, вот простая таблица с столбцом массива uuid:

CREATE TABLE someitems (
    items uuid[]
);

Но когда мы пытаемся создать на нем индекс:

CREATE INDEX someitems_items_index ON someitems USING GIN (items);

Получаем следующую ошибку:

ERROR:  data type uuid[] has no default operator class for access method "gin"

Поэтому мы должны создать тип индекса, который понимает, как сравнивать элементы массива uuid (uuid[]).
Читати далі…

Установка pgAdmin4 в Ubuntu 16.04

// 2 Грудня, 2016 | 41547 переглядів | 5 коментарів

Обновился графический интерфейс для управления СУБД PostgreSQL – pgAdmin 4. Будем надеяться что новая версия не будет так сильно “сыпаться” 🙂
Пока ещё нет ни deb-пакета, ни ссылок на репозиторий, но попробовать уже хочется 🙂

Так что сегодня я расскажу как поставить pgAdmin4 в режиме клиента (pgAdmin4 Desktop)

Читати далі…

Потоковая репликация в PostgreSQL 9.6

// 27 Листопада, 2016 | 30287 переглядів | коментарів (0)

Потоковая репликация (streaming replication) является передачей записей из WAL (Write-Ahead Log) от мастера к репликам. Писать при этом можно только в мастер, но читать можно как с мастера, так и с реплик. В итоге мы получаем не просто горизонтальное масштабирование, а ещё и отказоустойчивую архитектуру (failover).
Приступим к настройке реплики.

  1. Начальные условия
  2. Устанавливаем PostgreSQL 9.6 в Debian 8 (jessie) на оба сервера
  3. Настраиваем мастер (master)
  4. Настраиваем слейв (slave)
  5. Всё работает? Всё работает!
  6. А давайте что-то поломаем?!
  7. Догоняем прежний мастер до актуального состояния и возвращаем ему прежний статус

Начальные условия

Возьмём 2 сервера (в моём случаи – виртуальные машины) с чистым Debian 8.6 (jessie) amd64.

  1. master: 192.168.0.100
  2. slave: 192.168.0.200

Читати далі…

PostgreSQL 9.5 в Ubuntu 15.10+

// 15 Лютого, 2016 | 1317 переглядів | коментарів (0)

Добавляем в /etc/apt/sources.list адрес к репозиторию с последним PostgreSQL:

sudo -s
echo "deb https://apt.postgresql.org/pub/repos/apt/ `lsb_release -s -c`-pgdg main" >> /etc/apt/sources.list

Получаем “волшебный” ключик:

sudo wget --quiet -O - https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc|sudo apt-key add -

И теперь всё просто:

sudo apt-get update && sudo apt-get upgrade && sudo apt-get dist-upgrade

И можно поставить, если у вас ещё не было:

sudo apt-get install postgresql-9.5

Создаём и оптимизируем свой tile-сервер для OpenStreetMap на основе Ubuntu 14.04

// 15 Вересня, 2015 | 18013 переглядів | 2 коментарі

Сегодня я расскажу как довольно просто поднять и настроить свой собственный сервер карт (тайловый сервер) на основе Ubuntu Server 14.04 LTS и OpenStreetMap.

Итак начнём. Из Википедии:

OpenStreetMap (дословно «открытая карта улиц»), сокращённо OSM — некоммерческий веб-картографический проект по созданию силами сообщества участников-пользователей Интернета подробной свободной и бесплатной географической карты мира.

Есть довольно подробная официальная статья об установке и настройке tile-сервера, но есть ещё более простой и быстрый способ.
Читати далі…

PostgreSQL. Храним таблицу в оперативной памяти (RAM)

// 7 Листопада, 2013 | 7136 переглядів | коментарів (0)

Несмотря на то что PostgreSQL является довольно мощной базой данных, в ней отсутствует полноценная поддержка хранения таблиц в оперативной памяти.
Ниже я расскажу как заставить PostgreSQL хранить выбранные таблицы в оперативной памяти для быстрых операций с ними.
Всё будет происходить в Debian.

Создадим пустую папку для монтирования

mkdir /mnt/ramfs

И смонтируем в неё ramfs

mount -t ramfs none /mnt/ramfs

Создадим папку для PostgreSQL и назначим на неё права.

mkdir /mnt/ramfs/pgdata
chown postgres:postgres /mnt/ramfs/pgdata
chmod 600 /mnt/ramfs/pgdata

Далее зайдём под суперпользователем базы данных PostgreSQL – postgres

su postgres
psql

И создадим новый TABLESPACE, размещение которого мы укажим в папке с смонтированной ramfs

postgres=# CREATE TABLESPACE ram LOCATION '/mnt/ramfs/pgdata';

Выдадим права на работу с этом TABLESPACE нашему пользователю (например myuser)

postgres=# GRANT CREATE ON TABLESPACE ram TO myuser;

Теперь нам осталось только создать новую таблицу и указать при её создании TABLESPACE ram.
Например:

CREATE TABLE mytesttable (
    begin_ip ip4 NOT NULL,
    end_ip ip4 NOT NULL,
    begin_num bigint NOT NULL,
    end_num bigint NOT NULL,
    country_code character(2) NOT NULL,
    country_name character varying(255) NOT NULL,
    ip_range ip4r
)
TABLESPACE ram;

Теперь PostgreSQL будет работать с этой таблицей как и с другими даже не подозревая что она “лежит” в ОЗУ.

Это всё.
Удачи.

PostgreSQL. 10 продвинутых команд для DBA с примерами

// 25 Жовтня, 2013 | 66478 переглядів | коментарів (0)

Топ 10 самих популярных команд для управления сервером PostgreSQL для настоящих администраторов баз данных (DBA).
Большинство команд подходят как для консольной утилиты psql, так и для запуска через ваш клиент.

  1. Как найти самую большую таблицу в базе данных PostgreSQL?
  2. Как узнать размер всей базы данных PostgreSQL?
  3. Как узнать размер таблицы в базе данных PostgreSQL?
  4. Как узнать текущую версию сервера PostgreSQL?
  5. Как выполнить SQL-файл в PostgreSQL?
  6. Как отобразить список всех баз данных сервера PostgreSQL?
  7. Как отобразить список всех таблиц в базе данных PostgreSQL?
  8. Как показать структуру, индексы и прочие элементы выбранной таблицы в PostgreSQL?
  9. Как отобразить время выполнения запроса в консольной утилите PostgreSQL?
  10. Как отобразить все команды консольной утилиты PostgreSQL?

Читати далі…

PostgreSQL 9.2+ в Ubuntu 13.04+

// 3 Липня, 2013 | 578 переглядів | коментарів (0)

Добавляем в /etc/apt/sources.list адрес к репозиторию с последним PostgreSQL:

sudo -s
echo 'deb https://apt.postgresql.org/pub/repos/apt/ precise-pgdg main' >> /etc/apt/sources.list

Получаем волшебный ключик:

sudo wget --quiet -O - https://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc|sudo apt-key add -

И теперь всё просто:

sudo apt-get update && sudo apt-get upgrade && sudo apt-get dist-upgrade

И можно поставить, если у вас ещё не было:

sudo apt-get install postgresql-9.2

Делаем запросы в PostgreSQL из nginx кэшируя с помощью Redis

// 23 Травня, 2013 | 10332 переглядів | коментарів (0)

Сегодня я расскажу как можно работать с базой данных PostgreSQL с помощью nginx’a без application’a (например, PHP или любого другого). Т.е. эта технология абсолютно не зависит от языка, на котором сделан сайт/проект/система.
Мы будем использовать мощь PostgreSQL в хранимых процедурах (stored procedures/functions), а кэшировать с помощью быстрого Redis.
Читати далі…

PostgreSQL. Список таблиц с очень частой записью на диск

// 5 Квітня, 2013 | 1038 переглядів | коментарів (0)

Предел возможностей БД часто упирается в дисковые операции. Поэтому стоит оптимизировать эти операции, меняя логику, архитектуру, масштабируя и пр.
Запрос выведет статистику по таблицам в обратном порядке по сумме операций записи, т.е. сверху будут таблицы с наиболее интенсивной записью.

SELECT
    schemaname                                      AS schema,  -- схема
    relname                                         AS table,   -- таблица
    pg_size_pretty( pg_relation_size(relid) )       AS tsize,   -- размер
    n_tup_upd + n_tup_ins + n_tup_del               AS write,   -- операций записи (I/U/D)
    seq_scan + idx_scan                             AS read,    -- всего чтений
    n_tup_ins                                       AS ins,     -- операции INSERT
    n_tup_upd                                       AS upd,     -- операции UPDATE
    n_tup_del                                       AS del      -- операции DELETE
FROM
    pg_stat_user_tables
ORDER BY
    ( n_tup_upd + n_tup_ins + n_tup_del ) DESC

Кстати, так вы можете получить список всех индексов, а так — список неиспользуемых индексов.