Stress Testing
Configuration
Configure repository for stress testing
[global]
process-max=8
[global]
compress-type=lz4
[global]
compress-level=1
[global]
repo1-retention-full=1
[global]
log-timestamp=y
Create the pg-primary spool directory
mkdir -p -m 750 /var/spool/pgbackrest
chown postgres:postgres /var/spool/pgbackrest
Configure pg-primary for stress testing
[global]
process-max=8
[global]
log-timestamp=y
[global]
compress-type=lz4
[global]
compress-level=1
[global]
spool-path=/var/spool/pgbackrest
[global]
archive-async=y
[global:archive-push]
process-max=4
[global:archive-get]
process-max=4
Create the pg-standby spool directory
mkdir -p -m 750 /var/spool/pgbackrest
chown postgres:postgres /var/spool/pgbackrest
Configure pg-standby for stress testing
[global]
process-max=8
[global]
log-timestamp=y
[global]
compress-type=lz4
[global]
compress-level=1
[global]
spool-path=/var/spool/pgbackrest
[global]
archive-async=y
[global:archive-push]
process-max=4
[global:archive-get]
process-max=4
Create Tables and Load Data
Break Streaming Replication
Break streaming replication to force the standby to replicate from the archive during data load.
Break streaming replication by changing the replication password
psql -c "alter user replicator password 'bogus'"
Restart standby to break connection
pg_ctlcluster 16 demo restart
Create Tables
Create tables
bash -c 'for i in {1..1};
do psql -c "select create_test_table(${i?}, 1000, true)";
done'
Load Data
Load data
/usr/lib/postgresql/16/bin/pgbench -n -i -s 1
Fix Streaming Replication
Fix streaming replication so backups will work. Note that streaming replication will not start again until all WAL in the archive has been exhausted.
Fix streaming replication by changing the replication password
psql -c "alter user replicator password 'jw8s0F4'"
Testing
Full Backup
Full backup
pgbackrest --stanza=demo --type=full
--log-level-console=info --log-level-file=detail backup
Diff Backup with Delta and Block Incremental
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Diff backup
pgbackrest --stanza=demo --type=diff --delta --repo1-bundle
--repo1-block --log-level-console=info --log-level-file=detail backup
Incr Backup with Block Incremental
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Database updates
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Incr backup
pgbackrest --stanza=demo --type=incr --repo1-bundle
--repo1-block --log-level-console=info backup
Restore with Delta
Database updates so delta has something to restore
/usr/lib/postgresql/16/bin/pgbench -n -b simple-update -t 1
Stop
pg_ctlcluster 16 demo stop
Restore
pgbackrest --stanza=demo --type=standby --delta
--log-level-console=info --log-level-file=detail restore
Restore
Remove data
rm -rf /var/lib/postgresql/16/demo
Restore
pgbackrest --stanza=demo --type=standby
--log-level-console=info --log-level-file=detail restore
Start
pg_ctlcluster 16 demo start
Check cluster
psql -c "select count(*) from pg_class"