Postgres Guru | Базы данных

​​👨‍🎓 Находим таблицы, содержащие определённую колонку в PostgreSQL. Иногда нам необходимо найти все таблицы в базе данных, которые содержат определенную колонку. Такая задача может возникнуть для целей оптимизации базы данных, для ее аудита, или для каких-то нужд разработки приложения и его взаимодействия с базой данных. Давайте разберемся как можно решить такую задачу. В PostgreSQL есть стандартная схема баз данных - information_schema, которая содержит различные представления, из которых мы можем получить информацию о схемах базах данных, таблицах, типах данных и т.д. В нашем случае нам пригодится представление под названием information_schema.columns, которое как раз и содержит информацию о колонках таблиц. Предположим, что нам нужно найти все таблицы в базе данных, содержащие колонку с именем vendor. Запрос, решающий данную задачу будет выглядеть так: SELECT table_schema, table_name FROM information_schema.columns WHERE column_name = 'vendor' ORDER BY table_schema, table_name; Здесь: table_schema, table_name - имена схем и таблиц, в которых будет содержаться наша колонка; в выражении ORDER BY мы сортируем результат запроса по именам схем и таблиц, чтобы было удобней в нем ориентироваться. В результате получим имена всех таблиц и схем, в которых они расположены, содержащих колонку vendor. Таким образом с помощью представлений основной схемы PostgreSQL можно получать разную полезную информацию о структуре базы данных. Об остальных полезных представлениях information_schema поговорим в будущих постах. #queries

​​🤖 Импортируем JSON из файла в таблицу PostgreSQL. Про возможности работы с JSON в PostgreSQL мы уже с вами несколько раз говорили. Давайте рассмотрим ситуацию, когда нам нужно импортировать большой объем данных в формате JSON из файла. Казалось бы, что тут сложного? Берём команду COPY, указываем путь к файлу с JSON и готово! Но сложности могут возникнуть со структурой файла JSON. Откройте свой JSON файл и посмотрите как в нем организованы данные. Если они вот в таком формате: [ {"id":1,"first_name":"Maks","email":"[email protected]","gender":"Male"}, {"id":2,"first_name":"Elena","email":"[email protected]","gender":"Female"}, ... ] то придется его привести к нужному виду, чтобы все получилось. Все дело в том, что команда COPY не воспримет корректно такой формат JSON файла, когда данные разбиты на массивы, заключенные в символы [ ]. Нам нужен чистый JSON файл, разделенный на строки, так называемы Newline Delimited JSON или NDJSON. Выглядеть такой формат может так: {"id":1,"first_name":"Maks","email":"[email protected]","gender":"Male"} {"id":2,"first_name":"Elena","email":"[email protected]","gender":"Female"} Конвертировать JSON в NDJSON можно с помощью конвертера, расположенного вот по этой ссылке: ➡️ https://konbert.com/convert/json/to/ndjson Конвертер может переводить JSON и в другие форматы, типа CSV и т.д. Минусом конвертера является то, что он условно бесплатный, в бесплатной версии доступна конвертация файла размером не более 5 MB и общий размер файлов, конвертируемых за месяц не должен превышать 50 MB. По этому мы пойдем другим путем - бесплатным 😊. Поможет нам в этом утилита jq. Jq - мощная утилита, используемая для обработки и фильтрации входящих данных JSON. Установим утилиту командой: # sudo apt install jq Далее конвертируем наш JSON командой: # jq -c '.[ ]' путь_к_нашему_JSON_файлу > путь_к_новому_JSON_файлу Можно немного усложнить команду, чтобы удалить лишнюю табуляцию в файле и пустые строки: # cat путь_к_нашему_JSON_файлу | jq -cr '.[ ]' | sed 's/\\[tn]//g' > путь_к_новому_JSON_файлу Во втором варианте мы еще добавляем утилиту sed, которая и удалит лишние пустые строки и табуляцию. Теперь наш файл готов к загрузке в базу. Рекомендуется сначала создать временную таблицу и в нее загрузить JSON. Это позволит избежать возможной каши в данных в основной таблице, если что-то пойдет не так при загрузке, а так же сразу выявить все возможные ошибки. ⚠️ Перед массовой загрузкой данных лучше лишний раз подстраховаться и сделать резервную копию таблицы, в которую будете грузить данные. Создаем временную таблицу с единственной колонкой с типом JSON: CREATE TABLE temp (data json); Можно создать колонку и с типом JSONB, но загрузка пройдет быстрее если тип колонки будет JSON. Но для дальнейшего хранения данных в базе все же оптимальней использовать формат JSONB. О различиях в форматах JSON и JSONB мы уже писали. Теперь импортируем данные: \COPY temp (data) FROM 'путь_к_конвертированному_JSON_файлу'; Теперь мы можем перенести данные из временной таблицы в основную. Такая манипуляция нам так же позволит привести данные к нужным типам в основной таблице и перенести только нужные нам данные. Команда может выглядеть вот так: INSERT INTO имя_целевой_таблицы SELECT (data->>'id')::integer, data->>'first_name' FROM temp; После того как все что нужно перенесли, не забываем удалить временную таблицу: DROP TABLE temp; Вот такими не особо хитрыми манипуляциями мы можем загрузить данные из JSON файла в базу данных. #pgjson

