How to deal with PostgreSQL 11: Error [57P03]: FATAL: the database system is in recovery mode?

Question

I want a plpython3u language working in PostgreSQL 11 (I'm using Windows 10 machine).

I installed it successfully using the following command:

CREATE EXTENSION plpython3u;

I had to download python36.dll and save it in C:\Windows\System32 to successfully execute this because previously I was getting the following error.

I was unable to load "C:/Program Files/PostgreSQL/11/lib/plpython3.dll": The specified module could not be found.

For testing, I created the following function I got from the PostgreSQL Docs.

CREATE FUNCTION pymax (a integer, b integer)

  RETURNS integer

AS $$

  if a > b:

    return a

  return b

$$ LANGUAGE plpython3u;

It gave me the following error:

SQL Error [57P03]: FATAL: the database system is in recovery mode

This is what I got from the logs.

Current thread 0x000035b8 (most recent call first):

2020-01-16 20:10:17.136 CST [6980] LOG:  server process (PID 12532) was terminated by exception 0xC0000409

2020-01-16 20:10:17.136 CST [6980] DETAIL:  Failed process was running: CREATE FUNCTION public.pymax (a integer, b integer)

      RETURNS integer

    AS $$

      if a > b:

        return a

      return b

    $$ LANGUAGE plpython3u

2020-01-16 20:10:17.136 CST [6980] HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.

2020-01-16 20:10:17.136 CST [6980] LOG:  terminating any other active server processes

2020-01-16 20:10:17.229 CST [5636] WARNING:  terminating connection because of crash of another server process

2020-01-16 20:10:17.229 CST [5636] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.

2020-01-16 20:10:17.229 CST [5636] HINT:  In a moment you should be able to reconnect to the database and repeat your command.

2020-01-16 20:10:17.246 CST [6980] LOG:  all server processes terminated; reinitializing

2020-01-16 20:10:17.373 CST [4944] LOG:  database system was interrupted; last known up at 2020-01-16 20:09:02 CST

2020-01-16 20:10:17.392 CST [9880] FATAL:  the database system is in recovery mode

2020-01-16 20:10:17.509 CST [11412] FATAL:  the database system is in recovery mode

2020-01-16 20:10:17.623 CST [12472] FATAL:  the database system is in recovery mode

2020-01-16 20:10:17.730 CST [12480] FATAL:  the database system is in recovery mode

2020-01-16 20:10:17.843 CST [12432] FATAL:  the database system is in recovery mode

2020-01-16 20:10:17.951 CST [12492] FATAL:  the database system is in recovery mode

2020-01-16 20:10:18.060 CST [12744] FATAL:  the database system is in recovery mode

2020-01-16 20:10:18.175 CST [12160] FATAL:  the database system is in recovery mode

2020-01-16 20:10:18.298 CST [13084] FATAL:  the database system is in recovery mode

2020-01-16 20:10:18.828 CST [4944] LOG:  database system was not properly shut down; automatic recovery in progress

2020-01-16 20:10:18.835 CST [4944] LOG:  redo starts at 0/17FF400

2020-01-16 20:10:18.835 CST [4944] LOG:  redo done at 0/17FF438

2020-01-16 20:10:19.044 CST [6980] LOG:  database system is ready to accept connections

Fatal Python error: Py_Initialize: unable to load the file system codec

ModuleNotFoundError: No module named 'encodings'

Answer 1

I downloaded python36.dll and saved it in C:\Windows\System32. There is a lack of library versioning, a directory which has the executable is always on the shared library path and the ensuing sloppy habit of keeping random copies of the same shared library in various directories, Windows users got in the habit of downloading executable code from somewhere on the internet and running it.

This is not very safe. You must have got the wrong incarnation of the Python shared library, or you might be missing other important files, and as a consequence, PostgreSQL crashed when using it.

You need to remove DLL files from rogue downloads, then get a Python 3 installation package and install it. 

If you want to get more insights into SQL, check out this SQL Course from Intellipaat.