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

Необходимое программное обеспечение под ОС Astra Linux / Debian / Alt Linux

• Сервер PostgreSQL 12 или выше
  PostgreSQL или Postgres Pro

• postgresql-contrib (обычно устанавливаются вместе с сервером Postgres)

• htop

• Iotop

• sysstat

• pgbadger

• ssh (клиент и сервер SSH)

• mc (Midnight Commander)

• tar

• zip

Важно

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

Установка

Скачайте требуемый пакет с сайта https://postgrespro.ru/products/download или https://www.postgresql.org/download. Для установки следуйте инструкциям на сайте.

Добавление локализации

Отредактируйте файл /etc/locale.gen, найдите и раскомментируйте строку ru_RU.UTF-8

Выполните команду:

sudo locale-gen

Дополнительная настройка

• В конфигурационном файле postgresql.conf переопределите умолчательные параметры согласно конфигурации железа.
  Пример для сервера с конфигурацией ЦП: 4 ядра; ОЗУ: 8 Гб; Диск: SSD:
  Добавить в конец конфигурационного файла

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Memory Configuration
shared_buffers = 2GB
effective_cache_size = 6GB
work_mem = 41MB
maintenance_work_mem = 410MB

# Checkpoint Related Configuration
min_wal_size = 2GB
max_wal_size = 3GB
checkpoint_completion_target = 0.9
wal_buffers = -1

# Network Related Configuration
listen_addresses = '*'
max_connections = 100

# Storage Configuration
random_page_cost = 1.1
effective_io_concurrency = 200

# Worker Processes Configuration
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_parallel_workers = 2

max_locks_per_transaction = 500

Примечание

Для подбора параметров можно воспользоваться онлайн-мастером https://www.pgconfig.org

В мастере указать версию постгреса, выбрать профиль DataWare house and BI Applications и указать параметры железа сервера: количество ядер ЦП, размер ОЗУ, тип диска.

• Настройте аутентификацию по имени узла в файле pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             all                     scram-sha-256

Примечание

Для получения подробной информации по конфигурации Postgres воспользуйтесь документацией на сайте: https://postgrespro.ru/docs

Развертывание новой базы

Поменяйте пароль пользователю ОС c именем postgres

sudo passwd postgres

Подключитесь терминалом к серверу СУБД под административным пользователем

Переключитесь на пользователя «postgres»

su postgres

Подключитесь локально утилитой psql к СУБД Postgres

psql

Поменяйте пароль суперпользователя

alter user postgres password '<Новый пароль>';

Создайте пользователя

CREATE ROLE <userName> WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD '<UserPassword>'; 
GRANT pg_signal_backend TO <userName>;

Создайте новую БД, в качестве владельца укажите созданного пользователя

CREATE DATABASE "<имяБД>" WITH OWNER = <userName> ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8' CONNECTION LIMIT = -1 TEMPLATE template0;

Завершите сессию psql

\q

Подключитесь к созданной бд

psql <имяБД>

Подключите, необходимые для работы Global, расширения

CREATE EXTENSION if not exists plpgsql; 
CREATE EXTENSION if not exists fuzzystrmatch; 
CREATE EXTENSION if not exists pg_trgm; 
CREATE EXTENSION if not exists pg_stat_statements; 
CREATE EXTENSION if not exists "uuid-ossp"; 
CREATE EXTENSION if not exists dict_xsyn; 
CREATE EXTENSION if not exists ltree;

Завершите сессию psql

\q

Теперь БД готова к работе.

Развертывание поставочного дампа

При получении поставочного дампа нагоните его на созданную БД.

Если БД не пуста и содержит объекты, удалите их

При наличии прав суперпользователя Postgres можно удалить БД

DROP DATABASE <имяБД>;

и создать заново, выполнив шаги раздела «Развертывание новой базы»

Если прав суперпользователя Postgres нет, воспользуйтесть скриптом удаления

SET search_path TO public;
--
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT p.oid::regprocedure as sFunctionName    
FROM pg_proc p 
INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
inner join pg_roles a on p.proowner =a.oid
WHERE ns.nspname = current_schema
  and a.rolname =current_user 
  and p.probin is null) LOOP
        EXECUTE 'DROP FUNCTION IF EXISTS ' || r.sFunctionName || ' CASCADE';
    END LOOP;
END $$;
--
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;
--
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT  c.relname   
FROM pg_class c 
inner join pg_catalog.pg_namespace n on c.relnamespace =n.oid
inner join pg_roles a on c.relowner =a.oid
WHERE (c.relkind = 'S')
  and n.nspname =current_schema 
  and a.rolname =current_user) LOOP
        EXECUTE 'drop sequence IF EXISTS ' || quote_ident(r.relname) || ' CASCADE';
    END LOOP;
END $$;
--
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT  c.relname
FROM pg_class c 
inner join pg_catalog.pg_namespace n on c.relnamespace =n.oid
inner join pg_roles a on c.relowner =a.oid
where c.relkind ='v'
  and n.nspname =current_schema 
  and a.rolname =current_user) LOOP
        EXECUTE 'drop view IF EXISTS ' || quote_ident(r.relname) || ' CASCADE';
    END LOOP;
END $$;
--
SELECT lo_unlink(l.oid) 
FROM pg_largeobject_metadata l
inner join pg_roles a on l.lomowner =a.oid
WHERE a.rolname =current_user;

Поставочный дамп запакован архиватором tar

Имя файла архива имее следующий вид: <имяБД>_public_<дата>_<ччммсс>.tar Пример: demoDb_public_04.11.2022_170406.tar

Загрузите файл поставочного дампа на сервер Postgres в директорию /usr/dumpstore

Распакуйте архив

mkdir -p /tmp/global/Dump
tar -xvf /usr/dumpstore/<имяБД>_public_<дата>_<ччммсс>.tar -C /tmp/global/Dump --strip-components 1

Дамп распакуется в каталог /tmp/global/Dump/<имяБД>_public_<дата>_<ччммсс>

Восстановите БД из дампа

/opt/pgpro/std-12/bin/pg_restore --dbname=postgresql://<sUser>:<sPass>@localhost:5432/<sDbName> -O -x -v --no-tablespaces  --jobs=4 /tmp/global/Dump/<имяБД>_public_<дата>_<ччммсс>

/opt/pgpro/std-12/bin/pg_restore - путь до утилиты распаковки дампа (postgres pro 12)

<sUser> - имя пользователя БД

<sPass> - пароль

<sDbName> - имя БД

jobs=4 - количество потоков, указывать по количеству ядер