postgresql 数据库 FATAL: terminating connection due to conflict with recovery 的日志分析

红太狼 2022-12-11 12:19 128阅读 0赞

db: postgresql 9.6

模拟 FATAL: terminating connection due to conflict with recovery 对pid 执行 kill -11 命令

2020-09-30 17:04:43.742 CST,,,3459,,5f7445b3.d83,3,,2020-09-30 16:45:39 CST,,0,LOG,00000,"server process (PID 3483) was terminated by signal 11: Segmentation fault","Failed process was running: SELECT CASE WHEN pg_catalog.pg_is_in_recovery() THEN 0 ELSE ('x' || pg_catalog.substr(pg_catalog.pg_xlogfile_name(pg_catalog.pg_current_xlog_location()), 1, 8))::bit(32)::int END, CASE WHEN pg_catalog.pg_is_in_recovery() THEN GREATEST( pg_catalog.pg_xlog_location_diff(COALESCE(pg_catalog.pg_last_xlog_receive_location(), '0/0'), '0/0')::bigint, pg_catalog.pg_xlog_location_diff(pg_catalog.pg_last_xlog_replay_location(), '0/0')::bigint)ELSE pg_catalog.pg_xlog_location_diff(pg_catalog.pg_current_xlog_location(), '0/0')::bigint END",,,,,,,,""
    2020-09-30 17:04:43.742 CST,,,3459,,5f7445b3.d83,4,,2020-09-30 16:45:39 CST,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
    
    2020-09-30 17:04:43.743 CST,"postgres","pgbenchdb",4432,"[local]",5f7448de.1150,8,"idle",2020-09-30 16:59:10 CST,5/0,0,WARNING,57P02,"terminating connection because of crash of another server process","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.","In a moment you should be able to reconnect to the database and repeat your command.",,,,,,,"psql"
    2020-09-30 17:04:44.313 CST,,,3459,,5f7445b3.d83,5,,2020-09-30 16:45:39 CST,,0,LOG,00000,"all server processes terminated; reinitializing",,,,,,,,,""
    
    2020-09-30 17:04:44.759 CST,,,4441,,5f744a2c.1159,1,,2020-09-30 17:04:44 CST,,0,LOG,00000,"database system was interrupted while in recovery at log time 2020-09-30 16:35:42 CST",,"If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.",,,,,,,""
    
    2020-09-30 17:04:44.948 CST,,,4442,"127.0.0.1:58310",5f744a2c.115a,1,"",2020-09-30 17:04:44 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58310",,,,,,,,,""
    2020-09-30 17:04:44.957 CST,"postgres","postgres",4442,"127.0.0.1:58310",5f744a2c.115a,2,"",2020-09-30 17:04:44 CST,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
    2020-09-30 17:04:44.959 CST,,,4443,"127.0.0.1:58312",5f744a2c.115b,1,"",2020-09-30 17:04:44 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58312",,,,,,,,,""
    2020-09-30 17:04:44.959 CST,"postgres","postgres",4443,"127.0.0.1:58312",5f744a2c.115b,2,"",2020-09-30 17:04:44 CST,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
    2020-09-30 17:04:45.220 CST,,,4445,"127.0.0.1:58316",5f744a2d.115d,1,"",2020-09-30 17:04:45 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58316",,,,,,,,,""
    2020-09-30 17:04:45.228 CST,"postgres","postgres",4445,"127.0.0.1:58316",5f744a2d.115d,2,"",2020-09-30 17:04:45 CST,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
    2020-09-30 17:04:45.229 CST,,,4446,"127.0.0.1:58318",5f744a2d.115e,1,"",2020-09-30 17:04:45 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58318",,,,,,,,,""
    2020-09-30 17:04:45.230 CST,"postgres","postgres",4446,"127.0.0.1:58318",5f744a2d.115e,2,"",2020-09-30 17:04:45 CST,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
    2020-09-30 17:04:45.234 CST,,,4447,"127.0.0.1:58322",5f744a2d.115f,1,"",2020-09-30 17:04:45 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58322",,,,,,,,,""
    2020-09-30 17:04:45.238 CST,"postgres","postgres",4447,"127.0.0.1:58322",5f744a2d.115f,2,"",2020-09-30 17:04:45 CST,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
    2020-09-30 17:04:45.239 CST,,,4448,"127.0.0.1:58324",5f744a2d.1160,1,"",2020-09-30 17:04:45 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58324",,,,,,,,,""
    2020-09-30 17:04:45.239 CST,"postgres","postgres",4448,"127.0.0.1:58324",5f744a2d.1160,2,"",2020-09-30 17:04:45 CST,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
    
    2020-09-30 17:04:45.829 CST,,,4441,,5f744a2c.1159,2,,2020-09-30 17:04:44 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
    2020-09-30 17:04:45.852 CST,,,4441,,5f744a2c.1159,3,,2020-09-30 17:04:44 CST,1/0,0,LOG,00000,"redo starts at 0/560000C8",,,,,,,,,""
    
    2020-09-30 17:04:45.942 CST,,,4451,"127.0.0.1:58328",5f744a2d.1163,1,"",2020-09-30 17:04:45 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58328",,,,,,,,,""
    2020-09-30 17:04:45.946 CST,"postgres","postgres",4451,"127.0.0.1:58328",5f744a2d.1163,2,"",2020-09-30 17:04:45 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:45.947 CST,,,4452,"127.0.0.1:58330",5f744a2d.1164,1,"",2020-09-30 17:04:45 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58330",,,,,,,,,""
    2020-09-30 17:04:45.947 CST,"postgres","postgres",4452,"127.0.0.1:58330",5f744a2d.1164,2,"",2020-09-30 17:04:45 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:52.635 CST,,,4454,"127.0.0.1:58342",5f744a34.1166,1,"",2020-09-30 17:04:52 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58342",,,,,,,,,""
    2020-09-30 17:04:52.644 CST,"postgres","postgres",4454,"127.0.0.1:58342",5f744a34.1166,2,"",2020-09-30 17:04:52 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:52.645 CST,,,4455,"127.0.0.1:58344",5f744a34.1167,1,"",2020-09-30 17:04:52 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58344",,,,,,,,,""
    2020-09-30 17:04:52.645 CST,"postgres","postgres",4455,"127.0.0.1:58344",5f744a34.1167,2,"",2020-09-30 17:04:52 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:52.649 CST,,,4456,"127.0.0.1:58348",5f744a34.1168,1,"",2020-09-30 17:04:52 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58348",,,,,,,,,""
    2020-09-30 17:04:52.653 CST,"postgres","postgres",4456,"127.0.0.1:58348",5f744a34.1168,2,"",2020-09-30 17:04:52 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:52.653 CST,,,4457,"127.0.0.1:58350",5f744a34.1169,1,"",2020-09-30 17:04:52 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58350",,,,,,,,,""
    2020-09-30 17:04:52.654 CST,"postgres","postgres",4457,"127.0.0.1:58350",5f744a34.1169,2,"",2020-09-30 17:04:52 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:53.267 CST,,,4459,"127.0.0.1:58354",5f744a35.116b,1,"",2020-09-30 17:04:53 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58354",,,,,,,,,""
    2020-09-30 17:04:53.272 CST,"postgres","postgres",4459,"127.0.0.1:58354",5f744a35.116b,2,"",2020-09-30 17:04:53 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    2020-09-30 17:04:53.273 CST,,,4460,"127.0.0.1:58356",5f744a35.116c,1,"",2020-09-30 17:04:53 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=58356",,,,,,,,,""
    2020-09-30 17:04:53.273 CST,"postgres","postgres",4460,"127.0.0.1:58356",5f744a35.116c,2,"",2020-09-30 17:04:53 CST,,0,FATAL,57P03,"the database system is starting up",,,,,,,,,""
    
    2020-09-30 17:04:53.757 CST,,,4441,,5f744a2c.1159,4,,2020-09-30 17:04:44 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/67AC2B98",,,,,,,,,""
    2020-09-30 17:04:53.757 CST,,,4441,,5f744a2c.1159,5,,2020-09-30 17:04:44 CST,1/0,0,LOG,00000,"invalid record length at 0/67AC9F70: wanted 24, got 0",,,,,,,,,""
    2020-09-30 17:04:53.758 CST,,,3459,,5f7445b3.d83,6,,2020-09-30 16:45:39 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
    2020-09-30 17:04:53.806 CST,,,4462,,5f744a35.116e,1,,2020-09-30 17:04:53 CST,,0,LOG,00000,"started streaming WAL from primary at 0/67000000 on timeline 18",,,,,,,,,""