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

On Mon, Jun 4, 2018 at 9:17 AM, pavan95 <pavan.postgresdba@gmail.com> 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!

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 Mon, Jun 4, 2018 at 9:44 AM, pavan95 <pavan.postgresdba@gmail.com> 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!

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

Thank you so much for your replies.😀

On Mon, Jun 4, 2018, 7:25 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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 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).​

David J.

"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