Merge ~cjwatson/launchpad:update-pgkill-scripts into launchpad:master

Proposed by Colin Watson
Status: Merged
Approved by: Colin Watson
Approved revision: b166ec5666414d420f38d2047164282dafe2c6b8
Merge reported by: Otto Co-Pilot
Merged at revision: not available
Proposed branch: ~cjwatson/launchpad:update-pgkill-scripts
Merge into: launchpad:master
Diff against target: 143 lines (+34/-29)
2 files modified
utilities/pgkillactive.py (+12/-8)
utilities/pgkillidle.py (+22/-21)
Reviewer Review Type Date Requested Status
Ines Almeida Approve
Review via email: mp+448725@code.launchpad.net

Commit message

Update pgkillactive and pgkillidle scripts

Description of the change

Similar versions of these scripts exist in lp:losa-db-scripts and lp:postgresql-charm, and have been a bit more actively maintained there. However, it's still useful to have copies of these in the Launchpad tree, since the simplest way to migrate some current cron jobs from production will be to run these as part of the `launchpad-admin` charm.

Sync our versions up more closely with those maintained elsewhere, making the following changes:

 * Exclude the current process from being killed.
 * Terminate the PostgreSQL backend process remotely using `pg_terminate_backend`, rather than relying on running on the database host itself.
 * Make the `pgkillidle -u` option match either the username or the application name.
 * Update `pgkillidle` for `pg_stat_activity` changes in PostgreSQL >= 9.2.
 * Make `pgkillidle` print a little more information on the process being killed.
 * Minor changes to docstrings and help text.

To post a comment you must log in.
Revision history for this message
Ines Almeida (ines-almeida) wrote :

Looks good

