I had this foreign data wrapper server set up between my 2 databases stored locally and it worked perfectly for the longest time until today and I can't seem to figure out why. I did recently upgrade to Mac OS Sonoma so maybe that did something? But my PSQL version is still at 14.1 so that shouldn't have affected it.
I tried re-creating the FDW setup by doing the following but it hits a snag at the end
# As postgres in et_default. Make a user fdwu that can only read for safety
/c et_default
CREATE USER fdwu WITH PASSWORD 'pass';
GRANT USAGE ON SCHEMA public TO fdwu;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdwu;
# As postgres in local database. Create the basic connections. # Looks like this needs to be repeated on backup
\c bts_default
CREATE EXTENSION postgres_fdw;
CREATE SERVER fdw_et FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', port '5432', dbname 'et_default');
GRANT USAGE ON FOREIGN SERVER fdw_et TO myuser;
CREATE USER MAPPING FOR myuser SERVER fdw_et OPTIONS (user 'fdwu', password 'pass');
CREATE USER MAPPING FOR postgres SERVER fdw_et OPTIONS (user 'fdwu', password 'pass');
# As myuser in local database so that myuser has usage rights to these connections.
\c bts_default
CREATE SCHEMA fdw_et;
IMPORT FOREIGN SCHEMA public FROM SERVER fdw_et INTO fdw_et;
But upon running that last line, I get the error
psql:backup_server_recreate_fdw_schema.psql:4: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:backup_server_recreate_fdw_schema.psql:4: fatal: connection to server was lost
But it doesn't make sense because the server was created and it's all local
Update #1: Checked the server log and got this
2024-02-12 15:03:22.667 PST [407] LOG: server process (PID 8826) was terminated by signal 11: Segmentation fault: 11
2024-02-12 15:03:22.667 PST [407] DETAIL: Failed process was running:
SELECT a.* FROM fdw_et.sales_receivable a
2024-02-12 15:03:22.667 PST [407] LOG: terminating any other active server processes
2024-02-12 15:03:22.671 PST [407] LOG: all server processes terminated; reinitializing
2024-02-12 15:03:22.702 PST [8828] LOG: database system was interrupted; last known up at 2024-02-12 14:06:41 PST
2024-02-12 15:03:22.917 PST [8828] LOG: database system was not properly shut down; automatic recovery in progress
2024-02-12 15:03:22.920 PST [8828] LOG: redo starts at 1/1AD7C850
2024-02-12 15:03:22.920 PST [8828] LOG: invalid record length at 1/1AD7C888: wanted 24, got 0
2024-02-12 15:03:22.920 PST [8828] LOG: redo done at 1/1AD7C850 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-02-12 15:03:22.928 PST [407] LOG: database system is ready to accept connections