Show/Hide Toolbars

Руководство по тех. обслуживанию

Установка и настройка СУБД PostgreSQL

В данном разделе приведена инструкция для установки PostgreSQL для версии OS Ubuntu 20.04 LTS

1.  Добавляем официальные репозитории Postgres:

Используемые по умолчанию репозитории Ubuntu содержат пакеты Postgres, поэтому вы можете устанавливать их с помощью системы управления пакетами apt.

Поскольку это первый запуск apt в этой сессии, необходимо обновить локальный индекс пакетов. После этого установите пакет Postgres вместе с пакетом -contrib, который содержит дополнительные утилиты и функциональные возможности:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

sudo apt-get install wget

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

 

2. Устанавливаем СУБД Postgres 14

Используемые по умолчанию репозитории Ubuntu содержат пакеты Postgres, поэтому вы можете устанавливать их с помощью системы управления пакетами apt.

Поскольку это первый запуск apt в этой сессии, необходимо обновить локальный индекс пакетов. После этого установите пакет Postgres вместе с пакетом -contrib, который содержит дополнительные утилиты и функциональные возможности:

sudo apt-get update

sudo apt-get -y install postgresql-14

 

3. Устанавливаем заголовки и библиотеки плюс клиентскую часть (внимание, той же версии pg, что и в п.1)

Внимание: заголовки и библиотеки должны быть той же версии Postgres, что и в п.1

sudo apt-get -y install postgresql-server-dev-14        

sudo apt-get -y install postgresql-client-14        

sudo apt-get -y install postgresql-14-rum

 

4. Подготавливаем сервер к процессу сборки модулей Postgres, устанавливаем необходимое ПО

sudo apt-get install gcc gnupg make git

 

5. Устанавливаем модуль tds_fdw (https://github.com/tds-fdw/tds_fdw/blob/master/InstallUbuntu.md):

sudo apt-get update

sudo apt-get install libsybdb5 freetds-dev freetds-common

sudo apt-get install git

 Скачиваем с гитхаба

git clone https://github.com/tds-fdw/tds_fdw.git

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

cd tds_fdw

make USE_PGXS=1        

sudo make USE_PGXS=1 install

 

6. Устанавливаем модуль pg_hint_plan:

Если ранее git не был скачан, его необходимо скачать:

apt install git

Далее скачиваем репозиторий:

git clone https://github.com/ossc-db/pg_hint_plan.git

Переходим в каталог и собиарем:

cd ~/pg_hint_plan

make && make install

Заходим в psql и выполняем Load:

sudo su postgres

psql

create extension if not exists pg_hint_plan;

set pg_hint_plan.enable_hint_tables to on;

 

7.        Устанавливаем локаль ru_RU.utf8

sudo apt-get install language-pack-ru

sudo update-locale LANG=ru_RU.UTF-8

 Если есть дамп БД в cp1251, то, возможно, будет получена ошибка создания БД. Создаем и применяем эту локаль одной из следующих комманд:

sudo localedef -c -i ru_RU -f CP1251 ru_RU.CP1251        sudo gunzip /usr/share/i18n/charmaps/CP1251.gz        

sudo localedef -f /usr/share/i18n/charmaps/CP1251 -i /usr/share/i18n/locales/ru_RU /usr/lib/locale/ru_RU.cp1251

 

8. Исправляем формат даты-времени

 Создаем файл /etc/freetds/locales.conf со следующим наполнением:

[default]

date format = %b %e %Y %I:%M:%S.%z%p

 

9. Для доступа к базам с веб серверов необходимо внести изменения в файлы конфигурации.

 Вносим изменения в /etc/postgresql/14/main/pg_hba.conf

 Для соединения только из указанной подсети

host    all             all             192.168.4.0/24  md5

 Для соединения только из любой подсети

host    all             all             0.0.0.0/0       md5        

 Редактируем файл /etc/postgresql/ВерсияPG/main/postgresql.conf

 Для прослушивания всех подсетей (по умолчанию только localhost), добавляем строку (т.е. или раскомментариваем и правим):

listen_addresses = '*'

 

10. Перемещаем каталог данных pg. В качестве нового каталога используем /pgdata

Создаем каталог /pgdata

Останавливаем службу Postgres и копируем данные из текущего каталога в /pgdata

В файле конфигурации /etc/postgresql/14/main/postgresql.conf меняем:

data_directory = '/pgdata/1f'        

После перезапускаем службу

sudo service postgresql restart

 

11.        Создаем пользователя dbo, под которым которым будем подключаться из клиентских sql-средств с удаленных машин

su - postgres

psql -U postgres

postgres=# create user dbo with superuser createdb createrole inherit login password '******';

exit

exit

 

12.        Cоздаем расширения на сервере postgresql

su - postgres

psql -U postgres

postgres=# create user dbo with superuser createdb createrole inherit login password '*****';

postgres=# create extension if not exists tds_fdw schema public;

postgres=# create extension if not exists postgres_fdw schema public;

postgres=# create extension if not exists pgcrypto schema public;

postgres=# create extension if not exists pg_buffercache schema public;

postgres=# create extension if not exists btree_gin;

postgres=# create extension if not exists rum;

exit

exit

 

13.  Использование ролей и баз данных в PostgreSQL

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

После установки Postgres настроена на использование аутентификации ident, что значит, что выполняется привязка ролей Postgres с соответствующей системной учетной записью Unix/Linux. Если роль существует внутри Postgres, пользователь Unix/Linux с тем же именем может выполнить вход в качестве этой роли.

В ходе установки была создана учетную запись пользователя postgres, которая связана с используемой по умолчанию ролью Postgres. Чтобы использовать Postgres, вы можете войти в эту учетную запись.

Существует несколько способов использования этой учетной записи для доступа к Postgres.

Вы можете переключиться на учетную запись postgres на вашем сервере с помощью следующей команды:

sudo -i -u postgres

Теперь вы можете немедленно получить доступ к командной строке Postgres с помощью следующей команды:

psql

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

Закройте командную строку PostgreSQL с помощью следующей команды:

\q

В результате вы вернетесь в командную строку postgres в Linux.

 

14. Создаем новую роль

К настоящему моменту у вас есть только роль postgres, настроенная внутри базы данных. Вы можете создавать новые роли из командной строки с командой createrole. Флаг --interactive будет запрашивать имя новой роли, а также попросит указать, будут ли у этой роли права superuser.

Если вы выполнили вход в учетную запись postgres, то можете создать нового пользователя с помощью следующей команды:

postgres@server:~$ createuser --interactive

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

sudo -u postgres createuser --interactive

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

Output

Enter name of role to add: d10taskuser

Shall the new role be a superuser? (y/n) y

 

15. Создаем пользователя

Сперва необходимо войти под служебным пользователем postgres:

sudo -su postgres psql

Далее вы можете создать новую роль с помощью   команды create user, после чего указать пароль:

CREATE USER d10taskuser WITH PASSWORD 'YOUR_PASSWORD'

 

16.  Создаем новую базу данных

Для создания базы данных введите следующее:

CREATE DATABASE d10task_hd ENCODING UTF8

После чего вам необходимо предоставить доступ пользователю к созданной базе:

grant all privileges on database d10task_hd to d10taskuser

Для проверки введите следующее:

\l

Корректный результат проверки:

List of databases

 

17. Импортируем дамп базы данных

Скопируйте файл с расширением dump, содержащий структуру и контент базы данных, в определенную папку, например: /src/d10task_hd.dump. Данный файл вам может предоставить техническая поддержка.

Восстановите базу данных из файла с помощью следующих команд:

sudo -su postgres

psql -d d10task_hd < /src/d10task_hd.dump