====== Настройка базы данных PostgreSQL ======
===== Версия PostgreSQL =====
Поддерживаются версии PostgreSQL начиная с 13 (рекомендуется не ниже 14) версии с поддержкой ICU.

Для однообразной сортировки в PostgreSQL на Windows и Linux, и для сортировки как в SQL Server, используется провайдер icu, который использует внешнюю библиотеку ICU. Локали ICU можно использовать, только если поддержка ICU была включена в конфигурации сборки PostgreSQL.

Для установки PostgreSQL требуется сборка PostgreSQL начиная с 13 версии с поддержкой ICU.

===== Ограничения PostgreSQL =====
Основные ограничения PostgreSQL, которые отличаются от SQL Server (https://postgrespro.ru/docs/postgresql/14/limits):
  - Максимальная длина идентификатора 63 байта (имена таблиц, ключей, индексов и других объектов базы данных (https://postgrespro.ru/docs/postgresql/14/sql-syntax-lexical#SQL-SYNTAX-IDENTIFIERS)).
  - Максимальное кол-во столбцов в индексе 32.
  - По умолчанию поиск по строке (https://postgrespro.ru/docs/postgresql/14/datatype-character) чувствителен к регистру. Одно из решений использовать тип citext (https://postgrespro.ru/docs/postgresql/14/citext).

===== Настройка PostgreSQL =====
Надо настроить PostgreSQL, по умолчанию PostgreSQL использует 256 МБ памяти.

Для генерации настроек по конфигурации сервера надо зайти на https://www.pgconfig.org/ и заполнить параметры. 

Далее на вкладке "Export config" выберете формат ALTER SYSTEM commands  для изменения параметров PostgreSQL, запустите полученный скрипт на сервере PostgreSQL и перезапустите сервис PostgreSQL после выполнения скрипта.

После запуска скриптов надо перезапустить сервис PostgreSQL: <code bash> sudo service postgresql restart </code>

=== Пример для PostgreSQL 13: === \\

# OS Type: GNU/Linux based \\
# Architecrure: 64 Bits
# Storage type: ssd \\
# Application profile: ERP or long transaction applications\\
# CPUs num: 16 \\
# Total Memory: 40 GB \\
# Connections num: 1000 \\
# Data Storage: ssd \\
# DB Version: 13 \\

<code sql>
sql
-- Memory Configuration
ALTER SYSTEM SET shared_buffers TO '10GB';
ALTER SYSTEM SET effective_cache_size TO '30GB';
ALTER SYSTEM SET work_mem TO '14MB';
ALTER SYSTEM SET maintenance_work_mem TO '2GB';

-- Checkpoint Related Configuration
ALTER SYSTEM SET min_wal_size TO '2GB';
ALTER SYSTEM SET max_wal_size TO '3GB';
ALTER SYSTEM SET checkpoint_completion_target TO '0.9';
ALTER SYSTEM SET wal_buffers TO '-1';

-- Network Related Configuration
ALTER SYSTEM SET listen_addresses TO '*';
ALTER SYSTEM SET max_connections TO '1000';

-- Storage Configuration
ALTER SYSTEM SET random_page_cost TO '1.1';
ALTER SYSTEM SET effective_io_concurrency TO '200';

-- Worker Processes Configuration
ALTER SYSTEM SET max_worker_processes TO '8';
ALTER SYSTEM SET max_parallel_workers_per_gather TO '2';
ALTER SYSTEM SET max_parallel_workers TO '2';

-- Logging configuration for pgbadger
ALTER SYSTEM SET logging_collector TO 'on';
ALTER SYSTEM SET log_checkpoints TO 'on';
ALTER SYSTEM SET log_connections TO 'on';
ALTER SYSTEM SET log_disconnections TO 'on';
ALTER SYSTEM SET log_lock_waits TO 'on';
ALTER SYSTEM SET log_temp_files TO '0';
ALTER SYSTEM SET lc_messages TO 'C';

-- Adjust the minimum time to collect the data
ALTER SYSTEM SET log_min_duration_statement TO '10s';
ALTER SYSTEM SET log_autovacuum_min_duration TO '0';

-- CSV Configuration
ALTER SYSTEM SET log_destination TO 'csvlog';
</code>	 
После запуска скрипта с установкой настроек нужно перезапустить сервис PostgreSQL: <code bash> sudo service postgresql restart </code>


===== Настройка приложения =====

Статьи по настройкам быстродействию PostgreSQL через строку подключения:  

https://www.roji.org/prepared-statements-in-npgsql-3-2

https://www.npgsql.org/doc/prepare.html

В файле client.config:
		
В разделе ''<connectionStrings>'' заменить \\

  <add name="db" providerName="System.Data.SqlClient" connectionString="Server=.\SQL;Database=sl_3_24;User Id=user;Password=123;Connection Timeout=3000;Max Pool Size=2000;"/>
  <add name="dbCubes" providerName="System.Data.SqlClient" connectionString="Server=.\SQL;Database=sl_3_24;User Id=user;Password=123;Connection Timeout=300;Max Pool Size=2000;"/>
  <add name="busDb" providerName="System.Data.SqlClient" connectionString="Server=.\SQL;Database=sl_3_24;User Id=user;Password=123;Connection Timeout=300;Max Pool Size=2000;"/>

на

  <add name="db" providerName="Npgsql" connectionString="Server=localhost;Port=5432;Database=a2nta_db;User 
  Id=postgres;Password=123;Pooling=true;CommandTimeout=7200;Timeout=500;MaxPoolSize=500;Max Auto Prepare=100;Auto Prepare Min Usages=3;"/>
  <add name="dbCubes" providerName="Npgsql" connectionString="Server=localhost;Port=5432;Database=a2nta_db;User 
  Id=postgres;Password=123;Pooling=true;CommandTimeout=7200;Timeout=500;MaxPoolSize=500;Max Auto Prepare=100;Auto Prepare Min Usages=3;"/>
  <add name="busDb" providerName="Npgsql" connectionString="Server=localhost;Port=5432;Database=a2nta_db;User 
  Id=postgres;Password=123;Pooling=true;CommandTimeout=7200;Timeout=500;MaxPoolSize=500;Max Auto Prepare=100;Auto Prepare Min Usages=3;"/>


В разделе ''<appSettings>'' заменить

  <add key="BusTransportType" value="SqlServer" />

на

  <add key="BusTransportType" value="PostgreSql" />


В разделе

  <unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
  	<container name="db">


заменить

  <register type="SmartSuite.Data.Persistence.StorageProviderFactory, SmartSuite.Data" mapTo="SmartSuite.Data.Persistence.SQLServer.SqlStorageProviderFactory, SmartSuite.Data" />
  <register type="SL.App.ServerObjects.IServerObjectsProvider, SL.App" mapTo="SL.App.ServerObjects.SqlServer.ServerObjectsProvider, SL.SqlServer" />
  <register type="P2M.Cubes.Data.IDirectoryDataManager, smcorelib" mapTo="P2M.Cubes.Data.SQL.DirectoryDataManager, smcorelib" />
  <register type="P2M.Cubes.Data.IPivotDataManager, smcorelib" mapTo="P2M.Cubes.Data.SQL.PivotDataManager, smcorelib" />

на

  <register type="SmartSuite.Data.Persistence.StorageProviderFactory, SmartSuite.Data" mapTo="SmartSuite.PostgreSQL.PgStorageProviderFactory, SL.PostgreSQL" />
  <register type="SL.App.ServerObjects.IServerObjectsProvider, SL.App" mapTo="SL.App.ServerObjects.PostgreSQL.ServerObjectsProvider, SL.PostgreSQL" />
  <register type="P2M.Cubes.Data.IDirectoryDataManager, smcorelib" mapTo="SL.PostgreSQL.Data.DirectoryDataManager, SL.PostgreSQL" />
  <register type="P2M.Cubes.Data.IPivotDataManager, smcorelib" mapTo="SL.PostgreSQL.Data.PivotDataManager, SL.PostgreSQL" />
		
===== Настройка приложения для использования PostgreSQL с PgBouncer =====
При использовании PgBouncer надо добавить в строку подключения параметр (https://www.npgsql.org/doc/compatibility.html?q=pgbouncer):
  No Reset On Close=true

или отключить пул соединений в приложении в строке подключения:
  Pooling=false
  
Если используем PgBouncer в режиме //transaction pooling//, надо отключать prepared statements в приложение в строке подключения (надо убрать параметры "Max Auto Prepare" и "Auto Prepare Min Usages" или выставить "Max Auto Prepare=0" в строке подключения):

https://www.pgbouncer.org/faq.html#:~:text=to%20DEALLOCATE%20ALL%3B-,How%20to%20use%20prepared%20statements%20with%20transaction%20pooling%3F,prepared%20statements%20in%20the%20client


===== Установка в Ubuntu =====
==== Установка локали ====
https://www.postgresql.org/download/linux/ubuntu/

https://wiki.postgresql.org/wiki/Apt

Установка локали https://askubuntu.com/questions/76013/how-do-i-add-locale-to-ubuntu-server

В Linux в терминале выполнить:
  locale -a
  
Проверить, что в списке есть ru_RU.UTF-8, если есть — запустить (если нет - надо устанавливать русскую локаль):
<code bash>
  sudo locale-gen ru_RU 
  sudo locale-gen ru_RU.UTF-8
  sudo update-locale </code>
  
Если не сработало (зависит от дистрибутива Linux)
<code bash> sudo locale-gen ru_RU </code>
то запустить
 <code bash> sudo dpkg-reconfigure locales </code>
и выбрать нужные локали.

На базе postgres выполнить:
<code bash> SELECT pg_import_system_collations('pg_catalog'); </code>
Перезапустить сервис PostgreSQL:
<code bash> sudo service postgresql restart </code>

==== Настройка PostgreSQL ==== 
1. Пересоздание кластера PostgreSQL с другой локалью: 

http://breys.ru/4379.html 

<code bash>
  sudo pg_dropcluster --stop 13 main
  sudo pg_createcluster --locale ru_RU.utf8 --start 13 main
  sudo pg_ctlcluster 13 main start </code>
  
2. Как отредакnировать файл от имени админа

https://vitux.com/how-to-open-and-edit-files-and-folders-in-ubuntu-desktop-as-an-administrator/

3. Доступ извне (только для разработки, не использовать на продакшене)

https://blog.rvalitov.ru/development/yii2/postgresql-v-virtualbox/

Добавить строчку в самый низ в файл <nowiki> /etc/postgresql//main/pg_hba.conf </nowiki>

<code> host    all             all             0.0.0.0/0               trust </code>

В файле <nowiki> /etc/postgresql//main/postgresql.conf </nowiki> добавляем или меняем строчку ''listen_addresses = '*' '' (убедитесь, что она раскомментирована)

Перезагружаем сервис

<code bash> sudo service postgresql restart </code>

4. Настройка VirtualBox для доступа к PostgreSQL с хоста

https://serverfault.com/questions/225155/virtualbox-how-to-set-up-networking-so-both-host-and-guest-can-access-internet

5. Запуск psql https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04-ru
<code bash> sudo -u postgres psql </code>

===== Установка и развертывание на Astra Linux Special Edition x.7 =====

=== Что нужно знать ===

Установка незащищенной версии выполняется с подключенным компонентом astra-ce расширенного (extended) репозитория, установка защищенной версии выполняется без подключения этого компонента. 

На момент написания данной статьи в составе Astra Linux Special Edition РУСБ.10015-01 (очередное обновление 1.7) доступны следующие версии СУБД PostgreSQL:
  * версия 11.10-astra.se5 - защищенная версия в основном репозитории (на установочном диске);
  * версия 11.10-astra.se13 - защищенная версия в основном репозитории (оперативное обновление №1);
  * версия 11.12-astra.ce5 - незащищенная версия в компоненте astra-ce расширенного репозитория до оперативного обновления 1.7.2;
  * версия 14+240astra4 -  - незащищенная версия в компоненте astra-ce расширенного репозитория начиная с оперативного обновления 1.7.2.

<callout type="danger"> При установке СУБД PostgreSQL из компонента astra-ce расширенного репозитория защищенная версия СУБД, доработанная для взаимодействия с КСЗ Astra Linux Special Edition будет заменена на стандартную версию СУБД. 

Подробнее про структуру и использование репозиториев см. [[https://wiki.astralinux.ru/pages/viewpage.action?pageId=149062354|Репозитории Astra Linux Special Edition x.7: структура, особенности подключения и использования.]] </callout>

<callout type="warning">При установке незащищенной СУБД PostgreSQL версии 14 из компонента astra-ce расширенного репозитория 1.7.2 при наличии ранее установленной СУБД PostgreSQL версии 11 необходимо перед запуском нового кластера вручную удалить ранее установленные кластеры версии 11:

<code bash> pg_dropcluster 13 <имя_старого_кластера> </code>

</callout>

==== Установка пакетов ====
1. Подключить репозитории:
  * основной репозиторий и актуальное оперативное обновление основного репозитория;
  * только для установки незащищенной версии PostgreSQL - актуальное оперативное обновление расширенного репозитория, включая компонент astra-ce. Для установки защищенной версии подключение компонента astra-ce расширенного репозитория недопустимо.

2. Обновить список пакетов:

<code bash> sudo apt update </code>

3. Опционально: установить актуальные обновления:

<code bash> sudo apt dist-upgrade </code>

4. Проверить доступные версии:

<code bash> apt policy postgresql-13</code>
5. Установить пакет postgresql-13 старшей доступной версии:

<code bash> sudo apt install postgresql-13 </code>

или установить пакет postgresql-13 указанной версии:

<code bash> sudo apt install postgresql-13=<номер_версии></code>

6. Убедиться, что служба postgresql запустилась:

<code bash> systemctl status postgresql </code>

<code bash> ● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Fri 2021-09-10 12:48:20 MSK; 1min 26s ago
Main PID: 4338 (code=exited, status=0/SUCCESS)
   Tasks: 0 (limit: 4637)
   Memory: 0B
   CGroup: /system.slice/postgresql.service </code>
   
==== Первичная настройка СУБД PostgreSQL ====
1. Выполнить вход в сессию служебного пользователя postgres:

<code bash> sudo su - postgres </code>

Работая в сессии служебного пользователя postgres:
  * Установить пароль администратора СУБД:

<code bash> psql -c "alter user postgres with password '<указать_пароль>'" </code>
    * Вместо текста <пароль> указать устанавливаемый пароль;
    * Пароль заключается в одинарные кавычки;
    * Вся команда заключается в двойные кавычки.

  * Завершить работу в сессии служебного пользователя postgres:

<code bash> exit </code>

2. Наcтроить удаленный доступ к СУБД, для чего в конфигурационном файле /etc/postgresql/13/main/postgresql.conf проверить и установить параметр listen_addresses:
  * Значение по умолчанию — служба postgresql подключена ко всем сетевым интерфейсам:

<code bash> listen_addresses = '*' </code>

=== Допустимые значения: ===
  * Служба postgresql подключена ко всем сетевым интерфейсам IPv4:
<code bash> listen_addresses = '0.0.0.0' </code>

  * Служба postgresql подключена ко всем сетевым интерфейсам IPv6:

<code bash> listen_addresses = '::'  </code>

  * Разделенный запятыми список IP-адресов сетевых интерфейсов, к которым будет подключена служба:

<code bash> listen_addresses = '192.168.1.2,10.0.0.2'  </code>

Допускается использовать пустой список, тогда подключение будет возможно только через сокеты UNIX (подробнее см. документацию СУБД).

3. Если в конфигурацию были внесены изменения, то для того, чтобы сделанные изменения вступили в силу перезапустить службу postgresql:

<code bash> sudo systemctl restart postgresql  </code>

4. Проверить, к каким сетевым портам и интерфейсам подключена служба postgresql, можно командой:

<code bash> ss -tunelp | grep uid:`id -u postgres`  </code>

<code bash> 
tcp     LISTEN   0        1024             0.0.0.0:5432           0.0.0.0:*      uid:107 ino:32947 sk:5 <->
tcp     LISTEN   0        1024                [::]:5432              [::]:*      uid:107 ino:32948 sk:9 v6only:1 <->       
</code>   
              
Приведен вывод команды для службы, настроенной по умолчанию (параметр listen_addresses = '*', служба работает с портом 5432 на всех доступных сетевых интерфейсах IPv4 и IPv6);

5. Настроить активные сетевые экраны, разрешив доступ к сетевому порту postgresql (по умолчанию - порт 5432):
  * Для сетевого экрана ufw:

<code bash>sudo ufw allow 5432/tcp </code> 
  * Для сетевого экрана firewalld:

<code bash> sudo firewall-cmd --add-service=postgresql --zone=internal --permanent </code> 

==== Первичное тестирование работоспособности СУБД ==== 
1. Выполнить вход в сессию служебного пользователя postgres:

<code bash> sudo su - postgres </code> 
Работая в сессии служебного пользователя postgres:
  * Добавить тестового пользователя СУБД test_user1:

<code bash> createuser test_user1 </code> 

  * Добавить тестовую базу данных test_db, указав в качестве её владельца тестового пользователя:

<code bash> createdb test_db -O test_user1  </code>

  * Установить пароль тестового пользователя:

<code bash>  psql -c "alter user test_user1 with password '<указать_пароль>'" </code>

  * Подключиться к созданной тестовой базе данных и войти в сессию СУБД:

<code bash>  psql test_db  </code>

<code bash> psql (11.12 (Debian 11.12-astra.ce5))
Введите "help", чтобы получить справку.
test_db=# 
</code>

Дальнейшие команды выполняются в сессии СУБД:
    * Создать таблицу и добавить в нее данные:

<code sql> create table test_table ( id int,first_name text, last_name text ); </code>
<code sql> CREATE TABLE </code>

    * Добавить в таблицу данные:

<code sql> insert into test_table (id,first_name,last_name) values (2,'Иван','Иванов'); </code>
<code sql> INSERT 0 1 </code>

    * Вывести табличные данные:

<code sql> select * from test_table; </code>
<code sql>
 id | first_name | last_name
----+------------+-----------
  1 | Иван       | Иванов
(1 строка)
</code>
    * Выйти из сессии СУБД:

<code sql> exit </code>

    * Удалить тестовую базу данных:

<code sql> dropdb test_db </code>

    * Завершить работу в сессии служебного пользователя postgres:

<code sql> exit </code>

===== Бэкап и восстановление базы =====
https://postgrespro.ru/docs/postgresql/14/backup
	
Бэкап базы делается утилитой '' pg_dump '', нужно использовать '' pg_dump '' той же версии, которая установлена на сервере PostgreSQL:
	
<code bash>  pg_dump.exe --file "D:\\DbBackups\\a2nta_db.bak" --host "a2nta_db" --port "5432" --username "postgres" --password --verbose --format=c --blobs "a2nta_db" </code>
	

Для восстановления базы надо создать пустую базу скриптом:
<code sql>
  sql
  CREATE DATABASE "a2nta_db"
  WITH 
  OWNER = postgres
  ENCODING = 'UTF8'
  LC_COLLATE = 'ru_RU.utf8' -- для Linux
  LC_CTYPE = 'ru_RU.utf8' -- для Linux
  --LC_COLLATE = 'Russian_Russia.1251' -- для Windows
  --LC_CTYPE = 'Russian_Russia.1251' -- для Windows
  --TEMPLATE template0 -- нужно раскомметировать, если COLLATION в базе postgres (SHOW lc_collate;) отличается от значения в LC_COLLATE
  TABLESPACE = pg_default
  CONNECTION LIMIT = -1;	

  ALTER DATABASE "a2nta_db" SET search_path TO "$user", public, dbo;

  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
</code>

И запустить утилиту '' pg_restore '' , в созданную пустую базу:

<code bash> pg_restore.exe --host "localhost" --port "5432" --username "postgres" --password --dbname "a2nta_db" --verbose "D:\\DbBackups\\a2nta_db.bak"	</code>

Можно установить search_path на системном уровне, чтобы при каждом восстановление базы не надо было это настраивать отдельно:
<code sql>
  sql
  ALTER SYSTEM SET search_path 
  TO "$user", public, dbo;

  SELECT pg_reload_conf();
</code>

Нужно выдать права пользователю на базу (https://postgrespro.ru/docs/postgresql/14/sql-grant):
<code sql>
  sql
  ALTER DATABASE <base> OWNER TO <user>;
  GRANT postgres TO <user>;
</code>

=====  Полезные ссылки по PostgreSQL =====

  - Документация по PostgreSQL: https://www.postgresql.org/docs/
  - Документация по PostgreSQL на русском: https://postgrespro.ru/docs/
  - Онлайн утилита генерации настроек для конфигурации сервера PostgreSQL: https://www.pgconfig.org/
  - PgBouncer FAQ: https://www.pgbouncer.org/faq.html


=====  Решение проблем с PostgreSQL =====

==== Блокировки транзакций ====
Если возникают проблемы с операциями в БД PostgreSQL по причине конфликта блокировок транзакций, рекомендуется увеличить значение параметра ''max_locks_per_transaction'' в конфигурационном файле Сервера БД ''postgresql.conf'', например, до 100.

<code config [highlight_lines_extra="9"]>
#...

#--------------------------------------------------------
#	LOCK MANAGEMENT
#--------------------------------------------------------

# deadlock_timeout = 1s

max_locks_per_transaction = 100 	# min 10
#	(change requires restart)

#max_pred_locks_per_transaction = 64	# min 10
#	(change requires restart)

#...
</code>