Настройка PostgreSQL в Ubuntu 20.04 LTS

Короткая инструкция по установке и настройке

Чтобы установить:

apt install postgresql

После установки - настраиваем:

sudo -u postgres psql
postgres=# create database {DBNAME};
где DBNAME - имя базы

postgres=# create user {USERNAME} with encrypted password '{PASSWORD}';
USERNAME - имя пользователя
PASSWORD - пароль

postgres=# grant all privileges on database {DBNAME} to {USERNAME};
DBNAME - имя базы
USERNAME - имя пользователя

\q

Готово. Теперь PotgreSQL будет работать локально

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

nano /etc/postgresql/VERSION/main/pg_hba.conf
где VERSION - это версия PostgreSQL

по-умолчанию в Ubuntu 20:

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

внутри файла в самый конец надо вписать правило для соединений извне. Чтобы разрешить подключаться от любого IP:

host all all 0.0.0.0/0 md5

Подключаться с конкретного IP:

host all all 92.15.22.133/32 md5

Второй файл:

/etc/postgresql/VERSION/main/postgresql.conf
где VERSION - это версия PostgreSQL

по-умолчанию в Ubuntu 20:

/etc/postgresql/12/main/postgresql.conf

В нем надо вписать/отредактировать, чтобы стало вот так:

listen_addresses = '*' - для всех адресов
listen_addresses = '167.55.111.200' - для конкретного IP

Несколько популярных команд:

service postgresql restart - перезапустить (чтобы настройки вступили в силу)
service postgresql stop - остановить 
service postgresql start - запустить

Установка PostGIS

Выполняем из-под sudo:

add-apt-repository ppa:ubuntugis/ppa
apt update
apt install postgresql-12-postgis-3
apt install postgresql-12-postgis-3-scripts

после этого:

sudo -u postgres psql
postgres=# \c DATABASE_NAME
postgres=# CREATE EXTENSION postgis;
postgres=# CREATE EXTENSION postgis_topology;
postgres=# \q

Кратко: Входим в postgreSQL, выбираем базу и для нее - включаем эти extensions, после этого будет работать.

Какие еще команды бывают

Посмотреть список БД:

su - postgres
psql -l

Удалить базу данных:

DROP DATABASE database;
где database - имя базы данных, которую надо удалить

Как правильно восстановить автоинкрементное поле в PostgreSQL

SELECT MAX(id) FROM {table};
{table} - имя таблицы, для которой нам нужно восстановить работу автоинкремента

SELECT setval('{table}_id_seq', {max_id+1}, true);
{table} - имя таблицы, для которой нам нужно восстановить работу автоинкремента
{max_id+1} - это значение на 1 больше, чем результат исполнение предыдущего запроса

Делать так приходится, когда переносишь данные через TablePlus

Создать DUMP базы и потом его накатить на другую или ту же базу PostgreSQL

Чтобы создать дамп:

su - postgres

pg_dump {DATABASE} > {ANYNAME}.pgdump

Файл portal.dump создаться в /var/lib/postgresql/{ANYNAME}.pgdump на сервере

Чтобы накатить дамп на базу:

sudo -u postgres psql {DATABASE} < {ANYNAME}.pgdump

{ANYNAME}.pgdump это полный путь до дампа на диске

Что же делать, если таблички не видно после накатывания дампа? Что случилось? А случилось вот что: При создании дампа - postgre записал кто owner у таблиц и у вашего места, куда вы делаете восстановление дампа - owner другой.

Починить можно вот так:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" {DATABASE}` ; do  psql -c "alter table \"$tbl\" owner to {USERNAME}" {DATABASE} ; done

Запускать это надо из под пользователя postgres, то есть предварительно сделать:

su - postgres

Шпаргалка по командам

Показать список баз:

\l

Переключиться на Базу {DBNAME}

\c DBNAME

Поменять пароль пользователя:

ALTER USER {username} WITH PASSWORD '{new_password}';

Переименовать пользователя:

ALTER USER {old_username} RENAME TO {new_username};

Узнать размер базы DBNAME:

SELECT pg_size_pretty( pg_database_size( '{DBNAME}' ) );