1С и Linux: pg_basebackup

Показаны сообщения с ярлыком pg_basebackup. Показать все сообщения

суббота, 13 апреля 2019 г.

PostgreSQL 10.5-24 настройка WAL архивирования windows

Continuous WAL Archiving for Windows
Настройка непрерывного архивирования в PostgreSQL 9.6
Настройка непрерывной архивации

Временная папка для backup
c:\backup

Архивы будем складывать:
d:\PostgreSQLBackup\

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

max_replication_slots = 10
max_wal_senders = 10
wal_level = replica
archive_mode = on
#archive_command ='test ! -f /wal/%f && cp %p /wal/%f'
archive_command = 'copy "%p" "d:\\PostgreSQLBackup\\%f"'

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

Обслуживание базы
"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -d demo -U postgres -c "VACUUM FULL FREEZE VERBOSE ANALYZE;"

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\psql" -U postgres -c "VACUUM FULL FREEZE VERBOSE ANALYZE;"

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

pg_bbackup.bat

REM СОЗДАНИЕ РЕЗЕРВНОЙ КОПИИ КЛАСТЕРА POSTGRESQL
REM Запускаем из папки в которую складываем архивы можно в корне
REM Архивы помещаются в папку Backup в папке из которой запускаем скрипт
CLS
ECHO OFF
CHCP 1251
REM Установка переменных окружения
SET PGBIN=c:\Program Files\PostgreSQL\10.5-24.1C\bin
SET PGBACKUP="c:\backup"
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 В случае удачного резервного копирования удаляются вчерашние архивные записи
REM forfiles /P "d:\PostgreSQLBackup" /D -1 /C "cmd /c del @path /q"
GOTO End
:End

Сохранить.

Целесообразно настроить и расскоментировать в скрипте:
forfiles /P "d:\PostgreSQLBackup" /D -1 /C "cmd /c del @path /q"

где "d:\PostgreSQLBackup", то хранилище wal, которое указано
archive_command = 'copy "%p" "d:\\PostgreSQLBackup\\%f"'

Предполагаем что резервную копию кластера, делаем не реже одного раза в день,
в случае удачного резервного копирования удаляются вчерашние архивные записи wal

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

Остановить службу 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

Если удалили папку
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

Для пользователей нужны права на запись в папку:

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

После разархивирования архива

Смотрим файл

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

START WAL LOCATION: C/82000028 (file 000000010000000C00000082)
CHECKPOINT LOCATION: C/82000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2019-04-13 13:20:01 MSK
LABEL: pg_basebackup base backup

Находим имя начального wal (file 000000010000000C00000082)

Нужно проверить что он есть в d:\PostgreSQLBackup\

После этого можно очистить (вместе с папками)

"c:\Program Files\PostgreSQL\10.5-24.1C\data\pg_wal\"

Тонкий момент, поскольку настроено архивирование, логично перед запуском скопировать

d:\PostgreSQLBackup\ в d:\PostgreSQLBackup.bac\ со всеми wal файлами

Содержимое d:\PostgreSQLBackup\ очистить

Создадим файл recovery.conf

"c:\Program Files\PostgreSQL\10.5-24.1C\data\recovery.conf\"

$ sudo -u postgres cp /usr/share/postgresql/9.6/recovery.conf.sample /var/lib/postgresql/9.6/main/recovery.conf
$ sudo -u postgres nano /var/lib/postgresql/9.6/main/recovery.conf

Добавить в конец файла:

restore_command = 'copy "d:\\PostgreSQLBackup.bac\\%f" "%p"'
recovery_end_command='echo finish'

Сохранить.

Запускаем PostgreSQL от администратора!

net start pgsql-10.5-24.1C-x64

Служба "PostgreSQL Database Server 10.5-24.1C(x64)" запускается.

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

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

"c:\Program Files\PostgreSQL\10.5-24.1C\bin\pg_archivecleanup" d:\PostgreSQLBackup 000000020000000C000000A8

четверг, 11 апреля 2019 г.

PostgreSQL 9.6 настройка WAL архивирования ubuntu

PostgreSQL Backup and Recovery Orchestration: WAL Archiving
Point in time recovery: PITR using pg_basebackup with PostgreSQL 9.2
Настройка непрерывной архивации

$ sudo mkdir -p /backup
$ sudo chown -R postgres:postgres /backup

$ sudo mkdir -p /wal
$ sudo chown -R postgres:postgres /wal

sudo nano /etc/postgresql/9.6/main/pg_hba.conf
Раскомментировать:
local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

