Установка PostgreSQL 9.6 на Ubuntu 16.04

Nov 19, 2013 20:19 · 610 words · 3 minute read админство postgres

Что-то мне в последнее время честенько приходится ставить postgres. То на новый боевой сервер, то на свою персональную VPS, так что я решил написать заметочку на будущее. Авось и ещё кому-нибудь пригодится :) Процесс установки я буду описывать на голой системе (ubuntu 16.04), и все команды будут от root'а. Так работать, конечно, не принято - все мы знаем, что sudo наше всё, но здесь я ничем не рискую, а пользователя добавлю уже после описанного. Итак, поехали!

Установка PostgreSQL 9.6

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

$ apt install postgresql postgresql-contrib

В процессе установки у меня возникла проблема с локалью, а именно ошибка “Error: The locale requested by the environment is invalid.". Связано это с тем, что в системе не определены переменные LANGUAGE и LC_ALL. Для исправления их нужно прописать в /etc/default/locale:

$ echo 'LANGUAGE="en_US.UTF-8"' >> /etc/default/locale
$ echo 'LC_ALL="en_US.UTF-8"' >> /etc/default/locale

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

$ su postgres -c psql
[sudo] password for tyvik: 
psql (9.6.7)
Type "help" for help.

postgres=#

Но всё время заходить через него как-то не очень хорошо, к тому же это единственный способ подключения. Исправим настройки в файле /etc/postgresql/9.6/main/postgresql.conf, указав значения:

listen_addresses = '*'
password_encryption = on

В процессе эксплуатации рекомендуется listen_addresses установить в localhost, т.е. разрешить только локальные подключения. При сканировании nmap'ом порт 5432 уйдёт из списка открытых. Но это ещё не всё :) отдельно надо указать ещё каким пользователям как именно можно подключаться в файле /etc/postgresql/9.1/main/pg_hba.conf. Для начала заветная строчка:

host all all 0.0.0.0/0 md5

Теперь разберём подробнее:

  • host - откуда идёт подключение. Бывает локальным (local) или сетевым (host)
  • all - список баз, к которым разрешено подключаться
  • all - список пользователей, которым разрешено подключаться
  • 0.0.0.0/0 - диапазон IP-адресов, с которых принимается подключение. У меня разрешены все IP.
  • md5 - метод авторизации. Самый популярный md5 - по хешированному паролю. Ещё также используется trust - без запроса пароля. На самом деле их гораздо больше, ещё есть: reject, password, gss, sspi, krb5, ident, peer, pam, ldap, radius, cert.

Теперь можно проверить подключения из pgAdmin или psql. Следующим шагом будет импорт данных.

Миграция

Проведём миграцию в 2 шага: экспорт уже существующей базы и импорт в новую. Экспорт делается командой pg_dump:

$ pg_dump -U$pg_login --password $pg_dbname > $path_to_archive/db.sql

у pg_dump есть ключ --format=format:

  • plain - генерация обычного SQL-файла (по умолчанию).
  • custom - то же, что и plain, но добавляет метки объектов для pg_restore. Можно указывать отдельные объекты.
  • directory - то же, что и custom, но каждый объект будет экспортироваться в отдельный файл.
  • tar - то же, что и directory, но сжатый tar'ом.

Обычно требуется не только сделать дамп данных, но и сохранить куда-нибудь в надёжное место. Так что почему бы сразу и не заархивировать его?

$ pg_dump -U$pg_login --password $pg_dbname --host=$host | gzip > dump.sql.gz

Теперь у нас есть дамп, подготовим новую базу и зальём туда данные. В консоли psql создадим пользователя и БД:

create user new_user with password 'pass';
create database user_db with owner new_user;

В командной строке выполним импорт:

$ psql -Unew_user -d user_db < db.sql

или

$ pg_restore -d user_db -U new_user db.sql

ну или если дамп сжат, то

$ gunzip -c dump.sql.gz | psql --username=$pg_login --password --host=$host $pg_dbname

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

pg_dump

К слову, команда pg_dump имеет множество настроек, вот лишь небольшой список тех, которые мне доводилось применять:

  • --data-only - экспортировать только данные без DDL
  • --schema-only - экспортировать только DDL без данных
  • --schema=<> - выгрузить сразу несколько схем
  • --table=<> - выгрузить только отдельную таблицу
  • --disable-triggers - дописывает в экспорт команды по отключению триггеров и контроля целостности при восстановлении