Различия

Показаны различия между двумя версиями страницы.

--- service:postgres [20.07.2024 08:14] – [Переменные, условия] viacheslav
+++ service:postgres [30.07.2024 19:21] (текущий) – внешнее изменение 127.0.0.1
@@ Строка 1: / Строка 1: @@
+====== PostgreSQL ======
+<code bash>
+# Сделать новый контейнер
+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;"
+</code>
+===== Бэкап =====
+Бэкап баз
+<code bash>
+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 {} +
+</code>
+https://stackoverflow.com/questions/24718706/backup-restore-a-dockerized-postgresql-database
+===== Восстановление =====
+<code bash>
+# Восстановить базу 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
+</code>
+====== DBA1 ======
+https://postgrespro.ru/education/courses/DBA1\\
+Демо-база: https://postgrespro.ru/education/demodb
+===== Установка и управление сервером =====
+https://edu.postgrespro.ru/16/dba1-16/dba1_01_tools_install.html
+<code yaml>
+---
+# 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
+</code>
+Если база уже создана, то часовой пояс поменять через docker-compose.yml не получится. Нужно выполнить запрос ''SET TIME ZONE 'Europe/Moscow';''\\
+https://stackoverflow.com/questions/44394295/how-to-change-timezone-of-postgresql-9-5-on-docker
+[[https://postgrespro.ru/docs/postgresql/16/app-initdb|initdb]] - создание кластера БД. Каталог, где инициализируется кластер, не может принадлежать суперпользователю. Владельцем кластера обычно является пользователь postgres.
+PGDATA - переменная, указывающая на расположение каталога кластера. Её используют некоторые утилиты сервера, когда им нужно узнать расположение кластера, например, initdb и основная утилита управления сервером pg_ctl.
+Ключ ''-k'' или ''%%--data-checksums%%'' при инициализации кластера включает подсчёт контрольных сумм на страницах данных для выявления сбоев.
+<code bash>
+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
+</code>
+[[https://postgrespro.ru/docs/postgresql/16/app-pg-ctl|pg_ctl]] - старт/стоп/статус сервера, изменение конфигурации, запускается от имени владельца кластера БД.
+<code bash>
+# Запуск с логом. Без -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 (включено)
+</code>
+https://www.crunchydata.com/blog/fun-with-pg_checksums
+**Специфика Ubuntu:** вместо ''initdb'' используется ''pg_createcluster'', который запускается автоматически при установке пакета и создает кластер БД с именем "main". Для удаления кластера - ''pg_dropcluster''. Вместо ''pg_ctl'' - ''pg_ctlcluster''.
+<code bash>
+sudo pg_ctlcluster 16 main start # stop/restart/status/reload
+# Местоположение лога при пакетной установке
+ls -l /var/log/postgresql/postgresql-16-main.log
+</code>
+===== Использование psql =====
+psql — терминальный клиент для работы с СУБД. При запуске требуются параметры подключения. Выполняет команды SQL и psql.\\
+Содержит инструменты для интерактивной работы, а также для подготовки и выполнения скриптов.\\
+https://edu.postgrespro.ru/16/dba1-16/dba1_02_tools_psql.html\\
+https://postgrespro.ru/docs/postgresql/16/app-psql
+<code powershell>
+# Клиент на винде
+# Подключение к БД 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;
+</code>
+==== Форматирование вывода результатов запроса ====
+Формат по умолчанию - включены заголовки, итоговая строка с кол-вом строк и выравнивание:
+<code sql>
+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 строки)
+</code>
+  * ''\t'' - отключить заголовки и итоговой строки (кол-во строк).
+  * ''\a'' - отключить выравнивание.
+  * ''%%\pset fieldsep ' '%%'' - установить разделитель (здесь: пробел). ''\pset'' без аргументов выводит текущие настройки.
+  * ''\x'' - включить расширенный вывод.
+==== Взаимодействие с ОС ====
+<code bash>
+\! pwd
+# Задать переменную hello
+\setenv hello world
+\! echo hello
+world
+# \o[ut] - вывод в файл (в Windows тоже нужно использовать / в путях, например, c:/temp/out.txt)
+\o folder/out.txt # вывод всех последующих команд будет добавляться в указанный файл
+\o # Вернуть вывод на экран
+</code>
+==== Скрипты ====
+<code bash>
+# Сгенерировать строки путём запроса и выгрузить в файл. Если файл не указывать, то будет вывод на экран.
+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
+</code>
+==== Переменные, условия ====
+<code sql>
+-- Связать переменную 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
+-07-20 08:05:42.618286+00
+-- Вывести все переменные
+\set
+-- Условие - если переменная не определена, задать её (здесь: командой ОС)
+\if :{?working_dir}
+\else
+-- В винде будет `echo %cd%`
+\set working_dir `pwd`
+\endif
+</code>
+==== Настройка psql ====
+При запуске psql выполняются 2 скрипта, если они есть: общий системный скрипт ''psqlrc'' и пользовательский файл ''~/.psqlrc''. Местоположение системного скрипта можно узнать командой ''pg_config --sysconfdir''. По умолчанию оба файла отсутствуют.
+<code sql>
+-- Если добавить этот код в ~/.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;'
+</code>
+===== Настройка сервера =====
+https://edu.postgrespro.ru/16/dba1-16/dba1_03_tools_configuration.html\\
+[[https://postgrespro.ru/docs/postgresql/16/runtime-config|Описание параметров конфигурации]]
+Базовая конфигурация хранится в файлах конфигурации. Основной файл (путь по умолчанию) - ''$PGDATA/postgresql.conf''. Ряд параметров можно устанавливать для БД, пользователя или текущего сеанса. Если один и тот же параметр указан в конфигурационных файлах несколько
+раз, будет использоваться значение, считанное последним. Для применения изменений нужно перечитать конфиг и в некоторых случаях перезапустить сервер.
+Каталог с доп. файлами конфигурации - ''/etc/postgresql/16/main/conf.d''.
+<code sql>
+-- Показать путь к файлу конфигурации
+SHOW config_file;
+</code>