[Bug]: Backups don't seem to work when going from postgresql 15 to 18.3

[Scorch038]

Bug Description

Upgrade from postgresql 15-alpine to 18.3-alpine. The upgrade guide didn't seem to work so I was trying to update via backup and restore method.

Steps to Reproduce

Steps to reproduce the behavior:

1. Create Backup on postgresql 15-alpine
2. backup postgresql folder
3. Create new postgresql folder
4. Startup , create new login
5. Start backup

Expected Behavior

Expected restore to work

Actual Behavior

The Backup is not restored

Environment

• SparkyFitness Version: v0.16.5.6
• Running Env: Docker

Container Logs:

sparkyfitness-frontend-1 | 2026/04/08 17:19:26 [warn] 36#36: *31 a client request body is buffered to a temporary file /var/cache/nginx/client_temp/0000000001, client: 192.168.1.177, server: localhost, request: "POST /api/admin/backup/restore HTTP/1.1", host: "192.168.1.63:3004", referrer: "http://192.168.1.63:3004/admin"
sparkyfitness-db | 2026-04-08 17:19:26.468 UTC [49] FATAL: terminating connection due to administrator command
sparkyfitness-db | 2026-04-08 17:19:26.468 UTC [61] FATAL: terminating connection due to administrator command
sparkyfitness-server-1 | node:events:502
sparkyfitness-server-1 | throw er; // Unhandled 'error' event
sparkyfitness-server-1 | ^
sparkyfitness-server-1 |
sparkyfitness-server-1 | error: terminating connection due to administrator command
sparkyfitness-server-1 | at parseErrorMessage (/app/SparkyFitnessServer/node_modules/.pnpm/pg-protocol@1.13.0/node_modules/pg-protocol/src/parser.ts:394:9)
sparkyfitness-server-1 | at Parser.handlePacket (/app/SparkyFitnessServer/node_modules/.pnpm/pg-protocol@1.13.0/node_modules/pg-protocol/src/parser.ts:212:19)
sparkyfitness-server-1 | at Parser.parse (/app/SparkyFitnessServer/node_modules/.pnpm/pg-protocol@1.13.0/node_modules/pg-protocol/src/parser.ts:105:30)
sparkyfitness-server-1 | at Socket. (/app/SparkyFitnessServer/node_modules/.pnpm/pg-protocol@1.13.0/node_modules/pg-protocol/src/index.ts:7:48)
sparkyfitness-server-1 | at Socket.emit (node:events:524:28)
sparkyfitness-server-1 | at addChunk (node:internal/streams/readable:561:12)
sparkyfitness-server-1 | at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
sparkyfitness-server-1 | at Readable.push (node:internal/streams/readable:392:5)
sparkyfitness-server-1 | at TCP.onStreamRead (node:internal/stream_base_commons:191:23)
sparkyfitness-server-1 | at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
sparkyfitness-server-1 | Emitted 'error' event on BoundPool instance at:
sparkyfitness-server-1 | at Client.idleListener (/app/SparkyFitnessServer/node_modules/.pnpm/pg-pool@3.13.0_pg@8.20.0/node_modules/pg-pool/index.js:62:10)
sparkyfitness-server-1 | at Client.emit (node:events:524:28)
sparkyfitness-server-1 | at Client._handleErrorEvent (/app/SparkyFitnessServer/node_modules/.pnpm/pg@8.20.0/node_modules/pg/lib/client.js:393:10)
sparkyfitness-server-1 | at Client._handleErrorMessage (/app/SparkyFitnessServer/node_modules/.pnpm/pg@8.20.0/node_modules/pg/lib/client.js:404:12)
sparkyfitness-server-1 | at Connection.emit (node:events:524:28)
sparkyfitness-server-1 | at /app/SparkyFitnessServer/node_modules/.pnpm/pg@8.20.0/node_modules/pg/lib/connection.js:115:12
sparkyfitness-server-1 | at Parser.parse (/app/SparkyFitnessServer/node_modules/.pnpm/pg-protocol@1.13.0/node_modules/pg-protocol/src/parser.ts:106:9)
sparkyfitness-server-1 | at Socket. (/app/SparkyFitnessServer/node_modules/.pnpm/pg-protocol@1.13.0/node_modules/pg-protocol/src/index.ts:7:48)
sparkyfitness-server-1 | at Socket.emit (node:events:524:28)
sparkyfitness-server-1 | at addChunk (node:internal/streams/readable:561:12) {
sparkyfitness-server-1 | length: 116,
sparkyfitness-server-1 | severity: 'FATAL',
sparkyfitness-server-1 | code: '57P01',
sparkyfitness-server-1 | detail: undefined,
sparkyfitness-server-1 | hint: undefined,
sparkyfitness-server-1 | position: undefined,
sparkyfitness-server-1 | internalPosition: undefined,
sparkyfitness-server-1 | internalQuery: undefined,
sparkyfitness-server-1 | where: undefined,
sparkyfitness-server-1 | schema: undefined,
sparkyfitness-server-1 | table: undefined,
sparkyfitness-server-1 | column: undefined,
sparkyfitness-server-1 | dataType: undefined,
sparkyfitness-server-1 | constraint: undefined,

sparkyfitness-frontend-1 | 2026/04/08 17:19:26 [error] 36#36: *31 upstream prematurely closed connection while reading response header from upstream, client: 192.168.1.177, server: localhost, request: "POST /api/admin/backup/restore HTTP/1.1", upstream: "http://172.19.0.3:3010/api/admin/backup/restore", host: "192.168.1.63:3004", referrer: "http://192.168.1.63:3004/admin"
sparkyfitness-server-1 | file: 'postgres.c',

sparkyfitness-frontend-1 | 192.168.1.177 - - [08/Apr/2026:17:19:26 +0000] "POST /api/admin/backup/restore HTTP/1.1" 502 559 "http://192.168.1.63:3004/admin" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/146.0.0.0 Safari/537.36"
sparkyfitness-server-1 | line: '3353',
sparkyfitness-server-1 | routine: 'ProcessInterrupts',
sparkyfitness-server-1 | client: Client {
sparkyfitness-server-1 | _events: [Object: null prototype] { error: [Function (anonymous)] },
sparkyfitness-server-1 | _eventsCount: 1,
sparkyfitness-server-1 | _maxListeners: undefined,
sparkyfitness-server-1 | connectionParameters: ConnectionParameters {
sparkyfitness-server-1 | user: 'sparky',
sparkyfitness-server-1 | database: 'sparkyfitness_db',
sparkyfitness-server-1 | port: 5432,
sparkyfitness-server-1 | host: 'sparkyfitness-db',
sparkyfitness-server-1 | binary: false,
sparkyfitness-server-1 | options: undefined,
sparkyfitness-server-1 | ssl: false,
sparkyfitness-server-1 | client_encoding: '',
sparkyfitness-server-1 | replication: undefined,
sparkyfitness-server-1 | isDomainSocket: false,
sparkyfitness-server-1 | application_name: undefined,
sparkyfitness-server-1 | fallback_application_name: undefined,
sparkyfitness-server-1 | statement_timeout: false,
sparkyfitness-server-1 | lock_timeout: false,
sparkyfitness-server-1 | idle_in_transaction_session_timeout: false,
sparkyfitness-server-1 | query_timeout: false,
sparkyfitness-server-1 | connect_timeout: 0
sparkyfitness-server-1 | },
sparkyfitness-server-1 | user: 'sparky',
sparkyfitness-server-1 | database: 'sparkyfitness_db',
sparkyfitness-server-1 | port: 5432,
sparkyfitness-server-1 | host: 'sparkyfitness-db',
sparkyfitness-server-1 | replication: undefined,
sparkyfitness-server-1 | _Promise: [Function: Promise],
sparkyfitness-server-1 | _types: TypeOverrides {
sparkyfitness-server-1 | _types: {
sparkyfitness-server-1 | getTypeParser: [Function: getTypeParser],
sparkyfitness-server-1 | setTypeParser: [Function: setTypeParser],
sparkyfitness-server-1 | arrayParser: { create: [Function: create] },
sparkyfitness-server-1 | builtins: {
sparkyfitness-server-1 | BOOL: 16,
sparkyfitness-server-1 | BYTEA: 17,
sparkyfitness-server-1 | CHAR: 18,
sparkyfitness-server-1 | INT8: 20,
sparkyfitness-server-1 | INT2: 21,
sparkyfitness-server-1 | INT4: 23,
sparkyfitness-server-1 | REGPROC: 24,
sparkyfitness-server-1 | TEXT: 25,
sparkyfitness-server-1 | OID: 26,
sparkyfitness-server-1 | TID: 27,
sparkyfitness-server-1 | XID: 28,
sparkyfitness-server-1 | CID: 29,
sparkyfitness-server-1 | JSON: 114,
sparkyfitness-server-1 | XML: 142,
sparkyfitness-server-1 | PG_NODE_TREE: 194,
sparkyfitness-server-1 | SMGR: 210,
sparkyfitness-server-1 | PATH: 602,
sparkyfitness-server-1 | POLYGON: 604,
sparkyfitness-server-1 | CIDR: 650,
sparkyfitness-server-1 | FLOAT4: 700,
sparkyfitness-server-1 | FLOAT8: 701,
sparkyfitness-server-1 | ABSTIME: 702,
sparkyfitness-server-1 | RELTIME: 703,
sparkyfitness-server-1 | TINTERVAL: 704,
sparkyfitness-server-1 | CIRCLE: 718,
sparkyfitness-server-1 | MACADDR8: 774,
sparkyfitness-server-1 | MONEY: 790,
sparkyfitness-server-1 | MACADDR: 829,
sparkyfitness-server-1 | INET: 869,
sparkyfitness-server-1 | ACLITEM: 1033,
sparkyfitness-server-1 | BPCHAR: 1042,
sparkyfitness-server-1 | VARCHAR: 1043,
sparkyfitness-server-1 | DATE: 1082,
sparkyfitness-server-1 | TIME: 1083,
sparkyfitness-server-1 | TIMESTAMP: 1114,
sparkyfitness-server-1 | TIMESTAMPTZ: 1184,
sparkyfitness-server-1 | INTERVAL: 1186,
sparkyfitness-server-1 | TIMETZ: 1266,
sparkyfitness-server-1 | BIT: 1560,
sparkyfitness-server-1 | VARBIT: 1562,
sparkyfitness-server-1 | NUMERIC: 1700,
sparkyfitness-server-1 | REFCURSOR: 1790,
sparkyfitness-server-1 | REGPROCEDURE: 2202,
sparkyfitness-server-1 | REGOPER: 2203,
sparkyfitness-server-1 | REGOPERATOR: 2204,
sparkyfitness-server-1 | REGCLASS: 2205,
sparkyfitness-server-1 | REGTYPE: 2206,
sparkyfitness-server-1 | UUID: 2950,
sparkyfitness-server-1 | TXID_SNAPSHOT: 2970,
sparkyfitness-server-1 | PG_LSN: 3220,
sparkyfitness-server-1 | PG_NDISTINCT: 3361,
sparkyfitness-server-1 | PG_DEPENDENCIES: 3402,
sparkyfitness-server-1 | TSVECTOR: 3614,
sparkyfitness-server-1 | TSQUERY: 3615,
sparkyfitness-server-1 | GTSVECTOR: 3642,
sparkyfitness-server-1 | REGCONFIG: 3734,
sparkyfitness-server-1 | REGDICTIONARY: 3769,
sparkyfitness-server-1 | JSONB: 3802,
sparkyfitness-server-1 | REGNAMESPACE: 4089,
sparkyfitness-server-1 | REGROLE: 4096
sparkyfitness-server-1 | }
sparkyfitness-server-1 | },
sparkyfitness-server-1 | text: {},
sparkyfitness-server-1 | binary: {}
sparkyfitness-server-1 | },
sparkyfitness-server-1 | _ending: true,
sparkyfitness-server-1 | _ended: false,
sparkyfitness-server-1 | _connecting: false,
sparkyfitness-server-1 | _connected: true,
sparkyfitness-server-1 | _connectionError: false,
sparkyfitness-server-1 | _queryable: false,
sparkyfitness-server-1 | _activeQuery: null,
sparkyfitness-server-1 | enableChannelBinding: false,
sparkyfitness-server-1 | connection: Connection {
sparkyfitness-server-1 | _events: [Object: null prototype] {
sparkyfitness-server-1 | newListener: [Function (anonymous)],
sparkyfitness-server-1 | connect: [Function (anonymous)],
sparkyfitness-server-1 | sslconnect: [Function (anonymous)],
sparkyfitness-server-1 | authenticationCleartextPassword: [Function: bound _handleAuthCleartextPassword],
sparkyfitness-server-1 | authenticationMD5Password: [Function: bound _handleAuthMD5Password],
sparkyfitness-server-1 | authenticationSASL: [Function: bound _handleAuthSASL],
sparkyfitness-server-1 | authenticationSASLContinue: [Function: bound _handleAuthSASLContinue] AsyncFunction,
sparkyfitness-server-1 | authenticationSASLFinal: [Function: bound _handleAuthSASLFinal],
sparkyfitness-server-1 | backendKeyData: [Function: bound _handleBackendKeyData],
sparkyfitness-server-1 | error: [Function: bound _handleErrorEvent],
sparkyfitness-server-1 | errorMessage: [Function: bound _handleErrorMessage],
sparkyfitness-server-1 | readyForQuery: [Function: bound _handleReadyForQuery],
sparkyfitness-server-1 | notice: [Function: bound _handleNotice],
sparkyfitness-server-1 | rowDescription: [Function: bound _handleRowDescription],
sparkyfitness-server-1 | dataRow: [Function: bound _handleDataRow],
sparkyfitness-server-1 | portalSuspended: [Function: bound _handlePortalSuspended],
sparkyfitness-server-1 | emptyQuery: [Function: bound _handleEmptyQuery],
sparkyfitness-server-1 | commandComplete: [Function: bound _handleCommandComplete],
sparkyfitness-server-1 | parseComplete: [Function: bound _handleParseComplete],
sparkyfitness-server-1 | copyInResponse: [Function: bound _handleCopyInResponse],
sparkyfitness-server-1 | copyData: [Function: bound _handleCopyData],
sparkyfitness-server-1 | notification: [Function: bound _handleNotification],
sparkyfitness-server-1 | end: [
sparkyfitness-server-1 | [Function: bound onceWrapper] {
sparkyfitness-server-1 | listener: [Function (anonymous)]
sparkyfitness-server-1 | },
sparkyfitness-server-1 | [Function: bound onceWrapper] {
sparkyfitness-server-1 | listener: [Function (anonymous)]
sparkyfitness-server-1 | }
sparkyfitness-server-1 | ]
sparkyfitness-server-1 | },
sparkyfitness-server-1 | _eventsCount: 23,
sparkyfitness-server-1 | _maxListeners: undefined,
sparkyfitness-server-1 | stream: Socket {
sparkyfitness-server-1 | connecting: false,
sparkyfitness-server-1 | _hadError: false,
sparkyfitness-server-1 | _parent: null,
sparkyfitness-server-1 | _host: 'sparkyfitness-db',
sparkyfitness-server-1 | _closeAfterHandlingError: false,
sparkyfitness-server-1 | _events: {
sparkyfitness-server-1 | close: [Function (anonymous)],
sparkyfitness-server-1 | error: [Function: reportStreamError],
sparkyfitness-server-1 | prefinish: undefined,
sparkyfitness-server-1 | finish: undefined,
sparkyfitness-server-1 | drain: undefined,
sparkyfitness-server-1 | data: [Function (anonymous)],
sparkyfitness-server-1 | end: [ [Function: onReadableStreamEnd], [Function (anonymous)] ],
sparkyfitness-server-1 | readable: undefined,
sparkyfitness-server-1 | connect: undefined
sparkyfitness-server-1 | },
sparkyfitness-server-1 | _readableState: ReadableState {
sparkyfitness-server-1 | highWaterMark: 16384,
sparkyfitness-server-1 | buffer: [],
sparkyfitness-server-1 | bufferIndex: 0,
sparkyfitness-server-1 | length: 0,
sparkyfitness-server-1 | pipes: [],
sparkyfitness-server-1 | awaitDrainWriters: null,
sparkyfitness-server-1 | [Symbol(kState)]: 194519348
sparkyfitness-server-1 | },
sparkyfitness-server-1 | _writableState: WritableState {
sparkyfitness-server-1 | highWaterMark: 16384,
sparkyfitness-server-1 | length: 0,
sparkyfitness-server-1 | corked: 0,
sparkyfitness-server-1 | onwrite: [Function: bound onwrite],
sparkyfitness-server-1 | writelen: 0,
sparkyfitness-server-1 | bufferedIndex: 0,
sparkyfitness-server-1 | pendingcb: 0,
sparkyfitness-server-1 | [Symbol(kState)]: 17563956,
sparkyfitness-server-1 | [Symbol(kBufferedValue)]: null,
sparkyfitness-server-1 | [Symbol(kWriteCbValue)]: null
sparkyfitness-server-1 | },
sparkyfitness-server-1 | allowHalfOpen: false,
sparkyfitness-server-1 | _maxListeners: undefined,
sparkyfitness-server-1 | _eventsCount: 4,
sparkyfitness-server-1 | _sockname: null,
sparkyfitness-server-1 | _pendingData: null,
sparkyfitness-server-1 | _pendingEncoding: '',
sparkyfitness-server-1 | server: null,
sparkyfitness-server-1 | _server: null,
sparkyfitness-server-1 | [Symbol(async_id_symbol)]: 7518,
sparkyfitness-server-1 | [Symbol(kHandle)]: null,
sparkyfitness-server-1 | [Symbol(lastWriteQueueSize)]: 0,
sparkyfitness-server-1 | [Symbol(timeout)]: null,
sparkyfitness-server-1 | [Symbol(kBuffer)]: null,
sparkyfitness-server-1 | [Symbol(kBufferCb)]: null,
sparkyfitness-server-1 | [Symbol(kBufferGen)]: null,
sparkyfitness-server-1 | [Symbol(shapeMode)]: true,
sparkyfitness-server-1 | [Symbol(kCapture)]: false,
sparkyfitness-server-1 | [Symbol(kSetNoDelay)]: true,
sparkyfitness-server-1 | [Symbol(kSetKeepAlive)]: false,
sparkyfitness-server-1 | [Symbol(kSetKeepAliveInitialDelay)]: 0,
sparkyfitness-server-1 | [Symbol(kBytesRead)]: 23116,
sparkyfitness-server-1 | [Symbol(kBytesWritten)]: 6098
sparkyfitness-server-1 | },
sparkyfitness-server-1 | _keepAlive: false,
sparkyfitness-server-1 | _keepAliveInitialDelayMillis: 0,
sparkyfitness-server-1 | parsedStatements: {},
sparkyfitness-server-1 | ssl: false,
sparkyfitness-server-1 | _ending: true,
sparkyfitness-server-1 | _emitMessage: false,
sparkyfitness-server-1 | _connecting: true,
sparkyfitness-server-1 | [Symbol(shapeMode)]: false,
sparkyfitness-server-1 | [Symbol(kCapture)]: false
sparkyfitness-server-1 | },
sparkyfitness-server-1 | _queryQueue: [],
sparkyfitness-server-1 | binary: false,
sparkyfitness-server-1 | processID: 49,
sparkyfitness-server-1 | secretKey: -1507890505,
sparkyfitness-server-1 | ssl: false,
sparkyfitness-server-1 | _connectionTimeoutMillis: 0,
sparkyfitness-server-1 | _connectionCallback: null,
sparkyfitness-server-1 | saslSession: null,
sparkyfitness-server-1 | release: [Function (anonymous)],
sparkyfitness-server-1 | readyForQuery: true,
sparkyfitness-server-1 | hasExecuted: true,
sparkyfitness-server-1 | _poolUseCount: 34,
sparkyfitness-server-1 | [Symbol(shapeMode)]: false,
sparkyfitness-server-1 | [Symbol(kCapture)]: false
sparkyfitness-server-1 | }
sparkyfitness-server-1 | }
sparkyfitness-server-1 |
sparkyfitness-server-1 | Node.js v20.20.2

