-
Notifications
You must be signed in to change notification settings - Fork 0
/
pg_report.py
2248 lines (1949 loc) · 121 KB
/
pg_report.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
#!/usr/bin/env python3
#!/usr/bin/env python2
#!/usr/bin/env python
#!/usr/bin/python
###############################################################################
### COPYRIGHT NOTICE FOLLOWS. DO NOT REMOVE
###############################################################################
### Copyright (c) 2016 - 2022 SQLEXEC LLC
###
### Permission to use, copy, modify, and distribute this software and its
### documentation for any purpose, without fee, and without a written agreement
### is hereby granted, provided that the above copyright notice and this paragraph
### and the following two paragraphs appear in all copies.
###
### IN NO EVENT SHALL SQLEXEC LLC BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,
### INDIRECT SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS,
### ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
### SQLEXEC LLC HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
###
### SQLEXEC LLC SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
### LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
### PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS,
### AND SQLEXEC LLC HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES,
### ENHANCEMENTS, OR MODIFICATIONS.
###
###############################################################################
#
# Original Author: Michael Vitale, [email protected]
#
# Description: This python utility program performs PostgreSQL maintenance tasks.
#
# Inputs: all fields are optional except database.
# -h <hostname or IP address>
# -d <database>
# -n <schema>
# -p <PORT>
# -U <db user>
# -m [html format flag]
# -r [dry run flag]
# -v [verbose output flag, mostly used for debugging]
#
# Examples: run report on entire test database and output in web format
# ./pg_report.py -d dvdrental --html --dryrun
#
# Requirements:
# 1. python 2.6+ or 3.x
# 2. psql client
# 3. psutil for windows only: https://pypi.python.org/pypi?:action=display&name=psutil#downloads
# (fyi for gettting it on linux but not required: apt-get install python-psutil or yum install python-psutil)
#
# Download: git clone https://github.com/commandprompt/pg_report.git pg_report
#
# Assumptions:
# 1. db user defaults to postgres if not provided as parameter.
# 2. Max rows defaults to 10 million if not provided as parameter
# 3. Password must be in local .pgpass file or client authentication changed to trust or peer
# 4. psql must be in the user's path
# 5. Load detection assumes that you are running this script from the database host.
# 6. Make sure timing and pager are turned off (see .psqlrc)
#
# Cron Job Info:
# View cron job output: view /var/log/cron
# source the database environment: source ~/db_catalog.ksh
# Example cron job that does smart vacuum freeze commands for entire database every Saturday at 4am:
# * 4 * * 6 /usr/bin/python /var/lib/pgsql/pg_tools/pg_report.py -d evergreen -p 5432 --html >> /var/lib/pgsql/pgtools/pg_report_`/bin/date +'\%Y\%m\%d'`.log 2>&1
#
# NOTE: You may have to source the environment variables file in the crontab to get this program to work.
# #!/bin/bash
# source /home/user/.bash_profile
#
# Report logic:
# 1. Get database conflicts, deadlocks, and temp_files.
# 2. Unused indexes are identified where there are less than 20 index scans and thee size of the table is > 100 MB.
# 3. Bloated tables/indexes are identified where at least 20% of the table/index is bloated or the wasted bytes is > 1GB.
# 4. See if archiving is getting behind by more than 1000 WAL files.
# 5. Contrast PG memory configuration to recommended ones
# 6. Identify orphaned large objects.
# 7. List tables getting close to transaction wraparound (more than halfway to max freeze threshold).
# 8. list tables that have not been analyzed or vacuumed in the last 60 days or whose size has grown significantly.
#
# TODOs:
#
#
# History:
# who did it Date did what
# ========== ========= ==============================
# Michael Vitale 01/12/2016 Original coding using python 2.7.x on windows 8.1 and ubuntu 14.04 (pg 9.4)
# Michael Vitale 01/13/2016 Finished porting code from bash script, pg_refreshstats.sh
# Michael Vitale 01/14/2016 First crack at incorporated logic for report action.
# Michael Vitale 01/17/2016 Implemented report output in html
# Michael Vitale 01/18/2016 Fixed a bunch of bugs with html reporting
# Michael Vitale 01/20/2016 Removed linux dependency on psutils module.
# Enhanced unused indexes report to query slaves if available
# Michael Vitale 01/21/2016 Fixed bugs, normalized html output, added connection and locking report
# Michael Vitale 01/23/2016 Reworked html output to display health check at top of page and lists at the bottom.
# Michael Vitale 01/25/2016 Added more health check items: writers, network standbys. Implemented logic related to
# checkpoint, background and backend writers and the pg_stat_bgwriter table.
# Michael Vitale 01/27/2016 loop over tables being worked on, instead of executing
# them in batch: analyze, vacuum analyze, and vacuum freeze actions.
# Michael Vitale 01/28/2016 Fixed python piping to use bash as default shell
# Michael Vitale 10/04/2017 Fix queries based on PG versions since 9.5 (ie 9.6 and 10)
# Michael Vitale 10/16/2020 Qualify vacuumlo command with port number. It had assumed default, 5432
# Michael Vitale 12/08/2020 Majore rewrite: converted pg_maint health check portion to replace old pg_report that also did vacuum actions
# Michael Vitale 12/09/2020 Added new functionality and made changes to be more compatible across Python versions 2 and 3:
# Changed "<>" to <!=>
# Changed print "whatever" to print ("whatever")
# Removed Psycopg2 exception handling and replaced with general one.
# shell command can return an empty byte string in python3--> b'', so check for that as well.
# Michael Vitale 01/01/2021 v2.1 new features: Connection time avg, PG major/minor version evaluation
# v2.1 changes: store major and minor pg versions, not just major version for new logic coming
# v2.1 fixes: Fixed check against 9.6 version for wal directoy location.
# Michael Vitale 01/12/2021 v2.1 fix: Heroku instances should be treated like rds ones
# Michael Vitale 09/06/2021 v2.2 fix for print commands using parens; don't consider walsender for waiting/blocked queries
# Michael Vitale 05/29/2022 v2.3 Check local load
# Michael Vitale 06/23/2022 v2.4 Bug fixes. Do not check local resources for remote DB servers. Updated latest versions of PG.
################################################################################################################
import string, sys, os, time
#import datetime
from datetime import datetime
from datetime import date
import tempfile, platform, math
from decimal import *
import smtplib
import subprocess
from subprocess import Popen, PIPE
from optparse import OptionParser
import getpass
#############################################################################################
#globals
SUCCESS = 0
ERROR = -1
ERROR2 = -2
ERROR3 = -3
WARNING = -4
DEFERRED = 1
NOTICE = 2
TOOLONG = 3
HIGHLOAD = 4
DESCRIPTION="This python utility program performs a basic health check for a PostgreSQL cluster."
VERSION = 2.4
PROGNAME = "pg_report"
ADATE = "June 23, 2022"
PROGDATE = "2022-06-23"
MARK_OK = "[ OK ] "
MARK_WARN = "[WARN] "
#############################################################################################
########################### class definition ################################################
#############################################################################################
class maint:
def __init__(self):
self.dateprogstr = PROGDATE
self.dateprog = datetime.strptime(PROGDATE, "%Y-%m-%d")
self.datenowstr = datetime.now().strftime("%Y-%m-%d")
self.datenow = datetime.today()
self.datediff = self.datenow - self.dateprog
self.action = ''
self.dbhost = ''
self.dbport = 5432
self.dbuser = ''
self.database = ''
self.dryrun = False
self.verbose = False
self.connected = False
self.fout = ''
self.connstring = ''
self.local = False
self.actstring = ''
self.schemaclause = ' '
self.pid = os.getpid()
self.opsys = ''
self.tempdir = tempfile.gettempdir()
self.workfile = ''
self.workfile_deferred = ''
self.tempfile = ''
self.reportfile = ''
self.dir_delim = ''
self.totalmemGB = -1
self.pgbindir = ''
self.pgversionmajor = Decimal('0.0')
self.pgversionminor = '0.0'
self.html_format = False
self.programdir = ''
self.imageURL = "https://cloud.githubusercontent.com/assets/12436545/12725212/7a1a27be-c8df-11e5-88a6-4e6a88004daa.jpg"
self.slaves = []
self.slavecnt = 0
self.in_recovery = False
self.bloatedtables = False
self.unusedindexes = False
self.freezecandidates = False
self.analyzecandidates = False
self.timestartmins = time.time() / 60
self.loadthreshold = 70.0
# db config stuff
self.archive_mode = ''
self.max_connections = -1
self.datadir = ''
self.shared_buffers = -1
self.work_mem = -1
self.maint_work_mem = -1
self.eff_cache_size = -1
self.shared_preload_libraries = ''
self.pg_type = 'community'
self.overcommit_memory = -1
self.overcommit_ratio = -1
###########################################################
def set_dbinfo(self, dbhost, dbport, dbuser, database, schema, html_format, dryrun, verbose, argv):
self.dbhost = dbhost
self.dbport = dbport
self.dbuser = dbuser
self.database = database
self.schema = schema
self.html_format = html_format
self.dryrun = dryrun
self.verbose = verbose
# process the schema or table elements
total = len(argv)
cmdargs = str(argv)
if os.name == 'posix':
self.opsys = 'posix'
self.dir_delim = '/'
elif os.name == 'nt':
self.opsys = 'nt'
self.dir_delim = '\\'
else:
return ERROR, "Unsupported platform."
self.workfile = "%s%s%s_stats.sql" % (self.tempdir, self.dir_delim, self.pid)
self.workfile_deferred = "%s%s%s_stats_deferred.sql" % (self.tempdir, self.dir_delim, self.pid)
self.tempfile = "%s%s%s_temp.sql" % (self.tempdir, self.dir_delim, self.pid)
if self.html_format:
self.reportfile = "%s%s%s_report.html" % (self.tempdir, self.dir_delim, self.pid)
else:
self.reportfile = "%s%s%s_report.txt" % (self.tempdir, self.dir_delim, self.pid)
# construct the connection string that will be used in all database requests
# do not provide host name and/or port if not provided
if self.dbhost != '':
self.connstring = " -h %s " % self.dbhost
if self.database != '':
self.connstring += " -d %s " % self.database
if self.dbport != '':
self.connstring += " -p %s " % self.dbport
if self.dbuser != '':
self.connstring += " -U %s " % self.dbuser
if self.schema != '':
self.schemaclause = " and n.nspname = '%s' " % self.schema
# check if local connection for automatic checking of cpus, mem, etc.
if 'localhost' in self.dbhost or '127.0.0.1' in self.dbhost or dbhost == '':
# appears to be local host
self.local = True
if self.verbose:
print ("The total numbers of args passed to the script: %d " % total)
print ("Args list: %s " % cmdargs)
print ("connection string: %s Using localhost?(%r)." % (self.connstring, self.local))
self.programdir = sys.path[0]
# Make sure psql is in the path
if self.opsys == 'posix':
cmd = "which psql"
else:
# assume windows
cmd = "where psql"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "Unable to determine if psql is in path. rc=%d results=%s" % (rc,results)
return rc, errors
if 'psql' not in results:
msg = "psql must be in the path. rc=%d, results=%s" % (rc, results)
return ERROR, msg
pos = results.find('psql')
if pos > 0:
self.pgbindir = results[0:pos]
rc, results = self.get_configinfo()
if rc != SUCCESS:
errors = "rc=%d results=%s" % (rc,results)
return rc, errors
# get total memory total memory is in bytes
if self.local:
self.totalmemGB = self.get_physicalmem()
self.overcommit_memory, self.overcommit_ratio = self.get_kernelmemorycapacity()
# get pg bind directory from pg_config
rc, results = self.get_pgbindir()
if rc != SUCCESS:
errors = "rc=%d results=%s" % (rc,results)
return rc, errors
rc, results = self.get_pgversion()
if rc != SUCCESS:
return rc, results
# Validate parameters
rc, errors = self.validate_parms()
if rc != SUCCESS:
return rc, errors
return SUCCESS, ''
###########################################################
def validate_parms(self):
if self.database == '':
return ERROR, "Database not provided."
return SUCCESS, ""
###########################################################
def get_physicalmem(self):
if self.opsys == 'posix':
cmd = "free -g | grep Mem: | /usr/bin/awk '{ total=$2; } END { print \"total=\" total }'"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "unable to get Total Physical Memory. rc=%d %s\n" % (rc, results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
#print ("rc=%d results=%s" % (rc,results))
results = results.split('=')
totalmem_prettyGB = int(results[1].strip())
else:
# must be windows, nt
from psutil import virtual_memory
mem = virtual_memory()
totalmem_prettyGB = mem.total / (1024*1024*1024)
if self.verbose:
print (" total physical memory: %s GB" % totalmem_prettyGB)
return totalmem_prettyGB
###########################################################
def get_kernelmemorycapacity(self):
overcommit_memory = -1
overcommit_ratio = -1
if self.opsys == 'posix':
cmd = "cat /proc/sys/vm/overcommit_memory"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "unable to get overcommit_memory. rc=%d %s\n" % (rc, results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
overcommit_memory = int(results[0].strip())
cmd = "cat /proc/sys/vm/overcommit_ratio"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "unable to get overcommit_ratio. rc=%d %s\n" % (rc, results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
overcommit_ratio = int(results.strip())
else:
# must be windows, nt
# and we don't do windows at the current time
return -1, -1
if self.verbose:
print ("overcommit_memory: %s overcommit_ratio: %s" % (overcommit_memory, overcommit_ratio))
return overcommit_memory, overcommit_ratio
###########################################################
def cleanup(self):
if self.connected:
# do something here later if we enable a db driver
self.connected = false
# print ("deleting temp file: %s" % self.tempfile)
try:
os.remove(self.tempfile)
except OSError:
pass
return
###########################################################
def getnow(self):
now = datetime.now()
adate = str(now)
parts = adate.split('.')
return parts[0]
###########################################################
def getfilelinecnt(self, afile):
return sum(1 for line in open(afile))
###########################################################
def convert_humanfriendly_to_MB(self, humanfriendly):
# assumes input in form: 10GB, 500 MB, 200 KB, 1TB
# returns value in megabytes
hf = humanfriendly.upper()
valueMB = -1
if 'TB' in (hf):
pos = hf.find('TB')
valueMB = int(hf[0:pos]) * (1024*1024)
elif 'GB' in (hf):
pos = hf.find('GB')
value = hf[0:pos]
valueMB = int(hf[0:pos]) * 1024
elif 'MB' in (hf):
pos = hf.find('MB')
valueMB = int(hf[0:pos]) * 1
elif 'KB' in (hf):
pos = hf.find('KB')
valueMB = round(float(hf[0:pos]) / 1024, 2)
valuefloat = "%.2f" % valueMB
return Decimal(valuefloat)
###########################################################
def writeout(self,aline):
if self.fout != '':
aline = aline + "\r\n"
self.fout.write(aline)
else:
# default to standard output
print (aline)
return
###########################################################
def get_configinfo(self):
sql = "show all"
cmd = "psql %s -t -c \"%s\" > %s" % (self.connstring, sql, self.tempfile)
rc, results = self.executecmd(cmd, False)
if rc != SUCCESS:
# let calling function report the error
errors = "Unable to get config info: %d %s\ncommand=%s\n" % (rc, results, cmd)
#aline = "%s" % (errors)
#self.writeout(aline)
return rc, errors
f = open(self.tempfile, "r")
lineno = 0
count = 0
for line in f:
lineno = lineno + 1
aline = line.strip()
if len(aline) < 1:
continue
# v2.2 fix: things like "Timing is On" can appear as a line so bypass
if aline == 'Administrative queries:' or aline == 'Timing is on.' or aline == 'Timing is off.' or aline == 'Pager usage is off.' or aline == 'Pager is used for long output.' or ':activity' in aline or 'Time: ' in aline:
continue
# print ("DEBUG: aline=%s" % (aline))
fields = aline.split('|')
name = fields[0].strip()
setting = fields[1].strip()
#print ("name=%s setting=%s" % (name, setting))
if name == 'data_directory':
self.datadir = setting
# for pg rds version, 9.6, "show all" command does not have shared_preload_libraries! so rely on data_directory instead
if 'rdsdbdata' in self.datadir:
# could be rds or aurora
self.pg_type = 'rds'
# heroku indicator using aws in the background, also used for aws ec2! so change logic to imply community
elif self.datadir == '/database':
# self.pg_type = 'rds'
self.pg_type = 'community'
elif name == 'archive_mode':
self.archive_mode = setting
elif name == 'max_connections':
self.max_connections = int(setting)
elif name == 'shared_buffers':
# shared_buffers in 8kilobytes units from select from pg_settings, so convert to megabytes, but show gives user friendly form (10GB, 10MB, 10KB, etc.)
# self.shared_buffers = int(setting) / 8192
rc = self.convert_humanfriendly_to_MB(setting)
self.shared_buffers = rc
elif name == 'maintenance_work_mem':
# maintenance_work_mem in kilobytes units from select from pg_settings, so convert to megabytes, but show gives user friendly form (10GB, 10MB, 10KB, etc.)
# self.maint_work_mem = int(setting) / 1024
rc = self.convert_humanfriendly_to_MB(setting)
self.maint_work_mem = rc
elif name == 'work_mem':
# work_mem in kilobytes units from select from pg_settings, so convert to megabytes, but show gives user friendly form (10GB, 10MB, 10KB, etc.)
#self.work_mem = int(setting) / 1024
rc = self.convert_humanfriendly_to_MB(setting)
self.work_mem = rc
elif name == 'effective_cache_size':
# effective_cache_size in 8 kilobytes units from select from pg_settings, so convert to megabytes, but show gives user friendly form (10GB, 10MB, 10KB, etc.)
rc = self.convert_humanfriendly_to_MB(setting)
self.eff_cache_size = rc
elif name == 'shared_preload_libraries':
# we only care that it is loaded, not necessarily created
# for pg rds version, 9.6, "show all" command does not have shared_preload_libraries! so rely on data_directory instead
self.shared_preload_libraries = setting
if 'rdsutils' in self.shared_preload_libraries:
self.pg_type = 'rds'
elif name == 'rds.extensions':
self.pg_type = 'rds'
f.close()
if self.verbose:
print ("shared_buffers = %d maint_work_mem = %d work_mem = %d shared_preload_libraries = %s" % (self.shared_buffers, self.maint_work_mem, self.work_mem, self.shared_preload_libraries))
return SUCCESS, results
###########################################################
def executecmd(self, cmd, expect):
# NOTE: try and catch does not work for Popen
try:
# Popen(args, bufsize=0, executable=None, stdin=None, stdout=None, stderr=None, preexec_fn=None, close_fds=False, shell=False, cwd=None, env=None, universal_newlines=False, startupinfo=None, creationflags=0)
if self.opsys == 'posix':
p = Popen(cmd, shell=True, stdout=PIPE, stderr=PIPE, executable="/bin/bash")
else:
p = Popen(cmd, shell=True, stdout=PIPE, stderr=PIPE)
values2, err2 = p.communicate()
except exceptions.OSError as e:
if self.verbose:
print ("exceptions.OSError Error",e)
return ERROR, "Error(1)"
except BaseException as e:
if self.verbose:
print ("BaseException Error",e)
return ERROR, "Error(2)"
except OSError as e:
if self.verbose:
print ("OSError Error", e)
return ERROR, "Error(3)"
except RuntimeError as e:
if self.verbose:
print ("RuntimeError", e)
return ERROR, "Error(4)"
except ValueError as e:
if self.verbose:
print ("Value Error", e)
return ERROR, "Error(5)"
except Exception as e:
if self.verbose:
print ("General Exception Error", e)
return ERROR, "Error(6)"
except:
if self.verbose:
print ("Unexpected error:", sys.exc_info()[0])
return ERROR, "Error(7)"
if err2 is None or len(err2) == 0:
err = ""
else:
# python 3 returns values and err in byte format so convert accordingly
err = bytes(err2).decode('utf-8')
if values2 is None or len(values2) == 0:
values = ""
else:
# python 3 returns values and err in byte format so convert accordingly
values = bytes(values2).decode('utf-8')
values = values.strip()
rc = p.returncode
#if self.verbose:
# print ("rc=%d values=***%s*** errors=***%s***" % (rc, values, err))
if rc == 1 or rc == 2:
return ERROR2, err
elif rc == 127:
return ERROR2, err
elif err != "":
# do nothing since INFO information is returned here for analyze commands
# return ERROR, err
return SUCCESS, err
elif values == "" and expect == True:
return ERROR2, values
elif rc != SUCCESS:
return rc, err
elif values == "" and expect:
return ERROR3, 'return set is empty'
else:
return SUCCESS, values
###########################################################
def get_pgversion(self):
# v 2.1 fix: expected output --> 10.15-10.
#sql = "select substring(foo.version from 12 for 3) from (select version() as major) foo, substring(version(), 12, position(' ' in substring(version(),12))) as minor"
#sql = "select substring(version(), 12, position(' ' in substring(version(),12)))"
sql = "select trim(substring(version(), 12, position(' ' in substring(version(),12)))) || '-' || substring(foo.major from 12 for 3)as major from (select version() as major) foo"
# do not provide host name and/or port if not provided
cmd = "psql %s -t -c \"%s\" " % (self.connstring, sql)
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "%s\n" % (results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
# with version 10, major version format changes from x.x to x, where x is a 2 byte integer, ie, 10, 11, etc.
# values = bytes(values2).decode('utf-8')
results = str(results)
parsed = results.split('-')
amajor = parsed[1]
self.pgversionminor = parsed[0]
if self.verbose:
print ("results=%s parsed=%s amajor=%s aminor=%s" % (results, parsed, amajor, self.pgversionminor))
pos = amajor.find('.')
if pos == -1:
# must be a beta or rc candidate version starting at version 10 since the current version is 10rc1
self.pgversionmajor = Decimal(amajor[:2])
else:
self.pgversionmajor = Decimal(amajor)
return SUCCESS, str(results)
###########################################################
def get_readycnt(self):
# we cannot handle cloud types like AWS RDS
if self.pg_type == 'rds':
return SUCCESS, '0'
# version 10 replaces pg_xlog with pg_wal directory
if self.pgversionmajor > Decimal('9.6'):
xlogdir = "%s/pg_wal/archive_status" % self.datadir
else:
xlogdir = "%s/pg_xlog/archive_status" % self.datadir
sql = "select count(*) from (select pg_ls_dir from pg_ls_dir('%s') where pg_ls_dir ~ E'^[0-9A-F]{24}.ready$') as foo" % xlogdir
# do not provide host name and/or port if not provided
cmd = "psql %s -t -c \"%s\" " % (self.connstring, sql)
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "%s" % (results)
aline = "%s" % (errors)
#self.writeout(aline)
#return rc, errors
return WARNING, errors
return SUCCESS, str(results)
###########################################################
def get_datadir(self):
sql = "show data_directory"
# do not provide host name and/or port if not provided
cmd = "psql %s -t -c \"%s\" " % (self.connstring, sql)
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "%s\n" % (results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
return SUCCESS, str(results)
###########################################################
def get_pgbindir(self):
if self.opsys == 'posix':
cmd = "pg_config | grep BINDIR"
else:
cmd = "pg_config | find \"BINDIR\""
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
# don't consider failure unless bindir not already populated by "which psql" command that executed earlier
if self.pgbindir == "":
errors = "unable to get PG Bind Directory. rc=%d %s\n" % (rc, results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
else:
return SUCCESS, self.pgbindir
results = results.split('=')
self.pgbindir = results[1].strip()
if self.verbose:
print ("PG Bind Directory = %s" % self.pgbindir)
return SUCCESS, str(results)
###########################################################
def get_load(self):
if self.opsys == 'posix':
cmd = "cat /proc/cpuinfo | grep processor | wc -l"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "%s\n" % (results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
CPUs = int(results)
cmd = "uptime | grep -ohe 'load average[s:][: ].*' | awk '{ print $5 }'"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "%s\n" % (results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
LOAD15=Decimal(results)
LOADR= round(LOAD15/CPUs * 100,2)
#if self.verbose:
# print ("LOAD15 = %.2f CPUs=%d LOAD = %.2f%%" % (LOAD15, CPUs, LOADR))
#print ("DEBUGGGGGG LOAD15 = %.2f CPUs=%d LOAD = %.2f%%" % (LOAD15, CPUs, LOADR))
else:
# assume windows
cmd = "wmic cpu get loadpercentage"
rc, results = self.executecmd(cmd, True)
if rc != SUCCESS:
errors = "%s\n" % (results)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
#if self.verbose:
# print ("windows load: %d %s" % (rc, results))
LOAD = results.split('\n')
LOADR = int(LOAD[1])
return SUCCESS, str(LOADR)
###########################################################
def check_load(self):
rc, results = self.get_load()
if rc != SUCCESS:
return rc, results
load = Decimal(results)
if load > self.loadthreshold:
return HIGHLOAD, "Current load (%.2f%%) > Threshold load (%d%%)" % (load, self.loadthreshold)
else:
return SUCCESS, "Current load (%.2f%%) < Threshold load (%d%%)" % (load, self.loadthreshold)
###########################################################
def get_slaves(self):
if self.pgversionmajor < Decimal('9.1'):
# pg_stat_replication table does not exist
return SUCCESS, ""
sql = "select count(*) from pg_stat_replication where state = 'streaming'"
cmd = "psql %s -t -c \"%s\"" % (self.connstring, sql)
rc, results = self.executecmd(cmd, False)
if rc != SUCCESS:
errors = "Unable to get table/index bloat count: %d %s\nsql=%s\n" % (rc, results, sql)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
cols = results.split('|')
self.slavecnt = int(cols[0].strip())
# Also check whether this cluster is a master or slave
# self.in_recovery
sql = "select pg_is_in_recovery()"
cmd = "psql %s -t -c \"%s\"" % (self.connstring, sql)
rc, results = self.executecmd(cmd, False)
if rc != SUCCESS:
errors = "Unable to get master/slave status: %d %s\nsql=%s\n" % (rc, results, sql)
aline = "%s" % (errors)
self.writeout(aline)
return rc, errors
self.in_recovery = True if results == 't' else False
return SUCCESS, ""
###########################################################
def initreport(self):
# get the host name
if self.dbhost == '':
# tuples = os.uname()
tuples = platform.uname()
hostname = tuples[1]
else:
hostname = self.dbhost
now = str(time.strftime("%c"))
f = open(self.reportfile, "w")
contextline = "<H2><p>Host: %s</p><p>Database: %s</p><p>Generated %s</p></H2>\n" % (hostname, self.database, now)
info = \
"<!DOCTYPE html>\n" + \
"<HTML>\n" + \
"<HEAD>\n" + \
"<TITLE>PostgreSQL Report Analysis</TITLE>\n" + \
"</HEAD>\n" + \
"<style>" + \
".table1 {font-size:16px; border:1px solid black; border-collapse:collapse; }" + \
".table1 th { color:#000; text-align:left; border:1px solid black; padding: 5px;}" + \
".table1 td { color:#000099; text-align:left; border:1px solid black; padding: 5px;}" + \
"caption { text-align:left; caption-side: left; }" + \
"</style>" + \
"<BODY BGCOLOR=\"FFFFFF\">\n" + \
"<div id='container'>\n" + \
"<img src='" + self.imageURL + "' style='float: left;'/>\n" + \
"<p><H1>PostgreSQL Report Analysis</H1></p>\n" + \
"</div>\n" + contextline + \
"<a href=\"https://github.com/MichaelDBA/pg_report\">pg_report</a> is available on github.\n" + \
"Send me mail at <a href=\"mailto:[email protected]\"> [email protected]</a>.\n" + \
"<HR>\n"
f.write(info)
f.close()
return SUCCESS, ""
###########################################################
def finalizereport(self):
f = open(self.reportfile, "a")
info = "</BODY>\n</HTML>"
f.write(info)
f.close()
return SUCCESS, ""
###########################################################
def appendreport(self, astring):
f = open(self.reportfile, "a")
f.write(astring)
f.close()
return SUCCESS, ""
###########################################################
def do_report(self):
if self.html_format:
rc,results = self.initreport()
if rc != SUCCESS:
return rc, results
rc, results = self.get_slaves()
if rc != SUCCESS:
return rc, results
# do health checks
rc, results = self.do_report_healthchecks()
if rc != SUCCESS:
return rc, results
print ("")
# get pg memory settings
rc, results = self.do_report_pgmemory()
if rc != SUCCESS:
return rc, results
# get bloated tables and indexes
rc, results = self.do_report_bloated()
if rc != SUCCESS:
return rc, results
# get unused indexes
rc, results = self.do_report_unusedindexes()
if rc != SUCCESS:
return rc, results
# See what tables need to be analyzed, vacuumed, etc
rc, results = self.do_report_tablemaintenance()
if rc != SUCCESS:
return rc, results
if self.html_format:
rc,results = self.finalizereport()
if rc != SUCCESS:
return rc, results
print ("html report file generated: %s" % self.reportfile)
else:
print ("text report file generated: %s" % self.reportfile)
return SUCCESS, ""
###########################################################
def do_report_pgmemory(self):
if self.pg_type == 'rds':
# nothing much to report
return SUCCESS, ""
# shared_buffers:
# primitive logic: make shared buffers minimum 4GB or maximum 250GB or 25% of total memory
# newer versions of PG seem to be more efficient with higher values, so logic is:
# if pg 9.3 or lower max is 8GB, if pg 9.4 or higher 12 GB max
if self.pgversionmajor < Decimal('9.3'):
MAXGB = 8
else:
MAXGB = 250
MINGB = 2
percent25GB = self.totalmemGB * 0.25
shared_buffersGB = self.shared_buffers / 1024
if percent25GB > MAXGB:
recommended_shared_buffers = MAXGB
elif percent25GB < MINGB:
recommended_shared_buffers = percent25GB
else:
recommended_shared_buffers = percent25GB
if self.verbose:
print ("shared_buffers = %d percent25GB=%d recommended=%d totalmemGB=%d" % (self.shared_buffers, percent25GB, recommended_shared_buffers, self.totalmemGB))
# maintenance_work_mem
# current pg versions dont perform better with high values, since there is a hard-coded limit of the this memory that will be used,
# effectively making memory here unavailable for usage elsewhere, so general rule:
# MIN = 0.128GB, MAX 8 GB
MIN = 0.128
MAX = 8
if self.totalmemGB < 4:
recommended_maintenance_work_mem = MIN
elif self.totalmemGB < 8:
recommended_maintenance_work_mem = 0.256
elif self.totalmemGB < 16:
recommended_maintenance_work_mem = 0.512
elif self.totalmemGB < 32:
recommended_maintenance_work_mem = 1
elif self.totalmemGB < 64:
recommended_maintenance_work_mem = 2
elif self.totalmemGB < 96:
recommended_maintenance_work_mem = 4
else:
recommended_maintenance_work_mem = MAX
# work_mem
# need knowledge of SQL workload to do this effectivly, so for now, consider max connections and total memory
if self.max_connections < 200:
if self.totalmemGB < 4:
recommended_work_mem = 0.016
elif self.totalmemGB < 8:
recommended_work_mem = 0.032
elif self.totalmemGB < 16:
recommended_work_mem = 0.064
elif self.totalmemGB < 32:
recommended_work_mem = 0.128
elif self.totalmemGB < 64:
recommended_work_mem = 0.256
else:
recommended_work_mem = 0.512
else:
if self.totalmemGB < 8:
recommended_work_mem = 0.016
elif self.totalmemGB < 16:
recommended_work_mem = 0.032
elif self.totalmemGB < 32:
recommended_work_mem = 0.064
elif self.totalmemGB < 64:
recommended_work_mem = 0.128
else:
recommended_work_mem = 0.256
# effective_cache_size: settings shows it in 8kb chunks
# set it to 85% of memory
recommended_effective_cache_size = .85 * self.totalmemGB
totalf = "Current and recommended PG Memory configuration settings are based on a dedicated PG Server with one PG Instance. Total Physical Memory = %s GB" % self.totalmemGB
print (totalf)
print ("*** Consider changing these values if they differ significantly ***")
if self.html_format: