Re: [GENERAL] Archiving and recovering pg_stat_tmp
> > >> >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? > I am making specifications for a tool which captures query plan > statistics. I wanted its behavior to be the same as statistics collector > for everything except in the kind of statistics being collected. This is > the same premise used by pg_stat_statements. Hence the question wrt PITR > recovery. > In the case of my tool there could be as case that user wants to see the information like 5 slowest queries after archive recovery. For this i would need to build in logic to ensure the stats file read in during recovery is in sync with that timeline. It sounds complicated so was wondering how statistics collector handled it. Seems to me that the stats file is ignored in archive recovery mode,and deleted. This is consistent with its crash recovery behavior as well. regards Sameer
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Wed, Jun 26, 2013 at 2:10 PM, Jeff Janes 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Archiving and recovering pg_stat_tmp
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? Cheers, Jeff
Re: [GENERAL] Archiving and recovering pg_stat_tmp
> > > > 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? > I am sorry for being unclear. directories are /backup1/data /backup1/archive and /backup2/data /backup2/archive backup1 has data and archive directories copied after 1st shutdown backup2 has data and archive directories copied after 2nd shutdown Now i created recovery.conf in backup1/data and i replaced pgstats.stat from backup2 in backup1 (in data/global) Now i start Server pointing to backup1, where it uses recovery.conf to rollback to database snapshot after first shutdown I expected a failure because pgstats.stat do not reflect statistics of snapshot created from backup1 directory (1st shutdown) but of backup2 (2nd shutdown) regards Sameer
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Tue, Jun 25, 2013 at 11:32 PM, Sameer Thakur 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Archiving and recovering pg_stat_tmp
>>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 Thoughts? regards Sameer >> >> >
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Fri, Jun 21, 2013 at 11:35 AM, Amit Langote wrote: > On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur > 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 >
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Fri, Jun 21, 2013 at 2:44 PM, Sameer Thakur 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Archiving and recovering pg_stat_tmp
> >"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. regards Sameer > >
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Thu, Jun 20, 2013 at 8:32 PM, Amit Langote wrote: > On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Thu, Jun 20, 2013 at 6:05 PM, Sameer Thakur 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Archiving and recovering pg_stat_tmp
>Documentation mentions following: Thanks, but how does this relate to statistics recovery wrt PITR? regards Sameer
Re: [GENERAL] Archiving and recovering pg_stat_tmp
On Thu, Jun 20, 2013 at 3:17 PM, 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. > 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/1718942738eb65c8407fcd864883f...@fuzzy.cz When I checked on my 9.4dev installation, I found $PGDATA/pg_stat and there were per database .stat files. -- Amit Langote -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general