72 lines
2.1 KiB
Plaintext
72 lines
2.1 KiB
Plaintext
|
create table players (id int, about text, age int);
|
||
|
|
insert into players (id, about, age)
|
||
|
|
values (generate_series(1, 5000),
|
||
|
|
repeat('A cool player. ', 2) || 'My number is ' || trunc(random()*1000),
|
||
|
|
trunc(random()*10 * 2 + 10));
|
||
|
|
|
||
|
|
|
||
|
|
*******************************************
|
||
|
|
dbaquaris=> select count(*) from players;
|
||
|
|
5000
|
||
|
|
|
||
|
|
dbaquaris=> select current_timestamp;
|
||
|
|
2023-07-09 17:13:00.860309+02
|
||
|
|
*******************************************
|
||
|
|
|
||
|
|
|
||
|
|
insert into players (id, about, age)
|
||
|
|
values (generate_series(1, 100000),
|
||
|
|
repeat('A cool player. ', 2) || 'My number is ' || trunc(random()*1000),
|
||
|
|
trunc(random()*10 * 2 + 10));
|
||
|
|
|
||
|
|
*******************************************
|
||
|
|
dbaquaris=> select count(*) from players;
|
||
|
|
105000
|
||
|
|
|
||
|
|
dbaquaris=> select current_timestamp;
|
||
|
|
2023-07-09 17:36:08.502146+02
|
||
|
|
*******************************************
|
||
|
|
|
||
|
|
insert into players (id, about, age)
|
||
|
|
values (generate_series(1, 1000000),
|
||
|
|
repeat('A cool player. ', 2) || 'My number is ' || trunc(random()*1000),
|
||
|
|
trunc(random()*10 * 2 + 10));
|
||
|
|
|
||
|
|
|
||
|
|
*******************************************
|
||
|
|
dbaquaris=> select count(*) from players;
|
||
|
|
1105000
|
||
|
|
|
||
|
|
dbaquaris=> select current_timestamp;
|
||
|
|
2023-07-09 17:37:32.076851+02
|
||
|
|
*******************************************
|
||
|
|
|
||
|
|
# PITR to 2023-07-09 17:36:08
|
||
|
|
|
||
|
|
- stop PostgreSQL
|
||
|
|
- take one of the base backup before ther PITR and put it ion a temporary folder
|
||
|
|
|
||
|
|
mkdir /backup/postgresql/tmp
|
||
|
|
cd /backup/postgresql/tmp
|
||
|
|
gunzip -c /backup/postgresql/daily/2023-07-09__16_43_59_emptydb.tar.gz | tar -xvf -
|
||
|
|
|
||
|
|
- add in modify postgresql.conf
|
||
|
|
|
||
|
|
restore_command = 'cp /backup/postgresql/wal/%f %p'
|
||
|
|
recovery_target_time = '2023-07-09 17:36:08'
|
||
|
|
recovery_target_inclusive = true
|
||
|
|
|
||
|
|
- create recovery.signal file
|
||
|
|
touch recovery.signal
|
||
|
|
|
||
|
|
- start PostgreSQL server with the data in the temporary directory
|
||
|
|
pg_ctl start -D /backup/postgresql/tmp -l /tmp/reco.log
|
||
|
|
|
||
|
|
|
||
|
|
- check logfile; at the end of the recovery you will be asked to execute the following fonction in order to open the instance
|
||
|
|
select pg_wal_replay_resume();
|
||
|
|
|
||
|
|
- stop PostgreSQL server with the data in the temporary directory
|
||
|
|
pg_ctl stop -D /backup/postgresql/tmp -l /tmp/reco.log
|
||
|
|
|