# Создание и восстановление резервной копии с помощью pg_probackup
-- указываем семейство image-family=ubuntu-2004-lts
gcloud beta compute --project=celtic-house-266612 instances create postgres --zone=us-central1-a --machine-type=e2-medium --subnet=default --network-tier=PREMIUM --maintenance-policy=MIGRATE --service-account=933982307116-compute@developer.gserviceaccount.com --scopes=https://www.googleapis.com/auth/devstorage.read_only,https://www.googleapis.com/auth/logging.write,https://www.googleapis.com/auth/monitoring.write,https://www.googleapis.com/auth/servicecontrol,https://www.googleapis.com/auth/service.management.readonly,https://www.googleapis.com/auth/trace.append --image-family=ubuntu-2004-lts --image-project=ubuntu-os-cloud --boot-disk-size=10GB --boot-disk-type=pd-ssd --boot-disk-device-name=postgres --no-shielded-secure-boot --shielded-vtpm --shielded-integrity-monitoring --reservation-affinity=any

-- подключимся
gcloud compute ssh postgres

-- 15 постгреc
sudo apt update && sudo DEBIAN_FRONTEND=noninteractive apt upgrade -y -q && sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - && sudo apt-get update && sudo DEBIAN_FRONTEND=noninteractive apt -y install postgresql-15

pg_lsclusters

-- поставим pg_probackup
sudo sh -c 'echo "deb [arch=amd64] https://repo.postgrespro.ru/pg_probackup/deb/ $(lsb_release -cs) main-$(lsb_release -cs)" > /etc/apt/sources.list.d/pg_probackup.list' && sudo wget -O - https://repo.postgrespro.ru/pg_probackup/keys/GPG-KEY-PG_PROBACKUP | sudo apt-key add - && sudo apt-get update

-- если в списке нет вашей новейшей ОС можно:
/*
cd /etc/apt/sources.list.d
sudo nano pg_probackup.list
hirsute -> focal
main-hirsute -> focal
sudo apt update
*/

-- 15 поставим доп пакеты
sudo DEBIAN_FRONTEND=noninteractive apt install pg-probackup-15 pg-probackup-15-dbg postgresql-contrib postgresql-15-pg-checksums -y

-- Создаем каталог и устанавливаем переменную окружения BACKUP_PATH
sudo rm -rf /home/backups && sudo mkdir /home/backups && sudo chmod 777 /home/backups
sudo su postgres

echo "BACKUP_PATH=/home/backups/">>~/.bashrc
echo "export BACKUP_PATH">>~/.bashrc
cd $HOME
-- cd ~
. .bashrc

echo $BACKUP_PATH

-- Создадим роль в PostgreSQL для выполнения бекапов и дадим ему соответствующие права
-- права нужно будет выдавать в каждой БД!!!
-- https://postgrespro.github.io/pg_probackup/#pbk-install-and-setup
psql
create user backup;
ALTER ROLE backup NOSUPERUSER;
ALTER ROLE backup WITH REPLICATION;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;

-- в 15 версии другой скрипт %)
REVOKE ALL PRIVILEGES ON FUNCTION current_setting(text) FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION pg_switch_wal() FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION txid_current() FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION txid_current_snapshot() FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION txid_snapshot_xmax(txid_snapshot) FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION pg_create_restore_point(text) FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION pg_control_checkpoint() FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION pg_is_in_recovery() FROM backup;
REVOKE ALL PRIVILEGES ON FUNCTION pg_last_wal_replay_lsn() FROM backup;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM backup;
DROP USER backup;


