An Example To Connect To Remote Server And Download PostgreSQL Log File

fetch_log.js

const path = require('path');

const Ssh = require('node-ssh');
const moment = require('moment');
const tar = require('tar');

async function downloadSlowQueryLog(sshInfo, sshPassword, remoteSlowQueryLogDir, slowQueryLogFileName, localSlowQueryLogFilePath) {
    const ssh = new Ssh();

    const remoteSlowQueryLogFilePath = remoteSlowQueryLogDir + '/' + slowQueryLogFileName;

    // Connect to remote server
    await ssh.connect(sshInfo);

    // Compress log file
    await ssh.execCommand('sudo tar zcf ' + slowQueryLogFileName + '.tgz ' + slowQueryLogFileName, {stdin: sshPassword + '\n', cwd: remoteSlowQueryLogDir, options: {pty: true}});

    // Download log file
    await ssh.getFile(localSlowQueryLogFilePath + '.tgz', remoteSlowQueryLogFilePath + '.tgz');

    // Remove compressed log file from server
    await ssh.execCommand('sudo rm ' + slowQueryLogFileName + '.tgz', {stdin: sshPassword + '\n', cwd: remoteSlowQueryLogDir, options: {pty: true}});

    // Disconect from server
    await ssh.dispose();
}

async function main() {

    const sshPassword = 'SSH_PASSWORD';
    const sshInfo = {
        host: 'SSH_HOST',
        port: SSH_PORT,
        username: 'SSH_USER',
        password: sshPassword
    };

    const ts = moment().format('YYYYMMDDHHmmss');

    const slowQueryLogFileName = 'postgresql-Wed.log';
    const remoteSlowQueryLogDir = '/path/to/log/dir';
    const localSlowQueryLogDir = 'C:/path/to/local/log/dir';
    const localSlowQueryFileName = ts + '_' + slowQueryLogFileName;
    const localSlowQueryLogFilePath = path.join(localSlowQueryLogDir, localSlowQueryFileName);

    await downloadSlowQueryLog(sshInfo, sshPassword, remoteSlowQueryLogDir, slowQueryLogFileName, localSlowQueryLogFilePath);
    console.log('slow query log download completed.');

    // Decompress downloaded log file
    await tar.x({
        file: localSlowQueryLogFilePath + '.tgz',
        cwd: localSlowQueryLogDir
    });

    console.log('untar completed.');
}

main();