Ещё одна шпаргалка по PostgreSQL

Содержание

Содержание
INSERT IGNORE
JOIN в запросах UPDATE
Удаление автоинкремента поля таблицы
Просмотр и изменение таймаута запросов
Сжатие таблиц
10 самых больших таблиц и индексов
Список неиспользуемых таблиц
Список неиспользуемых индексов
Выполнение SQL-запросов из сценариев оболочки
Перенос данных из таблицы MySQL в таблицу PostgreSQL
Обновление PostgreSQL до нового релиза
Использование файла ~/.pgpass
Просмотр и завершение активных процессов
Установка и настройка pg_activity
Восстановление резервной копии базы данных
Скрипт для обновления локальной резервной копии базы данных
Просмотр списка расширений
Просмотр прав доступа
Удаление прав доступа к таблицам
Удаление прав доступа к последовательностям
Удаление прав доступа по умолчанию к таблицам
Удаление прав доступа по умолчанию к последовательностям
Удаление прав доступа к схеме
Удаление пользователя
Дополнительные материалы

INSERT IGNORE

Вместо INSERT IGNORE из MySQL можно воспользоваться INSERT INTO с выражением ON CONFLICT DO NOTHING:

INSERT INTO ncc_snmp(snmp_port,
                     snmp_version,
                     snmp_community,
                     snmpv3_contextname,
                     snmpv3_securityname,
                     snmpv3_securitylevel,
                     snmpv3_authprotocol,
                     snmpv3_authpassphrase,
                     snmpv3_privprotocol,
                     snmpv3_privpassphrase,
                     discovered)
SELECT DISTINCT snmp_port,
                snmp_version,
                snmp_community,
                snmpv3_contextname,
                snmpv3_securityname,
                snmpv3_securitylevel,
                snmpv3_authprotocol,
                snmpv3_authpassphrase,
                snmpv3_privprotocol,
                snmpv3_privpassphrase,
                true
FROM discovery_snmp
ON CONFLICT DO NOTHING;

JOIN в запросах UPDATE

В отличие от MySQL, в PostgreSQL:

в выражении SET должны быть указаны только имена колонок обновляемой таблицы, а указание имени таблицы вместе с колонкой считается ошибкой,
выражение WHERE обязательно; если в запросе участвует более одной таблицы, вторая таблица должна соединяться с обновляемой таблицей в рамках этого выражения,
выражение JOIN можно использовать только в том случае, если в запросе участвуют более двух таблиц.

Пример запроса:

UPDATE discovery_device
SET _snmp_id = ncc_snmp.id
FROM discovery_snmp
JOIN ncc_snmp ON ncc_snmp.snmp_port = discovery_snmp.snmp_port
  AND ncc_snmp.snmp_version = discovery_snmp.snmp_version
  AND ncc_snmp.snmp_community = discovery_snmp.snmp_community
  AND discovery_snmp.snmp_version IN (1, 2)
WHERE discovery_snmp.id = discovery_device.snmp_id;

В этом примере:

обновляется таблица discovery_device,
в выражении WHERE с ней соединяется таблица discovery_snmp,
в выражении JOIN к таблице discovery_snmp присоединяется таблица ncc_snmp.

Удаление автоинкремента поля таблицы

ALTER TABLE ufa_rs485_archives_bak ALTER COLUMN id DROP DEFAULT;

Просмотр и изменение таймаута запросов

Для просмотра текущего таймаута выполнения запросов можно воспользоваться следующим выражением:

SHOW statement_timeout;

Для изменения текущего таймаута в рамках сессии можно воспользоваться следующим запросом:

SET SESSION statement_timeout = '60 s';

Сжатие таблиц

Сжать таблицу, освободив неиспользуемое место, можно следующим образом:

VACUUM first_table, second_table, ...;

Дополнительно после ключевого слова VACUUM можно указать одно или несколько дополнительных ключевых слов:

FULL - полное сжатие, сопровождающееся блокировкой всей таблицы и копированием данных в другой файл,
ANALYZE - дополнительно обновить статистику, используемую оптимизатором запросов для выбора плана выполнения запроса,
VERBOSE - выводить отчёт по каждой обработанной таблице.

