On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur <samthakur74@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
