root/trunk/tools/log_audit_information.pl

Revision 169, 5.7 kB (checked in by depesz, 4 years ago)

Initial version, although should be perfectly functional

  • Property svn:executable set to *
Line 
1 #!/usr/bin/perl -w
2 use strict;
3
4 my $program = OmniTI::Log_Audit_Information->new();
5 $program->run();
6
7 exit;
8
9 package OmniTI::Log_Audit_Information;
10 use strict;
11 use Getopt::Long;
12 use File::Spec;
13 use English qw( -no_match_vars );
14 use Carp;
15 use File::Path;
16 use File::Basename;
17 use POSIX qw( strftime );
18
19 sub new {
20     my $self = bless {}, shift;
21     return $self;
22 }
23
24 sub run {
25     my $self = shift;
26     $self->read_command_line_arguments();
27
28     $self->{ 'time' } = time();
29     $self->{ 'time_str' } = strftime( '%Y-%m-%d %H:%M:%S', localtime $self->{ 'time' } );
30
31     $self->get_list_of_dbs();
32
33     $self->get_cluster_wide_data();
34
35     $self->get_database_data( $_ ) for @{ $self->{ 'dbs' } };
36     return;
37 }
38
39 sub get_database_data {
40     my $self     = shift;
41     my $database = shift;
42
43     for my $view ( qw( pg_stat_all_indexes pg_stat_all_tables pg_statio_all_indexes pg_statio_all_tables ) ) {
44         $self->print_time_prefixed(
45             $database . '-' . $view,
46             $self->call_sql( 'copy ( select * from ' . $view . ' ) to stdout', $database )
47         );
48     }
49     return;
50 }
51
52 sub get_cluster_wide_data {
53     my $self = shift;
54
55     $self->print_time_prefixed(
56         'databases',
57         $self->call_sql( 'copy ( select * from pg_stat_database ) to stdout' )
58     );
59     return;
60 }
61
62 sub print_time_prefixed {
63     my $self = shift;
64     my ( $type, @output ) = @_;
65     my $fh = $self->get_output_writer( $type );
66     for my $line ( @output ) {
67         printf $fh "%s\t%s", $self->{ 'time_str' }, $line;
68     }
69     close $fh;
70     return;
71 }
72
73 sub get_list_of_dbs {
74     my $self = shift;
75
76     my @output = $self->call_sql( 'SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1' );
77
78     chomp for @output;
79
80     $self->{ 'dbs' } = \@output;
81     return;
82 }
83
84 sub call_sql {
85     my $self     = shift;
86     my $sql      = shift;
87     my $database = shift;
88
89     my @psql_command = ( $self->{ 'psql-path' } );
90     push @psql_command, '-qAtX';
91     push @psql_command, ( '-U', $self->{ 'username' } ) if $self->{ 'username' };
92     push @psql_command, ( '-h', $self->{ 'host' } )     if $self->{ 'host' };
93     push @psql_command, ( '-p', $self->{ 'port' } )     if $self->{ 'port' };
94     push @psql_command, ( '-d', $database || $self->{ 'dbname' } );
95
96     push @psql_command, ( '-c', $sql );
97
98     my $psql_str = join ' ', map { quotemeta $_ } @psql_command;
99
100     open my $psql, '-|', $psql_str or croak( "Cannot run psql >$psql_str< : $OS_ERROR.\n" );
101     my @output = <$psql>;
102     close $psql;
103
104     croak( "Calling psql >$psql_str< failed. CHILD_ERROR=$CHILD_ERROR.\n" ) if $CHILD_ERROR;
105
106     return @output;
107 }
108
109 sub get_output_writer {
110     my $self = shift;
111
112     my $type = shift;
113
114     my $output_filename = strftime(
115         File::Spec->catfile( $self->{ 'log-path' }, '%Y/%m/db-audit-' . $type . '-%Y-%m-%d_%H00-%H59.log.gz' ),
116
117         localtime $self->{ 'time' },
118     );
119
120     my $output_dir = dirname( $output_filename );
121     mkpath( [ $output_dir ], 0, oct( "755" ) );
122
123     my $gzip_command = sprintf '%s -c - >> %s', quotemeta( $self->{ 'gzip-path' } ), quotemeta( $output_filename );
124
125     open my $fh, '|-', $gzip_command or croak( "Cannot open gzip writer >$gzip_command< : $OS_ERROR.\n" );
126     return $fh;
127 }
128
129 sub read_command_line_arguments {
130     my $self = shift;
131     my $vars = {
132         'log-path'  => '.',
133         'gzip-path' => 'gzip',
134         'psql-path' => 'psql',
135         'dbname'    => 'postgres',
136     };
137     unless ( GetOptions( $vars, 'psql-path|pp=s', 'port|p=i', 'username|U=s', 'host|h=s', 'log-path|lp=s', 'gzip-path|gp=s', 'dbname|d=s', 'help|?' ) ) {
138         $self->show_help_and_die();
139     }
140     $self->show_help_and_die() if $vars->{ 'help' };
141
142     # copy key/values from $vars to $self;
143     @{ $self }{ keys %{ $vars } } = values %{ $vars };
144
145     return;
146 }
147
148 sub show_help_and_die {
149     my $self = shift;
150     my ( $format, @args ) = @_;
151
152     if ( $format ) {
153         $format =~ s/\s*\z/\n\n/;
154         printf STDERR $format, @args;
155     }
156     print STDERR <<_END_OF_HELP_;
157 Syntax:
158     $PROGRAM_NAME [options]
159
160 Options:
161     [ database connection options]
162       -h,  --host=HOSTNAME      database server host or socket directory
163       -p,  --port=PORT          database server port
164       -U,  --username=USERNAME  database user name
165       -d,  --dbname=DBNAME      database name to connect
166
167     [ paths ]
168       -lp, --log-path=PATH      directory where audit logs will be stored
169       -pp, --psql-path=PATH     path to psql program
170       -gp, --gzip-path=PATH     path to gzip program
171
172     [ other ]
173       -?,  --help               show this help page
174
175 Defaults:
176     --dbname postgres
177     --log-path .
178     --psql-path psql
179     --gzip-path gzip
180
181 Description:
182     This program connects to PostgreSQL database (via psql), and logs (to compressed files) content of:
183     - pg_stat_database
184     - pg_stat_all_tables
185     - pg_stat_all_indexes
186     - pg_statio_all_tables
187     - pg_statio_all_indexes
188     First view (pg_stat_database) is fetched from only one database, as it contains data about all of them.
189     Following four views, are fetched separately for every database in given PostgreSQL installation.
190
191     Output of this is timestamped, and stored in compressed (gzip) files in given directory.
192
193     To simplify geting small time frames of data, datafiles are automatically rotated every hour. To prevent
194     creation of thousands of files in single directory, logs are stored in hierarchical directory structure
195     which is based on current year and month.
196
197     Data, after decompression, can be loaded to PostgreSQL, or analyzed using any other tools.
198
199     Each row, based on row from one of logged tables, contains (at the beginning) one extra column, with
200     timestamp in format:
201     YYYY-MM-DD HH-MM-SS
202     for example: 2010-06-15 21:34:56
203
204 _END_OF_HELP_
205     exit( 1 );
206 }
207
Note: See TracBrowser for help on using the browser.