Сохранить

$ sudo nano /etc/postgresql/9.6/main/postgresql.conf
Добавить:

max_replication_slots = 10
max_wal_senders = 10
wal_level = replica
archive_mode = on
archive_command ='test ! -f /wal/%f && cp %p /wal/%f'

Сохранить.

$ sudo systemctl restart postgresql

$ nano pg_basebackup.sh

#!/bin/sh
set -e
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
DATA_FTP=`date +"%Y-%m-%d_%H-%M-%S"` # без роатации
# Если на ftp мало места включить ротацию:
#DATA_FTP=`date +"%d"` # для ротации по номеру дня месяца
#DATA_FTP=`date +"%u"` # для ротации по номеру дня недели
DIR_TMP='/backup'
DIR_BACKUP='/home/user/backup'
DIR_YANDEX='/home/user/backup/yandex'
echo "--------------------------------------------------------------------" > /home/user/backup/backup.log
# Записываем информацию в лог с секундами
echo "$DATA Начало backup"
echo "$DATA Начало backup" >> /home/user/backup/backup.log

sudo /bin/su postgres -c "rm -rf $DIR_TMP/*" \
     && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D $DIR_TMP -Ft -z -Xf -P" \
     && sudo mv $DIR_TMP/base.tar.gz $DIR_BACKUP/base-$DATA.tar.gz

#echo $?

DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для backup"
     echo "$DATA Ошибка завершения для backup" >> /home/user/backup/backup.log
     exit
else
     echo "$DATA Успешное завершение backup"
     echo "$DATA Успешное завершение backup" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log

$ time sudo sh pg_basebackup.sh

restore:
$ sudo systemctl stop postgresql