Сложности теории проектирования баз данных #pgmeme

#pgmeme

​​⚒️ Инструмент, который позволяет настроить API для работы с запросами PostgreSQL. PostgREST — это автономный веб-сервер, который превращает вашу базу данных PostgreSQL непосредственно в RESTful API. Структурные ограничения и разрешения в базе данных определяют конечные точки и операции API. ✅ Безопасность : 📌 Управление доступом на основе ролей : Использует систему ролей и разрешений PostgreSQL для управления доступом. 📌 Безопасность на уровне строк : Поддерживает политики безопасности PostgreSQL на уровне строк для ограничения доступа к определенным строкам. ✅ Эффективность : Разработано с расчетом на легкость и эффективность, использующее собственные возможности PostgreSQL для быстрого извлечения и обработки данных. ✅ Масштабируемость : Может обрабатывать большие наборы данных и большие объемы трафика. ✅ Случаи использования: 📌 Создание RESTful API : Быстрое создание API для приложений без написания шаблонного кода. 📌 Микросервисы : Использование в качестве части архитектуры микросервисов, где каждый сервис может иметь собственную базу данных с выделенным экземпляром PostgREST. 📌 Интеграция данных : Интеграция с другими системами и службами с использованием стандартных протоколов HTTP. ✅ Установка и настройка: Чтобы установить PostgREST, вы можете загрузить бинарный файл из официальных релизов PostgREST GitHub или использовать менеджеры пакетов, такие как Homebrew, Docker или apt-get. Пример базовой настройки: Предположим есть таблица products из которой необходимо получить наименование товара и цены. 📌 Установка PostgREST Загрузите последнюю версию PostgREST с официальной страницы релизов . Подробнее запуск, установка. wget https://github.com/PostgREST/postgrest/releases/download/vX.X.X/postgrest-vX.X.X-linux-x64-static.tar.xz tar -xf postgrest-vX.X.X-linux-x64-static.tar.xz sudo mv postgrest /usr/local/bin/ Замените vX.X.Xномером последней версии. 📌 Настройка PostgREST Создайте файл конфигурации (postgrest.conf) со следующим содержимым: db-uri = "postgres://postgres:yourpassword@localhost:5432/mydatabase" db-schema = "public" db-anon-role = "postgres" 📌 Запуск PostgREST Запустите PostgREST, используя файл конфигурации: postgrest postgrest.conf 📌 Извлечение данных из products таблицы Теперь вы можете использовать HTTP-клиент, например curlPostman (это инструмент командной строки, используемый для передачи данных с сервера или на сервер с использованием различных протоколов, включая HTTP, HTTPS, FTP и т. д), для извлечения данных из таблицы products. Например curl http://localhost:3000/products Это вернет все строки таблицы products в формате JSON. Чтобы получить только цены, вы можете указать столбец: curl http://localhost:3000/products?select=price Приведенная выше команда вернет что-то вроде этого: [   {"price": 9.99},   {"price": 19.99} ] ✅ Подробная информация в документации.