10 самых больших таблиц и индексов

Подключаемся к интересующей базе данных, например, при помощи команды psql -d <database>, и выполняем запрос:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
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_relation_size(C.oid) DESC
LIMIT 10;

Или то же самое в байтах и с явными именами таблиц в запросе:

SELECT pg_namespace.nspname || '.' || pg_class.relname AS relation,
       pg_relation_size(pg_class.oid) AS size
FROM pg_class
LEFT JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE pg_namespace.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(pg_class.oid) DESC
LIMIT 10;

Источник: https://imbolc.name/notes/pg/size-of-databases

Список неиспользуемых таблиц

SELECT schemaname, 
       relname, 
       pg_size_pretty(pg_relation_size(schemaname ||'.'|| relname)) AS RelationSize
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  AND seq_scan + idx_scan = 0
ORDER BY pg_relation_size(schemaname ||'.'|| relname) DESC;

Список неиспользуемых индексов

Внимание! Этот запрос возвращает также ключи уникальности, удаление которых может привести к появлению дублирующихся записей и поломке приложения:

SELECT schemaname, 
       relname, 
       indexrelname,
       pg_size_pretty(pg_relation_size(schemaname ||'.'|| indexrelname)) AS RelationSize
FROM pg_stat_all_indexes
WHERE schemaname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  AND idx_scan = 0
  AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(schemaname ||'.'|| indexrelname) DESC;

Выполнение SQL-запросов из сценариев оболочки

Для выполнения SQL-запросов из сценариев оболочки можно воспользоваться такой конструкцией:

PGPASSWORD=p4$$w0rd psql -U username -d database -h host.domain.tld -t <<END
SELECT 1;
END

Перенос данных из таблицы MySQL в таблицу PostgreSQL

Сохраняем содержимое таблицы из MySQL:

$ mysqldump --single-transaction --no-create-info --add-locks=0 --complete-insert --compatible=postgresql -uroot -p base table > table.sql

Восстанавливаем резервную копию в PostgreSQL:

$ cat table.sql | psql database

Использованный источник: Import MySQL dump to PostgreSQL database

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

Обновление PostgreSQL до нового релиза

Создаём резервную копию. Убедитесь, что база данных не находится в процессе обновления.

$ pg_dumpall > outputfile

Устанавливаем Postgres 10. Следуем инструкциям на странице: PostgreSQL Apt Repository

Теперь запускаем apt-get install postgresql-10. Новая версия будет установлена рядом со старой версией.

Запускаем pg_lsclusters:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10  main    5433 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

Уже есть кластер main в 10 версии (потому что он создаётся по умолчанию при установке пакета). Это делается для того, чтобы свежие инсталляции работали сразу после установки без необходимости создавать кластер, но, конечно, это препятствует обновлению 9.6/main, т.к. уже есть 10/main. Рекомендуется удалить кластер 10 при помощи pg_dropcluster, а затем выполнить обновление при помощи pg_upgradecluster.

Останавливаем кластер 10 и удаляем его:

# pg_dropcluster 10 main --stop

Останавливаем все процессы и сервисы, пишущие в базу данных. Останавливаем базу данных:

# systemctl stop postgresql

Обновляем кластер 9.6:

# pg_upgradecluster -m upgrade 9.6 main

Запускаем PostgreSQL снова:

# systemctl start postgresql

Запускаем pg_lsclusters. Кластер 9.6 должен быть в состоянии down, а кластер 10 должен быть в состоянии online на порту 5432:

Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5433 down   postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

Первым делом проверяем, что всё работает. Затем удаляем кластер 9.6:

# pg_dropcluster 9.6 main --stop

Заметки по pg_upgradecluster:

Это руководство годится для обновления с 9.5 до 10.1. При обновлении с более старых версий, возможно потребуется пропустить -m upgrade на шаге №6:

# pg_upgradecluster 9.6 main

Если у вас очень большой кластер, вы можете воспользоваться pg_upgradecluster с опцией --link option, чтобы обновление происходило без копирования, на месте. Однако это опасно - если случится ошибка, можно потерять данные. Просто не используйте эту опцию без необходимости, т.к. -m upgrade работает достаточно быстро.

