pgbadger is a recent Postgres module presented during the lightning talks of PGCon 2012 by its original author Gilles Darold. It is thought as an alternative to PgFouine, able to replace it thanks to its flexibility, extensibility and performance.

So, like PGFouine, pgbadger is a Postgres log file analyzer, meaning that it is able to deparse, analyze the data of your log files and then provide statistics about your database: from overall data (query list, number of transactions), error counts (most frequent events) to more performance details like the queries that took the longest run time.
This means that basically you use in input log files from PostgreSQL, and you get in output an html or txt page that gives you all the statistics you want. So you do not need to sneak anymore in your log files to analyze what is happening anymore. Simply launch pgbadger, wait for parsing (which is pretty fast btw), and see.

Before describing more in details the functionalities of pgbadger, why is it an alternative to pgfouine?

  1. pgbadger is written in perl, pgfouine is written in php. So with pgbadger you do not need to install extra packages, Postgres core using natively perl. And well, perl is more performant than php. And php… is php…
  2. Developped by the community, for the community
  3. Latest release of pgfouine is from 2010, it doesn’t look to be that much maintained. pgbadger is a new project, young and dynamic, and more and more people are gathering to develop it.
  4. It is developped by cool guys, OK pgfouine also… That is maybe not a real argument…

Now, let’s put our hands on the beast. There are several ways to get this module.
First, you can fetch the code of the project directly from Github with those commands:
git clone https://github.com/dalibo/pgbadger.git
Also, you can download the tarball from here.
Then install it with those commands.
tar xzf pgbadger-1.x.tar.gz
cd pgbadger-1.x/
perl Makefile.PL
make && sudo make install

This will install pgbadger in /usr/bin/local and some man pages. You can refer to the README of the project for more details.

As told before, pgbadger is a log file deparser, so you need to set up the output of your log files correctly to allow pgbadger to look at your database server information. The more logging parameters you activate, the more information you will be able to get from your log files. Here are the settings I used for this post and the test below.
logging_collector = on
log_min_messages = debug1
log_min_error_statement = debug1
log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

You will need to customize those options depending on the information you want, just do not forget that setting up the root correctly is the key for success.
At the state of pgbadger 2.0, log_statement and log_duration cannot be activated, so take care not to use them.

For the purpose of this post and in order to produce a couple of log files, I ran a 5-minute pgbench test on a fresh Postgres server.
So the logs I obtained are not at all production-like, but enough to show what pgbadger can do.

By the way, pgbadger has several options making it pretty flexible, among them you have the possibility to specify multiple log files at the same time, specify an interval of time for the analysis, and far more. It is also possible to choose output format of result. Among the possible use cases I got on top of my head:

  • Creation of a text file report, and send it to a mailing list automatically
  • Creation of an html file, and upload it automatically to a web server
  • Base log analysis on a cron with a certain time interval
  • etc.

pgbadger has few dependencies, so it makes it pretty flexible for your environments. Once again the README of the project gives more examples of use, so do not hesitate to refer to it.

So, just after my short pgbench run, I got a set of log files ready for analysis. Now it is time to parse them.
Note: the pgbench test has been done with default values without thinking, so don’t worry about the bad performance results :)
$ ./pgbadger ~/pgsql/master/pg_log/postgresql-2012-08-30_132*
[========================>] Parsed 10485768 bytes of 10485768 (100.00%)
[========================>] Parsed 10485828 bytes of 10485828 (100.00%)
[========================>] Parsed 10485851 bytes of 10485851 (100.00%)
[========================>] Parsed 10485848 bytes of 10485848 (100.00%)
[========================>] Parsed 10485839 bytes of 10485839 (100.00%)
[========================>] Parsed 982536 bytes of 982536 (100.00%)

In result, I got a file called out.html (default, but customizable) showing a bunch of data, analyzing things automatically.
The most interesting part is perhaps the performance analysis, showing you a list of the less performant queries, so this will allow you to tune your database based on the log data obtained.

Here are some pictures showing portions of the output results you will get.

pgbadger example 1

pgbadger example 1


pgbadger example 2

pgbadger example 2

I forgot to tell something: this utility is 100% compatible with Postgres-XC, the only thing you need to do is just to launch pgbadger for each node of your cluster.

So, pgbadger is light, fast and is waiting for your love. It is one of those utilities that you can use not only for production database systems, but for extra things like benchmark or performance analysis. Its installation is easy, will not heavy your system with packages you might not want, so go ahead and use it.

Developers are sometimes looking for cheap solutions to have their own private repositories. There are multiple solutions for open source software such as source forge or GitHub that can provide wide and secured functionalities. However, in the case of the 1st solution it is not possible to create private repositories, and in the second case private repositories are possible but this solution is not worth the money for independant programmers.

The cheapest solution remains in having its own hosting service (buying a domain, creating a free domain, etc). Google that will for sure lead you to free services with dedicated domain names for example.

Most of the time such hosting services are shared-hosting based. This means that multiple users are using the same server for their websites. In this case normal users do not have root rights (well, normal!), so it is impossible to make fine settings of the configuration files of apache, like httpd.conf.

GIT supports http protocol for its repositories for a long time, but the original protocol uses WebDAV and is really heavy and slow. Roughly, you needed to send to remote server entire files and not diffs. However, since version 1.6.6, GIT supports smart HTTP protocol, this has speed up http repositories and you do not even need WebDAV. An important point, WebDAV can be activated in httpd.conf of your apache server with the keywords “DAV On” but this creates an error, that’s why the solution presented here only uses smart http.

So, to set your GIT repositories, what is needed first is GIT installed on your server.
git --version
git version 1.7.0.4

The important point is to have support of the command git-http-backend.

This done, you also need the apache modules mod_cgi, mod_alias, and mod_env to be activated.

Now, let’s go through the whole setting process. In the case of this tutorial, our goal is to create a private repository for a project called foo-project. The private repositories that will be set are protected in read and write by apache group management.

From the root repository of your domain http://www.example.com/, if you have a connection through ssh, go to the root repository that should be called public_html. Then type the following command:
htpasswd -c .htpasswd user-name
You can also do that in another folder or in a subdomain of course.

You will be requested to write a password. This command will create a file called .htpasswd containing data like this:
user-name: $encrypted-passwd
“user-name” can be the name you want. It is an apache-level security group, so if you want you don’t need to use the same user name as your Linux session. This file contains user and password data for the access to private repositories.

It is possible to add new users to this file with commands like:
htpasswd .htpasswd new-user-name

Then create a file called .htgroup. It contains the following data:
foo_write: user-name new-user-name
This file will be used to control the group data of apache. You can create for each private repository a group with a list of users. One line has to be used for each group. Keep in mind that it is easier to maintain the group list in a common file. However you can set group file in different files if you wish. Just don’t forget to list those files in appropriate .htaccess files.

Then it is time to create the access control to private repositories. Create a folder called git in the root folder public_html and move in it:
mkdir git
cd git

There you need to create a new CGI script that will be used to rewrite requested URL for private GIT repositories. With an editor, create a file called git-http-backend.cgi with the following data in it.
#!/bin/sh
#first we export the GIT_PROJECT_ROOT
export GIT_PROJECT_ROOT=/to/site/folder/public_html/git/
 
if [ -z "$REMOTE_USER" ]
then
 export REMOTE_USER=$REDIRECT_REMOTE_USER
fi
 
#and run your git-http-backend
/usr/bin/git-http-backend

GIT_PROJECT_ROOT is an environment variable pointing to the root folder of your GIT repositories. A mistake here may lead to an error 500…

Depending on the server of your shared hosting service, git-http-backend may not be in /usr/bin/ but in /usr/lib/git-core/ or whatever. Be sure to check where it is with the command:
which git-http-backend

Then create an .htaccess file in git to control URL rewrite. It contains the data:
Options +ExecCgi
 
#This is used for group/user access control
AuthName "Private Git Access"
AuthType Basic
AuthUserFile /to/site/folder/public_html/.htpasswd
AuthGroupFile /to/site/folder/public_html/.htgroup
Require valid-user
 
#This is the rewrite algorithm
RewriteEngine on
RewriteBase /git
SetHandler cgi-script
RewriteRule ^([a-zA-Z0-9._]*\.git/(HEAD|info/refs|objects/(info/[^/]+|[0-9a-f]{2}/[0-9a-f]{38}|pack/pack-[0-9a-f]{40}\.(pack|idx))|git-(upload|receive)-pack))$ /git/git-http-backend.cgi/$1

Then it is time to create the GIT repository of foo-project and move in it.
mkdir foo-project.git
cd foo-project.git

Now you should be in folder /to/site/folder/public_html/git/foo-project.git.

Then initialize your GIT repository with the following commands.
git --bare init
git --bare update-server-info
cp hooks/post-update.sample hooks/post-update
chmod a+x hooks/post-update
touch git-daemon-export-ok

This basically makes all the necessary settings to allow your folder to use smart http mode. If you don’t care about GIT details, just copy/paste that!

