vagrant init ubuntu/bionic64
Add forward port to connect to PostgreSQL inside VM. Add following lines in Vagrantfile
config.vm.network "forwarded_port", guest: 5432, host: 5432
Start VM
vagrant up
vagrant ssh
# add the repository
sudo tee /etc/apt/sources.list.d/pgdg.list <<END
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
END
# get the signing key and import it
wget https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo apt-key add ACCC4CF8.asc
# fetch the metadata from the new repo
sudo apt-get update
Install postgres, pgbackrest
sudo apt-get -y install postgresql-11 postgresql-contrib-11 pgbackrest
Verify Postgres already installed
sudo -iu postgres
psql --version psql
------
psql (PostgreSQL) 11.9 (Ubuntu 11.9-1.pgdg18.04+1)
Verify Pgbackrest already installed
pgbackrest
pgBackRest 2.30 - General help
Usage:
pgbackrest [options] [command]
Commands:
archive-get Get a WAL segment from the archive.
archive-push Push a WAL segment to the archive.
backup Backup a database cluster.
check Check the configuration.
expire Expire backups that exceed retention.
help Get help.
info Retrieve information about backups.
restore Restore a database cluster.
stanza-create Create the required stanza data.
stanza-delete Delete a stanza.
stanza-upgrade Upgrade a stanza.
start Allow pgBackRest processes to run.
stop Stop pgBackRest processes from running.
version Get version.
Use 'pgbackrest help [command]' for more information.
Logout postgres user
\q
We need configure pg_hba.conf to allow connect to PostgreSQL from outside VM. Add following line
host all all 10.0.2.2/32 trust
Restart postgresql
sudo service postgresql restart
Find postgresql.conf directory
sudo -iu postgres psql -U postgres -c 'SHOW config_file'
config_file
-----------------------------------------
/etc/postgresql/11/main/postgresql.conf
(1 row)
Update postgresql.conf
sudo vim /etc/postgresql/11/main/postgresql.conf
Add the below lines
listen_addresses = '*'
password_encryption='scram-sha-256'
archive_mode = on
Restart postgres
sudo service postgresql restart
Check postgresql configurations
sudo -iu postgres psql
SELECT name,setting,context,source FROM pg_settings WHERE NAME IN ('listen_addresses','archive_mode','password_encryption');
name | setting | context | source
---------------------+---------------+------------+--------------------
archive_mode | on | postmaster | configuration file
listen_addresses | * | postmaster | configuration file
password_encryption | scram-sha-256 | user | configuration file
(3 rows)
Logout postgres user
\q
sudo mkdir -p /var/lib/pgbackrest
sudo chmod 0750 /var/lib/pgbackrest
sudo chown -R postgres:postgres /var/lib/pgbackrest
Set permission
sudo chown -R postgres:postgres /var/log/pgbackrest
Create backup file of pgbackrest
sudo cp /etc/pgbackrest.conf /etc/pgbackrest.conf.backup
Generate password
openssl rand -base64 48
Update config
[global]
repo1-cipher-pass=nFYC2Vy6cqRejtXAw7OAc8jhl9ENLHOhE2L9QpqgKdHS85opIpr0O++BK9BLxC4B
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-diff=2
repo1-retention-full=2
log-level-console=info
log-level-file=debug
start-fast=y
[demo]
pg1-path=/var/lib/postgresql/11/main
Test
sudo -u postgres pgbackrest --stanza=demo stanza-create
2020-11-11 08:31:55.499 P00 INFO: stanza-create command begin 2.30: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/postgresql/11/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
2020-11-11 08:31:56.175 P00 INFO: stanza-create command end: completed successfully (677ms)
Update postgresql.conf
archive_command = 'pgbackrest --stanza=demo archive-push %p'
Reload postgres
sudo service postgresql restart
Check
sudo -iu postgres pgbackrest --stanza=demo check
2020-11-11 08:34:40.887 P00 INFO: check command begin 2.30: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/postgresql/11/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
2020-11-11 08:34:42.160 P00 INFO: WAL segment 000000010000000000000001 successfully archived to '/var/lib/pgbackrest/archive/demo/11-1/0000000100000000/000000010000000000000001-a2f02155f127e78c128cd15a4fd3628b48815956.gz'
2020-11-11 08:34:42.160 P00 INFO: check command end: completed successfully (1273ms)
Perform full backup
sudo -u postgres pgbackrest --stanza=demo --type=full backup
--------
2020-11-11 08:36:17.857 P00 INFO: new backup label = 20201111-083602F
2020-11-11 08:36:17.907 P00 INFO: backup command end: completed successfully (15806ms)
2020-11-11 08:36:17.908 P00 INFO: expire command begin 2.30: --log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=demo
2020-11-11 08:36:17.927 P00 INFO: expire command end: completed successfully (19ms)
Stop the PostgreSQL instance, and delete its data files, simulating a system administration disaster.
sudo service postgresql stop
sudo find /var/lib/postgresql/11/main -mindepth 1 -delete
Restore backup
sudo -iu postgres pgbackrest --stanza=demo --delta restore
Start postgres
sudo service postgresql start
Verify pgbackrest is working
sudo -u postgres pgbackrest --stanza=demo check
-----
2020-11-11 08:43:52.753 P00 INFO: check command begin 2.30: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/postgresql/11/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
2020-11-11 08:43:53.776 P00 INFO: WAL segment 000000020000000000000005 successfully archived to '/var/lib/pgbackrest/archive/demo/11-1/0000000200000000/000000020000000000000005-52ae3b097b6c1dc922930203e2af225e149bfd0b.gz'
2020-11-11 08:43:53.777 P00 INFO: check command end: completed successfully (1025ms)
Install cron
sudo apt-get -y install cron
Define cron
crontab -e
Select editor
Define 2 cron jobs:
- job 1 : Schedule a nightly full backup of database at midnight
- job 2 : Schedule a differential backup of database at the 6 hour marks except midnight
# m h dom mon dow command
0 0 * * * pgbackrest --type=full --stanza=demo backup
0 6,12,18 * * * pgbackrest --type=diff --stanza=demo backup
Diff backup
sudo -u postgres pgbackrest --stanza=demo --type=diff backup
Create a table
sudo -u postgres psql -c "begin; \
create table important_table (message text); \
insert into important_table values ('Important Data'); \
commit; \
select * from important_table;"
Get current timestamp of server
sudo -u postgres psql -Atc "select current_timestamp"
-----
2020-11-11 16:25:28.737294+00
Drop created table
sudo -u postgres psql -c "begin; \
drop table important_table; \
commit; \
select * from important_table;"
Stop postgresql
sudo service postgresql stop
Restore the demo cluster to 2020-11-11 16:25:28.737294+00
sudo -u postgres pgbackrest --stanza=demo --delta \
--type=time "--target=2020-11-11 16:25:28.737294+00" \
--target-action=promote restore
Start postgres
sudo service postgresql start
sudo -u postgres psql -c "select * from important_table"
Check in log file
sudo -u postgres cat /var/log/postgresql/postgresql-11-main.log
----
......
2020-11-11 16:34:01.122 UTC [7777] LOG: database system was interrupted; last known up at 2020-11-11 16:22:03 UTC
2020-11-11 16:34:01.142 UTC [7777] LOG: >>>> starting point-in-time recovery to 2020-11-11 16:25:28.737294+00 <<<<
2020-11-11 16:34:01.150 P00 INFO: archive-get command begin 2.30: [00000003.history, pg_wal/RECOVERYHISTORY] --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/postgresql/11/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=demo
.....
[TODO]