PostgreSQL 远程管理越来越简单，6个自动化脚本开胃菜

文摘 2024-10-16 06:00 天津

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2500人左右 1 + 2 + 3 + 4 +5 + 6 + 7）(1 2 3 4 5 群均已爆满,6群超过450人关闭自由申请，新人进7群，准备开8群)

Node.js 今天开始搞这个，原因很简单，因为要写程序对数据库进行管理，但我不想使用麻烦的Python, 超高难度的Go, Node.js正是一个写脚本的好工具，且部署简单，今天我们就开始部署Node.js且用他来管理一些PostgreSQL.

如果下载太慢，是那就是力访问网站太慢，需要修改nodejs源，具体方法下面。


curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.40.0/install.sh | bash
nvm install 22
node -v # should print `v20.18.0`
npm -v # should print `10.8.2`

上面在主机上安装node.js十分方便，安装完毕后，我们通过node -v npm -v 都可以获得对应的版本信息。

在安装中需要安装依赖包，如果依赖包在官方的网站无法进行下载，可以转换镜像源

[root@nodejs ~]# nrm ls
  npm ---------- https://registry.npmjs.org/
  yarn --------- https://registry.yarnpkg.com/
* tencent ------ https://mirrors.cloud.tencent.com/npm/
  cnpm --------- https://r.cnpmjs.org/
  taobao ------- https://registry.npmmirror.com/
  npmMirror ---- https://skimdb.npmjs.com/registry/
  huawei ------- https://repo.huaweicloud.com/repository/npm/

比如我们将镜像源改为腾讯的

[root@nodejs ~]# npm config set registry https://mirrors.cloud.tencent.com/npm/
[root@nodejs ~]# npm install pg

added 1 package in 2s
[root@nodejs ~]# npm list
root@ /root
├── mongo@0.1.0
├── mysql@2.18.1
├── postgres@3.4.4
└── redis@4.7.0

可以通过npm list 来对当前已经安装的模块进行查看。

下面我们简单的写一个访问PostgreSQL数据库的脚本，来远程访问数据库且进行一些信息的查询。

vacuum 有关的脚本

1 查看PostgreSQL 数据库中所有逻辑库中3天没有进行 vacuum,autovacuum的表的列表

const { Client } = require('pg');


const config = {
    host: '192.168.198.130',
    port: 5432,
    user: 'test',
    password: 'test'
};

async function checkTablesNotVacuumed() {
    const dbClient = new Client(config);
    
    try {
      
        await dbClient.connect();

   
        const dbsRes = await dbClient.query('SELECT datname FROM pg_database WHERE datistemplate = false;');
        const databases = dbsRes.rows.map(row => row.datname);

        for (const dbName of databases) {
            const client = new Client({ ...config, database: dbName });
            await client.connect();

            try {
                // 查询没有进行过 VACUUM 或 AUTOVACUUM 操作的表
                const query = `
                    SELECT
                        schemaname,
                        relname,
                        last_vacuum,
                        last_autovacuum
                    FROM
                        pg_stat_all_tables
                    WHERE
                        (last_vacuum IS NULL OR last_vacuum < NOW() - INTERVAL '3 days') AND
                        (last_autovacuum IS NULL OR last_autovacuum < NOW() - INTERVAL '3 days');
                `;

                const res = await client.query(query);
                
       
                if (res.rows.length > 0) {
                    console.log(`数据库: ${dbName} - 以下表在过去3天没有进行过 VACUUM 或 AUTOVACUUM：`);
                    res.rows.forEach(row => {
                        console.log(`表: ${row.schemaname}.${row.relname} | 上次 VACUUM: ${row.last_vacuum} | 上次 AUTOVACUUM: ${row.last_autovacuum}`);
                    });
                } else {
                    console.log(`数据库: ${dbName} - 所有表在过去3天内都有进行 VACUUM 或 AUTOVACUUM 操作。`);
                }
            } catch (err) {
                console.error(`查询数据库 ${dbName} 时出错:`, err);
            } finally {
                await client.end();
            }
        }
    } catch (err) {
        console.error('查询数据库列表时出错:', err);
    } finally {
        await dbClient.end();
    }
}


checkTablesNotVacuumed();

其中用户名和密码需要自己改，链接地址也需要改数据库会自行进行遍历

执行后的结果

2 查找PG数据库中，那个库的那个表执行 vacuum 超过5分钟

const { Client } = require('pg');


const config = {
    host: '192.168.198.130',
    port: 5432,
    user: 'test',
    password: 'test'
};