[BryceKrispiess]

What issues did you run into with the guide? I was successfully able to upgrade a few weeks ago, but I know others have had issues and the guide may need some tweaks.

Regarding backing up and restoring I haven't tried it that way, but seems a bit more complex.

Looks like your issue here postres terminated all connections to the DB and node had some clients idle in the DB causing node to crash. You would either need to handle the node errors within the server so node is prepared for it or reconnect to the DB after restoring so just restore the node server as part of the restore process.

The hope is we can get the guide to work for everyone so they can follow the steps easily. I know CJ was going to work on creating a video here in the coming weeks.

[BryceKrispiess]

@CodeWithCJ This is made me notice that the current backup tool does not work on postgres 18.3 I get an error when trying to use it.

The current one looks to use pg dump 15 so it would just need to be upgraded I believe.

[Scorch038]

What issues did you run into with the guide? I was successfully able to upgrade a few weeks ago, but I know others have had issues and the guide may need some tweaks.

Regarding backing up and restoring I haven't tried it that way, but seems a bit more complex.

Looks like your issue here postres terminated all connections to the DB and node had some clients idle in the DB causing node to crash. You would either need to handle the node errors within the server so node is prepared for it or reconnect to the DB after restoring so just restore the node server as part of the restore process.

The hope is we can get the guide to work for everyone so they can follow the steps easily. I know CJ was going to work on creating a video here in the coming weeks.