review: Approve
Revision history for this message
Colin Watson (cjwatson) :

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
diff --git a/utilities/pgkillactive.py b/utilities/pgkillactive.py
index c8e5e44..803e3d1 100755
--- a/utilities/pgkillactive.py
+++ b/utilities/pgkillactive.py
@@ -3,15 +3,12 @@
3# Copyright 2009 Canonical Ltd. This software is licensed under the3# Copyright 2009 Canonical Ltd. This software is licensed under the
4# GNU Affero General Public License version 3 (see the file LICENSE).4# GNU Affero General Public License version 3 (see the file LICENSE).
55
6"""Kill transaction that have hung around for too long.6"""Kill transactions that have hung around for too long."""
7"""
87
9__all__ = []8__all__ = []
109
11import _pythonpath # noqa: F40110import _pythonpath # noqa: F401
1211
13import os
14import signal
15import sys12import sys
16from optparse import OptionParser13from optparse import OptionParser
1714
@@ -34,7 +31,10 @@ def main():
34 type="int",31 type="int",
35 dest="max_seconds",32 dest="max_seconds",
36 default=60 * 60,33 default=60 * 60,
37 help="Maximum seconds time connections are allowed to remain active.",34 help=(
35 "Connections with a transaction older than MAX_SECONDS seconds "
36 "will be killed. If 0, all matched connections will be killed."
37 ),
38 )38 )
39 parser.add_option(39 parser.add_option(
40 "-q",40 "-q",
@@ -76,7 +76,10 @@ def main():
76 """76 """
77 SELECT usename, pid, backend_start, xact_start77 SELECT usename, pid, backend_start, xact_start
78 FROM pg_stat_activity78 FROM pg_stat_activity
79 WHERE xact_start < CURRENT_TIMESTAMP - '%d seconds'::interval %s79 WHERE
80 pid <> pg_backend_pid()
81 AND xact_start < CURRENT_TIMESTAMP - '%d seconds'::interval
82 %s
80 ORDER BY pid83 ORDER BY pid
81 """84 """
82 % (options.max_seconds, user_match_sql),85 % (options.max_seconds, user_match_sql),
@@ -88,7 +91,7 @@ def main():
88 if len(rows) == 0:91 if len(rows) == 0:
89 if not options.quiet:92 if not options.quiet:
90 print("No transactions to kill")93 print("No transactions to kill")
91 return 094 return 0
9295
93 for usename, pid, backend_start, transaction_start in rows:96 for usename, pid, backend_start, transaction_start in rows:
94 print(97 print(
@@ -101,7 +104,8 @@ def main():
101 )104 )
102 )105 )
103 if not options.dry_run:106 if not options.dry_run:
104 os.kill(pid, signal.SIGTERM)107 cur.execute("SELECT pg_terminate_backend(%s)", (pid,))
108 cur.close()
105 return 0109 return 0
106110
107111
diff --git a/utilities/pgkillidle.py b/utilities/pgkillidle.py
index 9985ae8..6b6df3b 100755
--- a/utilities/pgkillidle.py
+++ b/utilities/pgkillidle.py
@@ -3,15 +3,12 @@
3# Copyright 2009 Canonical Ltd. This software is licensed under the3# Copyright 2009 Canonical Ltd. This software is licensed under the
4# GNU Affero General Public License version 3 (see the file LICENSE).4# GNU Affero General Public License version 3 (see the file LICENSE).
55
6"""Kill <IDLE> in transaction connections that have hung around for too long.6"""Kill idle-in-transaction connections that have hung around for too long."""
7"""
87
9__all__ = []8__all__ = []
109
11import _pythonpath # noqa: F40110import _pythonpath # noqa: F401
1211
13import os
14import signal
15import sys12import sys
16from optparse import OptionParser13from optparse import OptionParser
1714
@@ -64,17 +61,24 @@ def main():
64 if len(args) > 0:61 if len(args) > 0:
65 parser.error("Too many arguments")62 parser.error("Too many arguments")
6663
67 ignore_sql = " AND usename <> %s" * len(options.ignore or [])64 ignore_sql = " AND %s NOT IN (usename, application_name)" * len(
65 options.ignore or []
66 )
6867
69 con = psycopg2.connect(options.connect_string)68 con = psycopg2.connect(options.connect_string)
70 cur = con.cursor()69 cur = con.cursor()
71 cur.execute(70 cur.execute(
72 """71 """
73 SELECT usename, pid, backend_start, query_start72 SELECT
73 usename, application_name, datname, pid,
74 backend_start, state_change, AGE(NOW(), state_change) AS age
74 FROM pg_stat_activity75 FROM pg_stat_activity
75 WHERE query = '<IDLE> in transaction'76 WHERE
76 AND query_start < CURRENT_TIMESTAMP - '%d seconds'::interval %s77 pid <> pg_backend_pid()
77 ORDER BY pid78 AND state = 'idle in transaction'
79 AND state_change < CURRENT_TIMESTAMP - '%d seconds'::interval
80 %s
81 ORDER BY age
78 """82 """
79 % (options.max_idle_seconds, ignore_sql),83 % (options.max_idle_seconds, ignore_sql),
80 options.ignore,84 options.ignore,
@@ -85,20 +89,17 @@ def main():
85 if len(rows) == 0:89 if len(rows) == 0:
86 if not options.quiet:90 if not options.quiet:
87 print("No IDLE transactions to kill")91 print("No IDLE transactions to kill")
88 return 092 return 0
8993
90 for usename, pid, backend_start, query_start in rows:94 for usename, appname, datname, pid, backend, state, age in rows:
91 print(95 print(80 * "=")
92 "Killing %s(%d), %s, %s"96 print("Killing %s(%d) %s from %s:" % (usename, pid, appname, datname))
93 % (97 print(" backend start: %s" % (backend,))
94 usename,98 print(" idle start: %s" % (state,))
95 pid,99 print(" age: %s" % (age,))
96 backend_start,
97 query_start,
98 )
99 )
100 if not options.dryrun:100 if not options.dryrun:
101 os.kill(pid, signal.SIGTERM)101 cur.execute("SELECT pg_terminate_backend(%s)", (pid,))
102 cur.close()
102 return 0103 return 0
103104
104105

Subscribers

People subscribed via source and target branches

to status/vote changes: