~stub/ubuntu/precise/pgbadger/devel : revision 5

25

-G | --nograph : disable graphs on HTML output. Enable by default.

26

-h | --help : show this message and exit.

27

-i | --ident name : programname used as syslog ident. Default: postgres

28

-I | --incremental : use incremental mode, reports will be generated by

29

days in a separate directory, --outdir must be set.

28

30

-j | --jobs number : number of jobs to run on parallel on each log file.

29

31

Default is 1, run as single process.

30

32

-J | --Jobs number : number of log file to parse in parallel. Default

40

42

-o | --outfile filename: define the filename for output. Default depends on

41

43

the output format: out.html, out.txt or out.tsung.

42

44

To dump output to stdout use - as filename.

45

-O | --outdir path : directory where out file must be saved.

43

46

-p | --prefix string : give here the value of your custom log_line_prefix

44

47

defined in your postgresql.conf. Only use it if you

45

48

aren't using one of the standard prefixes specified

47

50

includes additional variables like client ip or

48

51

application name. See examples below.

49

52

-P | --no-prettify : disable SQL queries prettify formatter.

50

-q | --quiet : don't print anything to stdout, even not a progress bar.

53

-q | --quiet : don't print anything to stdout, not even a progress bar.

51

54

-s | --sample number : number of query samples to store/display. Default: 3

52

55

-S | --select-only : use it if you want to report select queries only.

53

56

-t | --top number : number of queries to store/display. Default: 20

82

85

--disable-temporary : do not generate temporary report.

83

86

--disable-checkpoint : do not generate checkpoint report.

84

87

--disable-autovacuum : do not generate autovacuum report.

88

--charset : used to set the HTML charset to be used. Default: utf-8.

89

--csv-separator : used to set the CSV field separator, default: ,

90

--exclude-time regex : any timestamp matching the given regex will be

91

excluded from the report. Example: "2013-04-12 .*"

92

You can use this option multiple times.

93

--exclude-appname name : exclude entries for the specified application name

94

from report. Example: "pg_dump".

85

95

86

96

Examples:

87

97

119

129

This supposes that your log file and HTML report are also rotated every

120

130

week.

121

131

132

Or better, use the auto-generated incremental reports:

133

134

0 4 * * * /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log.1 \

135

-O /var/www/pg_reports/

136

137

will generate a report per day and per week in the given output

138

directory.

139

140

If you have a pg_dump at 23:00 and 13:00 each day during half an hour,

141

you can use pgbadger as follow to exclude these periods from the report:

142

143

pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log

144

145

This will help to not have all COPY order on top of slowest queries. You

146

can also use --exclude-appname "pg_dump" to solve this problem in a more

147

simple way.

148

122

149

DESCRIPTION

123

pgBadger is a PostgreSQL log analyzer built for speed with fully

150

pgBadger is a PostgreSQL log analyzer build for speed with fully

124

151

detailed reports from your PostgreSQL log file. It's a single and small

125

Perl script that aims to replace and out-perform the old PHP script

126

pgFouine.

127

128

By the way, we would like to thank Guillaume Smet for all the work he

129

has done on this really nice tool. We've been using it a long time, it

130

is a really great tool!

131

132

pgBadger is written in pure Perl language. It uses a Javascript library

133

to draw graphs so that you don't need additional Perl modules or any

134

other package to install. Furthermore, this library gives us additional

135

features, such as zooming.

152

Perl script that outperform any other PostgreSQL log analyzer.

153

154

It is written in pure Perl language and uses a javascript library

155

(flotr2) to draw graphs so that you don't need to install any additional

156

Perl modules or other packages. Furthermore, this library gives us more

157

features such as zooming. pgBadger also uses the Bootstrap javascript

158

library and the FontAwesome webfont for better design. Everything is

159

embedded.

136

160

137

161

pgBadger is able to autodetect your log file format (syslog, stderr or

138

csvlog). It is designed to parse huge log files, as well as gzip, zip or

139

bzip2 compressed files. See a complete list of features below.

162

csvlog). It is designed to parse huge log files as well as gzip

163

compressed file. See a complete list of features below.

164

165

All charts are zoomable and can be saved as PNG images.

166

167

You can also limit pgBadger to only report errors or remove any part of

168

the report using command line options.

169

170

pgBadger supports any custom format set into log_line_prefix of your

171

postgresql.conf file provide that you use the %t, %p and %l patterns.

172

173

pgBadger allow parallel processing on a single log file and multiple

174

files through the use of the -j option and the number of CPUs as value.

175

176

If you want to save system performance you can also use log_duration

177

instead of log_min_duration_statement to have reports on duration and

178

number of queries only.

140

179

141

180

FEATURE

142

181

pgBadger reports everything about your SQL queries:

143

182

144

Overall statistics.

183

Overall statistics

145

184

The most frequent waiting queries.

146

185

Queries that waited the most.

147

186

Queries generating the most temporary files.

150

189

Queries that took up the most time.

151

190

The most frequent queries.

152

191

The most frequent errors.

153

154

The following reports are also available with hourly charts:

155

156

Hourly queries statistics.

157

Hourly temporary file statistics.

158

Hourly checkpoints statistics.

159

Hourly restartpoints statistics.

192

Histogram of query times.

193

194

The following reports are also available with hourly charts divide by

195

periods of five minutes:

196

197

SQL queries statistics.

198

Temporary file statistics.

199

Checkpoints statistics.

200

Autovacuum and autoanalyze statistics.

201

202

There's also some pie reports of distribution about:

203

160

204

Locks statistics.

161

Queries by type (select/insert/update/delete).

205

ueries by type (select/insert/update/delete).

162

206

Distribution of queries type per database/application

163

207

Sessions per database/user/client.

164

208

Connections per database/user/client.

167

211

All charts are zoomable and can be saved as PNG images. SQL queries

168

212

reported are highlighted and beautified automatically.

169

213

214

You can also have incremental reports with one report per day and a

215

cumulative report per week.

216

170

217

REQUIREMENT

171

218

pgBadger comes as a single Perl script - you do not need anything other

172

219

than a modern Perl distribution. Charts are rendered using a Javascript

200

247

Note that multiprocessing can not be used with compressed files or CSV

201

248

files as well as under Windows platform.

202

249

250

INSTALLATION

251

Download the tarball from github and unpack the archive as follow:

252

253

tar xzf pgbadger-4.x.tar.gz

254

cd pgbadger-4.x/

255

perl Makefile.PL

256

make && sudo make install

257

258

This will copy the Perl script pgbadger to /usr/local/bin/pgbadger by

259

default and the man page into /usr/local/share/man/man1/pgbadger.1.

260

Those are the default installation directories for 'site' install.

261

262

If you want to install all under /usr/ location, use INSTALLDIRS='perl'

263

as an argument of Makefile.PL. The script will be installed into

264

/usr/bin/pgbadger and the manpage into /usr/share/man/man1/pgbadger.1.

265

266

For example, to install everything just like Debian does, proceed as

267

follows:

268

269

perl Makefile.PL INSTALLDIRS=vendor

270

271

By default INSTALLDIRS is set to site.

272

203

273

POSTGRESQL CONFIGURATION

204

274

You must enable and set some configuration directives in your

205

275

postgresql.conf before starting.

211

281

Here every statement will be logged, on busy server you may want to

212

282

increase this value to only log queries with a higher duration time.

213

283

Note that if you have log_statement set to 'all' nothing will be logged

214

with log_line_prefix. See next chapter for more information.

284

through log_min_duration_statement. See next chapter for more

285

information.

215

286

216

287

With 'stderr' log format, log_line_prefix must be at least:

217

288

241

312

log_disconnections = on

242

313

log_lock_waits = on

243

314

log_temp_files = 0

315

log_autovacuum_min_duration = 0

244

316

245

317

Do not enable log_statement as their log format will not be parsed by

246

318

pgBadger.

267

339

have log_statement set to 'all' nothing will be logged with

268

340

log_line_prefix.

269

341

270

Parallel processing

342

PARALLEL PROCESSING

271

343

To enable parallel processing you just have to use the -j N option where

272

344