async function checkLongRunningAutovacuum() {
    const dbClient = new Client(config);
    
    try {
     
        await dbClient.connect();

      
        const dbsRes = await dbClient.query('SELECT datname FROM pg_database WHERE datistemplate = false;');
        const databases = dbsRes.rows.map(row => row.datname);

        for (const dbName of databases) {
            const client = new Client({ ...config, database: dbName });
            await client.connect();

            try {
               
                const query = `
                    SELECT
                        p.relid::regclass AS table_name,
                        a.pid,
                        a.query_start,
                        now() - a.query_start AS duration
                    FROM
                        pg_stat_progress_vacuum p
                    JOIN
                        pg_stat_activity a ON p.pid = a.pid
                    WHERE
                        now() - a.query_start > interval '5 minutes';
                `;

                const res = await client.query(query);
                
               
                if (res.rows.length > 0) {
                    console.log(`数据库: ${dbName} - 以下表的 AUTOVACUUM 操作时间超过 5 分钟：`);
                    res.rows.forEach(row => {
                        console.log(`表: ${row.table_name} | 进程 ID: ${row.pid} | 开始时间: ${row.query_start} | 持续时间: ${row.duration}`);
                    });
                } else {
                    console.log(`数据库: ${dbName} - 没有表的 AUTOVACUUM 操作时间超过 5 分钟。`);
                }
            } catch (err) {
                console.error(`查询数据库 ${dbName} 时出错:`, err);
            } finally {
                await client.end();
            }
        }
    } catch (err) {
        console.error('查询数据库列表时出错:', err);
    } finally {
        await dbClient.end();
    }
}


checkLongRunningAutovacuum();

执行结果

与PostgreSQL权限有关的脚本

3 查询数据库中所有表的权限

const { Client } = require('pg');  

const config = {
    host: '192.168.198.130',
    port: 5432,
    user: 'test',
    password: 'test'
};

async function checkTablePrivileges() {
    const dbClient = new Client(config);
    
    try {
       
        await dbClient.connect();

      
        const dbsRes = await dbClient.query('SELECT datname FROM pg_database WHERE datistemplate = false;');
        const databases = dbsRes.rows.map(row => row.datname);

        for (const dbName of databases) {
            const client = new Client({ ...config, database: dbName });
            await client.connect();

            try {
             
                const query = `
                    SELECT 
                        table_schema, 
                        table_name, 
                        grantee, 
                        privilege_type 
                    FROM 
                        information_schema.table_privileges 
                    ORDER BY 
                        table_schema, 
                        table_name, 
                        grantee;
                `;

                const res = await client.query(query);
                
              
                if (res.rows.length > 0) {
                    console.log(`数据库: ${dbName} - 表权限信息：`);
                    res.rows.forEach(row => {
                        console.log(`模式: ${row.table_schema} | 表: ${row.table_name} | 用户: ${row.grantee} | 权限: ${row.privilege_type}`);
                    });
                } else {
                    console.log(`数据库: ${dbName} - 没有权限信息。`);
                }
            } catch (err) {
                console.error(`查询数据库 ${dbName} 时出错:`, err);
            } finally {
                await client.end();
            }
        }
    } catch (err) {
        console.error('查询数据库列表时出错:', err);
    } finally {
        await dbClient.end();
    }
}


checkTablePrivileges();

结果如下

4 查询赋值账号对表的权限占有的情况,这个脚本会比较复杂，且也是很多DBA搞不定的一个问题，比如你有A用户建立A 库，后续grant A to B 用户，B 用户，那么我咱们查出来与A用户有关联的所有数据库用户且这些用户对A库的那些表有权限，是从A用户赋予的权限上来的。

const { Client } = require('pg');


const roleName = process.argv[2]; 


if (!roleName) {
    console.error('请提供角色名称作为命令行参数，例如: node your_script.js 赋值权限账号');
    process.exit(1);  // 退出程序
}


const config = {
    host: '192.168.198.130',
    port: 5432,
    user: 'test',
    password: 'test',
    database: 'testdb'  // 指定你要连接的数据库
};