Some of the issues were already handled in the updated docker compose yaml file ( ie. postgresql/data to postgresql/) . I successfully dump the sql data base , however when I go to restore it it says the entries are already there. it seems to not update the DB either. Also it seems like my account does restore nor any of my entries. I haven't been able to pinpoint exactly where the error is occurring. I'll update you once I dig in. I accidentally caused an nginx issue which I was chasing all day.

UPDATE:
I got it to work by not doing "docker compose up -d" in section 5 , I just brought up the db container instead all the services, the did the following:

• ran "cat ./full_backup.sql | docker exec -i sparkyfitness-db psql -U sparky -d sparkyfitness_db"
• docker compose down sparkyfitness-db
• docker compose up ( just to check the logs)

[BryceKrispiess]

That’s so weird I had originally changed the docs to bring up all containers and not just the db container, mine db was getting overwritten without it. Seems like we may need to adjust the docs more and do some testing.

Did you have any issues with pg dump all overwriting your auth tables? I know a few have had that issue.

Feel free to submit a PR to update the docs with what worked for you.

[Scorch038]

Created PR: #1108

I did not have an issue with pg dump overwriting my auth tables.

[jsaltzman]

I followed the database upgrade today. I brought up all containers, and it looks like the server writes over your database before I was able to restore. I needed to bring up just the DB to fix this.

