postgresql segfault installing postgis extension

[happyjaxx]

Hi,

Unsure if I'm at the right place, but using pgedge platform, i'm having an issue on vanilla Debians (good'ol LXC/Proxmox , nothing fishy like Docker)

I have set up two dual node clusters in two locations, one pg16 and another pg17, both show hard failures upon installing postgis-pg16/17 via "./pgedge um install" (even with -a False, the shared_preload_libraries is still modified, the only difference is the instance isn't restarted and "CREATE EXTENSION IF NOT EXISTS postgis CASCADE" postgres" isn't attempted (which didn't work anyways since the daemon died before))

It usually goes like this:

pgedge@bdd-201:~/pgedge$ ./pgedge um install postgis-pg17

########### Installing postgis-pg17 ###############
  ['postgis-pg17']

Get:1 https://pgedge-download.s3.amazonaws.com/REPO postgis-pg17-3.5.0-1-amd
   50 MB [35%]
  100 MB [70%]
  141 MB [100%]

Unpacking postgis-pg17-3.5.0-1-amd.tgz
  new: shared_preload_libraries = 'pg_stat_statements, snowflake, spock, postgis-3'

pg17 stopping
pg17 starting on port 5432
pg_ctl: could not start server
Examine the log output.

$ pg17/bin/psql -p 5432 -c "CREATE EXTENSION IF NOT EXISTS postgis CASCADE" postgres
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?
pgedge@bdd-201:~/pgedge$

Logs would be like:

2025-01-28 10:53:27 UTC [370]: [3-1] user=,db=,app=,client= LOG:  starting PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.3.1 20240611 (Red Hat 13.3.1-2), 64-bit
2025-01-28 10:53:27 UTC [370]: [4-1] user=,db=,app=,client= LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-01-28 10:53:27 UTC [370]: [5-1] user=,db=,app=,client= LOG:  listening on IPv6 address "::", port 5432
2025-01-28 10:53:27 UTC [370]: [6-1] user=,db=,app=,client= LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-01-28 10:53:27 UTC [374]: [1-1] user=,db=,app=,client= LOG:  database system was interrupted; last known up at 2025-01-28 10:47:04 UTC
2025-01-28 10:53:27 UTC [374]: [2-1] user=,db=,app=,client= LOG:  database system was not properly shut down; automatic recovery in progress
2025-01-28 10:53:27 UTC [374]: [3-1] user=,db=,app=,client= LOG:  recovered replication state of node 1 to 0/2077118
2025-01-28 10:53:27 UTC [374]: [4-1] user=,db=,app=,client= LOG:  invalid record length at 0/2810C30: expected at least 24, got 0
2025-01-28 10:53:27 UTC [374]: [5-1] user=,db=,app=,client= LOG:  redo is not required
2025-01-28 10:53:27 UTC [372]: [1-1] user=,db=,app=,client= LOG:  checkpoint starting: end-of-recovery immediate wait
2025-01-28 10:53:27 UTC [372]: [2-1] user=,db=,app=,client= LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.005 s, sync=0.002 s, total=0.015 s; sync files=2, longest=0.002 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/2810C30, redo lsn=0/2810C30
2025-01-28 10:53:27 UTC [370]: [7-1] user=,db=,app=,client= LOG:  startup process (PID 374) was terminated by signal 11: Segmentation fault
2025-01-28 10:53:27 UTC [370]: [8-1] user=,db=,app=,client= LOG:  terminating any other active server processes
2025-01-28 10:53:27 UTC [370]: [9-1] user=,db=,app=,client= LOG:  shutting down due to startup process failure
2025-01-28 10:53:27 UTC [370]: [10-1] user=,db=,app=,client= LOG:  database system is shut down

and the proxmox kernel messages would be:

[Tue Jan 28 11:51:37 2025] postgres[911616]: segfault at ffffffffffffffff ip 00007fec3a4b7eca sp 00007ffd8889f6d0 error 5 in libc.so.6[7fec3a445000+155000] likely on CPU 28 (core 4, socket 0)
[Tue Jan 28 11:51:37 2025] Code: dc 2c 10 00 e8 27 63 fe ff 0f 1f 80 00 00 00 00 48 85 ff 0f 84 bf 00 00 00 55 48 8d 77 f0 53 48 83 ec 18 48 8b 1d 16 8f 13 00 <48> 8b 47 f8 64 8b 2b a8 02 75 5b 48 8b 15 9c 8e 13 00 64 48 83 3a
[Tue Jan 28 11:51:37 2025] postgres[911612]: segfault at ffffffffffffffff ip 00007fec3a4b7eca sp 00007ffd8889ec20 error 5 in libc.so.6[7fec3a445000+155000] likely on CPU 29 (core 5, socket 0)
[Tue Jan 28 11:51:37 2025] Code: dc 2c 10 00 e8 27 63 fe ff 0f 1f 80 00 00 00 00 48 85 ff 0f 84 bf 00 00 00 55 48 8d 77 f0 53 48 83 ec 18 48 8b 1d 16 8f 13 00 <48> 8b 47 f8 64 8b 2b a8 02 75 5b 48 8b 15 9c 8e 13 00 64 48 83 3a
[Tue Jan 28 11:51:37 2025] postgres[911613]: segfault at ffffffffffffffff ip 00007fec3a4b7eca sp 00007ffd8889d630 error 5 in libc.so.6[7fec3a445000+155000] likely on CPU 8 (core 8, socket 0)
[Tue Jan 28 11:51:37 2025] Code: dc 2c 10 00 e8 27 63 fe ff 0f 1f 80 00 00 00 00 48 85 ff 0f 84 bf 00 00 00 55 48 8d 77 f0 53 48 83 ec 18 48 8b 1d 16 8f 13 00 <48> 8b 47 f8 64 8b 2b a8 02 75 5b 48 8b 15 9c 8e 13 00 64 48 83 3a

Both couple of instances are plain Debian Templates from Proxmox (mostly up to date) on separate proxmox clusters (mine and a clients), updated to Debian 12.9... even installed libgdal25 in case it was a missing dep.

removing "postgis-3" from shared_preload_libraries allows the daemon to start hopefully, and the extension can be created on a database...

this could be 1000% PEBKAC, but it's a bit too easy to crash an instance by simply issuing a ./pgedge um install postgis-pgxx ... there might be something to explore

Thanks for your insight !
JaXX./.

[happyjaxx]

restoration also fails after a random amount of time... an issue on Debian ? (yes, table already exists)

2025-01-28 13:45:26 UTC [433]: [405-1] user=plopplik,db=plopplik,app=psql,client=10.99.17.110 ERROR:  relation "ticket_timer" already exists
2025-01-28 13:45:26 UTC [433]: [406-1] user=plopplik,db=plopplik,app=psql,client=10.99.17.110 STATEMENT:  CREATE TABLE tickets.ticket_timer (
	    id uuid DEFAULT extensions.uuid_generate_v4() NOT NULL,
	    ticket_id uuid NOT NULL,
	    start_date timestamp with time zone,
	    end_date timestamp with time zone,
	    created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
	    updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
	    deleted_at timestamp with time zone,
	    created_by uuid,
	    updated_by uuid,
	    deleted_by uuid
	);
2025-01-28 13:45:32 UTC [434]: [1-1] user=,db=,app=,client= LOG:  automatic analyze of table "plopplik.eligibility.eligibility"
	I/O timings: read: 13.659 ms, write: 1.428 ms
	avg read rate: 17.963 MB/s, avg write rate: 17.222 MB/s
	buffer usage: 813 hits, 630 misses, 604 dirtied
	system usage: CPU: user: 0.10 s, system: 0.02 s, elapsed: 0.27 s
2025-01-28 13:45:32 UTC [434]: [2-1] user=,db=,app=,client= LOG:  automatic analyze of table "plopplik.eligibility.department"
	I/O timings: read: 2.344 ms, write: 0.025 ms
	avg read rate: 17.188 MB/s, avg write rate: 17.188 MB/s
	buffer usage: 46 hits, 11 misses, 11 dirtied
	system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2025-01-28 13:45:32 UTC [434]: [3-1] user=,db=,app=,client= LOG:  automatic analyze of table "plopplik.eligibility.stats"
	I/O timings: read: 3.191 ms, write: 0.205 ms
	avg read rate: 16.113 MB/s, avg write rate: 15.381 MB/s
	buffer usage: 153 hits, 66 misses, 63 dirtied
	system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.03 s
2025-01-28 13:45:32 UTC [375]: [15-1] user=,db=,app=,client= LOG:  server process (PID 434) was terminated by signal 11: Segmentation fault
2025-01-28 13:45:32 UTC [375]: [16-1] user=,db=,app=,client= LOG:  terminating any other active server processes
2025-01-28 13:45:32 UTC [375]: [17-1] user=,db=,app=,client= LOG:  all server processes terminated; reinitializing
2025-01-28 13:45:32 UTC [435]: [1-1] user=,db=,app=,client= LOG:  database system was interrupted; last known up at 2025-01-28 13:43:52 UTC
2025-01-28 13:45:32 UTC [438]: [1-1] user=pgedge,db=plopplik,app=[unknown],client=10.99.49.110 FATAL:  the database system is in recovery mode
2025-01-28 13:45:32 UTC [435]: [2-1] user=,db=,app=,client= LOG:  database system was not properly shut down; automatic recovery in progress
2025-01-28 13:45:32 UTC [435]: [3-1] user=,db=,app=,client= LOG:  recovered replication state of node 1 to 0/2077118
2025-01-28 13:45:32 UTC [435]: [4-1] user=,db=,app=,client= LOG:  redo starts at 0/90000A0
2025-01-28 13:45:32 UTC [435]: [5-1] user=,db=,app=,client= LOG:  invalid record length at 0/98A0C40: expected at least 24, got 0
2025-01-28 13:45:32 UTC [435]: [6-1] user=,db=,app=,client= LOG:  redo done at 0/98A0C10 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.01 s
2025-01-28 13:45:32 UTC [436]: [1-1] user=,db=,app=,client= LOG:  checkpoint starting: end-of-recovery immediate wait
2025-01-28 13:45:32 UTC [436]: [2-1] user=,db=,app=,client= LOG:  checkpoint complete: wrote 1062 buffers (6.5%); 0 WAL file(s) added, 0 removed, 2 recycled; write=0.017 s, sync=0.006 s, total=0.035 s; sync files=36, longest=0.003 s, average=0.001 s; distance=8835 kB, estimate=8835 kB; lsn=0/98A0C40, redo lsn=0/98A0C40
2025-01-28 13:45:32 UTC [375]: [18-1] user=,db=,app=,client= LOG:  database system is ready to accept connections
2025-01-28 13:45:32 UTC [442]: [1-1] user=,db=,app=,client= LOG:  starting spock_failover_slots replica worker
2025-01-28 13:45:32 UTC [443]: [1-1] user=,db=,app=,client= LOG:  starting spock supervisor
2025-01-28 13:45:32 UTC [445]: [1-1] user=[unknown],db=postgres,app=spock manager 5,client= LOG:  manager worker [445] at slot 0 generation 1 detaching cleanly
2025-01-28 13:45:32 UTC [446]: [1-1] user=[unknown],db=plopplik,app=spock manager 16386,client= LOG:  starting spock database manager for database plopplik
2025-01-28 13:45:32 UTC [447]: [1-1] user=[unknown],db=plopplik,app=spock apply 16386:2239748012,client= LOG:  SPOCK sub_101_102: connected
2025-01-28 13:45:33 UTC [448]: [1-1] user=[unknown],db=template1,app=spock manager 1,client= LOG:  manager worker [448] at slot 2 generation 1 detaching cleanly
2025-01-28 13:45:37 UTC [449]: [1-1] user=pgedge,db=plopplik,app=spk_plopplik_plopplik_bdd_101_sub_201_101,client=10.99.49.110 LOG:  starting logical decoding for slot "spk_plopplik_plopplik_bdd_101_sub_201_101"
2025-01-28 13:45:37 UTC [449]: [2-1] user=pgedge,db=plopplik,app=spk_plopplik_plopplik_bdd_101_sub_201_101,client=10.99.49.110 DETAIL:  Streaming transactions committing after 0/902A158, reading WAL from 0/90074E8.
2025-01-28 13:45:37 UTC [449]: [3-1] user=pgedge,db=plopplik,app=spk_plopplik_plopplik_bdd_101_sub_201_101,client=10.99.49.110 STATEMENT:  START_REPLICATION SLOT "spk_plopplik_plopplik_bdd_101_sub_201_101" LOGICAL 0/98A0C40 (expected_encoding 'UTF8', min_proto_version '2', max_proto_version '2', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1700', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '0', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'spock.spock_hooks_setup', "spock.forward_origins" '', "spock.replication_set_names" '"default",default_insert_only,ddl_sql', "relmeta_cache_size" '-1', pg_version '170002', spock_version '4.0.9', spock_version_num '40009', spock_apply_pid '378')

pgedge@bdd-101:~/pgedge$ PGSSLMODE=require PGPASSWORD=xxxxxxxxx psql -h bdd-101.as39305.net -U plopplik -d plopplik -a < /srv/dump-plopplik-202501271549.sql
...
INSERT INTO entities.audit_trail VALUES ('3f3ad501-377c-47a8-a299-a148a8339650', '2023-12-21 09:52:12.476565+00', 'Added', 'product_price', 'edce3694-a3df-4379-b272-226f9f70a88f', '046738e6-c265-4dcf-9ff5-042f5a4453f6', 'a6b734b0-4b7a-478f-a845-19d4c4a51fc5', 'POST##/api/v1/Product/Variant', 'id', 'uuid', 'ProductPrice', 'Id', 'Guid', NULL, 'edce3694-a3df-4379-b272-226f9f70a88f');
INSERT 0 1
INSERT INTO entities.audit_trail VALUES ('53bb087a-3b16-4b65-b939-5060f9a3c335', '2023-12-21 09:52:12.476565+00', 'Added', 'product_variant', '2982b19e-0642-40ef-adc1-e31978ab56e2', '046738e6-c265-4dcf-9ff5-042f5a4453f6', 'a6b734b0-4b7a-478f-a845-19d4c4a51fc5', 'POST##/api/v1/Product/Variant', 'constructor_id', 'uuid', 'ProductVariant', 'ConstructorId', 'Guid', NULL, 'eaf1fc65-fcf6-4660-a148-856c5ed2dbe9');
WARNING:  terminating connection because of crash of another server process
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.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
SSL SYSCALL error: EOF detected
connection to server was lost
pgedge@plopplik-bdd-101:~/pgedge$ PGSSLMODE=require PGPASSWORD=xxxxxxxxx psql -h bdd-101.as39305.net -U plopplik -d plopplik -a < /srv/dump-plopplik-202501271549.sql
...
INSERT INTO entities.audit_trail VALUES ('1479c3e3-850f-47ec-b1cc-c97828919044', '2024-01-30 10:44:49.1175+00', 'Added', 'product_network_link', '1d61c2dd-bccf-4941-ac51-ad13eae623c4', '046738e6-c265-4dcf-9ff5-042f5a4453f6', '38d9a40f-0cc1-45bb-a82f-d3452c1fd3e7', 'POST##/api/v1/Product', 'id', 'uuid', 'ProductNetworkLink', 'Id', 'Guid', NULL, '1d61c2dd-bccf-4941-ac51-ad13eae623c4');
INSERT 0 1
INSERT INTO entities.audit_trail VALUES ('30b37a05-c68c-4b38-8516-00674fcc6126', '2024-01-30 10:44:49.1175+00', 'Added', 'product_network_link', '1d61c2dd-bccf-4941-ac51-ad13eae623c4', '046738e6-c265-4dcf-9ff5-042f5a4453f6', '38d9a40f-0cc1-45bb-a82f-d3452c1fd3e7', 'POST##/api/v1/Product', 'deleted_at', 'timestamp with time zone', 'ProductNetworkLink', 'DeletedAt', 'DateTime', NULL, '');
WARNING:  terminating connection because of crash of another server process
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.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
SSL SYSCALL error: EOF detected
connection to server was lost
pgedge@bdd-101:~/pgedge$

(names/ips etc more or less obfuscated)

😬

[happyjaxx]

( ok, last update was probably mildly different, disabling autovacuum on the tables before restoration seems to do wonders, though still getting a segfault at the very end)

[mmols]

@happyjaxx Sorry for the delayed response on this issue. We've resolved a number of build issues in our CLI distributed postgis package in recent versions. It might be worth checking on the issue again if this is still relevant for you, as I've not been able to reproduce this on Debian from my side.