Windows обслуживание баз PostgreSQL

Скрипт обслуживающий все базы (добавить в планировщик)

pg_vacuum.bat

REM СКРИПТ ОБСЛУЖИВАЮЩИЙ ВСЕ БАЗЫ В КЛАСТЕРЕ POSTGRESQL
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
SET PGBIN=C:\Program Files\PostgreSQL\10.5-24.1C\bin
SET PGUSER=postgres
SET PGPASSWORD=pass
REM Смена диска и переход в папку из которой запущен bat-файл
%~d0
CD %~dp0
REM Формирование имени файла резервной копии и файла-отчета
SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%_%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
SET LOGFILE=%DATETIME%.log
SET LOGPATH="Vacuum\%LOGFILE%"
REM Обслуживание баз кластера
IF NOT EXIST Vacuum MD Vacuum
CALL "%PGBIN%\vacuumdb" -zfa -U %PGUSER% 2>>%LOGPATH%
REM Анализ кода завершения
IF NOT %ERRORLEVEL%==0 GOTO Error
GOTO Successfull
REM В случае ошибки делается соответствующая запись в журнале
:Error
MSG * "Ошибка при Vacuum. Смотрите vacuum.log."
ECHO %DATETIME% Ошибки при Vacuum. Смотрите отчет %LOGFILE%. >> vacuum.log
GOTO End
REM В случае удачного Vacuum просто делается запись в журнал
:Successfull
ECHO %DATETIME% Успешное проведение Vacuum >> vacuum.log
GOTO End
:End


Сохранить.

Резервное копирование кластера PostgreSQL pg_basebackup Windows

Проверить
C:\Program Files\PostgreSQL\10.5-24.1C\data\postgresql.conf

max_replication_slots = 10
max_wal_senders = 10
wal_level = replica

Резервная копия кластера PostgreSQL
pg_basebackup.exe -U postgres -X fetch --format=tar -z -D "c:/backup" -P

Обслуживание базы
psql  -d demo -U postgres -c "VACUUM FULL FREEZE VERBOSE ANALYZE;"

Скрипт создающий резервную копию кластера (добавить в планировщик)

pg_bbackup.bat

REM СОЗДАНИЕ РЕЗЕРВНОЙ КОПИИ КЛАСТЕРА POSTGRESQL
REM Все действия в в папке где запускаем скрипт
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
REM SET PGBIN=C:\Program Files\PostgreSQL\11.5-19.1C\bin
SET PGBIN=F:\PostgreSQL\11.5-19.1C\bin
REM Папка в которой будут лежать копии в папке где запускаем скрипт
SET BACKUP="Backup"
REM Папка в которой формируются файлы, должна быть пустой и на SSD
SET PGBACKUP=C:\BackupTMP
DEL /F /Q %PGBACKUP%\base.tar.gz
SET PGUSER=postgres
SET PGPASSWORD=pass
REM Смена диска и переход в папку из которой запущен bat-файл
%~d0
CD %~dp0
REM Формирование имени файла резервной копии и файла-отчета
SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%_%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
SET DUMPFILE= %DATETIME%.tar.gz
SET LOGFILE=%DATETIME%.log
SET DUMPPATH="%BACKUP%\%DUMPFILE%"
SET LOGPATH="%BACKUP%\%LOGFILE%"
REM Создание резервной копии
IF NOT EXIST Backup MD Backup
CALL "%PGBIN%\pg_basebackup.exe" -U %PGUSER% -X fetch --format=tar -z -D %PGBACKUP% -P 2>%LOGPATH%
REM Анализ кода завершения
IF NOT %ERRORLEVEL%==0 GOTO Error
GOTO Successfull
REM В случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале
:Error
DEL %DUMPPATH%
MSG * "Ошибка при создании резервной копии базы данных. Смотрите backup.log."
ECHO %DATETIME% Ошибки при создании резервной копии базы данных %DUMPFILE%. Смотрите отчет %LOGFILE%. >> backup.log
GOTO End
REM В случае удачного резервного копирования просто делается запись в журнал
:Successfull
ECHO %DATETIME% Успешное создание резервной копии %DUMPFILE% >> backup.log
COPY %PGBACKUP%\base.tar.gz %DUMPPATH%
REM D -2 оставить файлы за 2 дня
forfiles /P "%BACKUP%" /D -2 /C "cmd /c del @path /q"
GOTO End
:End

Сохранить.  

Восстановление:

Остановить службу pgsql-10.5-24.1C-x64
net stop  pgsql-10.5-24.1C-x64
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" останавливается.
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" успешно остановлена.


Очистить содержимое:
C:\Program Files\PostgreSQL\10.5-24.1C\data

Разархивировать архив вида  2019-03-20_18-00-57.tar.gz
в C:\Program Files\PostgreSQL\10.5-24.1C\data

net start  pgsql-10.5-24.1C-x64
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" запускается.
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" успешно запущена.

Разархивировать архив вида  2019-03-20_18-00-57.tar.gz

в C:\Program Files\PostgreSQL\10.5-24.1C\data


Если удалили папку
C:\Program Files\PostgreSQL\10.5-24.1C\data



Создать

C:\Program Files\PostgreSQL\10.5-24.1C\data



Но нужно,
 
Задать на папку права полного доступа для пользователя NETWORK SERVICE от имени которого запускает  pgsql-10.5-24.1C-x64

Резервное копирование базы PostgreSQL pg_dump под windows

pgAdmin PostgreSQL Tools 

Создание бэкапа базы PostgreSQL для Windows

Как восстановить базу данных PostgreSQL из архива 

Создание пустой базы demo1 с помощью pgAdmin4

Создание базы demo1 запросом sql

 CREATE DATABASE demo1
    WITH
    OWNER = postgres
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'Russian_Russia.1251'
    LC_CTYPE = 'Russian_Russia.1251'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

psql -U postgres -c "CREATE DATABASE demo1 WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'Russian_Russia.1251' LC_CTYPE = 'Russian_Russia.1251' TABLESPACE = pg_default CONNECTION LIMIT = -1;"

Восстановление базы demo в предварительно созданную выше базу demo1:

pg_restore.exe --host "localhost" --port "5432" --username "postgres" --dbname "demo1" --verbose "demo2019-03-20_20-03-06.backup"


Восстановление базы demo в туже базу demo с заменой данных
C:\Program Files (x86)\pgAdmin 4\v4\runtime\pg_restore.exe --host "localhost" --port "5432" --username "postgres" --no-password --role "postgres" --dbname "demo" --clean --verbose "E:\\windows\\1\\demo\\DEMO20~1.BAC"

Тоже с использование pgAdmin4

Скрипт для автоматизации резервного копирования pg_dump_demo.bat
(создать для каждой базы добавить в планировщик)

REM СОЗДАНИЕ РЕЗЕРВНОЙ КОПИИ БАЗЫ ДАННЫХ demo POSTGRESQL
REM в папке запуска скрипта в папке %PGDATABASE%
REM для базы отличной от demo  изменить PGDATABASE (следующая строка)
SET PGDATABASE=demo
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
REM SET PGBIN=C:\Program Files\PostgreSQL\11.5-19.1C\bin
SET PGBIN=F:\PostgreSQL\11.5-19.1C\bin
SET PGHOST=localhost
SET PGPORT=5432
SET PGUSER=postgres
SET PGPASSWORD=pass
REM Удаляем в папке PGDATABASE все файл старше DAY
SET DAY=5
REM Смена диска и переход в папку из которой запущен bat-файл
%~d0
CD %~dp0
REM Формирование имени файла резервной копии и файла-отчета
SET DATETIME=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%_%TIME:~0,2%-%TIME:~3,2%-%TIME:~6,2%
SET DUMPFILE=%PGDATABASE%%DATETIME%.backup
SET LOGFILE=%PGDATABASE%%DATETIME%.log
SET DUMPPATH="%PGDATABASE%\%DUMPFILE%"
SET LOGPATH="%PGDATABASE%\%LOGFILE%"
REM Создание резервной копии
IF NOT EXIST %PGDATABASE% MD %PGDATABASE%
REM Удаляем в папке PGDATABASE все файл старше DAY
forfiles /p %PGDATABASE% /m * /s /d -%DAY% /c "cmd /c del @path /q" >nul 2>&1
REM Если батник будет запускаться планировщиком при загрузке
REM для исключения повторных срабатываний в один день
FORFILES /p "%PGDATABASE%" /M *.backup /D 0 >nul 2>&1
if not errorlevel 1 exit
CALL "%PGBIN%\pg_dump.exe" --format=custom --verbose --file=%DUMPPATH% 2>%LOGPATH%
REM Анализ кода завершения
IF NOT %ERRORLEVEL%==0 GOTO Error
GOTO Successfull
REM В случае ошибки удаляется поврежденная резервная копия и делается соответствующая запись в журнале
:Error
DEL %DUMPPATH%
MSG * "Ошибка при создании резервной копии базы данных. Смотрите backup.log."
ECHO %DATETIME% Ошибки при создании резервной копии базы данных %DUMPFILE%. Смотрите отчет %LOGFILE%. >> %PGDATABASE%.log
GOTO End
REM В случае удачного резервного копирования просто делается запись в журнал
:Successfull
ECHO %DATETIME% Успешное создание резервной копии %DUMPFILE% >> %PGDATABASE%.log
GOTO End
:End 

Вообще хорошо перед pg_dump
Остановить сервер 1С
"C:\Program Files\1cv8\8.3.13.1690\bin\ragent.exe" -stop
После pg_dump
"C:\Program Files\1cv8\8.3.13.1690\bin\ragent.exe" -start

PostgreSQL, версия 10.5-24.1C windows 10 postgresql.conf

Тест расчета зарплаты на 3000 сотрудников

Заполнить:                  285 с

Провести и закрыть:  215 с


Интересно запустить тест Гилева №1, до настройки  postgresql.conf

             Samsung SSD  850 PRO 256GB

c:\Program Files\PostgreSQL\10.5-24.1C\data\postgresql.conf
Добавить в конец:

#
max_connections = 1000
shared_buffers = 4GB
temp_buffers = 256MB
work_mem = 64MB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
wal_buffers = 16MB
min_wal_size = 2GB
max_wal_size = 4GB

default_statistics_target = 100
#effective_io_concurrency = 2
random_page_cost = 1.1
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 20s
bgwriter_delay = 20ms
bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400
synchronous_commit = off
checkpoint_completion_target = 0.9
#min_wal_size = 2GB
#max_wal_size = 4GB
ssl = off
fsync = on
commit_delay = 1000
commit_siblings = 5
row_security = off
max_files_per_process = 1000
standard_conforming_strings = off
escape_string_warning = off
max_locks_per_transaction = 256
#log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
#log_autovacuum_min_duration = 0
lc_messages='C'
#log_duration = on
#log_statement = all
log_destination = stderr
plantuner.fix_empty_table = 'on'
online_analyze.table_type = 'temporary'
online_analyze.verbose = 'off'
max_replication_slots = 10
max_wal_senders = 10
wal_level = replica  


Сохранить.

net stop  pgsql-10.5-24.1C-x64
C:\WINDOWS\SysWOW64\net.exe  stop pgsql-10.5-24.1C-x64
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" останавливается.
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" успешно остановлена.


net start  pgsql-10.5-24.1C-x64
C:\WINDOWS\SysWOW64\net.exe  start pgsql-10.5-24.1C-x64
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" запускается.
Служба "PostgreSQL Database Server 10.5-24.1C(x64)" успешно запущена.


Тест расчета зарплаты на 3000 сотрудников

Заполнить:                 240 с

Провести и закрыть: 198 с

Заполнить дает ускорение 15%


Интересно запустить тест Гилева № 1, после настройки  postgresql.conf

Как видим особо не влияет.




Посмотреть список баз:
"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -c "\l"

Посмотреть размер базы demo:

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -d demo -c "select pg_size_pretty( pg_database_size('demo')) postgres
Password for user postgres:
 postgres
----------
 2452 MB
(1 row)

Удаление базы demo

Вручную можно удалить так:

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -c "DROP DATABASE demo"
Password for user postgres:
DROP DATABASE


Создать базу demo лучше создать средствами 1с !!!:


Данные кластера
c:\Program Files\1cv8\srvinfo


cd "c:\Users\USR1CV8\AppData\Local\1C\1cv8"

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -c "SELECT name, setting, unit FROM pg_settings where category like '%Memory%';"

Посмотреть настройки
"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -c "SELECT name, setting, unit FROM pg_settings;"

Посмотреть конкретную настойку
"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -c "SELECT name, setting, unit FROM pg_settings where name like 'autovacuum_max_workers';"

Установка PostgreSQL, версия 10.5-24.1C windows 10

Настроить в биос на высшую производительность

Если процессор(ы) Xeon отключить HT. (на десктопных не надо)

.

 .

.

.

start → run → services.msc 

.

 .

 

 .

.

.

.

.

.

Изменить переменную среды PATH

Гилев рекомендует, если у Вас нет специфических задач  отключать этот протокол на сервере.

Просто снять галочку в сетевых интерфейсах недостаточно!

Чтобы отключить отдельные компоненты IPv6, выполните описанные далее действия.

1. Нажмите кнопку Пуск
   
   , введите regedit в поле Начать поиск, затем выберите regedit.exe в списке Программы.
2. В диалоговом окне Контроль учетных записей пользователей нажмите кнопку Продолжить.
3. Найдите и выберите следующий подраздел реестра:
   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters\
4. Дважды щелкните пункт DisabledComponents для изменения параметра DisabledComponents.Примечание. Если параметр DisabledComponents недоступен, его необходимо создать. Для этого выполните указанные ниже действия.
   1. В меню Правка выберите пункт Создать, а затем — Параметр DWORD (32 бита).
   2. Введите DisabledComponents и нажмите клавишу ВВОД.
   3. Дважды щелкните пункт DisabledComponents.
