如何在shell脚本中从postgresql中提取pg_backend_pid并将其传递给另一个进程? [英] How to extrace pg_backend_pid from postgresql in shell script and pass it to another process?

问题描述

我需要在命令行(或脚本)上运行 bin/psql 并将其 pg_backend_pid 打印出来，以便 pg_backend_pid 可以作为命令行参数传递给另一个进程(由 root 运行).我的问题是另一个进程需要在获得pid后运行.psql(具有相同的 pid 会话)然后在其他进程启动后运行查询.

I need to run bin/psql on the command line (or script) and print its pg_backend_pid out, so that the pg_backend_pid can be passed to another process (run by root) as command line argument. The problem for me is that the other process needs to run after it obtains the pid. The psql (with same pid session) then runs a query after the other process has started.

诀窍是 Psql 需要等到另一个进程获得 pg_backend_pid 并且它必须保持相同的会话.

The trick is that Psql needs to wait until the other process gets the pg_backend_pid and it has to remain the same session.

这可以通过 shell 脚本或 perl 来完成吗?

Can this be done through shell script or perl?

推荐答案

您需要在 bash 或 perl 中使用带有某种双向管道的协进程.在 Python 中你可以使用 os.popen2 命令；perl 还具有通过管道与子进程交互的工具.然而，如果可能的话，最好使用像 DBD::Pg 或 psycopg2 这样的本地语言数据库驱动程序.

You'll want to use a coprocess in bash, or in perl with some kind of two-way pipe. In Python you can use the os.popen2 command; perl also has facilities for interacting with subprocesses over pipes. However, it's much better to use native language database drivers like DBD::Pg or psycopg2 if at all possible.

如果您必须在 shell 中执行此操作，请参阅info bash"并搜索coprocess".

If you must do this in the shell, see "info bash" and search for "coprocess".

这是一个快速演示 bash 脚本，可帮助您入门.

Here's a quick demo bash script to get you started.

#!/bin/bash
set -e -u
DBNAME=whatever_my_db_is_called
coproc psql --quiet --no-align --no-readline --tuples-only -P footer=off --no-password "$DBNAME"
echo 'SELECT pg_backend_pid();' >&${COPROC[1]}
read -u ${COPROC[0]} backend_pid
echo "Backend PID is: ${backend_pid}"
echo "SELECT random();" >&${COPROC[1]}
read -u ${COPROC[0]} randnum
echo "q" >&${COPROC[1]}
wait %1
echo "Random number ${randnum} generated by pg backend ${backend_pid}"

psql 的参数是确保它不会因输入而暂停，不会将制表符或元字符解释为 readline 命令，并且不会漂亮地打印输出以便更容易在 shell 上进行交互.

The arguments to psql are to ensure it doesn't pause for input, doesn't interpret tabs or metachars as readline commands, and doesn't pretty-print output so it's easier to interact with on the shell.

或者，您可能根本不需要 psql，您只需要通过某种脚本与 PostgreSQL 服务器对话.如果是这种情况，使用带有 PostgreSQL 数据库接口的脚本语言会容易得多.在 Python 中，例如:

Alternately, it's possible you don't really need psql at all, you just need to talk to the PostgreSQL server via some kind of script. If that's the case, it'll be MUCH easier to just use a scripting language with a PostgreSQL database interface. In Python, for example:

#!/usr/bin/env python
import os
import sys
import psycopg2

def main():
        conn = psycopg2.connect("dbname=classads")
        curs = conn.cursor()
        curs.execute("SELECT pg_backend_pid();");
        pid = curs.fetchall()[0][0]
        # Do whatever you need to here,
        # like using os.system() or os.popen() or os.popen2() to talk to
        # system commands, using curs.execute() to talk to the database, etc.
        conn.close();

if __name__ == '__main__':
        main()

在 Perl 中，您可以使用 DBI 和 DBD::Pg 来实现类似的效果.

In Perl you can use DBI and DBD::Pg to achieve a similar effect.

这篇关于如何在shell脚本中从postgresql中提取pg_backend_pid并将其传递给另一个进程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！