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_activity ORDER 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(at)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 -c config_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(at)pryzbyj:~$ sudo kill -SEGV 12997
pryzbyj(at)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 -c config_file=/etc/postgresql/9.4/main/postgresql.conf

Justin