5. Введите любое из указанных ниже значений для настройки протокола IP версии 6, а затем нажмите кнопку ОК.
   1. Введите 0, чтобы включить все компоненты IP версии 6.Примечание. Значение «0» используется по умолчанию.
   2. Введите 0xffffffff, чтобы отключить все компоненты IP версии 6, кроме интерфейса замыкания на себя. При этом значении Windows Vista также будет использовать в политиках префиксов протокол IP версии 4 вместо IPv6.
   3. Введите 0x20, чтобы использовать в политиках префикса IP версии 4 вместо IP версии 6.
   4. Введите 0x10, чтобы отключить собственные интерфейсы IP версии 6.
   5. Введите 0x01, чтобы отключить все туннельные интерфейсы IP версии 6.
   6. Введите 0x11, чтобы отключить все интерфейсы IP версии 6, кроме интерфейса замыкания на себя.

В некоторых случаях, Допустимыйобъем памяти __ KB

Устанавливает предельный объем виртуального адресного пространства, после постоянного превышения которого в течение определенного времени (см. следующий параметр) рабочий процесс или менеджер кластера будет автоматически перезапущен. Нулевое значение означает, что критический объем памяти не задан и автоматический перезапуск процессов не выполняется.

А так же:
Интервал превышения допустимого объема памяти __ секунд
Определяет интервал времени постоянного превышения предельного объема виртуального адресного пространства, занимаемого рабочим процессом или менеджером кластера (см. предыдущий параметр), после которого выполняется автоматический перезапуск процесса. Нулевое значение означает, что автоматически перезапуск рабочих процессов не выполняется.

Выключенные процессы останавливать через __ секунд

Интервал времени, по истечении которого выключенный рабочий процесс принудительно останавливается, независимо от наличия соединений. Работа всех соединений с этим процессом завершается аварийно. Значение свойства может быть изменено во время работы кластера. Нулевое значение означает, что принудительное завершение процесса не выполняется. Менеджер кластера при стойком превышении предельного объема виртуального адресного пространства всегда перезапускается без ожидания.

Настройка: таймаута рестарта процессов нет, лимит памяти 10Гб, время превышения - 10с, время завершения - 300с. "Завершать принудительно". Настройка сервера - дефолт (8/128).

Настройка технологического журнала для расследования проблем:

Создать файл:
"c:\Program Files\1cv8\conf\logcfg.xml"

<?xml version="1.0"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
  <dump create="true" location="c:\dumps" type="3" prntscrn="false"/>
  <log location="c:\logs" history="96">
    <event>
      <eq property="name" value="PROC"/>
    </event>
    <event>
      <eq property="name" value="CONN"/>
    </event>
    <event>
      <eq property="name" value="EXCP"/>
    </event>
    <event>
      <eq property="name" value="ATTN"/>
    </event>
    <event>
      <eq property="name" value="CLSTR"/>
    </event>
    <event>
      <eq property="name" value="CALL"/>
    </event>
    <event>
      <eq property="name" value="SCALL"/>
    </event>
    <event>
      <eq property="name" value="SDBL"/>
    </event>
    <event>
      <eq property="name" value="DBMSSQL"/>
    </event>
    <property name="all"/>
  </log>
</config>

Заменить "c:\dumps", "c:\logs" на свои:
 <dump create="true" location="c:\dumps" type="3" prntscrn="false"/>
  <log location="c:\logs" history="96">


Другой вариант:

<?xml version="1.0" encoding="UTF-8"?>
<config xmlns="http://v8.1c.ru/v8/tech-log">
        <log location="c:\log" history="96">
                <event>
                        <ne property="name" value="DB2" />
                        <ne property="name" value="DBMSSQL" />
                        <ne property="name" value="DBMSSQLCONN" />
                        <ne property="name" value="DBORACLE" />
                        <ne property="name" value="DBPOSTGRS" />
                        <ne property="name" value="DBV8DBENG" />
                        <ne property="name" value="SDBL" />
                        <ne property="name" value="MEM" />
                        <ne property="name" value="TLOCK" />
                        <ne property="name" value="SRVC" />
                        <ne property="name" value="CALL" />
                        <ne property="name" value="CONN" />
                        <ne property="name" value="SCALL" />
                        <ne property="name" value="SCOM" />
                        <ne property="name" value="VRSREQUEST" />
                        <ne property="name" value="VRSRESPONSE" />
                </event>
                <property name="all" />
        </log>
</config>