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@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@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 -c
config_file=/etc/postgresql/9.4/main/postgresql.conf
Justin
