1

I am moving to a new Windows server and upgrading PostgreSQL to 9.6 and PostGIS to 2.3 while I am at it (old versions were 9.2.4 and 2.0).

I used pg_dumpall to dump the entire server. Came to 318GB.

Now I am attempting to use psql to restore it to the new server, but it keeps crashing with this error:

psql:d:/FullDataDump.dump:9794756: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
psql:d:/FullDataDump.dump:9794756: connection to server was lost

It is inconsistent as to when it crashes, however. Sometimes it will get through 1 database and sometimes 2 or 3. (Note that all databases have PostGIS extension enabled.)

Looking over the logs, the cause of the crash is consistent though:

2016-10-10 21:14:46 PDT ERROR:  rule "geometry_columns_delete" for relation "geometry_columns" already exists
2016-10-10 21:14:46 PDT STATEMENT:  CREATE RULE geometry_columns_delete AS ON DELETE TO geometry_columns DO INSTEAD NOTHING;
2016-10-10 21:14:46 PDT ERROR:  rule "geometry_columns_insert" for relation "geometry_columns" already exists
2016-10-10 21:14:46 PDT STATEMENT:  CREATE RULE geometry_columns_insert AS ON INSERT TO geometry_columns DO INSTEAD NOTHING;
2016-10-10 21:14:46 PDT ERROR:  rule "geometry_columns_update" for relation "geometry_columns" already exists
2016-10-10 21:14:46 PDT STATEMENT:  CREATE RULE geometry_columns_update AS ON UPDATE TO geometry_columns DO INSTEAD NOTHING;
2016-10-10 21:14:47 PDT LOG:  server process (PID 5548) was terminated by exception 0xC0000005
2016-10-10 21:14:47 PDT DETAIL:  Failed process was running: CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;
2016-10-10 21:14:47 PDT HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2016-10-10 21:14:47 PDT LOG:  terminating any other active server processes
2016-10-10 21:14:47 PDT WARNING:  terminating connection because of crash of     another server process
2016-10-10 21:14:47 PDT 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.
2016-10-10 21:14:47 PDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-10 21:14:47 PDT WARNING:  terminating connection because of crash of another server process
 2016-10-10 21:14:47 PDT 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.
2016-10-10 21:14:47 PDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-10 21:14:47 PDT WARNING:  terminating connection because of crash of another server process
2016-10-10 21:14:47 PDT 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.
2016-10-10 21:14:47 PDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-10 21:14:47 PDT WARNING:  terminating connection because of crash of another server process
2016-10-10 21:14:47 PDT 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.
2016-10-10 21:14:47 PDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-10 21:14:47 PDT WARNING:  terminating connection because of crash of another server process
2016-10-10 21:14:47 PDT 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.
2016-10-10 21:14:47 PDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-10 21:14:47 PDT WARNING:  terminating connection because of crash of another server process
2016-10-10 21:14:47 PDT 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.
2016-10-10 21:14:47 PDT HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2016-10-10 21:14:47 PDT FATAL:  the database system is in recovery mode
2016-10-10 21:14:47 PDT LOG:  all server processes terminated;     reinitializing
2016-10-10 21:14:47 PDT LOG:  database system was interrupted; last known up at 2016-10-10 21:12:23 PDT
2016-10-10 21:14:59 PDT LOG:  database system was not properly shut down; automatic recovery in progress
2016-10-10 21:14:59 PDT LOG:  redo starts at 19/4F39DC78
2016-10-10 21:15:06 PDT LOG:  invalid record length at 19/6D025490: wanted 24, got 0
2016-10-10 21:15:06 PDT LOG:  redo done at 19/6D025468
2016-10-10 21:15:06 PDT LOG:  last completed transaction was at log time            2016-10-10 21:14:47.757054-07
2016-10-10 21:15:06 PDT LOG:  MultiXact member wraparound protections are now enabled
2016-10-10 21:15:07 PDT LOG:  database system is ready to accept connections
2016-10-10 21:15:07 PDT LOG:  autovacuum launcher started

I am including several lines prior to the crash in case they have a story to tell.

If I run CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology from a query window on the database to which it was connected when it crashed, it works.

So why is it crashing the server when run from the dump file?

  • Are you maybe using a version of the extension that is intended for a different Postgres version? – a_horse_with_no_name Oct 11 '16 at 6:45
  • It does not help, but there is a recent question with a similar problem: dba.stackexchange.com/q/151190/6219 might be worth a bug report (supposedly on the PostGis side) – dezso Oct 11 '16 at 7:38
  • @a_horse_with_no_name. The extension installs via the SQL pane, so I assume there is no version conflict. I tried it again but this time piped: direct pg_dump | psql and I got the same thing so I think I can rule out file corruption. – Brad Mathews Oct 14 '16 at 16:17
  • @dezso, Yeah, I think a bug report is in order. – Brad Mathews Oct 14 '16 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.