Thread: 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

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.klaver@aklaver.com

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

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.klaver@aklaver.com

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.

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
> 

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Jun 4, 2018 at 6:44 AM, pavan95 <pavan.postgresdba@gmail.com> 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

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

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

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