Re: Function to track shmem reinit time

On Sat, Mar 03, 2018 at 01:00:52PM -0500, Peter Eisentraut wrote:
> On 2/28/18 07:11, Anastasia Lubennikova wrote:
> > Currently, if the 'restart_after_crash' option is on, postgres will just
> > restart.
> > And the only way to know that it happened is to regularly parse logfile
> > or monitor it, catching restart messages. This approach is really
> > inconvenient for
> > users, who have gigabytes of logs.
> 
> I find this premise a bit dubious.  Why have a log file if it's too big
> to find anything in it?  Server crashes aren't the only thing people are
> interested in.  So we'll need a function for "last $anything".

I think one can tell if it's crashed recently by comparing start time of parent
postmaster and its main children (I'm going to go put this in place for myself
now).

ts=# SELECT backend_type, backend_start, pg_postmaster_start_time(), backend_start-pg_postmaster_start_time() FROM
pg_stat_activityORDER BY backend_start LIMIT 1;
 
    backend_type     |         backend_start         |   pg_postmaster_start_time    |    ?column?     
---------------------+-------------------------------+-------------------------------+-----------------
 autovacuum launcher | 2018-03-02 00:21:11.604468-03 | 2018-03-02 00:12:46.757642-03 | 00:08:24.846826

pryzbyj@pryzbyj:~$ ps -O lstart -upostgres # on a separate server with 9.4 which doen't have backend_type, add in v10
  PID                  STARTED S TTY          TIME COMMAND
12982 Mon Feb 19 06:03:13 2018 S ?        00:00:33 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main
-cconfig_file=/etc/postgresql/9.4/main/postgresql.conf
 
12997 Mon Feb 19 06:03:14 2018 S ?        00:00:00 postgres: checkpointer process
                                                      
 
12998 Mon Feb 19 06:03:14 2018 S ?        00:00:08 postgres: writer process
                                                      
 
12999 Mon Feb 19 06:03:14 2018 S ?        00:00:08 postgres: wal writer process
                                                      
 
13000 Mon Feb 19 06:03:14 2018 S ?        00:00:19 postgres: autovacuum launcher process
                                                      
 
13001 Mon Feb 19 06:03:14 2018 S ?        00:00:45 postgres: stats collector process
                                                      
 

pryzbyj@pryzbyj:~$ sudo kill -SEGV 12997
pryzbyj@pryzbyj:~$ ps -O lstart -upostgres
  PID                  STARTED S TTY          TIME COMMAND
 8644 Sat Mar  3 12:05:34 2018 S ?        00:00:00 postgres: checkpointer process
                                                      
 
 8645 Sat Mar  3 12:05:34 2018 S ?        00:00:00 postgres: writer process
                                                      
 
 8646 Sat Mar  3 12:05:34 2018 S ?        00:00:00 postgres: wal writer process
                                                      
 
 8647 Sat Mar  3 12:05:34 2018 S ?        00:00:00 postgres: autovacuum launcher process
                                                      
 
 8648 Sat Mar  3 12:05:34 2018 S ?        00:00:00 postgres: stats collector process
                                                      
 
12982 Mon Feb 19 06:03:13 2018 S ?        00:00:33 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main
-cconfig_file=/etc/postgresql/9.4/main/postgresql.conf
 

Justin