​​⚒️ Расширение аудита PostgreSQL (pgAudit) pg_audit расширение PostgreSQL, предназначенное для подробного протоколирования действий базы данных. ✅ Основные характеристики pg_audit 📌 Подробная регистрация : pg_audit может регистрировать широкий спектр действий базы данных, включая SELECT, INSERT, UPDATE, DELETE и другие команды SQL. Фиксирует как успешные, так и неудачные попытки. 📌 Гибкая конфигурация аудита : Администраторы могут указать, какие команды и действия следует проверять, используя параметры конфигурации. Поддерживает аудит на основе ролей, позволяя устанавливать различные настройки аудита. 📌 Аудит на уровне объектов : pg_audit может проводить аудит определенных объектов базы данных, таких как таблицы, представления и функции. Это позволяет проводить целенаправленный аудит критически важных объектов, не перегружая журналы ненужной информацией. 📌 Ведение стандартных журналов PostgreSQL : Журналы аудита записываются в стандартные файлы журналов PostgreSQL, что упрощает доступ к ним и интеграцию с существующими решениями по управлению журналами. 📌Безопасность : Повышает безопасность, позволяя обнаруживать подозрительные действия и попытки несанкционированного доступа. Глубокий уровень детализации сеансов и объектов, чем стандартное ведение журнала, имеющееся в PostgreSQL. Этот уровень детализации может помочь выявить любые необычные или нерегулярные запросы; например, запросы к системным таблицам.  Аудит также предоставляет информацию о том, какие таблицы, представления, процедуры и функции больше не используются. Удаление этих элементов снижает вероятность того, что злоумышленник внедрит в них вредоносный код, который может оставаться незамеченным в течение многих лет. ✅ Пример установки и настройки Для использования pg_audit вам необходимо установить расширение и настроить его в соответствии с вашими требованиями аудита. Установка расширения : CREATE EXTENSION pg_audit; Конфигурация : Добавьте в файл следующие параметры конфигурации postgresql.conf: shared_preload_libraries = 'pg_audit' pgaudit.log = 'read, write' shared_preload_libraries: Обеспечивает pg_audit загрузку при запуске PostgreSQL. pgaudit.log: Указывает, какие классы команд следует проверять. Возможные варианты read: , write, function, role, ddl, и misc. Перезагрузить конфигурацию : sudo systemctl reload postgresql Пример конфигурации для аудита на уровне объектов : ALTER SYSTEM SET pgaudit.log = 'read, write, ddl'; Более подробную информацию можно найти в документации.

