Установка PostgreSQL на Ubuntu 20.04

Содержание скрыть

Установка PostgreSQL

Для установки PostgreSQL 14 на Ubuntu 20.04 необходимо:
Обновить пакеты.

sudo apt -y update
sudo apt -y upgrade

Добавить репозиторий и ключ, скрипт взят с официально сайта Postgres, но по какой-то причине не всегда отрабатывает.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql

Если версия Postgres Вам непринципиальна, воспользуйтесь уже существующим пакетом. В apt Ubuntu 20.04 уже есть пакет PostgreSQL 12. Установить можно одной из следующих команд:

sudo apt -y install postgresql

или

sudo apt -y install postgresql-12

Обновить список пакетов.

sudo apt -y update

Установить последнюю версию PostgreSQL, на момент написания статьи это PostgreSQL-14
Если вам нужна конкретная версия, используйте ‘postgresql-12‘ или другую аналогичную вместо ‘postgresql‘

apt -y install postgresql

После установки проверяем запущен ли PostgreSQL

systemctl status postgresql

Добавляем PostgreSQL в автозагрузку

systemctl enable postgresql

Настройка PostgreSQL

Переходим под аккаунт пользователя Postgres.

su postgres

Создаем нового пользователя если требуется.

createuser --interactive username

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

psql -Upostgres

Меняем пароль командой postgres=# \password postgres, после чего будет предложено 2 раза ввести новый пароль:
Enter new password for user «postgres»:
Enter it again:

\password username

Удалить пользователя можно командой dropuser username, но для этого нужно выйти из psql назад в пользователя с помощью команды \q

dropuser username

Вывести список пользователей, выполняется в psql

select * from pg_shadow;

Вывести список баз, выполняется в psql

select * from pg_database;

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

nano /etc/postgresql/14/main/pg_hba.conf

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

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

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

host    all             all             192.168.1.0/24            password

В итоге мой блок # IPv4 local connections: выглядит так:

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             192.168.1.0/24          password

Далее требуется отредактировать ещё один файл postgresql.conf

nano /etc/postgresql/14/main/postgresql.conf

Тут нас интересует блок CONNECTIONS AND AUTHENTICATION в котором нам необходимо указать какие IP адреса и порт будет слушать PostgreSQL.

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
                                        # (change requires restart)

В нашем случае это будут любые доступные адреса, для этого в данный блок мы добавим строку listen_addresses = ‘*’ — где звёздочка это любые адреса, порт мы оставляем по умолчанию 5432. Теперь наш блок выглядит так:

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

Перезапустить PostgreSQL для применения настроек.

systemctl restart postgresql

Теперь всё, что осталось сделать это открыть необходимый порт для PostgreSQL на сервере для входящих соединений.

ufw allow 5432