Re: PITR

[Murthy Nunna <mnunna@xxxxxxxx>]

Could someone please comment on this? Thanks.

 

From: Murthy Nunna
Sent: Tuesday, February 25, 2014 11:33 PM
To: Murthy Nunna; Raghavendra
Cc: desmodemone; pgsql-admin@xxxxxxxxxxxxxx
Subject: RE: PITR

 

All,

 

My database is in continuous recovery but it is not a traditional standby setup…. I am manually copying WALs (several thousands) from the source server to the server I am recovering. This means recovery is being done while WALs are being copied. It could so happen that a particular WAL could be open for recovery while it is still being copied (and not closed). Is this ok? I am asking because, I am seeing lots of LOG messages “unexpected pageaddr” as below.

I can do this differently if this method is not supported. I don’t want to end up with a corrupted database.

 

Thanks in advance for your advice.

 

2014-02-25 23:24:27 CST []LOG:  unexpected pageaddr 15D1/6E000000 in log file 5587, segment 22, offset 0

cp: cannot stat `/data1/pg_archlogs/ifb_prd/00000003000015D300000016': No such file or directory

2014-02-25 23:24:31 CST []LOG:  restored log file "00000003000015D300000016" from archive

2014-02-25 23:24:31 CST []LOG:  restored log file "00000003000015D300000017" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D300000018" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D300000019" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D30000001A" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D30000001B" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D30000001C" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D30000001D" from archive

2014-02-25 23:24:32 CST []LOG:  restored log file "00000003000015D30000001E" from archive

2014-02-25 23:24:33 CST []LOG:  unexpected pageaddr 15D1/77000000 in log file 5587, segment 31, offset 0

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D30000001F" from archive

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D300000020" from archive

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D300000021" from archive

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D300000022" from archive

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D300000023" from archive

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D300000024" from archive

2014-02-25 23:24:36 CST []LOG:  restored log file "00000003000015D300000025" from archive

2014-02-25 23:24:37 CST []LOG:  unexpected pageaddr 15D1/7E000000 in log file 5587, segment 38, offset 0

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D300000026" from archive

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D300000027" from archive

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D300000028" from archive

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D300000029" from archive

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D30000002A" from archive

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D30000002B" from archive

2014-02-25 23:24:41 CST []LOG:  restored log file "00000003000015D30000002C" from archive

2014-02-25 23:24:42 CST []LOG:  restored log file "00000003000015D30000002D" from archive

2014-02-25 23:24:42 CST []LOG:  restored log file "00000003000015D30000002E" from archive

2014-02-25 23:24:42 CST []LOG:  unexpected pageaddr 15D1/26000000 in log file 5587, segment 47, offset 0

 

 

 

 

 

 

From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Murthy Nunna
Sent: Monday, February 24, 2014 12:14 PM
To: Raghavendra
Cc: desmodemone; pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: PITR

 

Hi Raghavendra,

 

I used standby_mode=on and it worked. I can put checkpoints (not database checkpoint J) in between and still be in recovery state. This is what I wanted.

 

Thanks for your help!

 

Murthy

 

 

From: Raghavendra [mailto:raghavendra.rao@xxxxxxxxxxxxxxxx]
Sent: Monday, February 24, 2014 6:31 AM
To: Murthy Nunna
Cc: desmodemone; pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: PITR

 

On Sun, Feb 23, 2014 at 8:50 PM, Murthy Nunna <mnunna@xxxxxxxx> wrote:

Raghavendra,

 

Thanks for testing and confirming the behavior of “pause” setting.

 

While I understand your explanation, I feel I am still missing something. IMHO, when I say pause using “pause” setting, no matter what, I expect the recovery to wait for manual intervention.

 

I very much agree with your point that it has to pause when you ask for it, however, as per design (some other might comment on this well) am guessing it will open the database if no wals are there though you intentionally hide them. 

 

You can use (HOT STANDBY) standby_mode=on which does the same thing, it just waits for the WAL files but it won't open the database until you pass the trigger file. In hot standby, it apply the existing wals fed and wait for coming wals and it won't come out of recovery.  This you can try with below link.

 

http://wiki.postgresql.org/wiki/Hot_Standby

 

I myself can come up with number of reasons for doing so… e.g I may be purposely “hiding” some WALs somewhere else, or maybe I have several thousands of WALs that I want to parallelize the process of applying some logs while I recall some from tapes.

 

Let me know what you think.

 

Agreed it might be possible of not having wals at the moment and waiting for them to copy, however, I prefer in that case to use hot_standby. Pause just works in case if it sees some pending file in Arch.. location.

 

My explanation might not reach to your expectation, but I am sure few other's here might share their inputs.

--Raghav