Re: Archiving and recovering pg_stat_tmp

On Fri, Jun 21, 2013 at 11:35 AM, Amit Langote <amitlangote09(at)gmail(dot)com>wrote:

> On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur <samthakur74(at)gmail(dot)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

regards
Sameer

>
> --
> Amit Langote
>