PostgreSQL

# Сделать новый контейнер 
docker run --rm -d -e POSTGRES_PASSWORD=123456 -v /home/user/postgres:/var/lib/postgresql/data --name postgres postgres:15.0
# Создать базу
docker exec postgres psql -U postgres -c "create database testdb1;"
# Список баз
docker exec postgres psql -U postgres -t -c "select datname from pg_database where datistemplate = 'f';" | tr -d '\r\n'
# Подключиться к базе testdb1 в контейнере postgres интерактивно
docker exec -it postgres psql -U postgres -d testdb1
# Наполнение базы тестовыми данными - https://www.getsynth.com/docs/blog/2021/03/09/postgres-data-gen
 
# Список схем в БД testdb1
docker exec postgres psql -U postgres -d testdb1 -t -c "select schema_name from information_schema.schemata;" | tr -d '\r\n'
 
# Удалить (drop) базу
docker exec -it postgres psql -U postgres -c "DROP DATABASE testdb1;"

Бэкап

Бэкап баз

container=$(docker ps -qf "expose=5432")
user=postgres
backup_dir="/home/user/db_backups"
current_date=$(date +%F-%H-%M)
days_keep=7
 
mkdir -p $backup_dir/$current_date
for db_name in $(docker exec $container psql -U $user -t -c "select datname from pg_database where datistemplate = 'f';" 2>&1 | tr -d '\r\n')
do
  docker exec $container pg_dump -U $user $db_name > $backup_dir/$current_date/$db_name.dump
  gzip $backup_dir/$current_date/$db_name.dump
  # combined backup + gzip
  # docker exec $container pg_dump -U $user $db_name |gzip > $backup_dir/$current_date/$db_name.dump.gz
done
 
find $backup_dir -maxdepth 1 -type d -mtime +$days_keep -exec rm -r {} +

https://stackoverflow.com/questions/24718706/backup-restore-a-dockerized-postgresql-database

Восстановление

# Восстановить базу testdb1 из сжатого дампа
zcat db_backups/2024-04-11_16-46/testdb1.dump.gz |docker exec -i postgres psql -U postgres -d testdb1
 
# Удалить схему public базы testdb1 вместе с вложенными объектами (cascade)
docker exec -i postgres psql -U postgres -d testdb1 -c 'drop schema public cascade;'
# Восстановить схему public базы testdb1 из сжатого дампа
zcat db_backups/2024-04-11_16-46/testdb1/public.dump.gz |docker exec -i postgres psql -U postgres -d testdb1

DBA1

https://postgrespro.ru/education/courses/DBA1
Демо-база: https://postgrespro.ru/education/demodb

Установка и управление сервером

https://edu.postgrespro.ru/16/dba1-16/dba1_01_tools_install.html

---
# https://hub.docker.com/_/postgres

services:
  postgres:
    image: postgres:16-alpine
    ports:
      - 5432:5432
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_INITDB_ARGS: "--data-checksums"
      TZ: Europe/Moscow
      PGTZ: Europe/Moscow
    volumes:
      - ./data:/var/lib/postgresql/data

networks:
  default:
    name: postgres
    external: false

Если база уже создана, то часовой пояс поменять через docker-compose.yml не получится. Нужно выполнить запрос SET TIME ZONE 'Europe/Moscow';
https://stackoverflow.com/questions/44394295/how-to-change-timezone-of-postgresql-9-5-on-docker

initdb - создание кластера БД. Каталог, где инициализируется кластер, не может принадлежать суперпользователю. Владельцем кластера обычно является пользователь postgres.

PGDATA - переменная, указывающая на расположение каталога кластера. Её используют некоторые утилиты сервера, когда им нужно узнать расположение кластера, например, initdb и основная утилита управления сервером pg_ctl.

Ключ -k или --data-checksums при инициализации кластера включает подсчёт контрольных сумм на страницах данных для выявления сбоев.

mkdir /home/student/pgsql16/data
export PGDATA=/home/student/pgsql16/data
export PATH=/home/student/pgsql16/bin:$PATH
# Можно без -D, т. к. PGDATA задана. Если не указывать -U, то запрос будет идти от имени текущего пользователя.
initdb -U postgres -k -D /home/student/pgsql16/data

pg_ctl - старт/стоп/статус сервера, изменение конфигурации, запускается от имени владельца кластера БД.

# Запуск с логом. Без -D, т. к. PGDATA задана.
pg_ctl start -l /home/student/logfile
pg_ctl stop
 
# С какими параметрами собран кластер
docker exec postgres-postgres-1 pg_config --configure
 
# Проверка/включение проверки контрольных сумм (сервер нужно сначала остановить)
pg_ctl stop
sudo /usr/lib/postgresql/16/bin/pg_checksums --check -D /var/lib/postgresql/16/main
sudo /usr/lib/postgresql/16/bin/pg_checksums --enable -D /var/lib/postgresql/16/main
pg_ctl start
# Без остановки сервера статус контрольных сумм можно проверить
psql -Axtc 'show data_checksums'
docker exec -u postgres postgres-postgres-1 pg_controldata |grep checksum # Data page checksum version: 1 (включено)

