Re: Archiving and recovering pg_stat_tmp

On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur <samthakur74@gmail.com> wrote:
>
>> >"You need to have statistics recovered to the same state as they were
>> >when you took the FS level backup of your database after shutting down
>> >the server."
>
>     Correct
>>
>>
>> >"Shutting down" is important since that is when you would have
>> >statistics files ($PGDATA/pg_stat/*.stat) available to backup. They
>> >capture the statistics as of when the server was shut down.
>> Agreed
>
>
>    >What I did:
>>
>>
>> >1) Collect a few statistics in a result file from a currently running
>> >server. For example, the result of the query "select * from
>> >pg_stat_user_tables", into say stats1.txt
>>
>> >2) Clean shut down the server. Take a snapshot of the data directory,
>> >"cp -r $pgdata $pgbkp"
>>
>> >3) Start the server and run a few pgbench tests so that statistics
>> >change. Again collect stats, same as in (1) into say stats2.txt
>>
>> >4) Write $pgbkp/recovery.conf with appropriate restore_command and
>> >maybe recovery target (PITR), which I did not, though. Note that we
>> >have archiving enabled.
>>
>> >5) Start the server using -D $pgbkp (may be with port changed for the
>> >sake of testing).
>>
>> >6) After server started in (5) is done recovering and comes online,
>> >collect stats again into say stats3.txt
>>
>> >7) Compare stats3.txt with stats1.txt and stats2.txt.
>>
>> >8) I observed that stats3.txt == stats1.txt. That is stats after
>> >recovery are same as they were when the snapshot was taken.
>>
>> Thank you for all the effort! A question
>
>   When server was restarted in (5) which stats file was loaded stats1.txt or
> stats.2.txt?. I think it must have been stats1.txt as stats3.txt =
> stats1.txt. What happens if stats2.txt is loaded on (5) instead on
> stats1.txt? I am trying to figure out if the Server will reject stats file
> from a different timeline than the one its been rolled back to.

>I started the server in step (5) using the back up directory. And
>remember backup directory would contain stats as they are in
>stats1.txt. So, there wasn't a possibility of stats as they are in
>stats2.txt to be loaded.
Understood

>But, if you do PITR using the same directory (which I haven't), I
>think you would need to somehow replace the stats with the ones you
>want, may be from your backup of the same (that is, of
>pg_stat/*.stat), though I am not sure if that would be correct. I
>doubt if WAL replay (as in a consistent recovery mechanism :-) )
>accounts for the stats. I guess stats are not WAL logged (like changes
>to table data) since they are managed using temporary files in
>pg_stat_temp and hence may not be recoverable using WAL replay to a
>particular state using PITR. but I may be wrong.

>Thoughts?
I agree. Will try PITR with stats file from different timeline and confirm this

--
Amit Langote