Postgres closes connection on CREATE EXTENSION IF NOT EXISTS postgis

1

I am trying to set up PostGIS on a new Postgres database, and I’ve noticed this unexpected behaviour,

postgres=# CREATE EXTENSION IF NOT EXISTS postgis
postgres-# ;
server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

And I have no explanation yet for this.

The database was a recently set up version of Postgres 14.4.

In the logs of the database I find this;

2022-11-01T14:03:37Z app[5683076c12d28e] ams [info]exporter | INFO[5197] Semantic Version Changed on "fdaa:0:3abb:a7b:23c8:91e7:c0c7:2:5433": 0.0.0 -> 14.4.0  source="postgres_exporter.go:1539"
2022-11-01T14:05:34Z app[5683076c12d28e] ams [info]keeper   | 2022-11-01 14:05:34.740 UTC [688] LOG:  server process (PID 30897) was terminated by signal 9: Killed
2022-11-01T14:05:34Z app[5683076c12d28e] ams [info]keeper   | 2022-11-01 14:05:34.740 UTC [688] DETAIL:  Failed process was running: CREATE EXTENSION IF NOT EXISTS postgis
2022-11-01T14:05:34Z app[5683076c12d28e] ams [info]keeper   | 	;
2022-11-01T14:05:34Z app[5683076c12d28e] ams [info]keeper   | 2022-11-01 14:05:34.742 UTC [688] LOG:  terminating any other active server processes
2022-11-01T14:05:34Z app[5683076c12d28e] ams [info]keeper   | 2022-11-01 14:05:34.765 UTC [688] LOG:  all server processes terminated; reinitializing
2022-11-01T14:05:34Z app[5683076c12d28e] ams [info]keeper   | 2022-11-01 14:05:34.784 UTC [30938] LOG:  database system was interrupted; last known up at 2022-11-01 14:03:00 UTC

But that does not explain why it would crash.

Any ideas?

2

Try running fly machine status 5683076c12d28e and see what the exit event says. This sounds like it might be an OOM to me.

3

Thanks kurt! That was the root cause! This is something maybe worth making it to the docs. Kept searching for way too long for this. Again, thanks!

4

Hello. I got the same error when enabling extension even if I scale pg memory to 1024 MB

2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.848 UTC [688] LOG:  server process (PID 3101) was terminated by signal 9: Killed
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.848 UTC [688] DETAIL:  Failed process was running: CREATE EXTENSION postgis;
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.849 UTC [688] LOG:  terminating any other active server processes
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.868 UTC [688] LOG:  all server processes terminated; reinitializing
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.881 UTC [3126] LOG:  database system was interrupted; last known up at 2022-11-14 21:39:41 UTC
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.885 UTC [3127] FATAL:  the database system is in recovery mode
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.896 UTC [3128] FATAL:  the database system is in recovery mode
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14T21:42:29.897Z	ERROR	cmd/keeper.go:719	cannot get configured pg parameters	{"error": "pq: the database system is in recovery mode"}
2022-11-14T21:42:29Z app[9080567a236587] ord [info]keeper   | 2022-11-14 21:42:29.929 UTC [3126] LOG:  database system was not properly shut down; automatic recovery in progress