BUT, after I was able to get back online, I went to use the backup feature in SparkyFitness (I hadn't before, gasp), and I got an error. After some troubleshooting (Claude, sorry) it seems as though the pg_dump feature in Sparky only works with PG15 and not with PG 18.

Description:
After following the official Postgres 15 → 18.3 upgrade guide, the built-in backup feature fails with pg_dump process exited with code 1.
The root cause is that the sparkyfitness_server container ships with pg_dump version 15 (from Debian 12), but the database is now running Postgres 18.3. Postgres requires pg_dump and the server to be on the same major version.
Steps to reproduce:

Follow the official Postgres 15 → 18.3 upgrade guide
Attempt to run a backup from the SparkyFitness UI or via the scheduled backup feature

Error in server logs:
[ERROR] Backup failed: Error: pg_dump process exited with code 1
Confirmed root cause:
pg_dump: error: aborting because of server version mismatch
pg_dump: detail: server version: 18.3; pg_dump version: 15.16 (Debian 15.16-0+deb12u1)
Expected behavior: The server image should ship with a pg_dump version that matches the supported Postgres version (18.3).
Workaround: Run manual backups from the host using:
bashdocker exec -i sparkyfitness-sparkyfitness-db-1 pg_dumpall -U sparky > ~/sparkyfitness_backup_$(date +%Y%m%d).sql
Environment:

SparkyFitness server image: codewithcj/sparkyfitness_server:latest
Postgres image: postgres:18.3-alpine
Deployed via Docker Compose