PostgreSQL (Русский)

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

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

• Список всех возможных команд (например, ) для запросов

=> \h

• Подробное описание команды

=> \h CREATE_TABLE

• Подключаем определённую базу данных

=> \c <database>

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

=> \du

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

=> \dt

• Меняем пароль

=> \password

• Показать все используемые настройки

=> SHOW ALL;

• Выйти из psql

=> \q или CTRL+d

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

=> \?

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

На эту:

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

Измените , прописав метод аутентификации для каждого пользователя (или «all» для всех пользователей) на (предпочтительно) или (менее безопасно; по возможности стоит его избегать):

Если вы выбрали , также нужно изменить :

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

password_encryption = scram-sha-256

Перезапустите службу и заново пропишите пароли для пользователей с помощью SQL-запроса .

В разделе сonnections and authentications пропишите:

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

В разделе connections and authentications раскомментируйте или исправьте строку по вашему желанию на

listen_addresses = '*'

либо

listen_addresses = 'localhost,ip_у_сервера_в_сети'

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

где — IP-адрес клиента.

После этого необходимо перезапустить демон, чтобы изменения вступили в силу

$ systemctl restart postgresql

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

Если возникли проблемы взгляните на лог-файл сервера

# journalctl -u postgresql

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

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

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

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

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

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

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

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

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

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

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

ProtectHome=false

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

1. Первое, мы должны сбросить template1. Шаблоны не могут быть сброшены, так что мы сначала изменим его, как обычную базу данных:

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

2. Сейчас уже можно сбросить её:

DROP DATABASE template1;

3. Создаём новую базу данных, с новой кодировкой по умолчанию из template0:

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

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

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

5. (Рекомендация) Документация по PostgreSQL advises рекомендует "замораживать" изменения шаблона функцией VACUUM FREEZE:

\c template1
VACUUM FREEZE;

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

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

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

[postgres]$ createdb blog

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

\l

returns

                              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. Для обновления более старых версий PostgreSQL доступны пакеты AUR: , , , postgresql-93-upgrade^AUR, . Прочтите справочную страницу , чтобы понять, какие действия он выполняет.

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

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

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

Остановите службу . Проверьте статус службы, чтобы убедиться, что PostgreSQL завершился корректно, иначе не сможет отработать корректно.

Обновите пакеты postgresql, postgresql-libs и .

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

# 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

Не забудьте прописать опции , и/или по необходимости.

Обновите кластер, выполнив эту команду (замените на номер старой версии, например ):

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

Запустите службу .

Командой будут созданы скрипты и в каталоге и выведены инструкции по их использованию.

• генерирует статистику оптимизатора для нового кластера и должен запускаться от имени пользователя . Для его работы должен быть запущен.
• просто удаляет каталог и должен запускаться от имени пользователя, имеющего права записи в (например, от имени root).

Когда обновление будет полностью завершено, каталог /var/lib/postgres/tmp можно будет удалить.

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

# systemctl stop 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-12/bin/pg_ctl -D /var/lib/postgres/olddata/ start
# cp /usr/lib/postgresql/postgis-3.so /opt/pgsql-12/lib/ # Только если установлен postgis
[postgres]$ pg_dumpall -h /tmp -f /tmp/old_backup.sql
[postgres]$ /opt/pgsql-12/bin/pg_ctl -D /var/lib/postgres/olddata/ stop
# systemctl start postgresql.service
[postgres]$ psql -f /tmp/old_backup.sql postgres

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

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

Если вы видите ошибки вроде при навигации по дереву слева или при просмотре данных, удалите сервер из списка соединений в pgAdmin и добавьте его заново. Без этого pgAdmin продолжает считать его сервером PostgreSQL 11, что и приводит к таким ошибкам.