Thread: Archiving and recovering pg_stat_tmp

On Thu, Jun 20, 2013 at 3:17 PM, Sameer Thakur <samthakur74@gmail.com> wrote:
> Hello,
> I was trying to figure out how does one recover server statistics to the
> same snapshot to which a database is restored after PITR.
> The steps i had in mind were
> 1.Set up WAL archiving
> 2.On server shutdown one would need to backup  pg_stat_tmp along with file
> system level back of database
> 3. On server crash setup configuration for recovery mode
> 4. Restart server, which replays WAL files and hen moves from recovery to
> normal mode
> What will be behavior be regarding pg_stat_tmp? Will it be deleted on
> startup? Is it possible to recover the same statistics as on last server
> shutdown? ICan the statistics recovered to the same PITR?

Documentation mentions following:

"When the server shuts down, a permanent copy of the statistics data
is stored in the global subdirectory, so that statistics can be
retained across server restarts."

http://www.postgresql.org/docs/9.2/static/monitoring-stats.html

Though, I wonder if it was recently changed to $PGDATA/pg_stat instead
of $PGDATA/global per patch submitted in discussion:

http://www.postgresql.org/message-id/1718942738eb65c8407fcd864883f4c8@fuzzy.cz

When I checked on my 9.4dev installation, I found $PGDATA/pg_stat and
there were per database .stat files.

--
Amit Langote

On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur <samthakur74@gmail.com> wrote:
>>Documentation mentions following:
> Thanks, but how does this relate to statistics recovery wrt PITR?

Upon clean server shutdown, you have the statistics files stored in
the pg_stat (previously global/) directory, which persists across
server restarts, which, might even be applicable to a PITR, as far as
I can understand. This would need some testing, though, to be sure
that it is the case.

--
Amit Langote

On Thu, Jun 20, 2013 at 8:32 PM, Amit Langote <amitlangote09@gmail.com> wrote:
> On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur <samthakur74@gmail.com> wrote:
>>>Documentation mentions following:
>> Thanks, but how does this relate to statistics recovery wrt PITR?
>
> Upon clean server shutdown, you have the statistics files stored in
> the pg_stat (previously global/) directory, which persists across
> server restarts, which, might even be applicable to a PITR, as far as
> I can understand. This would need some testing, though, to be sure
> that it is the case.

So as I said, I gave it a try. Correct me if I am wrong in
understanding your requirement:

"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."

"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.

Now, later  you want to restore to that state (one in the above
backup) with statistics as in that backed up snapshot. So, you write a
recovery.conf in that backup directory with restore_command which
reads from an archive which you have setup for PITR purpose. When you
start the server using backup directory, it enters archive recovery
mode and then comes online. Now you may be wondering what the state of
statistics may be. When I tried, I got the same statistics as in the
file system snapshot. That is, the archive recovery (which brings
forward the database state to a later point time) did not in any way
affect the statistics.

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.



--
Amit Langote

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.

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?


--
Amit Langote

On Tue, Jun 25, 2013 at 11:32 PM, Sameer Thakur <samthakur74@gmail.com> wrote:
>>>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
>
> I did the following
> 1. Enabled archiving
> 2. Executed SQL's 3. Shutdown 4. copied data directory (including the
> pgstats.stat under global) and archive directory under backup/1
> repeated 2,3,4 once more
> So now i have 2 backup directories.
> Now i created recovery.conf under backup/1/data. It has recovery command
> copying from backup1 archive.
> Next i renamed pagstat.stat under backup1/data/global  as pgstat_1.stat.
> Next i copied pgstat.stat from backup 2 into backup 1 (under the usual
> global directory).
> Pointed the server to backup1 data directory and started it. Surprisingly it
> works with no complaints about wrong pgstat.stats
>

Do you have only one archive set up? What do you mean when you mention
"backup1" archive?
What kind of complaints did you see when you did in past? I am not
aware of such errors; am I missing some?


--
Amit Langote

Hello,

I was trying to figure out how does one recover server statistics to the same snapshot to which a database is restored after PITR.

On Wed, Jun 26, 2013 at 2:10 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Wednesday, June 19, 2013, Sameer Thakur wrote:
>>
>> Hello,
>> I was trying to figure out how does one recover server statistics to the
>> same snapshot to which a database is restored after PITR.
>
>
> Do you think that it is important to do so?  Are you experiencing problems
> which you believe are due to missing/out of date activity statistics?  Or is
> this more for curiosity?
>

In fact, I too am curious if there exists a way to recover?
Assuming statistics referred to by *.stats files are more of
statistics about database operation like blocks read, blocks hit, etc.
and NOT the statistics about data like relpages, reltuples, histogram,
null fractions, etc. (which may be of interest to planner), they are
not tracked by WAL logging, right?

As far as I can tell, using a periodic stats reporting tool (like
pg_statsinfo) would be the only way to preserve such historic
(snapshots-like) information about those statistics. Server itself
doesn't have any mechanism to preserve any historical information like
those stats, am I right in saying that?


--
Amit Langote