What finally remains is to create an .htaccess file in public_html/git/foo-project.git to control access to this repository.
Allow from all
Order allow,deny
#foo_write is the group is .htgroup. All the users of this group will be authorized to access this repository at will.
Require group foo_write

The setting on remote side is done. So now, here is how to access to the remote from your local machine.
You may either clone the new git repository.
git clone http://www.example.com/git/foo-project.git

Or add a remote URL.
mkdir myproject
cd myproject
git init
git remote add myproj http://www.example.com/git/foo-project.git
git fetch myproj

It may be necessary to install the library curl and set the file called .netrc in your home repository (accessible with $HOME/.netrc) like this:
machine www.example.com
login user-name
password $mypasswd

If you don’t want to use .netrc file you can directly add you user name in the remote URL.
http://www.example.com/git/foo-project.git
Becomes
http://user-name@example.com/git/foo-project.git
In this case you will be requested a password each time you interact with the remote folder. This is annoying so you should stick with curl.

Then, manage your folder as you always do. First begin by pushing your first commits to your newly-made repository. Here is an example:
echo "My first commit" > README
git add README
git commit -a
git push origin master

When pushing to your repository, you may find upload package errors. A common message is:
error: unpack failed: unpack-objects abnormal exit
Don’t panic. You made it well. It should not occur normally but it may happen in certain environments. This is a write permission issue. Be sure to have the repository “objects” set with correct write permissions to allow a push to be written correctly in remote repository.

An additional tip…
There are also a nice pure php solution to allow you to have a gitweb-like service in pure PHP.
GitPHP is a web frontend for git repositories. This is extremely handy in a shared hosting environment as you do not need to set httpd.conf and you don’t need root rights on your server.

This post is for fans of Monster Hunter and Database stuff.
If you study database systems, you can use this post as an application example.

You can find on the web a lot of applications that permits you to search charms of Monster Hunter Portable 3rd.
A couple of famous examples with:

The point is: How the hell did they built that?
Well, they built an interface application (javascript or php) that interacts with a database whose structure is more or less as in the picture below.

DB Charms details

This image shows the different table of the database.
(*) means the primary keys of each table. Multiple (*) means that the primary keys is built on multiple column data.
Arrows indicate the dependencies between each table. It is called a FOREIGN KEY.

The most important part is the detailed table. This one contains all the data for each Charms (Skills, points, charm name, stone name, table number). And as it is the one using the most data, it has been reduced a maximum to use only numerical IDs.

Creating those tables can be made with the following SQL for stone table:
CREATE TABLE stone_table (
stone_id integer,
name_jp varchar(30),
name_en varchar(30),
CONSTRAINT stone_list_key PRIMARY KEY(stone_id)); -- stone_id is now unique!!

Charms and Skill table have the same shape, so I think you can imagine how their SQL looks :) .
For the detailed table, something like that:

CREATE TABLE detailed_table (
range_id integer,
fabrication_id integer,
charm_id integer,
stone_id integer,
skill_1_id integer,
skill_point_1 integer,
skill_2_id integer,
skill_point_2 integer,
slots integer,
table_id integer,
CONSTRAINT detailed_key PRIMARY KEY(charm_id, seed_num), -- unique ID for each Charm
FOREIGN KEY (charm_id) references charms_table(charm_id), -- refers to charm table
FOREIGN KEY (stone_id) references stone_table(stone_id), -- refers to stone table
FOREIGN KEY (skill_1_id) references skill_table(skill_id), -- refers to skill table
FOREIGN KEY (skill_2_id) references skill_table(skill_id)); -- refers to skill table

A little bit of technical background…

  • PRIMARY KEY is a table constraint that forces the tuples (the lines) of the tables to have a unique value of it, this permits to avoid duplicate values in the table.
  • FOREIGN KEY a table constraint that permits to refer to another table.

Why using such a structure database structure?
For Charms searcher, the table containing the most data is the detailed table, so you need a light way to manage it.

For instance, imagine you want to look for Charms having the Stone name “Soldier Stone”.
You have to look in the stone table where is the line of the soldier Stone, and take its ID.
SELECT stone_id
FROM stone_table
WHERE en_name LIKE 'Soldier Stone';

With this ID, simply look for the data in the detailed table.
SELECT *
FROM detailed_table
WHERE stone_id = ??

?? being the value you found previously in the stone table.

Then the secret is to limit the number of requests to the Database if you deal with a large amount of data.
This can be done in 1 query to the charms Database with JOIN mechanisms.
And don’t worry, this is used in the current version of the Charms Searcher ;)

©2010-2013 Michael Paquier All content is ©Copyright of Otacoo.com 2010-2013. Privacy Policy - Terms of Use