Установка 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

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