N is the number of cores you want to use.

273

345

322

394

are all named with the following template tmp_pgbadgerXXXX.bin so they

323

395

can be easily identified.

324

396

325

INSTALLATION

326

Download the tarball from github and unpack the archive as follow:

327

328

tar xzf pgbadger-3.x.tar.gz

329

cd pgbadger-3.x/

330

perl Makefile.PL

331

make && sudo make install

332

333

This will copy the Perl script pgbadger to /usr/local/bin/pgbadger by

334

default and the man page into /usr/local/share/man/man1/pgbadger.1.

335

Those are the default installation directories for 'site' install.

336

337

If you want to install all under /usr/ location, use INSTALLDIRS='perl'

338

as an argument of Makefile.PL. The script will be installed into

339

/usr/bin/pgbadger and the manpage into /usr/share/man/man1/pgbadger.1.

340

341

For example, to install everything just like Debian does, proceed as

342

follows:

343

344

perl Makefile.PL INSTALLDIRS=vendor

345

346

By default INSTALLDIRS is set to site.

397

INCREMENTAL REPORTS

398

pgBadger include an automatic incremental report mode using option -I or

399

--incremental. When running in this mode, pgBadger will generate one

400

report per day and a cumulative report per week. Output is first done in

401

binary format into the mandatory output directory (see option -O or

402

--outdir), then in HTML format for daily and weekly reports with a main

403

index file.

404

405

The main index file will show a dropdown menu per week with a link to

406

the week report and links to daily reports of this week.

407

408

For example, if you run pgBadger as follow based on a daily rotated

409

file:

410

411

0 4 * * * /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log.1 \

412

-O /var/www/pg_reports/

413

414

you will have all daily and weekly reports for the full running period.

415

416

In this mode pgBagder will create an automatic incremental file into the

417

output directory, so you don't have to use the -l option unless you want

418

to change the path of that file. This mean that you can run pgBadger in

419

this mode each days on a log file rotated each week, it will not count

420

the log entries twice.

421

422

BINARY FORMAT

423

Using the binary format it is possible to create custom incremental and

424

cumulative reports. For example, if you want to refresh a pgbadger

425

report each hour from a daily PostgreSQl log file, you can proceed by

426

running each hour the following commands:

427

428

pgbadder --last-parsed .pgbadger_last_state_file -o sunday/hourX.bin /var/log/pgsql/postgresql-Sun.log

429

430

to generate the incremental data files in binary format. And to generate

431

the fresh HTML report from that binary file:

432

433

pgbadder sunday/*.bin

434

435

Or an other example, if you have one log file per hour and you want a

436

reports to be rebuild each time the log file is switched. Proceed as

437

follow:

438

439

pgbadger -o day1/hour01.bin /var/log/pgsql/pglog/postgresql-2012-03-23_10.log

440

pgbadger -o day1/hour02.bin /var/log/pgsql/pglog/postgresql-2012-03-23_11.log

441

pgbadger -o day1/hour03.bin /var/log/pgsql/pglog/postgresql-2012-03-23_12.log

442

...

443

444

When you want to refresh the HTML report, for example each time after a

445

new binary file is generated, just do the following:

446

447

pgbadger -o day1_report.html day1/*.bin

448

449

Adjust the commands following your needs.

347

450

348

451

AUTHORS

349

pgBadger is an original work from Gilles Darold. It is maintained by the

350

good folk at Dalibo and everyone who wants to contribute.

452

pgBadger is an original work from Gilles Darold.

453

454

The pgBadger logo is an original creation of Damien Clochard.

455

456

The pgBadger v4.x design comes from the "Art is code" company.

457

458

This web site is a work of Gilles Darold.

459

460

pgBadger is maintained by Gilles Darold, the good folks at Dalibo, and

461

every one who wants to contribute.

462

463

Many people have contributed to pgBadger, they are all quoted in the

464

Changelog file.

351

465

352

466

LICENSE

353

467

pgBadger is free software distributed under the PostgreSQL Licence.

354

468

355

469

356

470

357

471

A modified version of the SQL::Beautify Perl Module is embedded in

358

472