Postgres Guru | Базы данных
Становимся гуру PostgreSQL вместе! Сотрудничество: @Sferg007 Ссылка для друзей: https://t.me/+NRjYf8gGR3RmYmMy Сайт: https://ibtorg.ru Postgres Guru в VK https://vk.com/pg_guru
Ko'proq ko'rsatish2 265
Obunachilar
+324 soatlar
+137 kunlar
+12830 kunlar
- Kanalning o'sishi
- Post qamrovi
- ER - jalb qilish nisbati
Ma'lumot yuklanmoqda...
Obunachilar o'sish tezligi
Ma'lumot yuklanmoqda...
👨🎓 Находим таблицы, содержащие определённую колонку в 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👍 14
🤖 Импортируем 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👍 10
Photo unavailableShow in Telegram
Сложности теории проектирования баз данных #pgmeme
😁 8💯 1
⚒️ Инструмент, который позволяет настроить 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}
]
✅ Подробная информация в документации.👍 10🔥 4💯 1
⚒️ Расширение аудита 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';
Более подробную информацию можно найти в документации.👍 11🙏 1💯 1
🗜️ 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👍 8🤔 3
🤖 Ранжируем данные с помощью оконных функций в 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🔥 6👍 4💯 1
🛠️ Разбираемся с обновлениями 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🔥 5👍 3💯 1
Boshqa reja tanlang
Joriy rejangiz faqat 5 ta kanal uchun analitika imkoniyatini beradi. Ko'proq olish uchun, iltimos, boshqa reja tanlang.