Re: Archiving and recovering pg_stat_tmp - Mailing list pgsql-general

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