Thread: BUG #16426: In active/hot standby mode, create database and drop template databases cause standby restart failed

The following bug has been logged on the website:

Bug reference:      16426
Logged by:          BoChen
Email address:      bchen90@163.com
PostgreSQL version: 11.5
Operating system:   SUSE Linux Enterprise Server 11 (x86_64)
Description:

In active/hot standby mod, doing likes the following：

1) using createdb to create a database 'test'
2) drop template0 and template1 database
3) using 'pt_ctl stop -mi' to stop the standby db
4) restart the standby db, start failed

Run logs show that the failed reason is redoing xlog of create database
while the template database has been droped. After reading postgresql source
code. I suspect that the problem is related to not updating pg_contral file
when the standby redoing Xlog of checkpoint. 

Is this a Bug?

standby db run log:

startup 36289 2020-05-09 19:55:18.666 CGPCONTEXT:  WAL redo at 0/5000CE8 for
Standby/RUNNING_XACTS: nextXid 708 latestCompletedXid 706 oldestRunningXid
707; 1 xacts: 707
startup 36289 2020-05-09 19:55:18.666 CGPDEBUG:  prune KnownAssignedXids to
707 procarray.c (3765 KnownAssignedXidsRemovePreceding)
startup 36289 2020-05-09 19:55:18.666 CGPCONTEXT:  WAL redo at 0/5000D88 for
Standby/RUNNING_XACTS: nextXid 708 latestCompletedXid 706 oldestRunningXid
707; 1 xacts: 707
startup 36289 2020-05-09 19:55:18.666 CGPDEBUG:  record known xact 707
latestObservedXid 707 procarray.c (3205 RecordKnownAssignedTransactionIds)
startup 36289 2020-05-09 19:55:18.666 CGPCONTEXT:  WAL redo at 0/5000DC0 for
Database/CREATE: copy dir 1/1663 to 16384/1663
startup 36289 2020-05-09 19:55:18.673 CGPFATAL:  could not open directory
"base/1": No such file or directory fd.c (2714 ReadDirExtended)
startup 36289 2020-05-09 19:55:18.673 CGPCONTEXT:  WAL redo at 0/5000DC0 for
Database/CREATE: copy dir 1/1663 to 16384/1663
startup 36289 2020-05-09 19:55:18.673 CGPDEBUG:  shmem_exit(1): 1
before_shmem_exit callbacks to make ipc.c (246 shmem_exit)
startup 36289 2020-05-09 19:55:18.673 CGPDEBUG:  shmem_exit(1): 5
on_shmem_exit callbacks to make ipc.c (279 shmem_exit)
startup 36289 2020-05-09 19:55:18.673 CGPDEBUG:  proc_exit(1): 2 callbacks
to make ipc.c (206 proc_exit_prepare)
startup 36289 2020-05-09 19:55:18.673 CGPDEBUG:  exit(1) ipc.c (159
proc_exit)
postmaster 36286 2020-05-09 19:55:18.777 CGPLOG:  timezone has changed,
checked for 1 times. pgtz_hw.c (93 NeedUpdateTz)
postmaster 36286 2020-05-09 19:55:18.777 CGPLOG:  update last timezone
change time to (1589011223), check times=1. pgtz_hw.c (197 IsSameTimezone)
postmaster 36286 2020-05-09 11:55:18.777 UTCLOG:  tz change, now
g_LogTimeMem is 1, g_LogTimeLocal is 1, flag is 0. pgtz_hw.c (276
AdjustLogTzChgTimesByPostmaster)
postmaster 36286 2020-05-09 11:55:18.777 UTCDEBUG:  reaping dead processes
postmaster.c (2907 reaper)
postmaster 36286 2020-05-09 11:55:18.777 UTCDEBUG:  server process (PID
36287) exited with exit code 0 postmaster.c (3697 LogChildExit)
postmaster 36286 2020-05-09 11:55:18.777 UTCLOG:  startup process (PID
36289) exited with exit code 1 postmaster.c (3697 LogChildExit)

PG Bug reporting form <noreply@postgresql.org> writes:
> In active/hot standby mod, doing likes the following：

> 1) using createdb to create a database 'test'
> 2) drop template0 and template1 database

Removing the template0 database is not a supported configuration.
There are emergency situations where it could make sense to do it,
perhaps, but only if you then recreate it (using some other database
as template); and even then you'd better be a guru who knows exactly
what he's doing, or you'll mess up the installation beyond repair.

It's certainly no surprise that dump/restore type activities will
fail with no template for CREATE DATABASE to source from.

> Is this a Bug?

No, it's Pilot Error.

            regards, tom lane