Какой командой можно выполнить обновление конфигурации postgresql

PostgreSQL (Русский)

Состояние перевода: На этой странице представлен перевод статьи PostgreSQL. Дата последней синхронизации: 21 ноября 2022. Вы можете помочь синхронизировать перевод, если в английской версии произошли изменения.

PostgreSQL — это поддерживаемая сообществом система управления базами данных с открытым исходным кодом.

Установка

Важно: Смотрите раздел #Обновление PostgreSQL для выполнения обязательных шагов перед установкой обновлений пакета PostgreSQL.

Установите пакет postgresql . Он также создаст системного пользователя postgres.

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

Примечание: Команды, которые нужно запускать от имени пользователя postgres, в данной статье обозначены префиксом [postgres]$ .

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

Если у вас есть sudo и ваш пользователь прописан в sudoers:

$ sudo -iu postgres

$ su # su -l postgres

Смотрите также документацию sudo(8) или su(1) .

Начальная настройка

В первую очередь необходимо инициализировать кластер баз данных:

[postgres]$ initdb -D /var/lib/postgres/data

Где опция -D указывает на стандартное расположение данных кластера (если вы хотите использовать другой каталог, смотрите раздел #Изменение стандартного каталога данных). initdb принимает дополнительные аргументы:

• По умолчанию локаль и кодировка для кластера баз данных наследуются из вашего текущего окружения (используется значение $LANG). Если вас это не устраивает, вы можете прописать нужные параметры вручную с помощью опций —locale=локаль (где локаль должна быть одной из доступных системных локалей) и —encoding=кодировка для выбора кодировки (должна соответствовать выбранной локали). (После настройки базы данных вы сможете посмотреть используемые значения командой [postgres]$ psql -l .)
• Если каталог с данными расположен на файловой системе без контроля целостности данных, вы можете включить встроенный в PostgreSQL подсчёт контрольных сумм для повышения гарантий целостности — для этого добавьте аргумент —data-checksums . Дополнительная информация описана в разделе #Включение подсчёта контрольных сумм. (После настройки базы данных вы сможете посмотреть. включена ли эта функция, командой [postgres]$ psql -c «SHOW data_checksums» .)
• Другие доступные опции можно посмотреть в initdb —help или официальной документации.

Пример для русской локали:

[postgres]$ initdb --locale=ru_RU.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums

После инициализации на экране появится много строчек, некоторых из которых оканчиваются на . ок :

Файлы, относящиеся к этой СУБД, будут принадлежать пользователю "postgres". От его имени также будет запускаться процесс сервера. Кластер баз данных будет инициализирован с локалью "ru_RU.UTF-8". Кодировка БД по умолчанию, выбранная в соответствии с настройками: "UTF8". Выбрана конфигурация текстового поиска по умолчанию "russian". Контроль целостности страниц данных отключён. исправление прав для существующего каталога /var/lib/postgres/data. ок создание подкаталогов. ок выбирается реализация динамической разделяемой памяти. posix выбирается значение max_connections по умолчанию. 100 выбирается значение shared_buffers по умолчанию. 128MB выбирается часовой пояс по умолчанию. Europe/Moscow создание конфигурационных файлов. ок выполняется подготовительный скрипт. ок выполняется заключительная инициализация. ок сохранение данных на диске. ок initdb: предупреждение: включение метода аутентификации "trust" для локальных подключений Другой метод можно выбрать, отредактировав pg_hba.conf или используя ключи -A, --auth-local или --auth-host при следующем выполнении initdb. Готово. Теперь вы можете запустить сервер баз данных: pg_ctl -D /var/lib/postgres/data -l файл_журнала start

Если вы видите подобное, значит инициализация прошла успешно. Можно вернуться в обычного пользователя, выполнив команду exit в сеансе пользователя postgres.

Примечание: Подробнее об этом предупреждении читайте в разделе #Ограничение доступа к суперпользователю по умолчанию.

Совет: Если вы хотите использовать путь отличный от /var/lib/postgres , нужно отредактировать файл службы systemd. Если вы помещаете его в /home , не забудьте отключить ProtectHome .

• Если база данных располагается на файловой системе Btrfs, стоит отключить копирование при записи для каталога перед созданием любых баз данных.
• Если база данных располагается на файловой системе ZFS, прочтите ZFS#Databases перед созданием любых баз данных.

Наконец, запустите и включите службу postgresql.service .

Создание первой базы данных

Совет: Если имя роли/пользователя совпадает с именем вашего пользователя в Linux, вы сможете получить доступ к оболочке PostgreSQL без явного указания имени пользователя (что весьма удобно).

Становимся пользователем postgres. Добавляем нового пользователя базы данных с помощью команды createuser:

[postgres]$ createuser --interactive

Создаём новую базу данных от имени пользователя, имеющего доступ на чтение-запись, с помощью команды createdb (выполните эту команду в оболочке вашего обычного пользователя, если имя будущего владельца базы данных совпадает с вашим именем пользователя в Linux, в ином случае добавьте опцию -O имя-пользователя )

$ createdb имяМоейБазы

Совет: Если вы не выдали разрешение на создание баз данных вашему свежесозданному пользователю, добавьте опцию -U postgres к этой команде.

Знакомство с PostgreSQL

Доступ к оболочке базы данных

Становимся postgres пользователем. Запускаем основную оболочку базы данных psql, в которой мы сможем создавать, удалять базы данных/таблицы, задавать права и запускать команды SQL. Используйте опцию -d , чтобы указать название базы данных, которую вы создали (если опцию не указать, то psql попытается подключиться к базе, имя которой совпадает с именем пользователя).

[postgres]$ psql -d имяМоейБазы

Некоторые полезные команды:

=> \help

Подключение к определённой базе данных:

Список всех пользователей и их уровни доступа:

Краткая информация о всех таблицах в текущей базе данных:

Выход из оболочки psql :

=> \q или CTRL+d

Есть, конечно, много других мета-команд, но именно эти должны помочь вам начать работу. Для просмотра всех мета-команд введите:

Дополнительные настройки

Файл настроек сервера баз данных PostgreSQL — postgresql.conf . Этот файл находится в папке данных сервера, обычно /var/lib/postgres/data . В этой же папке находятся основные файлы настроек включая и pg_hba.conf , который определяет параметры аутентификации, как для локальных пользователей, так и для пользователей с других хостов.

Примечание: По умолчанию эта папка не доступна даже для просмотра (или поиска) от лица обычного пользователя.

Ограничение доступа к суперпользователю по умолчанию

По умолчанию pg_hba.conf разрешает подключение любого локального пользователя к любому пользователю базы данных, в том числе суперпользователю. Скорее всего это не то, что вам нужно, поэтому, чтобы разрешить подключение только пользователю postgres, измените эту строку:

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres peer

Можно добавить дополнительные строки в зависимости от ваших потребностей.

Требование пароля при входе

Измените /var/lib/postgres/data/pg_hba.conf , прописав метод аутентификации для каждого пользователя (или «all» для всех пользователей) на scram-sha-256 (предпочтительно) или md5 (менее безопасно; по возможности стоит его избегать):

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all пользователь scram-sha-256

Если вы выбрали scram-sha-256 , также нужно изменить /var/lib/postgres/data/postgresql.conf :

/var/lib/postgres/data/postgresql.conf

password_encryption = scram-sha-256

Перезапустите службу postgresql.service и заново пропишите пароли для пользователей с помощью SQL-запроса ALTER USER пользователь WITH ENCRYPTED PASSWORD ‘пароль‘; .

Доступ только через Unix-сокет

В разделе «connections and authentication» пропишите:

/var/lib/postgres/data/postgresql.conf

listen_addresses = ''

Это полностью отключит доступ через сеть. Не забудьте перезапустить службу postgresql.service для применения изменений.

Доступ с удалённых хостов

В разделе «connections and authentication» раскомментируйте или исправьте строку listen_addresses по вашему желанию, например:

/var/lib/postgres/data/postgresql.conf

listen_addresses = 'localhost,мой_локальный_ip'

Можно использовать ‘*’ для прослушивания всех доступных сетевых интерфейсов.

Примечание: PostgreSQL по умолчанию использует TCP-порт 5432 для удалённого доступа. Убедитесь, что этот порт открыт в вашем межсетевом экране и может принимать входящие соединения. Изменить порт можно в этом же файле настроек, под строкой listen_addresses .

Затем измените настройки аутентификации:

/var/lib/postgres/data/pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: host all all ip_адрес/32 md5

где ip_адрес — IP-адрес удалённого клиента.

Смотрите также документацию по pg_hba.conf.

Примечание: Ни отправка простого пароля, ни отправка md5-хэша (использованный в приведённом выше примере) через интернет не являются безопасными, если это не выполняется через защищенное SSL-соединение. Смотрите Secure TCP/IP Connections with SSL, чтобы узнать, как настроить PostgreSQL с использованием SSL.

Перезапустите службу postgresql.service для применения изменений.

В случае проблем посмотрите журнал сервера:

# journalctl -u postgresql.service

Настройка аутентификации через PAM

PostgreSQL предлагает несколько методов аутентификации. Если вы хотите разрешить пользователям аутентифицироваться с их системным паролем, необходимы дополнительные шаги. Сначала вам нужно включить PAM для соединения.

Например, та же конфигурация, что и выше, но с включенным PAM:

/var/lib/postgres/data/pg_hba.conf

# IPv4 local connections: host all all ip_адрес/32 pam

Однако сервер PostgreSQL работает без прав root и не сможет получить доступ к файлу /etc/shadow . Мы можем обойти это, разрешив группе postgres доступ к этому файлу:

# setfacl -m g:postgres:r /etc/shadow

Изменение стандартного каталога данных

По умолчанию PostgreSQL настроен на использование каталога /var/lib/postgres/data для хранения всех баз данных. Для его изменения выполните следующие шаги:

Создайте новый каталог и сделайте пользователя postgres его владельцем:

# mkdir -p /путь/к/pgroot/data # chown -R postgres:postgres /путь/к/pgroot

Войдите в пользователя postgres и выполните инициализацию кластера:

[postgres]$ initdb -D /путь/к/pgroot/data

Отредактируйте службу postgresql.service , создав drop-in файл и переопределив настройки Environment и PIDFile . Например:

/etc/systemd/system/postgresql.service.d/PGROOT.conf

[Service] Environment=PGROOT=/путь/к/pgroot PIDFile=/путь/к/pgroot/data/postmaster.pid

Если вы хотите использовать каталог в /home , добавьте ещё одну строку:

ProtectHome=false

Изменение кодировки новых баз данных на UTF-8

Примечание: Если вы выполнили initdb с опцией —encoding=UTF8 или с использованием локали UTF-8, выполнять эти шаги не нужно.

Когда создаётся новая база данных (например, командой createdb blog ), PostgreSQL просто копирует шаблон базы данных. Есть два стандартных шаблона: template0 — ванильный, и template1 , который используется по умолчанию и предназначен для редактирования администратором. Один из вариантов изменения кодировки новой базы данных — изменить шаблон template1 . Для этого зайдите в оболочку PostgreSQL ( psql ) и выполните следующее:

Сперва нужно удалить template1 . Шаблоны нельзя удалять, так что сперва нужно преобразовать его в обычную базу данных:

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

Теперь можно удалить:

DROP DATABASE template1;

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

CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UNICODE';

Теперь снова сделайте template1 шаблоном:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

По желанию, если вы не хотите, чтобы кто-либо подключался к этому шаблону, присвойте параметру datallowconn значение FALSE :

UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'template1';

Примечание: Этот шаг может привести к проблемам при обновлении через pg_upgrade .

Теперь вы можете создать базу данных, используя стандартные команды в терминале:

[postgres]$ createdb blog

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

List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -----------+----------+-----------+-----------+-------+---------------------- blog | postgres | UTF8 | C | C | postgres | postgres | SQL_ASCII | C | C | template0 | postgres | SQL_ASCII | C | C | =c/postgres : postgres=CTc/postgres template1 | postgres | UTF8 | C | C |

Включение подсчёта контрольных сумм

Если файлы вашей базы данных находятся на файловой системе, которая не использует контрольные суммы, то данные в ней могут незаметно повреждаться из-за битфлипов и аппаратных проблем. Хотя такие случаи редки, желательно включить встроенный в PostgreSQL подсчёт контрольных сумм, если вы заботитесь о целостности данных. Эта функция должна быть включена на уровне кластера, а не для отдельных баз данных или таблиц.

• Есть небольшое влияние на производительность, особенно при чтении больших массивов данных с диска. На операции в памяти это не влияет.
• PostgreSQL не может исправить повреждённые данные — он только прервёт транзакции, читающие с повреждённых страниц, чтобы предотвратить дальнейшее повреждение или получение некорректных результатов выполнения.
• Контрольные суммы охватывают только страницы данных (строк) на диске, но не метаданные или управляющие структуры. Страницы в памяти не проверяются. Хранилища с коррекцией ошибок и память с ECC по-прежнему полезны.

• Чтобы включить подсчёт контрольных сумм при создании кластера, добавьте аргумент —data-checksums к команде initdb .
• Чтобы проверить, включен ли подсчёт контрольных сумм, выполните [postgres]$ psql -c «SHOW data_checksums» (выведется off или on ).
• Чтобы включить подсчёт контрольных сумм на существующем кластере:

1. Остановите службу postgresql.service .
2. Выполните команду [postgres]$ pg_checksums —pgdata /var/lib/postgres/data —enable (или —disable , если вы хотите наоборот выключить эту функцию). Включение подсчёта контрольных сумм приведёт к перезаписи всех страниц базы данных, что займет некоторое время в больших базах данных.
3. Запустите службу postgresql.service .

Графические инструменты

• phpPgAdmin — Веб-интерфейс для администрирования PostgreSQL.

• pgAdmin — Комплексный графический интерфейс для управления PostgreSQL.

• pgModeler — Инструмент для моделирования баз данных PostgreSQL.

Список инструментов, поддерживающих несколько разных СУБД, можно посмотреть в статье List of applications/Documents#Database tools.

Обновление PostgreSQL

This article or section needs expansion.

Reason: How to upgrade when using third party extensions? (Discuss in Talk:PostgreSQL#pg_upgrade problem if extensions (like postgis) are used)

Для обновления до новой мажорной версии PostgreSQL (например, с версии 13.x на версию 14.y) необходима специальная процедура.

Примечание: Следуйте официальной документации по обновлению.

Важно: Приведённые ниже инструкции потенциально могут привести к потере данных. Не запускайте эти команды вслепую без понимания того, что они делают. Сделайте резервную копию перед началом.

Посмотреть текущую версию базы данных можно так:

# cat /var/lib/postgres/data/PG_VERSION

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

Минорные обновления вполне безопасны. Однако если вы случайно обновитесь до другой мажорной версии, то не сможете получить доступ к данным. Всегда проверяйте домашнюю страницу PostgreSQL, чтобы знать, какие шаги требуются для каждого обновления. Чтобы узнать, почему это так, смотрите политику управления версиями.

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

pg_upgrade

Утилита pg_upgrade пытается скопировать как можно больше совместимых данных между кластерами и обновить всё остальное. Как правило, это самый быстрый метод обновления большинства экземпляров, хотя он требует доступа к бинарным файлам исходной и целевой версий PostgreSQL. Прочтите справочную страницу pg_upgrade(1) , чтобы понять, какие действия он выполняет. Для нетривиальных экземпляров (например, с потоковой репликацией или трансляцией журналов) сперва ознакомьтесь с официальной документацией.

Для тех, кто хочет использовать pg_upgrade , доступен пакет postgresql-old-upgrade , который всегда отстаёт на одну мажорную версию от основного пакета PostgreSQL. Его можно установить параллельно с новой версией PostgreSQL. Для обновления более старых версий PostgreSQL доступны пакеты AUR, например postgresql-12-upgrade AUR . (Нужно использовать команду pg_upgrade из той версии PostgreSQL, на которую вы хотите обновиться.)

Обратите внимание, что каталог кластера баз данных не меняется от версии к версии, поэтому перед запуском pg_upgrade необходимо переименовать старый каталог данных и выполнить миграцию в новый каталог. Новый кластер баз данных необходимо инициализировать с теми же параметрами, что и старый.

Когда вы будете готовы к обновлению, выполните следующие шаги:

1. Пока старая база данных всё ещё доступна, соберите аргументы для команды initdb , которые использовались при создании базы. Команды для просмотра текущих настроек кластера описаны в разделе #Начальная настройка.
2. Остановите службу postgresql.service . (Проверьте статус юнита, чтобы убедиться, что PostgreSQL завершился корректно, иначе pg_upgrade не сможет отработать корректно.)
3. Обновите пакеты postgresql , postgresql-libs и postgresql-old-upgrade .
4. Переименуйте каталог со старым кластером и создайте каталог для нового кластера и временный каталог:

# mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data /var/lib/postgres/tmp # chown postgres:postgres /var/lib/postgres/data /var/lib/postgres/tmp [postgres]$ cd /var/lib/postgres/tmp

[postgres]$ initdb -D /var/lib/postgres/data --locale=ru_RU.UTF-8 --encoding=UTF8 --data-checksums

[postgres]$ pg_upgrade -b /opt/pgsql-PG_VERSION/bin -B /usr/bin -d /var/lib/postgres/olddata -D /var/lib/postgres/data

Примечание: Не забудьте обновить файлы конфигурации (например, pg_hba.conf и postgresql.conf ) для соответствия старому кластеру.

Примечание: Если pg_upgrade завершается с ошибкой The source cluster was not shut down cleanly , значит PostgreSQL не был остановлен перед запуском обновления. Остановите его, затем перезапустите кластер со старыми бинарными файлами, чтобы восстановить старые файлы кластера:

[postgres]$ /opt/pgsql-PG_VERSION/bin/pg_ctl start -D /var/lib/postgres/olddata && /opt/pgsql-PG_VERSION/bin/pg_ctl stop -D /var/lib/postgres/olddata

После этого можно снова выполнить pg_upgrade . Если обновиться всё равно не получается, остановите все процессы СУБД, откатитесь на более старую версию PostgreSQL, восстановите предыдущие данные кластера из резервных копий и перезапустите процесс обновления.

Выгрузка и загрузка вручную

Ещё можно сделать что-то вроде такого (после обновления и установки postgresql-old-upgrade ):

• В примере показано обновление с PostgreSQL 13; посмотрите в /opt/ установленную у вас версию postgresql-old-upgrade и исправьте команды по необходимости.
• Если вы меняли файл pg_hba.conf , вам может понадобиться временно разрешить полный доступ к старому кластеру с локальной системы. После обновления не забудьте прописать нужные вам настройки в новом кластере и перезапустить службу postgresql.service .

# mv /var/lib/postgres/data /var/lib/postgres/olddata # mkdir /var/lib/postgres/data # chown postgres:postgres /var/lib/postgres/data [postgres]$ initdb -D /var/lib/postgres/data [postgres]$ /opt/pgsql-13/bin/pg_ctl -D /var/lib/postgres/olddata/ start # cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-13/lib/ # Только если установлен postgis [postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql [postgres]$ /opt/pgsql-13/bin/pg_ctl -D /var/lib/postgres/olddata/ stop

[postgres]$ psql -f /tmp/old_backup.sql postgres

Решение проблем

Ускорение мелких транзакций

Если вы используете PostgreSQL на своей локальной машине для разработки и он медленный, то можете попробовать отключить synchronous_commit в конфигурации. Однако, не забывайте про его особенности.

/var/lib/postgres/data/postgresql.conf

synchronous_commit = off

Запретить запись на диск во время бездействия

PostgreSQL периодически обновляет свою статистику, лежащую в файле. По умолчанию этот файл находится на диске, что не даёт отдыхать жёсткому диску (и изнашивает его), заставляя его шуметь. Однако можно легко и безопасно переместить статистику в ОЗУ с помощью такой настройки:

/var/lib/postgres/data/postgresql.conf

stats_temp_directory = '/run/postgresql'

Проблемы с pgAdmin 4 после обновления до PostgreSQL 12

Если вы видите ошибки вроде string indices must be integers при навигации по дереву слева или column rel.relhasoids does not exist при просмотре данных, удалите сервер из списка соединений в pgAdmin и добавьте его заново. Без этого pgAdmin продолжает считать его сервером PostgreSQL 11, что и приводит к таким ошибкам.

PostgreSQL не может запуститься после обновления пакета при использовании расширений

Причина скорее всего в том, что существующий пакет не скомпилирован для новой версии (а она может быть актуальной), решение — пересобрать пакет вручную или дождаться обновления пакета расширения.

Не удаётся запустить PostgreSQL со старой версией базы данных при обновлении до новой версии с расширениями

Это происходит потому, что старая версия postgres из пакета postgresql-old-upgrade не имеет необходимых расширений (.so файлов) в своём каталоге lib. Предлагаемое здесь решение грязное и может вызвать много проблем, поэтому сохраните резервную копию базы данных на всякий случай. В целом, скопируйте необходимые .so файлы расширений из /usr/lib/postgresql/ в /opt/pgsql-XX/lib/ (не забудьте заменить XX на мажорную версию пакета postgresql-old-upgrade ).

Например, для timescaledb:

# cp /usr/lib/postgresql/timescaledb*.so /opt/pgsql-13/lib/

Важно: Хотя копирования .so файлов обычно достаточно, может понадобиться скопировать больше файлов в правильные расположения в /opt/pgsql-XX/ .

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

$ pacman -Ql имя_пакета 

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

Retrieved from «https://wiki.archlinux.org/index.php?title=PostgreSQL_(Русский)&oldid=793745»

• Pages with broken package links
• Pages or sections flagged with Template:Expansion

Какой командой можно выполнить обновление конфигурации postgresql

ALTER SYSTEM — изменить параметр конфигурации сервера

Синтаксис

ALTER SYSTEM SET параметр_конфигурации < TO | = >< значение | 'значение' | DEFAULT > ALTER SYSTEM RESET параметр_конфигурации ALTER SYSTEM RESET ALL

Описание

Оператор ALTER SYSTEM применяется для изменения параметров конфигурации сервера, распространяющихся на весь кластер баз данных. Пользоваться им может быть удобнее, чем вручную редактировать файл postgresql.conf . ALTER SYSTEM записывает заданное значение параметра в файл postgresql.auto.conf , который считывается сервером в дополнение к postgresql.conf . При указании в качестве значения параметра DEFAULT или применении формы RESET соответствующий элемент конфигурации удаляется из postgresql.auto.conf . Удалить все настроенные таким способом параметры позволяет предложение RESET ALL .

Значения, установленные командой ALTER SYSTEM , вступают в силу после следующей перезагрузки конфигурации сервера либо после перезапуска сервера (если это параметры, воспринимаемые только при запуске). Перезагрузить конфигурацию сервера можно, вызвав SQL-функцию pg_reload_conf() , выполнив pg_ctl reload или отправив сигнал SIGHUP главному серверному процессу.

Выполнить ALTER SYSTEM могут только суперпользователи. А так как эта команда работает непосредственно с файловой системой и не может быть отменена, её нельзя поместить в блок транзакции или функцию.

Параметры

параметр_конфигурации

Имя устанавливаемого параметра конфигурации. Список доступных параметров приведён в Главе 19. значение

Новое значение параметра. Значениями могут быть строковые константы, идентификаторы, числа или списки таких элементов через запятую, в зависимости от конкретного параметра. Если в качестве значения указать DEFAULT , параметр и его значение удаляется из postgresql.auto.conf .

Замечания

С помощью этой команды нельзя задать data_directory, равно как и другие параметры, недопустимые в postgresql.conf (например, предустановленные параметры).

Другие способы настройки параметров описаны в Разделе 19.1.

Примеры

Установка уровня ведения журнала транзакций ( wal_level ):

ALTER SYSTEM SET wal_level = replica;

Отмена изменения, восстановление значения, заданного в postgresql.conf :

ALTER SYSTEM RESET wal_level;

Совместимость

Оператор ALTER SYSTEM является расширением PostgreSQL .

Какой командой можно выполнить обновление конфигурации postgresql

pg_upgrade -b старый_каталог_bin [ -B новый_каталог_bin ] -d старый_каталог_конфигурации -D новый_каталог_конфигурации [ параметр . ]

Описание

Программа pg_upgrade (ранее называвшаяся pg_migrator ) позволяет обновить данные в каталоге базы данных PostgreSQL до последней основной версии PostgreSQL без операции выгрузки/восстановления данных, обычно необходимой при обновлениях основной версии, например, при переходе от 12.14 к 13.10 или от 14.9 к 15.5. Эти действия не требуются при установке корректирующей версии, например, при переходе от 12.7 к 12.8 или от 14.1 к 14.5.

С выходом новых основных версий в PostgreSQL регулярно добавляются новые возможности, которые часто меняют структуру системных таблицы, но внутренний формат хранения меняется редко. Учитывая этот факт, pg_upgrade позволяет выполнить быстрое обновление, создавая системные таблицы заново, но сохраняя старые файлы данных. Если при обновлении основной версии формат хранения данных изменится так, что данные в старом формате окажутся нечитаемыми, pg_upgrade не сможет произвести такое обновление. (Сообщество разработчиков постарается не допустить подобных ситуаций.)

Программа pg_upgrade делает всё возможное, чтобы убедиться в том, что старый и новый кластеры двоично-совместимы, в частности проверяя параметры времени компиляции и разрядность (32/64 бита) исполняемых файлов. Важно, чтобы и все внешние модули тоже были двоично-совместимыми, хотя это pg_upgrade проверить не может.

pg_upgrade поддерживает обновление с версии 9.2.X и новее до текущей основной версии PostgreSQL , включая бета-выпуски и сборки снимков кода.

Параметры

pg_upgrade принимает следующие аргументы командной строки:

-b каталог_bin
—old-bindir= каталог_bin

каталог с исполняемыми файлами старой версии PostgreSQL; переменная окружения PGBINOLD -B каталог_bin
—new-bindir= каталог_bin

каталог с исполняемыми файлами новой версии PostgreSQL, по умолчанию это каталог, в котором располагается pg_upgrade ; переменная окружения PGBINNEW -c
—check

только проверить кластеры, не изменять никакие данные -d каталог_конфигурации
—old-datadir= каталог_конфигурации

каталог конфигурации старого кластера; переменная окружения PGDATAOLD -D каталог_конфигурации
—new-datadir= каталог_конфигурации

каталог конфигурации нового кластера; переменная окружения PGDATANEW -j число_заданий
—jobs= число_заданий

число одновременно задействуемых процессов или потоков -k
—link

использовать жёсткие ссылки вместо копирования файлов в новый кластер -N
—no-sync

По умолчанию pg_upgrade ждёт, пока все файлы обновлённого кластера не будут надёжно записаны на диск. С данным параметром pg_upgrade завершается быстрее, без ожидания, но в случае последующего сбоя операционной системы каталог данных может оказаться испорченным. Этот параметр может быть полезен при тестировании; в производственной среде применять его не следует. -o параметры
—old-options параметры

параметры, передаваемые непосредственно старой программе postgres ; несколько параметров складываются вместе -O параметры
—new-options параметры

параметры, передаваемые непосредственно новой программе postgres ; несколько параметров складываются вместе -p порт
—old-port= порт

номер порта старого кластера; переменная окружения PGPORTOLD -P порт
—new-port= порт

номер порта нового кластера; переменная окружения PGPORTNEW -r
—retain

сохранить SQL и журналы сообщений даже при успешном завершении -s каталог
—socketdir= каталог

каталог, в котором будет создавать сокеты процесс postmaster во время обновления; по умолчанию выбирается текущий рабочий каталог; переменная окружения PGSOCKETDIR -U имя_пользователя
—username= имя_пользователя

имя пользователя, установившего кластер; переменная окружения PGUSER -v
—verbose

включить подробные внутренние сообщения -V
—version

показать версию, а затем завершиться —clone

Использовать эффективное клонирование файлов (в ряде систем это называется « reflink » ) вместо копирования файлов в новый кластер. В результате файлы данных могут копироваться практически мгновенно, как и с использованием -k / —link , но последующие изменения не будут затрагивать старый кластер.

Клонирование файлов поддерживается не во всех операционных системах и только с определёнными файловыми системами. Если этот режим выбран, но клонирование не поддерживается, при выполнении pg_upgrade произойдёт ошибка. В настоящее время оно поддерживается в Linux (с ядром 4.5 или новее) с Btrfs и XFS (если файловая система была создана с поддержкой reflink), а также в macOS с APFS. —copy

Копировать файлы в новый кластер. Это поведение по умолчанию. (См. также —link и —clone .) -?
—help

показать справку, а затем завершиться

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

Далее описан план обновления с использованием pg_upgrade :

Переместить старый кластер (необязательно)

Если ваш каталог инсталляции привязан к версии, например, /opt/PostgreSQL/16 , перемещать его не требуется. Все графические инсталляторы выбирают при установке каталоги, привязанные к версии.

Если ваш каталог инсталляции не привязан к версии, например /usr/local/pgsql , необходимо переместить каталог текущей инсталляции PostgreSQL, чтобы он не конфликтовал с новой инсталляцией PostgreSQL . Когда текущий сервер PostgreSQL отключён, каталог этой инсталляции PostgreSQL можно безопасно переместить; если старый каталог /usr/local/pgsql , его можно переименовать, выполнив:

mv /usr/local/pgsql /usr/local/pgsql.old

Собрать новую версию при установке из исходного кода

Соберите из исходного кода новую версию PostgreSQL с флагами configure , совместимыми с флагами старого кластера. Программа pg_upgrade проверит результаты pg_controldata , чтобы убедиться, что все параметры совместимы, прежде чем начинать обновление.

Установить новые исполняемые файлы PostgreSQL

Установите новые исполняемые файлы сервера и вспомогательные файлы. Программа pg_upgrade включена в инсталляцию по умолчанию.

При установке из исходного кода, если вы хотите разместить сервер в нестандартном каталоге, воспользуйтесь переменной prefix :

make prefix=/usr/local/pgsql.new install

Инициализировать новый кластер PostgreSQL

Инициализируйте новый кластер, используя initdb . При этом так же необходимо указать флаги initdb , совместимые с флагами в старом кластере. Многие готовые инсталляторы выполняют это действие автоматически. Запускать новый кластер не требуется.

Установить разделяемые объектные файлы расширения

Многие расширения и пользовательские модули, как из contrib , так и из других источников, используют разделяемые объектные файлы (или библиотеки DLL), например, pgcrypto.so . Если они использовались в старом кластере, разделяемые объектные файлы, соответствующие новому исполняемому файлу сервера, должны быть установлены в новом кластере, обычно средствами операционной системы. Не загружайте определения схемы, например, выполняя CREATE EXTENSION pgcrypto , потому что они будут скопированы из старого кластера. Если доступны обновления расширений, pg_upgrade сообщит об этом и создаст скрипт, который можно будет запустить позже, чтобы обновить эти расширения.

Скопируйте пользовательские файлы полнотекстового поиска

Скопировать пользовательские файлы полнотекстового поиска (словари, тезаурусы, списки синонимов и стоп-слов) из старого кластера в новый.

Настроить аутентификацию

Программа pg_upgrade будет подключаться к новому и старому серверу несколько раз, так что имеет смысл установить режим аутентификации peer в pg_hba.conf или использовать файл ~/.pgpass (см. Раздел 34.16).

Остановить оба сервера

Убедитесь в том, что оба сервера баз данных остановлены. Для этого в Unix можно выполнить:

pg_ctl -D /opt/PostgreSQL/12 stop pg_ctl -D /opt/PostgreSQL/16 stop

А в Windows, с соответствующими именами служб:

NET STOP postgresql-12 NET STOP postgresql-16

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

Подготовиться к обновлению ведомых серверов

Если вы производите обновление ведомых серверов (как описано в разделе Шаг 11), удостоверьтесь, что эти серверы находятся в актуальном состоянии, запустив pg_controldata в старых ведущем и ведомых кластерах. Убедитесь в том, что « Положение последней контрольной точки » во всех кластерах одинаковое. Также смените wal_level на replica в файле postgresql.conf нового ведущего кластера.

Запустить pg_upgrade

Всегда запускайте программу pg_upgrade от нового сервера, а не от старого. pg_upgrade требует указания каталогов данных старого и нового кластера, а также каталогов исполняемых файлов ( bin ). Вы можете также определить имя пользователя и номера портов, и нужно ли копировать файлы данных (по умолчанию), клонировать их или создавать ссылки на них.

Если выбрать вариант со ссылкой на данные, обновление выполнится гораздо быстрее (так как файлы не копируются) и потребует меньше места на диске, но вы лишитесь возможности обращаться к вашему старому кластеру, запустив новый после обновления. Этот вариант также требует, чтобы каталоги данных старого и нового кластера располагались в одной файловой системе. (Табличные пространства и pg_wal могут находиться в других файловых системах.) Вариант с клонированием работает так же быстро и экономит место на диске, но позволяет сохранить рабочее состояние старого кластера при запуске нового. Для этого варианта тоже требуется, чтобы старый и новый каталоги данных находились в одной файловой системе. Клонирование возможно только в некоторых операционных системах с определёнными файловыми системами.

Параметр —jobs позволяет задействовать для копирования/связывания файлов и для выгрузки/восстановления схем баз данных несколько процессорных ядер. В качестве начального значения параметра стоит выбрать максимум из числа процессорных ядер и числа табличных пространств. Этот параметр может радикально сократить время обновления сервера со множеством баз данных, работающего в многопроцессорной системе.

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

RUNAS /USER:postgres "CMD.EXE" SET PATH=%PATH%;C:\Program Files\PostgreSQL\16\bin;

Наконец, запустить pg_upgrade с путями каталогов в кавычках, например, так:

pg_upgrade.exe --old-datadir "C:/Program Files/PostgreSQL/12/data" --new-datadir "C:/Program Files/PostgreSQL/16/data" --old-bindir "C:/Program Files/PostgreSQL/12/bin" --new-bindir "C:/Program Files/PostgreSQL/16/bin"

При запуске pg_upgrade проверит два кластера на совместимость и, если всё в порядке, выполнит обновление. Также возможно запустить pg_upgrade —check , чтобы ограничиться только проверками (при этом старый сервер может продолжать работать). Команда pg_upgrade —check также сообщит, какие коррективы вам нужно будет внести вручную после обновления. Если вы планируете использовать режим ссылок на данные или клонирования, укажите вместе с —check или —clone параметр —link , чтобы были проведены специальные проверки для этого режима. Программе pg_upgrade требуются права на запись в текущий каталог.

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

Если при восстановлении схемы базы данных происходит ошибка, pg_upgrade завершает свою работу и вы должны вернуться к старому кластеру, как описывается ниже в Шаг 17. Чтобы попробовать pg_upgrade ещё раз, вы должны внести коррективы в старом кластере, чтобы pg_upgrade могла успешно восстановить схему. Если проблема возникла в модуле contrib , может потребоваться удалить этот модуль contrib в старом кластере, а затем установить его в новом после обновления (предполагается, что этот модуль не хранит пользовательские данные).

Обновить ведомые серверы с потоковой репликацией и трансляцией журнала

Если вы используете режим ссылок и у вас реализована потоковая репликация (см. Подраздел 27.2.5) или трансляция журнала (см. Раздел 27.2) для ведомых серверов, вы можете быстро обновить эти серверы следующим образом. Вам не нужно будет запускать на них pg_upgrade , вместо этого вы выполните rsync на ведущем. Не запускайте никакие серверы на этом этапе.

Если вы не используете режим ссылок, либо у вас нет rsync или вы не хотите его использовать, либо если вам нужен более простой вариант, пропустите инструкции в этом разделе и просто пересоздайте ведомые серверы сразу после завершения pg_upgrade и запуска нового ведущего сервера.

Установите новые исполняемые файлы PostgreSQL на ведомых серверах

Убедитесь в том, что на всех ведомых серверах установлены новые исполняемые и вспомогательные файлы.

Убедитесь в том, что новые каталоги данных на ведомых серверах не существуют

Новые каталоги данных ведомых серверов должны отсутствовать либо быть пустыми. Если запускалась программа initdb , удалите новые каталоги данных на ведомых.

Установить разделяемые объектные файлы расширения

Установите на новых ведомых серверах те же разделяемые объектные файлы расширения, что вы установили в новом ведущем кластере.

Остановите ведомые серверы

Если ведомые серверы продолжают работу, остановите их, следуя приведённым выше инструкциям.

Сохраните файлы конфигурации

Сохраните все нужные вам файлы конфигурации из старых каталогов конфигурации ведомых серверов, в частности postgresql.conf (и все файлы, включённые в него), postgresql.auto.conf и pg_hba.conf , так как они будут перезаписаны или удалены на следующем этапе.

Запустите rsync

Когда используется режим ссылок, ведомые серверы можно быстро обновить, применив rsync . Для этого в каталоге, внутри которого находятся каталоги старого и нового кластера, для каждого ведомого сервера выполните на ведущем :

rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir

Здесь каталоги old_cluster и new_cluster задаются относительно текущего каталога на ведущем, а remote_dir находится над каталогами старого и нового кластера на ведомом. Структура подкаталогов в заданных каталогах на ведущем и ведомых серверах должна быть одинаковой. Обратитесь к странице руководства rsync , где подробно описано, как указать удалённый каталог, например так:

rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/12 \ /opt/PostgreSQL/16 standby.example.com:/opt/PostgreSQL

Проверить, что будет делать команда, можно, воспользовавшись параметром rsync —dry-run . Выполнить rsync на ведущем необходимо как минимум с одним ведомым, но затем, пока обновлённый ведомый остаётся остановленным, можно запускать rsync на нём для обновления других ведомых.

В ходе этой операции записываются ссылки, созданные режимом ссылок pg_upgrade , связывающие файлы нового и старого кластера на ведущем сервере. Затем в старом кластере ведомого находятся соответствующие файлы и в новом кластере ведомого создаются ссылки на них. Файлы, не связанные ссылками на ведущем, копируются с него на ведомый. (Обычно их объём невелик.) Это позволяет произвести обновление ведомого быстро. К сожалению, при этом rsync будет напрасно копировать файлы, связанные с временными и нежурналируемыми таблицами, так как они обычно не будут существовать на ведомых серверах.

Если у вас есть табличные пространства, вам потребуется выполнить подобную команду rsync для каталогов всех табличных пространств, например:

rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_12_201909212 \ /vol1/pg_tblsp/PG_16_202307071 standby.example.com:/vol1/pg_tblsp

Если вы вынесли pg_wal за пределы каталогов данных, нужно будет запустить rsync и для этих каталогов.

Настройте ведомые серверы с потоковой репликацией и трансляцией журнала

Восстановить pg_hba.conf

Если вы изменяли pg_hba.conf , восстановите его исходное состояние. Также может потребоваться скорректировать другие файлы конфигурации в новом кластере, чтобы они соответствовали старому, например, postgresql.conf (и файлы, включённые в него) и postgresql.auto.conf .

Запустить новый сервер

Теперь можно безопасно запустить новый сервер, а затем ведомые серверы, синхронизированные с ним с помощью rsync .

Действия после обновления

Если после обновления требуются какие-то дополнительные действия, программа pg_upgrade выдаст предупреждения об этом по завершении работы. Она также сгенерирует файлы скриптов, которые должны запускаться администратором. Эти скрипты будут подключаться к каждой базе данных, требующей дополнительных операций. Каждый такой скрипт следует выполнять командой:

psql --username=postgres --file=script.sql postgres

Эти скрипты могут выполняться в любом порядке, а после выполнения их можно удалить.

Внимание

Обычно к таблицам, задействованным в перестраивающих базу скриптах, опасно обращаться, пока эти скрипты не сделают свою работу; при этом можно получить некорректный результат или плохую производительность. К таблицам, не задействованным в таких скриптах, можно обращаться немедленно.

Статистика

Так как статистика оптимизатора не передаётся в процессе работы pg_upgrade , вы получите указание запустить соответствующую команду для воссоздания этой информации после обновления. Возможно, для этого вам понадобится установить параметры подключения к новому кластеру.

Удалить старый кластер

Если вы удовлетворены результатами обновления, вы можете удалить каталоги данных старого кластера, запустив скрипт, упомянутый в выводе pg_upgrade после обновления. (Автоматическое удаление невозможно, если в старом каталоге данных находятся дополнительные табличные пространства.) Также вы можете удалить каталоги старой инсталляции (например, bin , share ).

Возврат к старому кластеру

Если выполнив команду pg_upgrade , вы захотите вернуться к старому кластеру, возможны следующие варианты:

Если использовался ключ —check , в старом кластере ничего не меняется; его можно просто перезапустить.

Если не использовался ключ —link , в старом кластере ничего не меняется; его можно просто перезапустить.

Если использовался ключ —link , у старого и нового кластера могут оказаться общие файлы данных:

Если работа pg_upgrade была прервана до начала расстановки ссылок, в старом кластере ничего не меняется; его можно просто перезапустить.

Если вы не запускали новый кластер, старый кластер не претерпел никаких изменений, за исключением того, что при создании ссылки на данные к имени $PGDATA/global/pg_control было добавлено окончание .old . Чтобы продолжить использование старого кластера, достаточно убрать окончание .old из имени файла $PGDATA/global/pg_control ; после этого старый кластер можно будет перезапустить.

Замечания

Программа pg_upgrade создаёт различные временные файлы, например выгружая схему базы, в своём рабочем каталоге pg_upgrade_output.d внутри каталога данных нового кластера. При каждом запуске в этом рабочем каталоге создаётся подкаталог с именем, содержащим отметку времени в формате стандарта ISO 8601 ( %Y%m%dT%H%M%S ), и в данный подкаталог помещаются все создаваемые файлы. Каталог pg_upgrade_output.d со всем содержимым удаляется автоматически в случае успешного выполнения pg_upgrade ; но если возникает проблема, сохранённые в нём файлы могут быть полезны для диагностики.

Программа pg_upgrade запускает на короткое время процессы postmaster со старым и новым каталогом данных. Временные файлы сокетов Unix для взаимодействия с этими процессами по умолчанию создаются в текущем рабочем каталоге. В некоторых ситуациях путь к файлу в текущем каталоге может оказаться слишком длинным для имени сокета. В этом случае вы можете передать параметр -s , чтобы файлы сокетов создавались в другом каталоге с более коротким путём. В целях безопасности этот каталог не должен быть доступен для чтения и записи другим пользователям. (В Windows это не поддерживается.)

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

Для проверки развёртывания новой версии создайте копию только схемы старого кластера, наполните этот кластер фиктивными данными, и попробуйте обновить его.

pg_upgrade не поддерживает обновление баз данных, в которых есть таблицы со столбцами, имеющими следующие системные типы данных reg* , ссылающиеся на OID:

regcollation

regconfig

regdictionary

regnamespace

regoper

regoperator

regproc

regprocedure

(Обновление regclass , regrole и regtype поддерживается.)

Если вы хотите использовать режим ссылок на данные, но при этом исключить изменения в старом кластере при запуске нового, вам может подойти режим клонирования. Если же этот режим недоступен, сделайте копию старого кластера и обновите его в этом режиме. Чтобы получить рабочую копию старого кластера, воспользуйтесь командой rsync и создайте предварительную копию кластера при работающем сервере, а затем отключите старый сервер и ещё раз запустите rsync —checksum , чтобы привести эту копию в согласованное состояние. (Ключ —checksum необходим, потому что rsync различает время с точностью только до секунд.) При этом вы можете исключить некоторые файлы, например postmaster.pid , как описано в Подразделе 26.3.3. Если ваша файловая система поддерживает снимки файловой системы или копирование при записи, вы можете воспользоваться этим для создания копии старого кластера и табличных пространств; при этом важно, чтобы такие снимки и копии файлов создавались одномоментно или когда сервер баз данных отключён.

См. также

Пред.	Наверх	След.
pg_test_timing	Начало	pg_waldump

СУБД PostgreSQL

Установка выполнена из пакета. Каталог установки PostgreSQL:

student$ sudo ls -l /usr/lib/postgresql/9.6 

total 8 drwxr-xr-x 2 root root 4096 дек 26 2016 bin drwxr-xr-x 2 root root 4096 дек 26 2016 lib

Владелец ПО сервера — пользователь root.

В Ubuntu доступ к утилитам сервера выполняется через обертки. Например, для управления сервером вместо pg_ctl используется pg_ctlcluster.

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

Если требуется явным образом остановить или запустить сервер, обновить конфигурацию, то это делается командами:

• Остановить сервер : pg_ctlcluster 9.6 main stop
• Запустить сервер : pg_ctlcluster 9.6 main start
• Перезапустить : pg_ctlcluster 9.6 main restart
• Обновить конфигурацию: pg_ctlcluster 9.6 main reload

Кластер баз данных установлен в каталог: /var/lib/postgresql/9.6/main

В последующих темах мы будем ссылаться на этот каталог как $PGDATA, по имени переменной ОС, которую можно установить для использования в некоторых утилитах сервера.

Владельцем каталога является пользователь postgres. Вот его содержимое:

postgres$ ls -l /var/lib/postgresql/9.6/main

total 88 drwx------ 27 postgres postgres 4096 сен 1 15:50 base drwx------ 2 postgres postgres 4096 сен 1 15:50 global drwx------ 2 postgres postgres 4096 дек 26 2016 pg_clog drwx------ 2 postgres postgres 4096 дек 26 2016 pg_commit_ts drwx------ 2 postgres postgres 4096 дек 26 2016 pg_dynshmem drwxr-xr-x 2 postgres root 4096 авг 25 22:15 pg_log drwx------ 4 postgres postgres 4096 дек 26 2016 pg_logical drwx------ 4 postgres postgres 4096 дек 26 2016 pg_multixact drwx------ 2 postgres postgres 4096 авг 25 22:17 pg_notify drwx------ 2 postgres postgres 4096 дек 26 2016 pg_replslot drwx------ 2 postgres postgres 4096 дек 26 2016 pg_serial drwx------ 2 postgres postgres 4096 дек 26 2016 pg_snapshots drwx------ 2 postgres postgres 4096 авг 25 22:17 pg_stat drwx------ 2 postgres postgres 4096 янв 10 2017 pg_stat_tmp drwx------ 2 postgres postgres 4096 дек 26 2016 pg_subtrans drwx------ 2 postgres postgres 4096 авг 25 22:16 pg_tblspc drwx------ 2 postgres postgres 4096 дек 26 2016 pg_twophase -rw------- 1 postgres postgres 4 дек 26 2016 PG_VERSION drwx------ 3 postgres postgres 4096 авг 25 22:14 pg_xlog -rw------- 1 postgres postgres 107 авг 25 22:17 postgresql.auto.conf -rw------- 1 postgres postgres 133 авг 25 22:17 postmaster.opts -rw------- 1 postgres postgres 93 авг 25 22:17 postmaster.pid

Основной файл конфигурации postgresql.conf расположен в:

postgres$ ls -l /etc/postgresql/9.6/main

total 48 -rw-r--r-- 1 postgres postgres 315 дек 26 2016 environment -rw-r--r-- 1 postgres postgres 143 дек 26 2016 pg_ctl.conf -rw-r----- 1 postgres postgres 4641 авг 25 22:17 pg_hba.conf -rw-r----- 1 postgres postgres 1636 дек 26 2016 pg_ident.conf -rw-r--r-- 1 postgres postgres 22465 сен 1 15:50 postgresql.conf -rw-r--r-- 1 postgres postgres 317 дек 26 2016 start.conf

Здесь же находятся и другие конфигурационные файлы.

Журнал сервера находится здесь:

postgres$ ls -l /var/log/postgresql/postgresql-9.6-main.log

-rw-r----- 1 postgres adm 6596 сен 1 15:50 /var/log/postgresql/postgresql-9.6-main.log

Заглянем в конец журнала:

postgres$ tail -n 10 /var/log/postgresql/postgresql-9.6-main.log

2017-08-25 22:17:18 MSK [2434-1] LOG: shutting down 2017-08-25 22:17:18 MSK [2428-4] LOG: database system is shut down 2017-08-25 22:17:19 MSK [11441-1] LOG: database system was shut down at 2017-08-25 22:17:18 MSK 2017-08-25 22:17:19 MSK [11441-2] LOG: MultiXact member wraparound protections are now enabled 2017-08-25 22:17:19 MSK [11440-1] LOG: database system is ready to accept connections 2017-08-25 22:17:19 MSK [11445-1] LOG: autovacuum launcher started 2017-08-25 22:17:19 MSK [11447-1] [unknown]@[unknown] LOG: incomplete startup packet 2017-08-25 22:17:21 MSK [11578-1] r@access_overview ERROR: permission denied for relation t 2017-08-25 22:17:21 MSK [11578-2] r@access_overview STATEMENT: UPDATE s.t SET key = key+1 WHERE key = 2; 2017-09-01 15:50:50 MSK [11440-2] LOG: received SIGHUP, reloading configuration files

Параметры конфигурации

Проверим значение параметра work_mem:

=> SHOW work_mem; 

work_mem ---------- 4MB (1 row)

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

4MB — это значение по умолчанию и оно слишком мало. Допустим мы хотим увеличить его для всех до 16MB. Для этого можно внести изменения в postgresql.conf и обновить конфигурацию.

Редактируем postgresql.conf любым текстовым редактором. Для целей демонстрации воспользуемся утилитами командной строки. Файл принадлежит пользователю postgres, поэтому вносим правки именно этим пользователем.

postgres$ sed '/^work_mem/d' -i /etc/postgresql/9.6/main/postgresql.conf

postgres$ echo 'work_mem = 16MB' >> /etc/postgresql/9.6/main/postgresql.conf

Заглянем в конец файла:

postgres$ tail -n 5 /etc/postgresql/9.6/main/postgresql.conf

# CUSTOMIZED OPTIONS #------------------------------------------------------------------------------ # Add settings for extensions here work_mem = 16MB

Теперь нужно обновить конфигурацию. Воспользуемся pg_ctlcluster. Эту утилиту также нужно запускать из под postgres или через sudo:

student$ sudo pg_ctlcluster 9.6 main reload

Указание reload заставляет PostgreSQL перечитать файлы конфигурации. Это происходит без перезагрузки сервера.

Еще раз проверим значение параметра work_mem:

=> SHOW work_mem; 

work_mem ---------- 16MB (1 row)

Изменения вступили в силу.

Большинству параметров можно установить новое значение для текущего сеанса во время выполнения. Например, если мы собираемся выполнить запрос, сортирующий большой объем данных, то для сеанса можно увеличить значение work_mem:

=> SET work_mem = '64MB'; 

=> SHOW work_mem; 

work_mem ---------- 64MB (1 row)

Новое значенние действует только в текущем сеансе или в текущей транзакции (SET LOCAL).

Работа в psql

Проверим текущее подключение:

=> \conninfo 

You are connected to database "tools_overview" as user "student" via socket in "/var/run/postgresql" at port "5432".

Команда \c[onnect] выполняет новое подключение, не покидая psql.

psql умеет выводить результат запросов в разных форматах:

• формат с выравниванием значений;
• формат без выравнивания;
• расширенный формат.

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

=> select name, setting, unit from pg_settings limit 7; 

name | setting | unit -------------------------+------------+------ allow_system_table_mods | off | application_name | psql | archive_command | (disabled) | archive_mode | off | archive_timeout | 0 | s array_nulls | on | authentication_timeout | 60 | s (7 rows)

Ширина столбцов выровнена по значениям.

Также выводится строка заголовков и итоговая строка.

Команды psql для переключения режима выравнивания:

• \a — переключатель режима: с выравниванием/без выравнивания.
• \t — переключатель отображения строки заголовка и итоговой строки.

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

Output format is unaligned.