Использованные материалы:

Документация: Upgrading a PostgreSQL Cluster
Gist #1: delameko/upgrade-postgres-9.5-to-9.6.md
Gist #2: johanndt/upgrade-postgres-9.3-to-9.5.md
What happens if I interrupt or cancel pg_upgradecluster?
Ubuntu manpage for pg_upgradecluster

P.S. Это руководство годится для обновления с 9.6 до 11 и с 10 до 11.

Источник: Upgrade PostgreSQL from 9.6 to 10.0 on Ubuntu 16.10 - A Step-by-Step Guide

Использование файла ~/.pgpass

В домашнем каталоге пользователя можно разместить файл ~/.pgpass, в котором можно указать пароли, которые клиенты PostgreSQL будут использовать для подключения к базам данных. Формат фала такой:

<server>:<port>:<database>:<user>:<password>

Например, для подключения к базе данных ncc через локальный PgBouncer под пользователем ncc может использоваться такая строчка:

localhost:6432:ncc:ncc:Sho9aePh6Koog8ag

Просмотр и завершение активных процессов

Для просмотра активных процессов можно воспользоваться одним из запросов:

SELECT * FROM pg_stat_activity WHERE state = 'active';
SELECT pid, state, usename, query FROM pg_stat_activity;

Вежливое завершение процесса:

SELECT pg_cancel_backend(PID);

Принудительное завершение процесса:

SELECT pg_terminate_backend(PID);

Где PID - идентификатор процесса для завершения.

Установка и настройка pg_activity

Утилита pg_activity используется для просмотра SQL-запросов, выполняемых в СУБД PostgreSQL в настоящее время. По существу эта утилита аналогична более известной утилите mytop для MySQL.

Установим pg_activity:

# apt-get install pg-activity

Для локального запуска pg_activity пользователем stupin без запроса пароля отредактируем файл /etc/postgresql/9.6/main/pg_hba.conf следующим образом:

local   all             stupin                                  peer

Перезагрузим PostgreSQL с новой конфигурацией:

# /etc/init.d/postgresql reload

Теперь создадим пользователя stupin в СУБД и выдадим ему права.

Переключимся на учётную запись администратора СУБД PostgreSQL:

# su - postgres

Запустим SQL-клиента PostgreSQL:

$ psql postgres

Выполним следующие SQL-запросы для создания пользователя stupin и предоставления прав, необходимых для работы pg_activity:

CREATE USER stupin;
ALTER USER stupin WITH SUPERUSER;

Для запуска pg_activity этого должно быть достаточно.

Восстановление резервной копии базы данных

Для восстановления содержимого таблиц базы данных из резервной копии поверх имеющихся таблиц и с игнорированием ошибок смены владельца базы данных можно воспользоваться командой следующего вида:

PGPASSWORD="p4$$w0rd" pg_restore -cO -U stupin -d database backup.bak

Скрипт для обновления локальной резервной копии базы данных

Для автоматизации пересоздания локального пользователя, базы данных, скачивания резервной копии с удалённого сервера, доступного по SSH, и восстановления скачанной резервной копии в локальную базу данных можно воспользоваться скриптом dev db.sh. Настройки для скачивания резервной копии и развёртывания базы данных можно выставить в переменных внутри самого скрипта. Скрипту нужно передать один аргумент, при помощи которого указывается действие, которое необходимо выполнить:

create - создать пользователя и базу данных,
drop - удалить пользователя и базу данных,
test - проверить доступность базы данных для пользователя,
fetch - скачать резервную копию с удалённого сервера,
restore - восстановить резервную копию в локальную базу данных.

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

$ ./dev_db.sh 
./dev_db.sh create | drop | test | fetch | restore

Для настройки беспарольного доступа к удалённому серверу по SSH можно обратиться к статье Настройка SSH.

Просмотр списка расширений

Для просмотра списка расширений, активных в базе данных, нужно подключиться к ней консольным клиентом psql и выполнить команду:

\dx

Для просмотра доступных расширений и их версий можно воспользоваться следующим запросом:

SELECT name, version, installed
FROM   pg_available_extension_versions;