pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSegmentation fault when running queries in sequence
10+ messages / 3 participants
[nested] [flat]
* Segmentation fault when running queries in sequence
@ 2023-09-23 10:53 Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 11:38 ` Re: Segmentation fault when running queries in sequence abbas alizadeh <ramkly@yahoo.com>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Matt Gibbins @ 2023-09-23 10:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I am running postgresql through an SSH tunnel to process a collection of
files on the server loading them into the database.
Specifically GTFS files
The process is as follows.
find ../Database\ management/SQL/Create\ tables/Landing -type f -name
'Bus *.sql' | sort -n | while read -r f;
do
echo "$f";
./Query\ to\ server "$f";
./Query\ to\ server "../Database management/SQL/Create
tables/AllServices/Insert landing - bus.sql";
done
The 'Query to server' script initiates an SSL session with the following
statement where postgresql_tcanalysis initiates an SSH tunnel to the
database on the remote server which I have SSH connection to.
postgresql_tcanalysis < "$1"
There is no problem with the SSH connection and running individual queries.
The issue occurs when stepping through the queries.
The find statement results in 19 queries. It is necessary to have
individual queries as the content of the GTFS files are not consistent
with regard to fields.
Attached is the STDOUT messages showing the progress of the the process
to segmentation fault.
Running the individual queries where the segfault occurs does not occur
for the individual query. The segfault only occurs when doing the bulk
process.
Also attached is the section from
/var/log/postgresql/postgresql-15-main.log relating to the segfault
occurence.
Any suggestions for identifying the cause appreciated.
Regards
Matt.
../Database management/SQL/Create tables/Landing/Bus tables_20200417.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
?column?
----------
Agency
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Calendar
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------------
Calendar_Dates
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Routes
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Stops
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
------------
Stop_Times
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Trips
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Shapes
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
SSL SYSCALL error: EOF detected
connection to server was lost
../Database management/SQL/Create tables/Landing/Bus tables_20200701.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210219.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210610.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210823.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210930.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20211125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20220120.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20230324.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20230717.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20200417.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20200701.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210408.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210824.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20211125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20220120.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20230324.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20230717.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Attachments:
[text/plain] messages.txt (8.9K, ../../728d1797-efdd-4298-8ab2-042a28ddaae2@fastmail.com.au/3-messages.txt)
download | inline:
../Database management/SQL/Create tables/Landing/Bus tables_20200417.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
?column?
----------
Agency
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Calendar
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------------
Calendar_Dates
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Routes
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Stops
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
------------
Stop_Times
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Trips
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
?column?
----------
Shapes
(1 row)
DROP TABLE
CREATE TABLE
load_tc_gtfs
--------------
(1 row)
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
INSERT 0 0
SSL SYSCALL error: EOF detected
connection to server was lost
../Database management/SQL/Create tables/Landing/Bus tables_20200701.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210219.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210610.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210823.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20210930.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20211125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20220120.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20230324.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables_20230717.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20200417.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20200701.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210408.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20210824.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20211125.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20220120.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20230324.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
../Database management/SQL/Create tables/Landing/Bus tables school_20230717.sql
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
Connection to localhost (::1) 9999 port [tcp/*] succeeded!
psql: error: connection to server at "localhost" (::1), port 9999 failed: FATAL: the database system is in recovery mode
[text/x-log] postgresql-15-main-segfault.log (6.5K, ../../728d1797-efdd-4298-8ab2-042a28ddaae2@fastmail.com.au/4-postgresql-15-main-segfault.log)
download | inline:
2023-09-23 20:39:34.629 AEST [2667486] LOG: server process (PID 2675514) was terminated by signal 11: Segmentation fault
2023-09-23 20:39:34.629 AEST [2667486] DETAIL: Failed process was running: with stop_times as (select trip_id,stop_sequence from allservices.stop_times)
2023-09-23 20:39:34.629 AEST [2667486] LOG: terminating any other active server processes
2023-09-23 20:39:34.638 AEST [2675518] nc_admin@nextcloud_db FATAL: the database system is in recovery mode
2023-09-23 20:39:34.680 AEST [2675520] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:34.682 AEST [2667486] LOG: all server processes terminated; reinitializing
2023-09-23 20:39:34.867 AEST [2675521] LOG: database system was interrupted; last known up at 2023-09-23 20:35:56 AEST
2023-09-23 20:39:34.884 AEST [2675525] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:34.982 AEST [2675527] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.099 AEST [2675529] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.192 AEST [2675535] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.284 AEST [2675537] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.384 AEST [2675539] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.483 AEST [2675541] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.580 AEST [2675543] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.678 AEST [2675545] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.780 AEST [2675547] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.873 AEST [2675549] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:35.973 AEST [2675551] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.081 AEST [2675553] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.204 AEST [2675555] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.319 AEST [2675557] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.431 AEST [2675559] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.523 AEST [2675561] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.621 AEST [2675563] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.718 AEST [2675565] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.818 AEST [2675567] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:36.917 AEST [2675569] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.012 AEST [2675571] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.116 AEST [2675573] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.216 AEST [2675575] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.308 AEST [2675581] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.407 AEST [2675583] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.512 AEST [2675585] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.617 AEST [2675587] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.716 AEST [2675589] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.816 AEST [2675591] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:37.919 AEST [2675593] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:38.018 AEST [2675595] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:38.115 AEST [2675597] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:38.207 AEST [2675599] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:38.310 AEST [2675601] matthew@tcanalysis FATAL: the database system is in recovery mode
2023-09-23 20:39:38.859 AEST [2675521] LOG: database system was not properly shut down; automatic recovery in progress
2023-09-23 20:39:38.929 AEST [2675521] LOG: redo starts at 1/A741D4E0
2023-09-23 20:39:39.067 AEST [2675521] LOG: invalid record length at 1/A9F9F798: wanted 24, got 0
2023-09-23 20:39:39.067 AEST [2675521] LOG: redo done at 1/A9F9F770 system usage: CPU: user: 0.08 s, system: 0.05 s, elapsed: 0.13 s
2023-09-23 20:39:39.414 AEST [2675522] LOG: checkpoint starting: end-of-recovery immediate wait
2023-09-23 20:39:44.791 AEST [2675615] nc_admin@nextcloud_db FATAL: the database system is not yet accepting connections
2023-09-23 20:39:44.791 AEST [2675615] nc_admin@nextcloud_db DETAIL: Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.795 AEST [2675616] nc_admin@nextcloud_db FATAL: the database system is not yet accepting connections
2023-09-23 20:39:44.795 AEST [2675616] nc_admin@nextcloud_db DETAIL: Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.799 AEST [2675617] nc_admin@nextcloud_db FATAL: the database system is not yet accepting connections
2023-09-23 20:39:44.799 AEST [2675617] nc_admin@nextcloud_db DETAIL: Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.803 AEST [2675618] nc_admin@nextcloud_db FATAL: the database system is not yet accepting connections
2023-09-23 20:39:44.803 AEST [2675618] nc_admin@nextcloud_db DETAIL: Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.807 AEST [2675619] nc_admin@nextcloud_db FATAL: the database system is not yet accepting connections
2023-09-23 20:39:44.807 AEST [2675619] nc_admin@nextcloud_db DETAIL: Consistent recovery state has not been yet reached.
2023-09-23 20:39:44.947 AEST [2675522] LOG: checkpoint complete: wrote 7309 buffers (1.4%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.654 s, sync=4.146 s, total=5.600 s; sync files=73, longest=0.080 s, average=0.057 s; distance=44552 kB, estimate=44552 kB
2023-09-23 20:39:45.024 AEST [2667486] LOG: database system is ready to accept connections
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
@ 2023-09-23 11:38 ` abbas alizadeh <ramkly@yahoo.com>
2023-09-24 00:20 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
1 sibling, 1 reply; 10+ messages in thread
From: abbas alizadeh @ 2023-09-23 11:38 UTC (permalink / raw)
To: Matt Gibbins <matt_gibbins@fastmail.com.au>; +Cc: pgsql-admin@lists.postgresql.org
He Matt
Most of time segment fault is because of broken index, reindex the table (or if you can whole db) it might help get rid of segment faults
Regards
Abbas
> On 23 Sep 2023, at 2:53 PM, Matt Gibbins <matt_gibbins@fastmail.com.au> wrote:
>
>
> I am running postgresql through an SSH tunnel to process a collection of files on the server loading them into the database.
>
> Specifically GTFS files
>
> The process is as follows.
>
> find ../Database\ management/SQL/Create\ tables/Landing -type f -name 'Bus *.sql' | sort -n | while read -r f;
> do
> echo "$f";
> ./Query\ to\ server "$f";
> ./Query\ to\ server "../Database management/SQL/Create tables/AllServices/Insert landing - bus.sql";
> done
> The 'Query to server' script initiates an SSL session with the following statement where postgresql_tcanalysis initiates an SSH tunnel to the database on the remote server which I have SSH connection to.
>
> postgresql_tcanalysis < "$1"
> There is no problem with the SSH connection and running individual queries.
>
> The issue occurs when stepping through the queries.
>
> The find statement results in 19 queries. It is necessary to have individual queries as the content of the GTFS files are not consistent with regard to fields.
>
> Attached is the STDOUT messages showing the progress of the the process to segmentation fault.
>
> Running the individual queries where the segfault occurs does not occur for the individual query. The segfault only occurs when doing the bulk process.
>
> Also attached is the section from /var/log/postgresql/postgresql-15-main.log relating to the segfault occurence.
>
> Any suggestions for identifying the cause appreciated.
>
> Regards
> Matt.
>
>
>
>
>
> <messages.txt>
> <postgresql-15-main-segfault.log>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 11:38 ` Re: Segmentation fault when running queries in sequence abbas alizadeh <ramkly@yahoo.com>
@ 2023-09-24 00:20 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
0 siblings, 0 replies; 10+ messages in thread
From: Matt Gibbins @ 2023-09-24 00:20 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org
On 23/9/23 21:38, abbas alizadeh wrote:
> He Matt
> Most of time segment fault is because of broken index, reindex the
> table (or if you can whole db) it might help get rid of segment faults
>
> Regards
> Abbas
Abbas
Thanks for the tip.
Unfortunately reindexing the database did not resolve the issue.
Matt.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
@ 2023-09-23 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-09-23 14:26 UTC (permalink / raw)
To: Matt Gibbins <matt_gibbins@fastmail.com.au>; +Cc: pgsql-admin@lists.postgresql.org
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
> Attached is the STDOUT messages showing the progress of the the process
> to segmentation fault.
Not a lot of information there. Can you get a stack trace from
the crash?
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
Also, if you're not on the latest minor version of PG 15, first
update and see if that resolves the problem.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-23 23:39 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 23:56 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Matt Gibbins @ 2023-09-23 23:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
On 24/9/23 00:26, Tom Lane wrote:
> Matt Gibbins<matt_gibbins@fastmail.com.au> writes:
>> Attached is the STDOUT messages showing the progress of the the process
>> to segmentation fault.
> Not a lot of information there. Can you get a stack trace from
> the crash?
>
> https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
>
> Also, if you're not on the latest minor version of PG 15, first
> update and see if that resolves the problem.
>
> regards, tom lane
Tom
Thank you.
This is the extent of information I am able to obtain at the moment.
FYI the system is Linux canberramjg.ddns.net 6.1.0-12-amd64 #1 SMP
PREEMPT_DYNAMIC Debian 6.1.52-1 (2023-09-07) x86_64 GNU/Linux
The Debian version Debian GNU/Linux 12 (bookworm)
The postgresql version installed is 15.4-2.pgdg120+1 which is the
latest from the distro.
Have enabled stack trace for postgresql. Noticed an interesting problem
with running the stack trace.
Of note my process is driven by remote execution of SQL queries via SSH
tunnel.
When I launch gdb for stack trace on the server the process hangs.
Regards
Matt.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
@ 2023-09-23 23:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 00:46 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-09-23 23:56 UTC (permalink / raw)
To: Matt Gibbins <matt_gibbins@fastmail.com.au>; +Cc: pgsql-admin@lists.postgresql.org
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
> When I launch gdb for stack trace on the server the process hangs.
If you mean you're attaching gdb to the active server process, yes
that would stop execution. You could just say "continue" and
wait for the segfault to occur.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 23:56 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-24 00:46 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-24 00:54 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Matt Gibbins @ 2023-09-24 00:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
On 24/9/23 09:56, Tom Lane wrote:
> Matt Gibbins<matt_gibbins@fastmail.com.au> writes:
>> When I launch gdb for stack trace on the server the process hangs.
> If you mean you're attaching gdb to the active server process, yes
> that would stop execution. You could just say "continue" and
> wait for the segfault to occur.
>
> regards, tom lane
Tom
Thanks for that
I launched gdb on the server with sudo gdp -p <port>.
Launched the client on the remote connection then executed continue in
the gdb prompt on the server.
Initial response
Continuing.
Program received signal SIGUSR1, User defined signal 1.
I executed continue and the gdb reported '[Detaching after fork from
child process 2790431]' for several sequences and the remote process
then crashed.
No further information available other than 'Detaching after fork...'.
Regards
Matt.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 23:56 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 00:46 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
@ 2023-09-24 00:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 02:50 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-09-24 00:54 UTC (permalink / raw)
To: Matt Gibbins <matt_gibbins@fastmail.com.au>; +Cc: pgsql-admin@lists.postgresql.org
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
> I executed continue and the gdb reported '[Detaching after fork from
> child process 2790431]' for several sequences and the remote process
> then crashed.
I think this means that you attached to the parent postmaster
process, not to the per-session child process where the crash
will occur.
On the whole, attaching to a live server process is kind of the
hard way to do this anyhow. I'd recommend enabling saving of
core dump files and then gdb'ing the core file. That avoids
needing to identify in advance the process that is going to crash.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 23:56 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 00:46 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-24 00:54 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-09-24 02:50 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-24 02:56 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Matt Gibbins @ 2023-09-24 02:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin@lists.postgresql.org
On 24/9/23 10:54, Tom Lane wrote:
> Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
>> I executed continue and the gdb reported '[Detaching after fork from
>> child process 2790431]' for several sequences and the remote process
>> then crashed.
> I think this means that you attached to the parent postmaster
> process, not to the per-session child process where the crash
> will occur.
>
> On the whole, attaching to a live server process is kind of the
> hard way to do this anyhow. I'd recommend enabling saving of
> core dump files and then gdb'ing the core file. That avoids
> needing to identify in advance the process that is going to crash.
>
> regards, tom lane
Tom
Thank you for your patience.
I have now created a core dump of the process. It is approximately 4G in
size.
Rather large for an email.
As I am not familiar with core dump processing I require some further
guidance.
Matt.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Segmentation fault when running queries in sequence
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 23:56 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 00:46 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-24 00:54 ` Re: Segmentation fault when running queries in sequence Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 02:50 ` Re: Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
@ 2023-09-24 02:56 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2023-09-24 02:56 UTC (permalink / raw)
To: Matt Gibbins <matt_gibbins@fastmail.com.au>; +Cc: pgsql-admin@lists.postgresql.org
Matt Gibbins <matt_gibbins@fastmail.com.au> writes:
> I have now created a core dump of the process. It is approximately 4G in
> size.
> Rather large for an email.
It would be of no value anyway except on a system exactly like yours.
> As I am not familiar with core dump processing I require some further
> guidance.
The details are in the wiki page I pointed you to before.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-09-24 02:56 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-23 10:53 Segmentation fault when running queries in sequence Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 11:38 ` abbas alizadeh <ramkly@yahoo.com>
2023-09-24 00:20 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-23 23:39 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-23 23:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 00:46 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-24 00:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-24 02:50 ` Matt Gibbins <matt_gibbins@fastmail.com.au>
2023-09-24 02:56 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox