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