​​🗜️ Pgcat - пуллер соединений для PostgreSQL на стероидах. Почему на стероидах? А потому что Pgcat умеет все тоже самое, что и PgBouncer, о котором мы уже писали и гораздо больше. Вот основные возможности Pgcat: ✅ Пуллер соединений. Здесь все тоже самое, что и в PgBouncer. Поддерживается два режима: режим сессий и режим транзакций; ✅ Балансировщик соединений. А вот и пошли стероиды 😁. Pgcat умеет автоматически балансировать запросы на чтение между основным сервером и серверами-репликами; ✅ Автоматическая обработка отказа (Failover). Pgcat может автоматически переключать запросы между репликами, если увидит, что одна из реплик вышла из строя; ✅ Статистика для админов. Сбор статистики подключений. При чём статистику можно собирать и с PgBouncer. Также статистику можно собирать и в Prometheus; ✅ Динамическое применение изменений конфигурации. Т.е. вам не нужно перезапускать Pgcat, чтобы применить новую конфигурацию. Так умеет и PgBouncer; ✅ Использование всех ядер процессора для оптимизации работы. Pgcat умеет распределять нагрузку между ядрами процессора для оптимизации своей работы. И это еще не все! Поддержка SSL соединений, шифрование соединения с помощью MD5 и SCRAM-SHA-256 и т.д. И это только те возможности, которые уже работают стабильно, и которыми можно пользоваться в продакшене. А есть еще экспериментальные возможности, работа на которыми еще плотно ведется. Среди них: шардин запросов на основе расширенного протокола SQL-запросов и на основе регулярных выражений, а также автоматический шардинг. Короче, я думаю я вас уже убедил попробовать Pgcat 😁. Тем более, что установка и настройка такие же простые как у PgBouncer. Установку можно произвести, собрав Pgcat из исходных кодов, установить его из репозитория разработчика, или запустив Docker-контейнер. Давайте посмотрим как установить Pgcat из репозиториев на Ubuntu: # echo "deb [trusted=yes] https://apt.postgresml.org $(lsb_release -cs) main" | \ # sudo tee -a /etc/apt/sources.list && \ #sudo apt install Pgcat Компоненты Pgcat установятся в следующие директории: Исполняемые файлы Pgcat - /usr/bin/Pgcat Файл описания службы Pgcat - /usr/systemd/system/pgcat.service Файл конфигурации Pgcat - /etc/pgcat.example.toml Прежде чем запустить Pgcat, нужно переименовать файл конфигурации pgcat.example.toml в pgcat.toml. Здесь находится официальный GitHub проекта: ➡️ https://github.com/postgresml/pgcat Здесь можно посмотреть описание всех настроек: ➡️ https://github.com/postgresml/pgcat/blob/main/CONFIG.md Резюмируя, можно сказать, что Pgcat подойдет тем кому нужен не только пуллинг соединений, но и различные продвинутые возможности. Для пуллинга соединений можно использовать и PgBouncer, он с этой работой справляется очень хорошо. #pgutils

​​🤖 Ранжируем данные с помощью оконных функций в PostgreSQL. Про оконные функции в PostgreSQL и что это такое мы уже писали. В той же заметке мы упоминали функцию RANK (). Сейчас разберёмся с ней более подробно и посмотрим в чем ее отличие от других подобных функций. Ранжированием называется присвоение рейтинга строке на основании определенных условий. Это может быть полезно при создании каких-то аналитических отчетов. Функция RANK () назначает рейтинг строке. У этой функции есть одна особенность, заключается она в том, что если у нас есть две первые строки с одинаковым рейтингом, например 1, то следующая строка получит рейтинг 3, а не 2 и т.д. Т.е. функция оставляет зазор в рейтинге после связанных одним значением строк на кол-во таких строк. Зачем это нужно разберемся далее. Синтаксис функции будет следующим: RANK() OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC] Здесь: partition_expression - выражение, которое позволит нам разбить результат запроса на партиции (окна) и ранжировать каждое такое окно отдельно. Можно и не разбивать; sort_expression - сортировка результата запроса каждого окна или запроса целиком. Давайте смотреть на примере. Допустим у нас есть какая-то такая таблица; CREATE TABLE student_grade ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, subject VARCHAR(20) NOT NULL, grade INT NOT NULL ); Т.е. здесь у нас таблица оценок гипотетических студентов по определенным предметам (subject). Мы можем ранжировать студентов по их оценкам в рамках конкретного предмета, например так: SELECT *, RANK() OVER ( PARTITION BY subject ORDER BY grade DESC) FROM student_grade; В результате получим ранжирование студентов по оценкам по каждому предмету отдельно. Если у нас будет несколько студентов с одинаковыми оценками в рамках одного предмета, то функция RANK () сделает зазор в рейтинге. В PostgreSQL существует еще одна функция для ранжирования - DENSE_RANK (). Основное ее отличие от функции RANK () заключается в том, что она не оставляет ни каких зазоров в рейтинге. Т.е. если у нас первые две строки с одинаковыми значениями получили рейтинг 1, то третья строка получит рейтинг 2, если у нас дальше будут несколько строк с одинаковыми значениями, то они получат рейтинг 3 и т.д. Синтаксис у этой функции такой же как и у предыдущей: DENSE_RANK () OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC] Когда какую функцию использовать? RANK () используется в том случае когда помимо рейтинга вам нужно ещё узнать и место строки в этом рейтинге, а DENSE_RANK () для простого ранжирования результата запроса. Таким образом наша любимая PostgreSQL представляет нам достаточно мощные функции для выполнения аналитических задач. Так что пользуемся! 💪😊 #pgfunc

​​🛠️ Разбираемся с обновлениями PostgreSQL ч. 3. В первой и второй части мы с вами посмотрели на минорные обновления, и изучили один из способов перехода на на новую версию PostgreSQL с помощью pg_dump. Но такой способ обновления не подойдет, если у вас большая база данных, точнее процесс обновления будет мучительно долгим. Поэтому, в этой заметке рассмотрим еще один способ перехода на новую основную версию PostgreSQL с помощью утилиты pg_upgrade. Преимуществом утилиты pg_upgrade является то, что она на основании свежеутсановленного кластера PostgreSQL новой версии, сравнивает структуру баз данных и кластеров, и если все ок, то просто делает жесткие символические ссылки (hardlink) на старый кластер. Таким образом обновление большого кластера PostgreSQL может занять несколько минут, а не часы как в случае переноса баз с помощью pg_dump. Для того, чтобы у нас все случилось, нам нужен свежеустановленный PostgreSQL той версии, на которую переходим. Новую версию можно установить и на тот же самый сервер, где у вас стоит старая версия, только обязательно нужно поменять порт в настройках. Потом порт можно будет изменить на стандартный. О том как установить две версии PostgreSQL на один сервер мы уже писали. Новый кластер PostgreSQL нам нужно только проинициализировать, но не запускать, pg_upgrade сделает это самостоятельно после завершения обновления. Также нам пока не надо заботиться о переносе файла postgresql.conf, мы сделаем это позже. Первое, что нам нужно сделать перед обновлением, это запустить утилиту pg_upgrade с ключем -- check. Утилита проверит совместимость двух кластеров без фактического обновления. Утилите нужно передать 4-е параметра: ✅ Путь к папке с установочными файлами старой версии PostgreSQL; ✅ Путь к папке с установочными файлами новой версии PostgreSQL; ✅ Путь к папке кластера старой версии PostgreSQL; ✅ Путь к папке кластера новой версии PostgreSQL. Проще это сделать, задав переменные окружения, например вот так: # export PGBINOLD=/путь_к_старой_PostgreSQL/bin # export PGBINNEW=/путь_к_новой_PostgreSQL/bin # export PGDATAOLD=/путь_к_старому_кластеру/ # export PGDATANEW=/путь_к_новому_кластеру/ А потом запустить утилиту: # pg_upgrade --check Нам нужно увидеть сообщение: *Clusters are compatible* Если появились какие-то ошибки в совместимости кластеров, то их нужно исправить. Одной из распространенных ошибок является: does not use data checksums but the new one does Эта ошибка говорит о том, что новый кластер инициализирован с проверкой контрольных сумм, а старый нет. Придется удалить новый кластер и заново его инициализировать, но уже без флага --data-checksums. Проверка контрольных сумм можно включить уже после обновления. ⚠️ После того как вы устранили все ошибки совместимости, нужно обязательно сделать бэкап вашего старого кластера! Если у вас есть реплики, то их тоже нужно подготовить к обновлению. На каждую реплику нужно установить новую версию PostgreSQL, но не инициализировать кластер. Реплики обновляться после обновления основного сервера. Теперь запускаем само обновление! Если вы задали переменные окружения, то достаточно запустить команду: # pg_upgrade --link Дожидаемся сообщения: Upgrade Complete Но на этом еще не все! Нам нужно скопировать конфигурационные файлы postgresql.conf, pg_hba.conf, и pg_ident.conf со старого кластера в новый. Файлы должны прижиться в новом кластере без проблем, так как их формат не меняется в новых версиях. Единственное, что в новых версиях могут появиться новые параметры, которых нет в старых. Поэтому вам придется либо перенести ваши настройки в новый файл, либо наоборот. Хорошей практикой является внесения своих настроек в самый низ файлов, так сразу становится понятно, что вы меняли. Осталось сделать различные служебные процедуры типо VACUUM ANALYZE, обновить расширения и включить проверку контрольных сумм. #pgsettings

​​Тонкий SQL юмор #pgmeme