postgres database complete recovery

Поиск

Список

Период

Сортировка

От	Pavan Kumar
Тема	postgres database complete recovery
Дата	21 апреля 2019 г. 00:34:17
Msg-id	CA+M0sHExeO4N7urL2AD17YqxDpFPNoqLUX+1UNNBo=sD9+UyxQ@mail.gmail.com обсуждение исходный текст
Список	pgsql-general

Дерево обсуждения

Postgres complete recovery.

Hello experts,

i am working on postgres backup and recovery, with pg_basebackup utility.

for some reason i was not able to perform complete recovery, recovery is completed till the available wal log not not able to

perform complete recovery committed transaction on wal logs.

any one performed complete database recovery ? if yes, kindly share the procedure.

here is the test case i have done

wal log setting on postgres

=====>>> enable archive log mode for a postgres cluster

wal_level = replica # minimal, replica, or logical

archive_mode = off # enables archiving; off, on, or always

archive_command = 'cp %p /pgdata/archive/5444/%f' # command to use to archive a logfile segment

======>>>> verify archive log setting

[postgres@pgsql-lnx1 datadir_clone]$ psql -p 5444 postgres

postgres=# show archive_mode;

archive_mode

--------------

(1 row)

postgres=# show archive_command

postgres-# ;

archive_command

-------------------------------

cp %p /pgdata/archive/5444/%f

(1 row)

postgres=# show wal_level;

wal_level

-----------

replica

(1 row)

===== >>> force manual swtich

postgres=# select pg_switch_wal();

pg_switch_wal

---------------

0/13A7EBC8

(1 row)

postgres=# \! ls -l /pgdata/archive/5444/

total 32768

-rw-------. 1 postgres postgres 16777216 Mar 4 04:53 000000010000000000000013

-rw-------. 1 postgres postgres 16777216 Mar 4 04:54 000000010000000000000014

postgres=#

to perform hot backup, with low level api

1. login to postgres server with psql

2. enable start backup (select pg_start_backup('hotbackup_20190304');

3. use os command to copy data, tablespace, wal location to backup media

4. stop backup (select pg_stop_backup('hotbackup_20190304');)

pg_basebackup tool .

makes binary copy of database cluster files

it will automatically put database in backup mode and ends once backup is completed.

it uses stream protocol

below parameter has to be st for pg_basebackup

wal_level = replica # minimal, replica, or logical

archive_mode = off # enables archiving; off, on, or always

archive_command = 'cp %p /pgdata/archive/5444/%f' # command to use to archive a logfile segment

#max_wal_senders = 10 # max number of walsender processes

modify hba_file.conf

# Allow replication connections from localhost, by a user with the

# replication privilege.

local replication postgres md5

host replication postgres 127.0.0.1/32 md5

host replication postgres ::1/128 md5

#Allow remote replication connections from localhost, by a user with the

host replication <username> <ip adderess>/32 md5

restart database

pg_ctl -D $PWD stop

run pg_Basebackup

[postgres@pgsql-lnx1 online]$ pg_basebackup -h localhost -p 5444 -D /pgbkup/backup/fsbackup/online -X stream -v > /tmp/hotbackup.log 2>&1

Password:

note -X is used to copy latest wal segment (16mb) to self start datababse after restore and recovery

view the file /tmp/hotbackup.log file to see the backup information.

[postgres@pgsql-lnx1 online]$ cat /tmp/hotbackup.log

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

pg_basebackup: write-ahead log start point: 0/19000028 on timeline 1

pg_basebackup: starting background WAL receiver

pg_basebackup: write-ahead log end point: 0/19000130

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

[postgres@pgsql-lnx1 online]$

[postgres@pgsql-lnx1 online]$ pwd

/pgbkup/backup/fsbackup/online

[postgres@pgsql-lnx1 online]$ cat backup_label

START WAL LOCATION: 0/19000028 (file 000000010000000000000019)

CHECKPOINT LOCATION: 0/19000060

BACKUP METHOD: streamed

BACKUP FROM: master

START TIME: 2019-04-04 05:44:46 CDT

LABEL: pg_basebackup base backup

[postgres@pgsql-lnx1 online]$

====== >>>>>perform recovery:

create table test and insert few rows

ltuser=> create table test (eno int);

CREATE TABLE

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> select count(*) from test;

count

-------

10000

(1 row)

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> select count(*) from test;

count

-------

20000

(1 row)

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> commit;

WARNING: there is no transaction in progress

COMMIT

ltuser=> select count(*) from test;

count

-------

40000

(1 row)

ltuser=> \! ls -lart /pgdata/archive/5444/

total 114700

drwxrwxr-x. 4 postgres postgres 30 Mar 4 04:41 ..

-rw-------. 1 postgres postgres 16777216 Mar 4 04:53 000000010000000000000013

-rw-------. 1 postgres postgres 16777216 Mar 4 04:54 000000010000000000000014

-rw-------. 1 postgres postgres 16777216 Mar 4 05:39 000000010000000000000015

-rw-------. 1 postgres postgres 16777216 Mar 4 05:43 000000010000000000000016

-rw-------. 1 postgres postgres 305 Mar 4 05:43 000000010000000000000017.00000028.backup

-rw-------. 1 postgres postgres 16777216 Mar 4 05:43 000000010000000000000017

-rw-------. 1 postgres postgres 16777216 Mar 4 05:44 000000010000000000000018

-rw-------. 1 postgres postgres 305 Mar 4 05:44 000000010000000000000019.00000028.backup

drwxrwxr-x. 2 postgres postgres 4096 Mar 4 05:44 .

-rw-------. 1 postgres postgres 16777216 Mar 4 05:44 000000010000000000000019

ltuser=> \! ls -lart /pgdata/datadir_clone/pg_wal

total 213004

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 00000001000000000000001B

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 00000001000000000000001C

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 00000001000000000000001D

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 00000001000000000000001E

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 00000001000000000000001F

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 000000010000000000000020

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 000000010000000000000021

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 000000010000000000000022

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 000000010000000000000024

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 000000010000000000000023

-rw-------. 1 postgres postgres 16777216 Mar 4 03:08 000000010000000000000025

drwx------. 19 postgres postgres 4096 Mar 4 05:39 ..

-rw-------. 1 postgres postgres 16777216 Mar 4 05:44 000000010000000000000019

-rw-------. 1 postgres postgres 305 Mar 4 05:44 000000010000000000000019.00000028.backup

drwx------. 3 postgres postgres 4096 Mar 4 05:59 .

drwx------. 2 postgres postgres 96 Mar 4 05:59 archive_status

-rw-------. 1 postgres postgres 16777216 Mar 4 06:24 00000001000000000000001A

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> insert into test values (generate_series(1,10000));

INSERT 0 10000

ltuser=> select count(*) from test;

count

-------

70000