-- в 15 версии
BEGIN;
CREATE ROLE backup WITH LOGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_backup_start(text, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_backup_stop(boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;
COMMIT;

ALTER ROLE backup WITH REPLICATION;

exit
-- Инициализируем наш бекап
pg_probackup-15 init


-- В нашей директории для бекапов появились следующие папки
cd $BACKUP_PATH
ls -l 

-- Инициализируем инстанс main
pg_probackup-15 add-instance --instance 'main' -D /var/lib/postgresql/15/main

-- Создадим новую базу данных
psql -c "CREATE DATABASE otus;"

-- Таблицу в этой базе данных и заполним ее тестовыми данными
psql otus -c "CREATE TABLE test(i int);"
psql otus -c "INSERT INTO test VALUES (10), (20), (30);"
psql otus -c "SELECT * FROM test;"

-- Создадим резервную копию.  Команда backup принимает три параметра:
    - `-b` - тип создания резервной копии. Для первого запуска нужно создать полную копию кластера PostgreSQL, поэтому команда `FULL`
    - параметр `-–stream` указывает на то, что нужно вместе с созданием резервной копии, параллельно передавать wal по слоту репликации. Запуск потоковой передачи wal.
    - параметр `--temp-slot` указывает на то, что потоковая передача wal-ов будет использовать временный слот репликации


-- посмотреть настройки
pg_probackup-15 show-config --instance main

pg_probackup-15 backup --instance 'main' -b FULL --stream --temp-slot

-- Видим, что наш бекап успешно создался. Однако есть два предупреждения

-- Первое указывает на то, что у нас не включена контрольная сумма
-- изменить можно только на выключенном кластере
pg_ctlcluster 15 main stop

/usr/lib/postgresql/15/bin/pg_checksums -D /var/lib/postgresql/15/main --enable

pg_ctlcluster 15 main start

pg_lsclusters

-- Второе - копии не под суперпользователем, 
-- а под созданным специально для этого пользователем
-- у нас уже есть созданный выше пользователь backup

pg_probackup-15 show

-- Давайте теперь в нашу таблицу test внесем дополнительные данные
psql otus -c "insert into test values (4);"

-- создадим !!инкрементальную!! копию под пользователем backup
pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -U backup 


-- че не так?









-- зададим пароль backup
psql -c "ALTER USER backup PASSWORD 'otus123';"

pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -h localhost -U backup -W


-- rm ~/.pgpass
echo "localhost:5432:otus:backup:otus123">>~/.pgpass
chmod 600 ~/.pgpass

pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -h localhost -U backup --pgdatabase=otus

-- nano /etc/postgresql/15/main/pg_hba.conf
-- host    otus   backup localhost scram-sha-256
-- host    otus   backup localhost md5
-- psql -c "select pg_reload_conf()"
-- psql -c 'select * from pg_hba_file_rules'

-- обязательно выдать в нужной бд права на запуск функций!!
psql -d otus
BEGIN;
GRANT USAGE ON SCHEMA pg_catalog TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_backup_start(text, boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_backup_stop(boolean) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;
COMMIT;


pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -h localhost -U backup --pgdatabase=otus
pg_probackup-15 show
pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -h localhost -U backup -d otus -p 5432




-- Резервные копии успешно создались, но запрашивается пароль..

-- https://postgrespro.ru/docs/postgrespro/15/app-pgprobackup
cat ~/.pgpass
psql -U backup -h localhost -p 5432
psql -d otus -U backup -h localhost -p 5432
pg_probackup-15 backup --instance 'main' -b FULL --stream -d otus -U backup -h localhost -p 5432

-- Note
-- If you are planning to rely on .pgpass for authentication when running backup in STREAM mode, 
-- then .pgpass must contain credentials for replication database, used to establish connection via replication protocol. 
-- Example: pghost:5432:replication:backup_user:my_strong_password 
nano ~/.pgpass

pg_probackup-15 backup --instance 'main' -b FULL --stream -d otus -U backup -h localhost -p 5432

-- а давайте проверим эту БД
psql
\l

-- и гдее??


-- если вдруг нужно сжатие
-- [--compress-algorithm=алгоритм_сжатия] [--compress-level=уровень_сжатия]
-- https://postgrespro.ru/docs/postgrespro/15/app-pgprobackup#PBK-OPTIONS

-- Если что-то пошло не так, то можно удалить привязку инстанса:
-- pg_probackup-15 del-instance --instance 'main'






pg_probackup-15 show


-- восстановим нашу копию ???
-- создадим новый кластер

pg_createcluster 15 main2
rm -rf /var/lib/postgresql/15/main2

pg_probackup-15 restore --instance 'main' -i 'RUV8QY' -D /var/lib/postgresql/15/main2 
-- если не задали переменную окружения
-- -B /home/backups


pg_ctlcluster 15 main2 start

-- если вдруг ничего не заработало
-- сделаем нормальный фулл с уже включенной чексуммой
/* pg_probackup-15 backup --instance 'main' -b FULL --stream --temp-slot -h localhost -U backup -d otus -p 5432
pg_probackup-15 show
rm -rf /var/lib/postgresql/15/main2
pg_probackup-15 restore --instance 'main' -i 'RC55KJ' -D /var/lib/postgresql/15/main2
pg_ctlcluster 15 main2 start
*/

-- Проверяем, что данные восстановились
psql otus -p 5433 -c 'select * from test;'

-- дифференциальные бэкапы
-- PTRACK
-- https://github.com/postgrespro/ptrack


-- политика хранения резервных копии
pg_probackup-15 backup --instance 'main' -b FULL --stream
pg_probackup-15 show

-- хранение одной полной копии базы данных
-- pg_probackup-15 set-config --instance  'main' --retention-redundancy=1

-- pg_probackup-15 delete --instance  'main' --delete-expired --delete-wal
-- pg_probackup-15 show

-- старше 7 дней и не больше 2 полных копий
-- pg_probackup-15 delete --instance 'main' --delete-expired --retention-window=7 --retention-redundancy=2


-- работа с целостностью данных
-- Останавливаем сервер PostgreSQL 
-- изменения в файл базы данных
-- запускаем сервер PostgreSQL
-- протестируем в конце - есть неиллюзорный шанс получить тыкву)
psql -d otus -c "select pg_relation_filepath('test')";

pg_ctlcluster 15 main stop
/usr/lib/postgresql/15/bin/pg_checksums -D /var/lib/postgresql/15/main --disable
nano /var/lib/postgresql/15/main/base/16385/16386

pg_ctlcluster 15 main start
psql -d otus -c "select * from test;"

-- дополнительное расширение amcheck
-- https://postgrespro.ru/docs/postgresql/14/amcheck
psql -d otus -c "CREATE EXTENSION amcheck"

-- Запускаем проверки целостности базы данных
pg_probackup-15 checkdb -D /var/lib/postgresql/15/main
psql -d otus -c "select * from test;"


-- настройки и скрипт бэкапа
-- посмотреть настройки
pg_probackup-15 show-config --instance main
-- https://habr.com/ru/company/barsgroup/blog/515592/


-- PITR
-- https://postgrespro.ru/docs/postgrespro/13/app-pgprobackup#PBK-PERFORMING-POINT-IN-TIME-PITR-RECOVERY

psql otus -c "insert into test values (5);"

-- сколько дней хранить wal
-- pg_probackup set-config --instance db1 --wal-depth=3

pg_probackup-15 show

pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -h localhost -U backup -d otus -p 5432

pg_ctlcluster 15 main2 stop
rm -rf /var/lib/postgresql/15/main2

pg_probackup-15 show
date
pg_probackup-15 restore --instance 'main' -i 'RC55TZ' -D /var/lib/postgresql/15/main2 -B /home/backups --recovery-target-time="2022-05-19 17:56:00+00"

-- настроим непрерывное архивирование
-- https://postgrespro.ru/docs/postgrespro/13/app-pgprobackup#PBK-SETTING-UP-CONTINUOUS-WAL-ARCHIVING
-- https://habr.com/ru/company/barsgroup/blog/516088/
psql -c 'alter system set archive_mode = on'

-- изза кавычек не смог написать в 1 строку(
-- вручную каталог(
psql 
alter system set archive_command = 'pg_probackup-15 archive-push -B /home/backups/ --instance=main --wal-file-path=%p --wal-file-name=%f --compress';
exit
pg_ctlcluster 15 main restart

psql -c 'show archive_mode'
psql -c 'show archive_command'

psql otus -c "insert into test values (9);"
pg_probackup-15 backup --instance 'main' -b FULL --stream --temp-slot -h localhost -U backup -d otus -p 5432

pg_probackup-15 show
psql otus -c "insert into test values (10);"
pg_probackup-15 backup --instance 'main' -b DELTA --stream --temp-slot -h localhost -U backup -d otus -p 5432

-- -i 'R2TH83' - !!! не указываем
pg_probackup-15 restore --instance 'main' -D /var/lib/postgresql/15/main2 --recovery-target-time="2022-05-19 18:02:03+00"
-- sudo -u postgres pg_probackup-15 restore --instance 'main' -D /var/lib/postgresql/15/main2 --recovery-target-lsn="0/15000168"
WARNING: Thread [1]: Could not read WAL record at 0/D000168
ERROR: Thread [1]: WAL segment "/home/backups/wal/main/00000001000000000000000D" is absent
WARNING: Recovery can be done up to time 2022-05-19 10:43:29+00, xid 766 and LSN 0/D000168
ERROR: Not enough WAL records to time 2022-05-19 10:49:03+00


pg_ctlcluster 15 main2 start

cat /var/log/postgresql/postgresql-14-main2.log


!!!! --restore-as-replica
pg_probackup-15 restore --instance 'main' -D /var/lib/postgresql/15/main2 -B /home/backups --recovery-target-time="2021-11-19 11:38:03+00" --restore-as-replica

-- Проверяем, что данные восстановились без последних изменений

psql otus -p 5433 -c 'select * from test;'

gcloud compute instances delete postgres