walg_upgrade_and_retain_issue.sh

# simulates v11 to v12 migration and 'wal-g delete retain' choosing by name rather by time
# NB! don't forget to configure ~/.walg.json and ~/.aws/credentials
# NB! default Postgres binary paths assume Debian / Ubuntu

export PGHOST=localhost
export PGBINOLD=/usr/lib/postgresql/11/bin/
export PGBINNEW=/usr/lib/postgresql/12/bin/
export PGDATAOLD=pg11
export PGDATANEW=pg12
export PGPORT=6432

# abort on error
set -e

echo "purging old instance (if any)"
$PGBINOLD/pg_ctl -l logfile-old -D $PGDATAOLD stop || :
rm -rf $PGDATAOLD || :

echo "init old version instance"
$PGBINOLD/initdb $PGDATAOLD &> /dev/null
echo "setting PORT to $PGPORT and configuring wal-g as archive_command"
echo "port=$PGPORT" >> $PGDATAOLD/postgresql.conf
echo "archive_mode=on" >> $PGDATAOLD/postgresql.conf
echo "archive_command='wal-g wal-push %p'" >> $PGDATAOLD/postgresql.conf

echo "starting old instance"
$PGBINOLD/pg_ctl -l logfile-old -D $PGDATAOLD start

$PGBINOLD/pgbench -i postgres &>/dev/null

echo "pushing 1st backup (OLD)"
wal-g backup-push $PGDATAOLD

echo "doing some transactions and advance WAL so something is also archived"
$PGBINOLD/pgbench -T1 postgres &>/dev/null
$PGBINOLD/psql -d postgres -qXc "select pg_switch_wal()"
$PGBINOLD/pgbench -T1 postgres &>/dev/null

echo "pushing 2nd backup (OLD)"
wal-g backup-push $PGDATAOLD

echo "increasing timeline of OLD" # recovery.conf works for pre v12 only
echo "standby_mode=on" > $PGDATAOLD/recovery.conf
$PGBINOLD/pg_ctl -l logfile-old -D $PGDATAOLD restart
sleep 1
$PGBINOLD/pg_ctl -l logfile-old -D $PGDATAOLD promote

echo "pushing 3nd backup (OLD)"
wal-g backup-push $PGDATAOLD


echo "preparing to UPGRADE, init new ver. instance"
echo "purging new instance (if any)"
$PGBINNEW/pg_ctl -l logfile-new -D $PGDATANEW stop || :
rm -rf $PGDATANEW || :

$PGBINNEW/initdb $PGDATANEW &> /dev/null
echo "setting PORT to $PGPORT and configuring wal-g as archive_command"
echo "port=$PGPORT" >> $PGDATANEW/postgresql.conf
echo "archive_mode=on" >> $PGDATANEW/postgresql.conf
echo "archive_command='wal-g wal-push %p'" >> $PGDATANEW/postgresql.conf

echo "stopping old"
$PGBINOLD/pg_ctl -l logfile-old -D $PGDATAOLD stop

echo "doing pg_upgrade"
$PGBINNEW/pg_upgrade &> upgrade.log

echo "starting new instance"
$PGBINNEW/pg_ctl -l logfile-new -D $PGDATANEW start

echo "pushing 4th backup (NEW)"
wal-g backup-push $PGDATANEW

echo "doing some transactions and advance WAL so something is also archived"
$PGBINNEW/pgbench -T1 postgres &>/dev/null
$PGBINNEW/psql -d postgres -qXc "select pg_switch_wal()"
$PGBINNEW/pgbench -T1 postgres &>/dev/null

echo "pushing 5th backup (NEW)"
wal-g backup-push $PGDATANEW

echo "simulating some more normal work to push some WALs..."
$PGBINNEW/psql -d postgres -qXc "select pg_switch_wal()"
$PGBINNEW/pgbench -T1 postgres &>/dev/null
$PGBINNEW/psql -d postgres -qXc "select pg_switch_wal()"
$PGBINNEW/pgbench -T1 postgres &>/dev/null
$PGBINNEW/psql -d postgres -qXc "select pg_switch_wal()"
$PGBINNEW/pgbench -T1 postgres &>/dev/null

echo "wal-g backup-list (BEFORE delete)"
wal-g backup-list
# name                          last_modified        wal_segment_backup_start
# base_00000001000000000000000D 2020-01-22T16:54:54Z 00000001000000000000000D
# base_000000010000000000000010 2020-01-22T16:55:09Z 000000010000000000000010
# base_000000020000000000000013 2020-01-22T16:58:06Z 000000020000000000000013
# base_000000010000000000000022 2020-01-22T17:04:32Z 000000010000000000000022
# base_000000010000000000000025 2020-01-22T17:06:27Z 000000010000000000000025

echo "wal-g delete retain 2 --confirm &> delete.log"
wal-g delete retain 2 --confirm &> delete.log

echo "wal-g backup-list (AFTER delete)"
wal-g backup-list
# name                          last_modified        wal_segment_backup_start
# base_000000020000000000000013 2020-01-22T16:58:06Z 000000020000000000000013
# base_000000010000000000000025 2020-01-22T17:06:27Z 000000010000000000000025

echo "script end"