async function checkRolePrivileges() {
    const client = new Client(config);

    try {
       
        await client.connect();
        console.log(`已成功连接到 PostgreSQL 数据库，正在查询角色 ${roleName} 的权限信息`);

      
        const query = `
            WITH inherited_roles AS (
                SELECT r1.rolname AS master_role,
                       r2.rolname AS member_role
                FROM pg_roles AS r1
                JOIN pg_auth_members m ON r1.oid = m.roleid
                JOIN pg_roles r2 ON m.member = r2.oid
                WHERE r1.rolname = $1
            )
            SELECT t_grant_table.grantee AS user_name,
                   ir.member_role AS member,
                   t_grant_table.table_catalog AS database_name,
                   t_grant_table.table_schema,
                   t_grant_table.table_name,
                   t_grant_table.privilege_type
            FROM information_schema.role_table_grants t_grant_table
            JOIN inherited_roles ir ON t_grant_table.grantee = ir.master_role;
        `;

        const res = await client.query(query, [roleName]);
        
      
        if (res.rows.length > 0) {
            console.log('以下是角色权限信息：');
            res.rows.forEach(row => {
                console.log(`用户: ${row.user_name} | 成员: ${row.member} | 数据库: ${row.database_name} | 模式: ${row.table_schema} | 表: ${row.table_name} | 权限: ${row.privilege_type}`);
            });
        } else {
            console.log('没有找到相关权限信息。');
        }
    } catch (err) {
        console.error('查询角色权限时出错:', err);
    } finally {
       
        await client.end();
        console.log('数据库连接已关闭');
    }
}


checkRolePrivileges();

执行的结果，这里注意参数给的是system，这里在PG数据库中建立了system账号，且建立了一个数据库叫system,system对system数据库具有owner的权限，同时我们建立一个数据库账号叫nomarl，我们使用了grant system to nomarl,然后我们想查询当前与system 账号有关的连的账号是那些，且他们都对那些表有权限，什么权限，就可以通过下面的脚本进行查询了。

与连接有关的

5 清理超过你指定时间的数据库连接


const { Client } = require('pg');


const database = process.argv[2]; // 数据库名
const idleTime = process.argv[3]; // 时间限制，如 '5 minutes'
const user = process.argv[4];     // 可选的用户名


if (!database || !idleTime) {
    console.error('请提供数据库名称和时间限制，例如：node clean_connections.js <database> <time> [<user>]');
    process.exit(1);
}


const config = {
    host: '192.168.198.130',
    port: 5432,
    user: 'test',  
    password: 'test',  
    database: 'postgres' 
};


async function cleanIdleConnections() {
    const client = new Client(config);

    try {
    
        await client.connect();


        let query = `
            SELECT pid, usename, state, backend_start, query_start, state_change
            FROM pg_stat_activity
            WHERE datname = $1
            AND (now() - query_start) > $2::interval
        `;

        const params = [database, idleTime];
        if (user) {
            query += ` AND usename = $3`;
            params.push(user);
        }

     
        const res = await client.query(query, params);

      
        if (res.rows.length > 0) {
            console.log(`以下连接超过 ${idleTime} 被清理:`);
            for (const row of res.rows) {
                console.log(`终止连接：PID: ${row.pid}, 用户: ${row.usename}, 开始时间: ${row.backend_start}`);

          
                await client.query(`SELECT pg_terminate_backend($1)`, [row.pid]);
            }
        } else {
            console.log(`没有发现连接超过 ${idleTime} 的用户。`);
        }

    } catch (err) {
        console.error('执行查询时出错:', err);
    } finally {
       
        await client.end();
    }
}


cleanIdleConnections();

这个脚本的功能是通过执行带有参数的命令，直接将指定的数据库的超过预定时间的连接，直接termial,通过这样的方法满足管理者的需求。

具体执行的效果图

命令方式，node kill_conncion.js 数据库名 ‘时间’ 用户名（可选）

6 查询当前数据库指定数据库长时间运行语句的连接

const { Client } = require('pg');


const databaseName = process.argv[2]; 
const timeLimit = process.argv[3];   


const config = {
    host: '192.168.198.130',  
    port: 5432,              
    user: 'test',       
    password: 'test' 
};


const client = new Client({
    ...config,
    database: databaseName     
});


async function checkLongRunningQueries() {
    try {
        await client.connect();

        const query = `
            SELECT
                pid,
                usename AS username,
                query,
                state,
                now() - query_start AS duration,
                query_start
            FROM
                pg_stat_activity
            WHERE
                state = 'active' 
                AND now() - query_start > $1::interval
                AND query != '<IDLE>' 
            ORDER BY
                duration DESC;
        `;

     
        const res = await client.query(query, [timeLimit]);

        if (res.rows.length > 0) {
            console.log(`查询到执行时间超过 ${timeLimit} 的SQL语句：`);
            res.rows.forEach(row => {
                console.log(`PID: ${row.pid} | 用户: ${row.username} | 执行时间: ${row.duration} | 开始时间: ${row.query_start} | SQL: ${row.query}`);
            });
        } else {
            console.log(`没有查询到执行时间超过 ${timeLimit} 的 SQL 语句。`);
        }
    } catch (err) {
        console.error('查询出错:', err);
    } finally {
        await client.end();
    }
}


checkLongRunningQueries();