Поддерживаются версии PostgreSQL начиная с 13 (рекомендуется не ниже 14) версии с поддержкой ICU.
Для однообразной сортировки в PostgreSQL на Windows и Linux, и для сортировки как в SQL Server, используется провайдер icu, который использует внешнюю библиотеку ICU. Локали ICU можно использовать, только если поддержка ICU была включена в конфигурации сборки PostgreSQL.
Для установки PostgreSQL требуется сборка PostgreSQL начиная с 13 версии с поддержкой ICU.
Основные ограничения PostgreSQL, которые отличаются от SQL Server (https://postgrespro.ru/docs/postgresql/14/limits):
Надо настроить PostgreSQL, по умолчанию PostgreSQL использует 256 МБ памяти.
Для генерации настроек по конфигурации сервера надо зайти на https://www.pgconfig.org/ и заполнить параметры.
Далее на вкладке «Export config» выберете формат ALTER SYSTEM commands для изменения параметров PostgreSQL, запустите полученный скрипт на сервере PostgreSQL и перезапустите сервис PostgreSQL после выполнения скрипта.
После запуска скриптов надо перезапустить сервис PostgreSQL:
sudo service postgresql restart
=== Пример для 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
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';
После запуска скрипта с установкой настроек нужно перезапустить сервис PostgreSQL:
sudo service postgresql restart
Статьи по настройкам быстродействию 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" />
При использовании 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.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, если есть — запустить (если нет - надо устанавливать русскую локаль):
sudo locale-gen ru_RU sudo locale-gen ru_RU.UTF-8 sudo update-locale
Если не сработало (зависит от дистрибутива Linux)
sudo locale-gen ru_RU
то запустить
sudo dpkg-reconfigure locales
и выбрать нужные локали.
На базе postgres выполнить:
SELECT pg_import_system_collations('pg_catalog');
Перезапустить сервис PostgreSQL:
sudo service postgresql restart
1. Пересоздание кластера PostgreSQL с другой локалью:
sudo pg_dropcluster --stop 13 main sudo pg_createcluster --locale ru_RU.utf8 --start 13 main sudo pg_ctlcluster 13 main start
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/
Добавить строчку в самый низ в файл /etc/postgresql//main/pg_hba.conf
host all all 0.0.0.0/0 trust
В файле /etc/postgresql//main/postgresql.conf добавляем или меняем строчку listen_addresses = '*' (убедитесь, что она раскомментирована)
Перезагружаем сервис
sudo service postgresql restart
4. Настройка VirtualBox для доступа к PostgreSQL с хоста
5. Запуск psql https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-18-04-ru
sudo -u postgres psql
Установка незащищенной версии выполняется с подключенным компонентом astra-ce расширенного (extended) репозитория, установка защищенной версии выполняется без подключения этого компонента.
На момент написания данной статьи в составе Astra Linux Special Edition РУСБ.10015-01 (очередное обновление 1.7) доступны следующие версии СУБД PostgreSQL:
Подробнее про структуру и использование репозиториев см. Репозитории Astra Linux Special Edition x.7: структура, особенности подключения и использования.
pg_dropcluster 13 <имя_старого_кластера>
1. Подключить репозитории:
2. Обновить список пакетов:
sudo apt update
3. Опционально: установить актуальные обновления:
sudo apt dist-upgrade
4. Проверить доступные версии:
apt policy postgresql-13
5. Установить пакет postgresql-13 старшей доступной версии:
sudo apt install postgresql-13
или установить пакет postgresql-13 указанной версии:
sudo apt install postgresql-13=<номер_версии>
6. Убедиться, что служба postgresql запустилась:
systemctl status postgresql
● 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
1. Выполнить вход в сессию служебного пользователя postgres:
sudo su - postgres
Работая в сессии служебного пользователя postgres:
psql -c "alter user postgres with password '<указать_пароль>'"
exit
2. Наcтроить удаленный доступ к СУБД, для чего в конфигурационном файле /etc/postgresql/13/main/postgresql.conf проверить и установить параметр listen_addresses:
listen_addresses = '*'
listen_addresses = '0.0.0.0'
listen_addresses = '::'
listen_addresses = '192.168.1.2,10.0.0.2'
Допускается использовать пустой список, тогда подключение будет возможно только через сокеты UNIX (подробнее см. документацию СУБД).
3. Если в конфигурацию были внесены изменения, то для того, чтобы сделанные изменения вступили в силу перезапустить службу postgresql:
sudo systemctl restart postgresql
4. Проверить, к каким сетевым портам и интерфейсам подключена служба postgresql, можно командой:
ss -tunelp | grep uid:`id -u postgres`
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 <->
Приведен вывод команды для службы, настроенной по умолчанию (параметр listen_addresses = '*', служба работает с портом 5432 на всех доступных сетевых интерфейсах IPv4 и IPv6);
5. Настроить активные сетевые экраны, разрешив доступ к сетевому порту postgresql (по умолчанию - порт 5432):
sudo ufw allow 5432/tcp
sudo firewall-cmd --add-service=postgresql --zone=internal --permanent
1. Выполнить вход в сессию служебного пользователя postgres:
sudo su - postgres
Работая в сессии служебного пользователя postgres:
createuser test_user1
createdb test_db -O test_user1
psql -c "alter user test_user1 with password '<указать_пароль>'"
psql test_db
psql (11.12 (Debian 11.12-astra.ce5)) Введите "help", чтобы получить справку. test_db=#
Дальнейшие команды выполняются в сессии СУБД:
CREATE TABLE test_table ( id INT,first_name text, last_name text );
CREATE TABLE
INSERT INTO test_table (id,first_name,last_name) VALUES (2,'Иван','Иванов');
INSERT 0 1
SELECT * FROM test_table;
id | first_name | last_name ----+------------+----------- 1 | Иван | Иванов (1 строка)
exit
dropdb test_db
exit
https://postgrespro.ru/docs/postgresql/14/backup
Бэкап базы делается утилитой pg_dump , нужно использовать pg_dump той же версии, которая установлена на сервере PostgreSQL:
pg_dump.exe --file "D:\\DbBackups\\a2nta_db.bak" --host "a2nta_db" --port "5432" --username "postgres" --password --verbose --format=c --blobs "a2nta_db"
Для восстановления базы надо создать пустую базу скриптом:
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";
И запустить утилиту pg_restore , в созданную пустую базу:
pg_restore.exe --host "localhost" --port "5432" --username "postgres" --password --dbname "a2nta_db" --verbose "D:\\DbBackups\\a2nta_db.bak"
Можно установить search_path на системном уровне, чтобы при каждом восстановление базы не надо было это настраивать отдельно:
SQL ALTER SYSTEM SET search_path TO "$user", public, dbo; SELECT pg_reload_conf();
Нужно выдать права пользователю на базу (https://postgrespro.ru/docs/postgresql/14/sql-grant):
SQL ALTER DATABASE <base> OWNER TO <user>; GRANT postgres TO <user>;
Если возникают проблемы с операциями в БД PostgreSQL по причине конфликта блокировок транзакций, рекомендуется увеличить значение параметра max_locks_per_transaction в конфигурационном файле Сервера БД postgresql.conf, например, до 100.
#...
#--------------------------------------------------------
# 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)
#...