Re: How to get postmaster shut down time in postgres?
Hi Tom Lane, I'm glad that I got a reply from you. And yes you are exactly correct. I am looking for the time that shutdown begins. I fulfilled my requirement by adding a piece of code before the initiation of shutdown in the postgresql service file located in /etc/init.d folder of my ubuntu server. It works perfectly fine and the sample output is shown as below: reported |shutdown_time | startup_time -+--+-- 2018-06-05 12:15:00.63332 | /2018-06-05 12:15:00/ | 2018-06-05 12:09:57.625046+05:30 | | 2018-06-05 12:15:05.28899+05:30 Where the shutdown time in the first row is the latest shutdown time and second row startup time is the server start time. I hope this will suffice my requirement. If you have found anything wrong in this kindly correct me. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: How to get postmaster shut down time in postgres?
Hi Tom Lane, I'm glad that I got a reply from you. And yes you are exactly correct. I am looking for the time that shutdown begins. I fulfilled my requirement by adding a piece of code before the initiation of shutdown in the postgresql service file located in /etc/init.d folder of my ubuntu server. It works perfectly fine and the sample output is shown as below: reported |shutdown_time | startup_time -+--+-- 2018-06-05 12:15:00.63332 | /2018-06-05 12:15:00/ | 2018-06-05 12:09:57.625046+05:30 | | 2018-06-05 12:15:05.28899+05:30 In my view this work meets my requirement. Kindly suggest if it appropriate to do like this. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: How to get postmaster shut down time in postgres?
Hi Tom Lane, I'm glad that I got a reply from you. And yes you are exactly correct. I am looking for the time that shutdown begins. I fulfilled my requirement by adding a piece of code before the initiation of shutdown in the postgresql service file located in /etc/init.d folder of my ubuntu server. It works perfectly fine and the sample output is shown as below: reported |shutdown_time | startup_time -+--+-- 2018-06-05 12:15:00.63332 | /2018-06-05 12:15:00/ | 2018-06-05 12:09:57.625046+05:30 | | 2018-06-05 12:15:05.28899+05:30 In my view this work meets my requirement. Kindly suggest if it appropriate to do like this. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: How to get postmaster shut down time in postgres?
"David G. Johnston" writes: > On Mon, Jun 4, 2018 at 6:44 AM, pavan95 wrote: >> The main reason for my requirement is to find the time swing between server >> stop and start. > Not all server stops are logged/evented (i.e., crashes), though by > definition all successful starts are (or at least can be).​ Yeah, the server-crash case is the one that makes this requirement hard to solve internally to the server. In a normal ("smart") shutdown, there might be a case for letting something happen just before we begin the final shutdown sequence, but there's no such hook at present. Anyway, that still leaves a definitional question --- are you looking for the time that shutdown begins, or when it's done? By definition, the latter is too late to make a table entry. One thing you might consider is running pg_controldata just before you start the server, and noting its report of "pg_control last modified". That would either be the shutdown-done point in a non-crash case, or (probably) the last checkpoint time in a crash case. As others mentioned, checking for the time of the last message in the postmaster log would likely provide a more accurate result, but it's also a lot more complicated. regards, tom lane
Re: How to get postmaster shut down time in postgres?
On Mon, Jun 4, 2018 at 6:44 AM, pavan95 wrote: > The main reason for my requirement is to find the time swing between server > stop and start. > ​Not all server stops are logged/evented (i.e., crashes), though by definition all successful starts are (or at least can be).​ David J.
Re: How to get postmaster shut down time in postgres?
If i were you, i would parse the logfile as others already mentioned. It is easy and clean. Another option might be to modify the init script and add a timestamp to a file whenever it stops/starts your server. Or maybe you can add to the init file a script that fills in your table, but i m not sure it is best practice. Else, we use monitoring for that, but in most cases the timestamps will not be that accurate. regards, fabio pardi On 06/04/2018 03:17 PM, pavan95 wrote: > Hi all, > > Is there any way to get postmaster(postgres database) shutdown time? Please > help!! > > Thanks in Advance. > > > Regards, > Pavan > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html >
Re: How to get postmaster shut down time in postgres?
On 06/04/2018 08:44 AM, pavan95 wrote: Hi Adrian/Melvin, Thanks for your prompt replies. Yeah, I'm aware of that way. But my requirement is to get the server shutdown time whenever that event occurs and insert into a table dynamically!! Is it possible? You want to trap the shutdown action and write to an audit table just before it happens? -- Angular momentum makes the world go 'round.
Re: How to get postmaster shut down time in postgres?
Thank you so much for your replies.😀 On Mon, Jun 4, 2018, 7:25 PM Adrian Klaver wrote: > On 06/04/2018 06:44 AM, pavan95 wrote: > > Hi Adrian/Melvin, > > > > Thanks for your prompt replies. Yeah, I'm aware of that way. > > > > But my requirement is to get the server shutdown time whenever that event > > occurs and insert into a table dynamically!! Is it possible? > > A quick scan shows no built in function to do this. Looks like you will > need to parse the log file. > > > > > The main reason for my requirement is to find the time swing between > server > > stop and start. > > > > Thanks in advance. > > > > Regards, > > Pavan > > > > > > > > -- > > Sent from: > http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: How to get postmaster shut down time in postgres?
On 06/04/2018 06:44 AM, pavan95 wrote: Hi Adrian/Melvin, Thanks for your prompt replies. Yeah, I'm aware of that way. But my requirement is to get the server shutdown time whenever that event occurs and insert into a table dynamically!! Is it possible? A quick scan shows no built in function to do this. Looks like you will need to parse the log file. The main reason for my requirement is to find the time swing between server stop and start. Thanks in advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to get postmaster shut down time in postgres?
On Mon, Jun 4, 2018 at 9:44 AM, pavan95 wrote: > Hi Adrian/Melvin, > > Thanks for your prompt replies. Yeah, I'm aware of that way. > > But my requirement is to get the server shutdown time whenever that event > occurs and insert into a table dynamically!! Is it possible? > > The main reason for my requirement is to find the time swing between server > stop and start. > > Thanks in advance. > > Regards, > Pavan > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general- > f1843780.html > > >... my requirement is to get the server shutdown time whenever that event >occurs and insert into a table dynamically!! Is it possible? Since you have not stated your O/S, we cannot provide a specific solution. However, I can suggest that you use a command line script to grab the time from the log and then write to a file. EG. grep / find -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!
Re: How to get postmaster shut down time in postgres?
Hi Adrian/Melvin, Thanks for your prompt replies. Yeah, I'm aware of that way. But my requirement is to get the server shutdown time whenever that event occurs and insert into a table dynamically!! Is it possible? The main reason for my requirement is to find the time swing between server stop and start. Thanks in advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: How to get postmaster shut down time in postgres?
On Mon, Jun 4, 2018 at 9:17 AM, pavan95 wrote: > Hi all, > > Is there any way to get postmaster(postgres database) shutdown time? > Please > help!! > > Thanks in Advance. > > > Regards, > Pavan > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general- > f1843780.html > > Look in your postgresql log. You should see a line similar to this" 2018-06-04 09:21:27 EDT LOG: database system is shut down -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command – UXC* Employment by invitation only!
Re: How to get postmaster shut down time in postgres?
On 06/04/2018 06:17 AM, pavan95 wrote: Hi all, Is there any way to get postmaster(postgres database) shutdown time? Please help!! Assuming you have the Postgres logging turned on it will be in the log: The log when the server was shutdown: 2018-06-03 21:11:41.739 PDT-0LOG: database system is shut down The log when it was next started up: 2018-06-04 05:46:33.118 PDT-0LOG: database system was shut down at 2018-06-03 21:11:41 PDT Thanks in Advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Adrian Klaver adrian.kla...@aklaver.com
How to get postmaster shut down time in postgres?
Hi all, Is there any way to get postmaster(postgres database) shutdown time? Please help!! Thanks in Advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html