https://www.crunchydata.com/blog/fun-with-pg_checksums

Специфика Ubuntu: вместо initdb используется pg_createcluster, который запускается автоматически при установке пакета и создает кластер БД с именем «main». Для удаления кластера - pg_dropcluster. Вместо pg_ctl - pg_ctlcluster.

sudo pg_ctlcluster 16 main start # stop/restart/status/reload
# Местоположение лога при пакетной установке
ls -l /var/log/postgresql/postgresql-16-main.log

Использование psql

psql — терминальный клиент для работы с СУБД. При запуске требуются параметры подключения. Выполняет команды SQL и psql.
Содержит инструменты для интерактивной работы, а также для подготовки и выполнения скриптов.
https://edu.postgrespro.ru/16/dba1-16/dba1_02_tools_psql.html
https://postgrespro.ru/docs/postgresql/16/app-psql

# Клиент на винде
# Подключение к БД postgres, пользователь postgres, хост ubuntu, порт 5432
psql.exe -d postgres -U postgres -h ubuntu -p 5432
# Информация о подключении в консоли postgres
\conninfo
Вы подключены к базе данных "postgres" как пользователь "postgres" (сервер "ubuntu": адрес "192.168.1.32", порт "5432").
# новое подключение, не покидая psql
\c[onnect]
# Команды SQL, в отличие от команд psql, могут располагаться на нескольких строках.
SELECT schemaname, tablename, tableowner 
FROM pg_tables 
LIMIT 5;

Форматирование вывода результатов запроса

Формат по умолчанию - включены заголовки, итоговая строка с кол-вом строк и выравнивание:

SELECT schemaname, tablename, tableowner FROM pg_tables LIMIT 3;
 schemaname |    tablename     | tableowner
------------+------------------+------------
 pg_catalog | pg_statistic     | postgres
 pg_catalog | pg_type          | postgres
 pg_catalog | pg_foreign_table | postgres
(3 строки)

\t - отключить заголовки и итоговой строки (кол-во строк).
\a - отключить выравнивание.
\pset fieldsep ' ' - установить разделитель (здесь: пробел). \pset без аргументов выводит текущие настройки.
\x - включить расширенный вывод.

Взаимодействие с ОС

\! pwd
# Задать переменную hello
\setenv hello world
\! echo hello
world
# \o[ut] - вывод в файл (в Windows тоже нужно использовать / в путях, например, c:/temp/out.txt)
\o folder/out.txt # вывод всех последующих команд будет добавляться в указанный файл
\o # Вернуть вывод на экран

Скрипты

# Сгенерировать строки путём запроса и выгрузить в файл. Если файл не указывать, то будет вывод на экран.
SELECT format('SELECT count(*) FROM %I;', tablename) FROM pg_tables LIMIT 3
\g (tuples_only=on format=unaligned) tmp/dba1_log
# Выполнить файл как скрипт (\i[nclude]), ниже другие варианты запуска скрипта
\i tmp/dba1_log
psql < tmp/dba1_log
psql -f tmp/dba1_log
# Можно запрос сразу выполнить как скрипт
SELECT format('SELECT count(*) FROM %I;', tablename) FROM pg_tables LIMIT 3 \gexec

Переменные, условия

-- Связать переменную User с переменной окружения USER (в Windows переменную %USERNAME% нужно писать без %)
\getenv Username USER
-- Задать переменную Test
\SET Test Hi
-- Вывод
\echo :Test :Username!
Hi student!
-- Запись запроса в переменную (запрос должен возвращать только одну запись)
SELECT now() AS curr_time \gset
\echo :curr_time
2024-07-20 08:05:42.618286+00
-- Вывести все переменные
\SET
 
-- Условие - если переменная не определена, задать её (здесь: командой ОС)
\IF :{?working_dir}
\ELSE
-- В винде будет `echo %cd%`
\SET working_dir `pwd`
\endif

Настройка psql

При запуске psql выполняются 2 скрипта, если они есть: общий системный скрипт psqlrc и пользовательский файл ~/.psqlrc. Местоположение системного скрипта можно узнать командой pg_config –sysconfdir. По умолчанию оба файла отсутствуют.

-- Если добавить этот код в ~/.psqlrc, то сразу после старта psql можно будет ввести :top5 для просмотра 5 самых больших таблиц
\SET top5 'SELECT tablename, pg_total_relation_size(schemaname||''.''||tablename) AS bytes FROM pg_tables ORDER BY bytes DESC LIMIT 5;'

Настройка сервера

https://edu.postgrespro.ru/16/dba1-16/dba1_03_tools_configuration.html
Описание параметров конфигурации

Базовая конфигурация хранится в файлах конфигурации. Основной файл (путь по умолчанию) - $PGDATA/postgresql.conf. Ряд параметров можно устанавливать для БД, пользователя или текущего сеанса. Если один и тот же параметр указан в конфигурационных файлах несколько раз, будет использоваться значение, считанное последним. Для применения изменений нужно перечитать конфиг и в некоторых случаях перезапустить сервер.

Каталог с доп. файлами конфигурации - /etc/postgresql/16/main/conf.d.

-- Показать путь к файлу конфигурации
SHOW config_file;