\l [список БД] (SELECT * FROM pg_database;)
\с db_name [подклчюение к БД db_name]
\с postgres template1 localhost 5432 - подключение к БД template1 пользователем postgres текущего сервера с указанным портом
\conninfo [просмотр инфы о текущей БД и о активном пользователе]
\dS+ [список табл., схем, размеров]
\d+ t [просмотреть степень сжатия табл. t]
\d t [индексы табл. t]
\dnS [список схем]
\dn+ alice [привилегии и роли]
\du [Роли]
\q [выйти из psql]
\du+ [пользователи] (Select * from "pg_users";)
\db [просмотр табл. пространств]
\! pwg [запуск команд shell]
\seten TEST Hello [установить переменной TEST значение Hello
\set [какие переменные сейчас есть]
\unset TEST [сбросить значение переменной TEST]
\echo :TEST [просмотреть переменную TEST]
\? variables [переменные]
\help [списко команд]
\h команда [синтаксис команды]
\o db_log - [запись вывода команды в файл db_log]
\i db_log [Запуск внешнего скрипта]
- PGDATABASE
- PGHOST
- PGOPTIONS
- PGPORT
- PGUSER
- PG_COLOR (использования цвета в сообщениях - always (всегда), auto (автоматически) и never (никогда)).
SELECT * FROM pg_database_size('test_pik4');
SELECT pg_database_size/1024/1024 AS pg_database_size_mb FROM pg_database_size('test_pik4'); - в Mb
TOP 5 самых больших таблиц:
SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;
Выгрузка в файл:
psql -d postgres -t -A -F "," -c "select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;" > output.csv
Создание и удаление БД:
CREATE DATABASE usersdb;
DROP DATABASE usersdb;
Переименовать БД:
SELECT pg_terminate_backend (pid) FROM pg_stat_activity WHERE datname = 'database1';
ALTER DATABASE "database1" RENAME TO "database1_old";
Посмотреть под каким пользователем мы подключены
SELECT user;
Список логинов:
SELECT usename FROM pg_user;
Список ролей:
SELECT * FROM pg_roles;
Отключение права на вход:
ALTER ROLE bob NOLOGIN;
Сброс пароля для роли:
ALTER ROLE bob WITH PASSWORD 'your_password';
удалить все объекты bob:
DROP OWNED BY bob;
удалить роль bob:
DROP ROLE bob;
переназначение овнера объектов alice новому логину bob:
REASSING OWNER BY alice TO bob;
Создание нового пользователя (Роли)
CREATE ROLE alice LOGIN PASSWORD 'securePass1';
CREATE ROLE john SUPERUSER LOGIN PASSWORD 'securePass1';
CREATE ROLE dba CREATEDB LOGIN PASSWORD 'Abcd1234';
- SELECT - чтение данных (можно задавать на уровне столбцов)
- INSERT - вставка данных (можно задавать на уровне столбцов)
- UPDATE - изменение данных (можно задавать на уровне столбцов)
- REFERENCES - внешний ключ (можно задавать на уровне столбцов)
- DELETE - удаление данных
- TRUNCATE - очистка таблицы
- TRIGGER - создание триггеров
Назначение владельца:
ALTER TABLE table_name OWNER TO new_owner;
Добавление прав на таблицу для роли joe:
GRANT UPDATE ON accounts TO joe;
Удаление прав на таблицу для роли joe:
REVOKE ALL ON accounts FROM joe;
- Суперпользователи (SUPERUSER) - полный доступо ко всем объектам, проверки не выполняются
- Владельцы (OWNER) - доступ в рамках выданных привилегий (изначально получает полный набор), а также действия, не регламентируемые привилегиями, например удаление, выдача и отзы привилигеий и т.д.
- Остальные роли - доступ исключительно в рамках выданных привилегий
Дополнительные привилегии по умолчанию:
ALTER DEFAULT PRIVILEGES IN SCHEMA схема GRANT привилегии ON класс_объектов TO роль;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLES TO webuser;
ALTER DEFAULT PRIVILEGES IN SCHEMA схема REVOKE привилегии ON класс_объектов TO роль;
CREATE ROLE alice LOGIN;
CREATE SCHEMA alice;
GRANT CREATE, USAGE ON SCHEMA alice to alice;
ALTER SCHEMA alice OWNER TO alice;
- r -- SELECT ("read", чтение) Можно задавать на уровне столбцов
- w -- UPDATE ("write", запись) Можно задавать на уровне столбцов
- a -- INSERT ("append", добавление) Можно задавать на уровне столбцов
- d -- DELETE (удаление данных)
- D -- TRUNCATE (очистка таблицы)
- x -- REFERENCES (внешний ключ) Можно задавать на уровне столбцов
- t -- TRIGGER (создание триггеров)
- X -- EXECUTE (выполнение с правами: SECURITY INVOKER - вызвавшего (по умолчнанию); SECURITY DEFINER - создавшего)
- U -- USAGE
- C -- CREATE
- с -- CONNECT
- T -- TEMPORARY
- arwdDxt -- ALL PRIVILEGES (все права для таблиц)
!!! Привилегии можно выдавать и на определенные столбцы, например
GRANT INSERT(id, name) ON Table1 TO bob;
GRANT SELECT(name) ON Table1 TO bob;
GRANT ALL ON Table1 TO bob; - выдать все привилегии
Настройки репликации:
SELECT name, setting FROM pg_settings WHERE name in ('wal_level', 'max_wal_senders');
SELECT type, user_name, address, auth_method FROM pg_hba_file_rules WHERE database = ARRAY['replication'];
Состояние репликации:
SELECT * FROM pg_stat_replication \gx
select to_char(replay_lag,'HH24:MI:SS.MS') from pg_stat_replication;
Просмотр существующих слотов:
SELECT * FROM pg_replication_slots;
Просмотр "неактивных" слотов ("зависшие" слоты репликации):
SELECT * FROM pg_replication_slots WHERE active = 'f';
Удаление слотов репликации:
select pg_drop_replication_slot('slot_name');
Удаление всех "неактивных" слотов:
SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE active = 'f';
Проверка статуса режима восстановления и перевод в режим чтения-записи.:
SELECT pg_is_in_recovery();
-- RETURN t
SELECT pg_promote();
-- RETURN t
SELECT pg_is_in_recovery();
-- RETURN f
Также можно PROMOTE сделать
su - postgres
pg_ctl promote -D /var/lib/pgsql/14/data
Инструменты:
- pg_dump - бэкапирование одной конкретной БД
- pg_dumpall - бэкапирование целого кластера или инстанса.
- pg_basebackup - бинарное бэкапирование целого кластера или инстанса. (необходимо быть суперпользователем или иметь права REPLICATION)
Получить список БД для бэкапа
su - postgres -c 'psql -A -q -t -c "select datname from pg_database"'
- -A - Невыровненный режим вывода (Равнозначно команде \pset format unaligned.)
- -q - Указывает, что psql должен работать без вывода дополнительных сообщений.\
- -t - Отключает вывод имён столбцов и результирующей строки с количеством выбранных записей.
- -c - Передаёт psql команду для выполнения
export PGPASSWORD=$POSTGRES_PASSWORD && pg_dump -U $POSTGRES_USER database2 | gzip > /backup_location/database2.sql.gz"
-- Выгрузка всех схем, имена которых начинаются с east или west, заканчиваются на gsm и не содержат test:
pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
-- То же самое, но с использованием регулярного выражения:
pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
Параметры для подключения:
- -h - имя сервера.
- -p - порт.
- -U - имя пользователя.
- -W - Принудительно запрашивать пароль перед подключением к базе данных
Параметры создания резервной копии
- -a - сохраняем только данные. Например, при использовании этого параметра связи между таблицами не сохраняются.
- -B - не сохраняем большие объекты в дампе.
- -c - Добавить удаление (DROP) объектов, прежде чем пересоздавать их.
- -C - добавляем в скрипт команды для создания БД и подключения к ней. Применимо только к SQL скриптам.
- -E - устанавливаем определенную кодировку дампа.
- -f - каталог, куда сохраняем дамп.
- -F - формат дампа. (p - SQL скрипт(по умолчанию); c - архив; d - каталог; t - формат .tar)
- -g - Выгружать только глобальные объекты (роли и табличные пространства), без баз данных
- -j - выполняем утилиту в многопоточном формате.
- -n - сохраняем схемы, удовлетворяющие шаблону.
- -N - не сохраняем схемы, удовлетворяющие шаблону.
- -o - добавляем в скрипт команды, связанные с установкой владельцев
- -O - не добавляем в скрипт команды, связанные с установкой владельцев.
- -s - сохраняем только схемы.
- -t - сохранить таблицы, удовлетворяющие шаблону.
- -T - не сохраняем таблицы, удовлетворяющие шаблону.
- -x - не сохраняем права доступа.
- -Z - выбираем уровня сжатия (0 - не сжимать, 9 - максимальный).
pg_dumpall -c -g > _globals.sql
export PGPASSWORD=$POSTGRES_PASSWORD && pg_dump -Fd $db -j $(nproc) -h localhost -U $POSTGRES_USER -f /backup_location/$db
export PGPASSWORD=$POSTGRES_PASSWORD && sudo -u postgres pg_basebackup -U $POSTGRES_USER -R -Ft -z -D /backup_location
- -R - Создать файл standby.signal и добавить параметры конфигурации в файл postgresql.auto.conf в целевом каталоге (Упрощает восстановление)
- -F - формат бэкапа. (p - текст (по умолчанию); t - формат .tar)
- -D - Целевой каталог, куда будет записана копия.
- -z - Включает gzip-сжатие выводимого tar-файла с уровнем компрессии по умолчанию
psql -f _global.sql
dropdb dbname
createdb -T template0 dbname
gunzip -c dbname.gz | psql dbname
psql -f _global.sql
pg_restore -c -C -d postgres -v --if-exists -F d -j$(nproc) UserDatabase
- -c - Удалить (DROP) объекты базы данных, прежде чем пересоздавать их.
- -C - Создать базу данных, прежде чем восстанавливать данные.
- -F d - Задаёт формат архива (d - Каталог)
- -j - Количесто параллельных потоков
- -d - Имя контекста (postgres)
- -v - Включает режим подробных сообщений.
- --if-exists - удаление происходит только если обьект существует.
Восстановление Base:
tar xzf /backup_location/base.tar.gz -C /pgdata
Определение табличных пространств:
cat /pgdata/tablespace_map
16575 /data_tblspc 16576 /index_tblspc
Распаковка табличных пространств:
tar xzf 16575.tar.gz -C /data_tblspc
tar xzf 16576.tar.gz -C /index_tblspc
Распаковка WAL:
tar xzf pg_wal.tar.gz -C /pgdata/pg_wal
Ставим "сигнальные" файлы:
su - postgres -c "touch /var/lib/pgsql/14/data/recovery.signal"
Запускаемся:
systemctl start postgresql-14
Ждем пока поднимется все. Подключаемся psql и смотрим, что восстановилось.
Старт службы в рабочем режиме:
systemctl stop postgres-14
rm -f /var/lib/pgsql/14/data/recovery.signal
rm -f /var/lib/pgsql/14/data/standby.signal
systemctl start postgres-14
Показывает списов и размер WAL файлов:
SELECT * FROM pg_ls_waldir();
SELECT COUNT(name) AS CountWAL, SUM(size)/1024/1024 AS SizeWAL_Mb, SUM(size)/1024/1024/1024 AS SizeWAL_Gb FROM pg_ls_waldir();
Позиции записи («хвоста») и вставки («головы») WAL в общем:
SELECT pg_current_wal_lsn(), pg_current_wal_insert_lsn();
Параметры:
- FULL - режим «полной» очистки, который может освободить больше пространства, но выполняется гораздо дольше и запрашивает исключительную блокировку таблицы. (Требуется доп.место на диске). Похожим образом работает TRUNCATE.
- FREEZE - агрессивная «заморозка» кортежей. Агрессивная заморозка всегда выполняется при перезаписи таблицы, поэтому в режиме FULL это указание избыточно.
- VERBOSE - Выводит подробный отчёт об очистке для каждой таблицы.
- ANALYZE - Обновляет статистику, которую использует планировщик для выбора наиболее эффективного способа выполнения запроса.
-- Очистка одной таблицы test, проведение её анализа для оптимизатора и печать подробного отчёта о действиях:
VACUUM (VERBOSE, ANALYZE) test;
-- Проведение очистки всей БД, проведение анализа для оптимизатора всеи печать подробного отчёта о действиях:
VACUUM (VERBOSE, ANALYZE);
vacuumdb - обертка для использования в ОС
Особенности:
- Выполняется параллельно с другими транзакциями
- Чистый запуск нагружает подсистему ввода-вывода
- Редкий запуск приводит к росту размера файлов
Вставка данных:
INSERT INTO Test1(ID, Name, Date) SELECT i, 'Test', '2021-09-30' FROM generate_series(1,1000000) i;
Посмотреть настройки:
SELECT name, setting FROM pg_setting;
Применить новые настройки без рестарта службы:
SELECT pg_reload_conf();
Вывод версии:
SELECT version();
Транзакции:
- RESET work_mem; [сбросить значение по умолчанию]
- BEGIN; [начать транзакцию]
- SET work_mem TO '64MB'; [установить значение для транзакции]
- SHOW work_mem; [покажет уже 64Мб]
- ROLLBACK; [откатили транзакцию вернулось 12Мб]
Прибить все коннекты к конкретному инстансу:
select pg_terminate_backend(pid) from pg_stat_activity where 1=1 and datname='posplus_masterdata_pos-integration_new_streams' and pid <> pg_backend_pid();
- pg_backend_pid - это текущая сессия (что б себя не кильнуть)
- pg_terminate_backend() киляет сессию
Создание пустого файла для решения проблемы с местом (bash):
dd if=/dev/zero of=file2gb bs=4k iflag=fullblock,count_bytes count=2G
Что запрашивать у разработчика приложения для конфигурирования Postgres:
- Обьем данных
- Размер БД (Одна БД или несколько, сколько таблиц, их связь)
- Будет ли партиционирование (Если да как реализовано - например по времени, по магазинам)
- Предполагаемое максимальное количество соединений
- Локали и спец. настройки.
====================================