Удалим кластер (сделать копию!!!):
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/*"

Поднимим базовую копию кластера:
$ sudo -u postgres tar -xvC /var/lib/postgresql/9.6/main -f /home/user/backup/base-2019-04-12_13-15-20.tar.gz

Просто поднять, без wal, но мы этого делать не будем
#$ sudo systemctl start postgresql

$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/pg_xlog/*"

Заодно просто логи
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/pg_log/*"

#Скопируем /wal файлы из архива в /wal.bac :
$ sudo mkdir -p /wal.bac
$ sudo chown -R postgres:postgres /wal.bac
$ sudo -u postgres cp /wal/* /wal.bac

#Скопируем /wal файлы из архива в /var/lib/postgresql/9.6/main/pg_xlog :
$ sudo /bin/su postgres -c "cp /wal.bac/* /var/lib/postgresql/9.6/main/pg_xlog"

# Удалим /wal/*
#$ sudo /bin/su postgres -c "rm -rf /wal/*"

Добавим файл recovery.conf

$ sudo -u postgres cp /usr/share/postgresql/9.6/recovery.conf.sample /var/lib/postgresql/9.6/main/recovery.conf
$ sudo -u postgres nano /var/lib/postgresql/9.6/main/recovery.conf

Добавить в конец файла:

restore_command = 'cp /wal.bac/%f "%p"'

Сохранить.

$ sudo systemctl start postgresql

Очистка логов транзакций postgresql

$ sudo systemctl stop postgresql
$ sudo -u postgres /usr/lib/postgresql/9.6/bin/pg_controldata /var/lib/postgresql/9.6/main/
.....
Положение последней конт. точки:      33/99000028
Положение предыдущей конт. точки:     33/98000098
Положение REDO последней конт. точки: 33/99000028
Файл WAL c REDO последней к. т.:      000000010000003300000099
.....
NextXID последней конт. точки:        0:2067748
NextOID последней конт. точки:        4303721
.....
Выполняем команду pg_resetxlog которой указываем NextOID и NextXID (команда выполняется из под пользователя postgres)

$ sudo -u postgres /usr/lib/postgresql/9.6/bin/pg_resetxlog -o 4303721 -x 2067748 -f /var/lib/postgresql/9.6/main/

Журнал транзакций сброшен

$ sudo systemctl start postgresql

среда, 20 марта 2019 г.

Резервное копирование кластера 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

среда, 20 февраля 2019 г.

Скрипт backup postgres pg_basebackup

Шифрование файлов backup с использованием gpg и открытого публичного ключа
Вариант с gpg:
Просомтр ключей
$ gpg --list-keys
gpg: создан каталог `/home/user/.gnupg'
gpg: создан новый файл настроек `/home/user/.gnupg/gpg.conf'
gpg: ВНИМАНИЕ: параметры в `/home/user/.gnupg/gpg.conf' при этом запуске еще не действуют
gpg: создана таблица ключей `/home/user/.gnupg/pubring.gpg'
gpg: /home/user/.gnupg/trustdb.gpg: создана таблица доверия

Импорт открытого ключа
$ gpg --import pubkey.txt
gpg: создана таблица ключей `/home/user/.gnupg/secring.gpg'
gpg: ключ 9BD0BCD3: импортирован открытый ключ "backup <mail@backup.com>"
gpg: Всего обработано: 1
gpg:               импортировано: 1 (RSA: 1)

Импорт закрытого ключа(нужен на момент восстановления)
user@u1604:~$ gpg --allow-secret-key-import --import privkey.txt
gpg: ключ 9BD0BCD3: импортирован закрытый ключ
gpg: ключ 9BD0BCD3: "backup <mail@backup.com>" не изменен
gpg: Всего обработано: 1
gpg:              неизмененных: 1
gpg:       считано закрытых ключей: 1
gpg:   импортировано закрытых ключей: 1

$ gpg --edit-key mail@backup.com
gpg (GnuPG) 1.4.20; Copyright (C) 2015 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

Закрытый ключ доступен.

pub 4096R/9BD0BCD3 создан: 2019-02-17 годен до: никогда       применимость: SC
                     доверие: неизвестной действительность: неизвестной
sub 4096R/4452E03E создан: 2019-02-17 годен до: никогда       применимость: E
[неизвестно] (1). backup <mail@backup.com>

gpg> trust Enter;
pub 4096R/9BD0BCD3 создан: 2019-02-17 годен до: никогда       применимость: SC
                     доверие: неизвестной действительность: неизвестной
sub 4096R/4452E03E создан: 2019-02-17 годен до: никогда       применимость: E
[неизвестно] (1). backup <mail@backup.com>

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

1 = Не знаю или не буду отвечать
2 = НЕ доверяю
3 = Доверяю ограниченно
4 = Полностью доверяю
5 = Абсолютно доверяю
m = вернуться в главное меню

Ваше решение? 5
Вы действительно хотите сделать этот ключ абсолютно доверенным? (y/N) y

pub 4096R/9BD0BCD3 создан: 2019-02-17 годен до: никогда       применимость: SC
                     доверие: абсолютное действительность: неизвестной
sub 4096R/4452E03E создан: 2019-02-17 годен до: никогда       применимость: E
[неизвестно] (1). backup <mail@backup.com>
Учтите, что показанная действительность ключа может быть неверной,
пока Вы не перезапустите программу.
gpg> q

$ sudo mkdir /backup
$ sudo chown -R postgres:postgres /backup

sudo nano /etc/postgresql/9.6/main/pg_hba.conf
Раскомментировать:
local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

Сохранить

$ sudo nano /etc/postgresql/9.6/main/postgresql.conf
Добавить:

max_replication_slots = 10
max_wal_senders = 10
wal_level = replica

Сохранить.

$ sudo systemctl restart postgresql

$ nano backup_ftp.sh

#!/bin/sh
set -e
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
DATA_FTP=`date +"%Y-%m-%d_%H-%M-%S"` # без роатации
# Если на ftp мало места включить ротацию:
#DATA_FTP=`date +"%d"` # для ротации по номеру дня месяца
#DATA_FTP=`date +"%u"` # для ротации по номеру дня недели
DIR_TMP='/backup'
DIR_BACKUP='/home/user/backup'
DIR_YANDEX='/home/user/backup/yandex'
echo "--------------------------------------------------------------------" > /home/user/backup/backup.log
# Записываем информацию в лог с секундами
echo "$DATA Начало backup"
echo "$DATA Начало backup" >> /home/user/backup/backup.log

sudo /bin/su postgres -c "rm -rf $DIR_TMP/*" \
     && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D $DIR_TMP -Ft -z -Xf -P" \
     && sudo mv $DIR_TMP/base.tar.gz $DIR_BACKUP/base-$DATA.tar.gz \
     && sudo /bin/su user -c \
     "gpg -e -r mail@backup.com --cipher-algo AES256 -o $DIR_YANDEX/base-$DATA_FTP.tar.gz.gpg \
     $DIR_BACKUP/base-$DATA.tar.gz" \
     && curl --upload-file $DIR_YANDEX/base-$DATA_FTP.tar.gz.gpg ftp://test:test@192.168.0.5 \
     && sudo /bin/su user -c "rm $DIR_YANDEX/base-$DATA_FTP.tar.gz.gpg"

#&& sudo /bin/su user -c "yandex-disk start"

#echo $?

DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для backup"
     echo "$DATA Ошибка завершения для backup" >> /home/user/backup/backup.log
     exit
else
     echo "$DATA Успешное завершение backup"
     echo "$DATA Успешное завершение backup" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log

#делаем vacuumdb
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
# Записываем информацию в лог с секундами
echo "$DATA Начало vacuum full"
echo "$DATA Начало vacuum full" >> /home/user/backup/backup.log
sudo /bin/su postgres -c "/usr/bin/vacuumdb -zfa" >> /home/user/backup/backup.log

DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для vacuum full"
     echo "$DATA Ошибка завершения для vacuum full" >> /home/user/backup/backup.log
     exit
else
     echo "$DATA Успешное завершение vacuum full"
     echo "$DATA Успешное завершение vacuum full" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log

# посылаем сообщение
sleep 2
#echo "backup" | mutt -s "backup" f2@vt.com -a $BACKUP_DIR/backup.log

Сохранить.

Запуск:
$ sudo sh backup_ftp.sh

Запуск по расписанию:

$ sudo -i
# crontab -e
Добавить в конец (сработает в 18:01):
# каждые день в 18.00

0 18 * * * sh /home/user/backup_ftp.sh
Сохранить.

Смотреть задания:
# crontab -l

Проверка расшифровки:

d:\us01>gpg --allow-secret-key-import --import privkey.txt
gpg: ключ 521D03509BD0BCD3: "backup <mail@backup.com>" не изменен
gpg: ключ 521D03509BD0BCD3: импортирован секретный ключ
gpg: Всего обработано: 1
gpg:                   неизмененных: 1
gpg:     прочитано секретных ключей: 1
gpg: импортировано секретных ключей: 1

d:\gpg>gpg -d -o gpg -d -r mail@backup.com -o base-7.tar.gz base-7.tar.gz.gpg

Короткий вариант:

$ nano pg_basebackup.sh

#!/bin/sh
set -e
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
DATA_FTP=`date +"%Y-%m-%d_%H-%M-%S"` # без роатации
# Если на ftp мало места включить ротацию:
#DATA_FTP=`date +"%d"` # для ротации по номеру дня месяца
#DATA_FTP=`date +"%u"` # для ротации по номеру дня недели
DIR_TMP='/backup'
DIR_BACKUP='/home/user/backup'
DIR_YANDEX='/home/user/backup/yandex'
echo "--------------------------------------------------------------------" > /home/user/backup/backup.log
# Записываем информацию в лог с секундами
echo "$DATA Начало backup"
echo "$DATA Начало backup" >> /home/user/backup/backup.log

sudo /bin/su postgres -c "rm -rf $DIR_TMP/*" \
     && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D $DIR_TMP -Ft -z -Xf -P" \
     && sudo mv $DIR_TMP/base.tar.gz $DIR_BACKUP/base-$DATA.tar.gz

#echo $?

DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для backup"
     echo "$DATA Ошибка завершения для backup" >> /home/user/backup/backup.log
     exit
else
     echo "$DATA Успешное завершение backup"
     echo "$DATA Успешное завершение backup" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log

$ time sudo sh pg_basebackup.sh

понедельник, 11 февраля 2019 г.

pg_basebackup

Backup Using pg_basebackup
Backup and Restore a PostgreSQL Cluster With Multiple Tablespaces Using pg_basebackup
Как не потерять данные в PostgreSQL
10 способов сделать резервную копию в PostgreSQL
#RuPostgres Live 9: Бэкапы — Андрей Бородин, Андрей Сальников, Григорий Смолкин

Использование:
pg_basebackup [ПАРАМЕТР]...

Параметры, управляющие выводом:
-D, --pgdata=КАТАЛОГ   сохранить базовую копию в указанный каталог
-F, --format=p|t       формат вывода (p (по умолчанию) - простой, t - tar)
-r, --max-rate=СКОРОСТЬ макс. скорость передачи данных в целевой каталог
                         (в КБ/с, либо добавьте суффикс "k" или "M")
-R, --write-recovery-conf
                         записать recovery.conf для репликации
-S, --slot=ИМЯ_СЛОТА   использовать заданный слот репликации
-T, --tablespace-mapping=СТАРЫЙ_КАТАЛОГ=НОВЫЙ_КАТАЛОГ
                         перенести табличное пространство из старого каталога
                         в новый
-x, --xlog             включить в копию требуемые файлы WAL (режим fetch)
-X, --xlog-method=fetch|stream
                         включить в копию требуемые файлы WAL, используя
                         заданный метод
      --xlogdir=КАТАЛОГ_XLOG
                         расположение каталога с журналом транзакций
-z, --gzip             сжать выходной tar
-Z, --compress=0-9     установить уровень сжатия выходного архива

Общие параметры:
-c, --checkpoint=fast|spread
                         режим быстрых или распределённых контрольных точек
-l, --label=МЕТКА      установить метку резервной копии
-P, --progress         показывать прогресс операции
-v, --verbose          выводить подробные сообщения
-V, --version          показать версию и выйти
-?, --help             показать эту справку и выйти

Параметры подключения:
-d, --dbname=СТРОКА    строка подключения
-h, --host=ИМЯ         имя сервера баз данных или каталог сокетов
-p, --port=ПОРТ        номер порта сервера БД
-s, --status-interval=ИНТЕРВАЛ
                         интервал между передаваемыми серверу
                         пакетами состояния (в секундах)
-U, --username=NAME    connect as specified database user
-U, --username=ИМЯ     имя пользователя баз данных
-w, --no-password      не запрашивать пароль
-W, --password         запрашивать пароль всегда (обычно не требуется)

Базовая резервная копия PostgreSQL 10

Настройка:
sudo nano /etc/postgresql/9.6/main/pg_hba.conf
Раскомментировать:
local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

$ sudo nano /etc/postgresql/9.6/main/postgresql.conf
Добавить:

max_replication_slots = 10
max_wal_senders = 10
wal_level = replica

Сохранить.

$ sudo

$ sudo su - postgres
$ psql
=#
SELECT name, setting

FROM pg_settings
WHERE name IN ('wal_level','max_wal_senders','max_replication_slots');

postgres=# SELECT name, setting
postgres-# FROM pg_settings
postgres-# WHERE name IN ('wal_level','max_wal_senders','max_replication_slots');
         name          | setting
-----------------------+---------
max_replication_slots | 10
max_wal_senders       | 10
wal_level             | replica
(3 строки)

$ sudo systemctl restart postgresql

разовый backup:
$ sudo mkdir /backup
$ sudo chown -R postgres:postgres /backup
$ sudo -u postgres /usr/bin/pg_basebackup -D /backup -Ft -z -Xf -P
ниже тоже самое несколько другая запись
#$ sudo -u postgres /usr/bin/pg_basebackup -x --format=tar -z -D /backup -P
2667320/2667320 КБ (100%), табличное пространство 1/1

На другом компьютере restore:
$ sudo systemctl stop postgresql
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/*"
$ sudo -u postgres tar -xvC /var/lib/postgresql/9.6/main -f /home/user/base.tar.gz
$ sudo systemctl start postgresql

Для скрипта:

$ sudo /bin/su postgres -c "rm -rf /backup/*" && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -x --format=tar -z -D /backup -P" && sudo mv /backup/base.tar.gz /home/user/backup/base-$(date +%Y-%m-%d_%H-%M-%S).tar.gz

или

$ sudo /bin/su postgres -c "rm -rf /backup/*" && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D /backup -Ft -z -Xf -P" && sudo mv /backup/base.tar.gz /home/user/backup/base-$(date +%Y-%m-%d_%H-%M-%S).tar.gz

$ nano backup.sh

Вставить текст ниже:

#!/bin/sh
set -e
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
#echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log
# Записываем информацию в лог с секундами
echo "$DATA Start backup"
echo "$DATA Start backup" >> /home/user/backup/backup.log

sudo /bin/su postgres -c "rm -rf /backup/*" && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D /backup -Ft -z -Xf -P" && sudo mv /backup/base.tar.gz /home/user/backup/base-$DATA.tar.gz && sudo /bin/su postgres -c "/usr/bin/vacuumdb -zfa" >> /home/user/backup/backup.log

#echo $?
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для backup"
     echo "$DATA Ошибка завершения для backup" >> /home/user/backup/backup.log
else
     echo "$DATA Успешное завершение backup"
     echo "$DATA Успешное завершение backup" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log

Сохранить.

Однократный запуск:$ sudo sh backup.sh

Запуск по расписанию:

$ sudo -i
# crontab -e
Добавить в конец (сработает в 17:00):
# каждые день в 17.00

0 17 * * * sh /home/user/backup.sh
Сохранить.

Смотреть задания:
# crontab -l

На другом компьютере restore:
$ sudo systemctl stop postgresql
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/main/*"
$ sudo -u postgres tar -xvC /var/lib/postgresql/9.6/main -f /home/user/base-2019-02-12_17-09-35.tar.gz
$ sudo systemctl start postgresql

Если понадобится на одном компьютере развернуть 2 экземпляр postgresql:

$ sudo pg_createcluster --locale ru_RU.UTF-8 9.6 beta


Creating new cluster 9.6/beta ...
  config /etc/postgresql/9.6/beta
  data   /var/lib/postgresql/9.6/beta
  locale ru_RU.UTF-8
  socket /var/run/postgresql
  port   5433
Warning:
 systemd does not know about the new cluster yet. Operations like 
"service postgresql start" will not handle it. To fix, run:
  sudo systemctl daemon-reload

$ sudo systemctl daemon-reload
Для запуска нужен restart:
$ sudo systemctl restart postgresql
$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.6 beta 5433 down postgres /var/lib/postgresql/9.6/beta pg_log/postgresql-%a.log
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main pg_log/postgresql-%a.log

$ ss -tunpl | grep 5432
tcp    LISTEN     0      128       *:5432                  *:*
tcp    LISTEN     0      128      :::5432                 :::*

$ ss -tunpl | grep 5433
tcp    LISTEN     0      128       *:5433                  *:*
tcp    LISTEN     0      128      :::5433                 :::*

$ ps aux | grep postgres
postgres 4252 0.0 0.0 315616 27704 ? S 18:18 0:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib postgresql/9.6/beta -c config_file=/etc/postgresql/9.6/beta/postgresql.conf
postgres 4253 0.0 0.5 4597816 182016 ? S 18:18 0:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
........

В созданный кластер сделаем restore:
$ sudo systemctl stop postgresql
$ sudo /bin/su postgres -c "rm -rf /var/lib/postgresql/9.6/beta/*"
$ sudo -u postgres tar -xvC /var/lib/postgresql/9.6/beta -f /home/user/base-2019-02-12_17-09-35.tar.gz
$ sudo systemctl start postgresql

Имя базы рекомендую указать с указанием кластера:

Удаление кластера.
После работы с дополнительным кластером можно его удалить:

$ sudo pg_dropcluster --stop 9.6 beta
$ sudo systemctl daemon-reload

Короткий вариант:

$ sudo mkdir /backup
$ sudo chown -R postgres:postgres /backup

sudo nano /etc/postgresql/9.6/main/pg_hba.conf
Раскомментировать:
local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

Сохранить

$ sudo nano /etc/postgresql/9.6/main/postgresql.conf
Добавить:

max_replication_slots = 10
max_wal_senders = 10
wal_level = replica

Сохранить.

$ sudo systemctl restart postgresql

$ nano pg_basebackup.sh

#!/bin/sh
set -e
DATA=`date +"%Y-%m-%d_%H-%M-%S"`
DATA_FTP=`date +"%Y-%m-%d_%H-%M-%S"` # без роатации
# Если на ftp мало места включить ротацию:
#DATA_FTP=`date +"%d"` # для ротации по номеру дня месяца
#DATA_FTP=`date +"%u"` # для ротации по номеру дня недели
DIR_TMP='/backup'
DIR_BACKUP='/home/user/backup'
DIR_YANDEX='/home/user/backup/yandex'
echo "--------------------------------------------------------------------" > /home/user/backup/backup.log
# Записываем информацию в лог с секундами
echo "$DATA Начало backup"
echo "$DATA Начало backup" >> /home/user/backup/backup.log

sudo /bin/su postgres -c "rm -rf $DIR_TMP/*" \
     && sudo /bin/su postgres -c "/usr/bin/pg_basebackup -D $DIR_TMP -Ft -z -Xf -P" \
     && sudo mv $DIR_TMP/base.tar.gz $DIR_BACKUP/base-$DATA.tar.gz

#echo $?

DATA=`date +"%Y-%m-%d_%H-%M-%S"`
if [ $? -ne 0 ]
then
     echo "$DATA Ошибка завершения для backup"
     echo "$DATA Ошибка завершения для backup" >> /home/user/backup/backup.log
     exit
else
     echo "$DATA Успешное завершение backup"
     echo "$DATA Успешное завершение backup" >> /home/user/backup/backup.log
fi
echo "--------------------------------------------------------------------" >> /home/user/backup/backup.log

$ time sudo sh pg_basebackup.sh

суббота, 13 апреля 2019 г.