I'm trying to use node.js and pg to write data from a message buffer to a postgres table.
This works pretty good, until postgres is restarted. Then the script fails and terminates.
As I'm quite new to js maybe you have some hints on how to proceed and what I'm doing wrong. My goal is to have a script which automatically reconnects if the connection is lost.
This is not the full code but hopefully includes all necessary information:
const { Pool } = require('pg');
const pgPoolConfig = {
host: 'host',
user: 'user',
database: 'database',
port: 5432,
connectionTimeoutMillis: 5000,
idleTimeoutMillis: 10000,
max: 10,
};
const pool = new Pool(pgPoolConfig);
pool.on('error', (err, client) => {
console.error('Unexpected error on idle client', err);
client.release();
setTimeout(connectToDatabase, 5000);
});
pool.on('release', (err, client) => {
if (!err) {
console.log('Client released');
} else {
console.error('Error releasing client');
}
});
function connectToDatabase() {
pool.connect()
.then((client, done) => {
console.log('Connected to database');
buffer2db(client, done);
})
.catch((err) => {
console.error('Error connecting to database', err);
setTimeout(connectToDatabase, 5000); // Retry after 5 seconds
});
}
function buffer2db(client, done) {
if (messageBuffer.length === 0) {
// console.log('Message Buffer is empty');
setTimeout(() => buffer2db(client, done), 10); // Check queue again after 10 ms
return;
}
const entry = messageBuffer.shift(); // Dequeue an entry from the queue
client.query('INSERT INTO database.test (text_field) VALUES ($1)', [entry.message])
.then(result => {
console.log('Entry inserted successfully:', entry);
setTimeout(() => buffer2db(client, done), 10); // Check queue again after 10 ms
})
.catch(err => {
console.error('Error executing query', err);
});
}
connectToDatabase();
Error message:
node:events:497
throw er; // Unhandled 'error' event
^
error: terminating connection due to administrator command
at Parser.parseErrorMessage (/home/user/scripts/node_modules/pg-protocol/dist/parser.js:287:98)
at Parser.handlePacket (/home/user/scripts/node_modules/pg-protocol/dist/parser.js:126:29)
at Parser.parse (/home/user/scripts/node_modules/pg-protocol/dist/parser.js:39:38)
at Socket.<anonymous> (/home/user/scripts/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (node:events:519:28)
at addChunk (node:internal/streams/readable:559:12)
at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
at Readable.push (node:internal/streams/readable:390:5)
at Pipe.onStreamRead (node:internal/stream_base_commons:190:23)
Emitted 'error' event on Client instance at:
at Client._handleErrorEvent (/home/user/scripts/node_modules/pg/lib/client.js:341:10)
at Client._handleErrorMessage (/home/user/scripts/node_modules/pg/lib/client.js:352:12)
at Connection.emit (node:events:519:28)
at /home/user/scripts/node_modules/pg/lib/connection.js:117:12
at Parser.parse (/home/user/scripts/node_modules/pg-protocol/dist/parser.js:40:17)
at Socket.<anonymous> (/home/user/scripts/node_modules/pg-protocol/dist/index.js:11:42)
[... lines matching original stack trace ...]
at Readable.push (node:internal/streams/readable:390:5) {
length: 116,
severity: 'FATAL',
code: '57P01',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'postgres.c',
line: '3204',
routine: 'ProcessInterrupts'
}
If postgres is not running when the script is started but is started afterwards, everything works and a connection is established.
Thanks for your ideas and help!