-- postgres15
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

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 tree
sudo pg_lsclusters

-- wal-g v1.1.1 - 2022
-- wal-g v2.0.1 
-- https://github.com/wal-g/wal-g
-- install

-- https://github.com/wal-g/wal-g/releases
-- !!! wget https://github.com/wal-g/wal-g/releases/download/v2.0.1/wal-g-fdb-ubuntu-20.04-amd64.tar.gz
-- sudo rm /usr/local/bin/wal-g
wget https://github.com/wal-g/wal-g/releases/download/v2.0.1/wal-g-pg-ubuntu-20.04-amd64.tar.gz && tar -zxvf wal-g-pg-ubuntu-20.04-amd64.tar.gz && sudo mv wal-g-pg-ubuntu-20.04-amd64 /usr/local/bin/wal-g

sudo ls -l /usr/local/bin/wal-g

sudo rm -rf /home/backups && sudo mkdir /home/backups && sudo chmod 777 /home/backups

-- Создаем файл конфигурации для wal-g
sudo su postgres
nano ~/.walg.json

-- https://github.com/wal-g/wal-g/blob/master/docs/PostgreSQL.md
-- https://github.com/wal-g/wal-g/blob/master/docs/STORAGES.md

{
    "WALG_FILE_PREFIX": "/home/backups",
    "WALG_COMPRESSION_METHOD": "brotli",
    "WALG_DELTA_MAX_STEPS": "5",
    "PGDATA": "/var/lib/postgresql/15/main",
    "PGHOST": "/var/run/postgresql/.s.PGSQL.5432"
}

-- опция для дебага
--     "WALG_LOG_LEVEL": "DEVEL"

mkdir /var/lib/postgresql/15/main/log

-- postgresql.conf
echo "wal_level=replica" >> /var/lib/postgresql/15/main/postgresql.auto.conf
echo "archive_mode=on" >> /var/lib/postgresql/15/main/postgresql.auto.conf
echo "archive_command='wal-g wal-push \"%p\" >> /var/lib/postgresql/15/main/log/archive_command.log 2>&1' " >> /var/lib/postgresql/15/main/postgresql.auto.conf 
echo "archive_timeout=60" >> /var/lib/postgresql/15/main/postgresql.auto.conf 
echo "restore_command='wal-g wal-fetch \"%f\" \"%p\" >> /var/lib/postgresql/15/main/log/restore_command.log 2>&1' " >> /var/lib/postgresql/15/main/postgresql.auto.conf

cat ~/15/main/postgresql.auto.conf

-- Перезапускаем кластер PostgreSQL
pg_ctlcluster 15 main stop
pg_ctlcluster 15 main start
cd /home/backups

-- Создадим новую базу данных
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;"

-- бэкап
wal-g backup-push /var/lib/postgresql/15/main 

cat /var/log/postgresql/postgresql-15-main.log
cat /var/lib/postgresql/15/main/log/archive_command.log



wal-g backup-list

psql otus -c "UPDATE test SET i = 3 WHERE i = 30"

-- make delta
wal-g backup-push /var/lib/postgresql/15/main

wal-g backup-list

cd /home/backups
tree

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

wal-g backup-fetch /var/lib/postgresql/15/main2 LATEST


-- сделаем файл для восстановления из архивов wal
touch "/var/lib/postgresql/15/main2/recovery.signal"

pg_ctlcluster 15 main2 start

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

ls -la /home/backups/wal_005


-- Настроим на Gstore
rm $HOME/.walg.json
nano ~/.walg.json
{
    "WALG_GS_PREFIX": "gs://walgg",
    "GOOGLE_APPLICATION_CREDENTIALS" : "/var/lib/postgresql/celtic-house-266612-65d95e64c26a.json",
    "WALG_COMPRESSION_METHOD": "brotli",
    "WALG_DELTA_MAX_STEPS": "5",
    "PGDATA": "/var/lib/postgresql/15/main",
    "PGHOST": "/var/run/postgresql/.s.PGSQL.5432"
}

gcloud compute instances list

scp /mnt/d/download/celtic-house-266612-65d95e64c26a.json aeugene@34.66.129.195:/home/aeugene/
cp /home/aeugene/celtic-house-266612-65d95e64c26a.json /var/lib/postgresql/celtic-house-266612-65d95e64c26a.json
chown postgres:postgres /var/lib/postgresql/celtic-house-266612-65d95e64c26a.json

wal-g backup-push /var/lib/postgresql/15/main

wal-g backup-list

-- восстановимся
pg_ctlcluster 15 main2 stop

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

wal-g backup-fetch /var/lib/postgresql/15/main2 LATEST

touch "/var/lib/postgresql/15/main2/recovery.signal"


pg_ctlcluster 15 main2 start


gcloud compute instances delete postgres