How to extrace pg_backend_pid from postgresql in shell script and pass it to another process?

by

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.

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

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}"

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.

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()

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

More Related Contents:

  1. How to use shell variables in perl command call in a bash shell script?
  2. Explain the deviousness of the Perl “preamble”
  3. Converting relative path into absolute path?
  4. How can i do this in perl, shortcut truthtable [closed]
  5. How to retrieve the values of required keys in Perl [closed]
  6. Perl: Meaning of double squared brackets after a string?
  7. What does this HTML::Parser() code do in Perl? [closed]
  8. How to print lines between two patterns, inclusive or exclusive (in sed, AWK or Perl)?
  9. Why use strict and warnings?
  10. Why does modern Perl avoid UTF-8 by default?
  11. Why is three-argument open calls with autovivified filehandles a Perl best practice?
  12. What’s the difference between Perl’s backticks, system, and exec?
  13. How can I parse dates and convert time zones in Perl?
  14. Which one is good practice, a lexical filehandle or a typeglob?
  15. How do I tell if a variable has a numeric value in Perl?
  16. Perl memory usage profiling and leak detection?
  17. How should I use the “my” keyword in Perl?
  18. Global symbol requires explicit package name
  19. How do I include functions from another file in my Perl script?
  20. How can I start an interactive console for Perl?
  21. What does =~ do in Perl? [closed]
  22. Using my with parentheses and only one variable
  23. How to convert letters with accents, umlauts, etc to their ASCII counterparts in Perl?
  24. How do I tell what type of value is in a Perl variable?
  25. What should I use for a Perl script’s shebang line?
  26. How can I find memory leaks in long-running Perl program?
  27. Why do Perl variables need to start with $, %,@ (sigils)?
  28. Print lines from one file that are not contained in another file
  29. Perl DBD::Oracle Module installation
  30. Why doesn’t Perl’s each() iterate through the entire hash the second time?

Leave a Comment