Welcome to End Point’s blog

Ongoing observations by End Point people

Graphing System Statistics with Grafana

Graphing System Statistics (the old fashioned way)

Since the mid 2000s system administrators who wanted to have a visual representations of their systems statistics had access to Graphite. This tool allows for elaborating graphs of values collected periodically to provide a representation of the data over time. Coupling this feature with collectd, which among many built-in supported metrics offer the possibility of sending system statistics to a central location running Graphite, allows to create a single portal for viewing statistics of your entire infrastructure easily. While this still remains a nice setup the graphical visualization capabilities of Graphite and rrdtool left some room for growth.

Enter Grafana

Grafana is a Graphite installation front-end that offers a very robust graphing/plotting library (provided by Flot) along with templates for creating similar displays for multiple datasets. Here you can see a comparison of the same dataset in both graphing libraries:

Graphite (rrdtool)

Grafana (Flot)

Data Analysis

Once you have setup collectd and Graphite to gather simple metrics from the servers you wish to monitor, you will easily have some basic instrumentation for focusing on system resources monitoring which is very helpful to keep an eye on your infrastructure performance and health. However the more data you start to harvest with collectd's many plugins the bigger will be the need for some aggregation and analysis of the data to better understand what the data could be communicating. In this example there are two graphs, the top is a measure of the network traffic going across the external interface of a network firewall, and the bottom is a measure of the total traffic transformed using a logarithmic base 10 function on the data.

Within the logarithmic graph it's easier to perceive the magnitude of the value, as a change in that graph of 1.0 in either direction would reflect a 10 fold change in the underlying data. Using this approach gives an operator a view of magnitude of the change and would so being able to easily track any spikes in the data values. Luckily Graphite offers a huge number of possible functions to elaborate the data before actually displaying it in the graph, they are all clearly documented here.


Going further you may want different contextual groups to aggregate systems by host or by application and with Grafana you can create a dashboard view which is customizable and can be populate with all the needed data. Here is an example of a dashboard for a system we have already seen:

Mousing over any of the data within the charts will allow for detailed examination of the data values measured at that time period and provides the legend for each color in the chart. Changing the dataset timeframe is as simple as adjusting the dropdown near the top of the page, or clicking and dragging a duration onto one of the graphs. The graphing library Flot provides a huge number of features and a very modern visual style which improved on what Graphite had to offer with rrdtool.


Graphite and collectd offered (and still do) a really robust data collection and analysis tools for measuring groups of computers. However this data seemed trapped in a display front-end which just could not meet the needs administrators who wanted to investigate deeper the collected data. Now Grafana provides a vastly improved graphing engine (also thanks to Flot), and combines all the needed tools like templates and dashboards to really empower users and system administrators about what they could do with the collected data. We won't be the first to say it but we do confirm that the combination of the great gathering and analysis capabilities powered by Graphite and collectd with a robust front-end with Grafana creates a very powerful tuning and monitoring stack.

Coding style guides across languages


Before you can start programming production code in any programming language you should know a few things like syntax, purpose, coding standards, good practices. There is another thing that is really important too and can help developers that work with multiple projects and languages: coding style guides. This is just a set of rules that developers should take care about when writing code. They are not so different across various programming languages. In this article, I will sum up and list coding style guides for the most popular languages.

It's not a crime to use different styling, but standardizing this helps in the process of creating software. When your team is using the same standard it's much easier to read each other's code.

There is one universal resource when it comes to programming style guides. Many of you will already have read it: the "Clean Code" book by Robert C. Martin.

Something else that should be mentioned here:

  1. When you don't know how to write a structure, read guides.
  2. When you know how to write a structure but it's different than standardized, ask other developers about it.
  3. When your structure is different than all the other structures made by your co-workers, consider changing it.
  4. When someone will force you to change your styling code, ask her/him/them why, even if she/he/they are your manager or boss.
  5. When there is no good coding style guide for your language, create one.
  6. Try to have the same habits of coding in your team and company -- it's a time saver.
  7. Even if you've adapted to some structures be open for change.
  8. Respect experience. If senior developers tell you that your code is weird, there is probably something wrong with it.

Order of the guides is not random, most important and trusted first. Not all the guides cover all the topics so it would be wise to real all of them when you plan to write code in such a language.

Let's begin.








Visual Basic .NET


Visual Basic





Maybe it's not the most important programming part but it's something you need to think about.

If we will try to compare those rules to human language, English for example, see "Romeo and Juliet", Act III:

Come, come, thou art as hot a Jack in thy mood as
any in Italy, and as soon moved to be moody, and as
soon moody to be moved.

Now try to remove writing standards:

MERCUTIO Come, come, thou art as hot a Jack in thy mood as any in Italy, and as soon moved to be moody, and as soon moody to be moved.

It's not easy to read this one liner. Our code is the same: Be smart, listen to more experienced people, read style guides and you will make live easier for you and other people too.

Perl’s CPAN is 20 years old

This is just a short note to celebrate the fact that the Comprehensive Perl Archive Network (CPAN) turned 20 years old yesterday!

CPAN began as a way to collect, mirror, and distribute open-source Perl packages. Over time it led to development of better packaging and module naming conventions; formed a community for feedback, testing, and contributions; and became one of the great strengths of the Perl world.

It is rare to find some needed functionality that is not available on CPAN. These days a more common problem is finding too much choice there, and needing to choose between several modules based on which are better maintained and supported on current versions of Perl, or kept current against external dependencies.

Perl does not get as much press these days as it once did, but it continues to thrive and improve. On that topic, our former co-worker Steph Skardal sent me an article called We’re still catching up to Perl by Avdi Grimm of Ruby fame. It is not an in-depth language comparison, just a brief observation to his fellow Rubyists that there is plenty to be learned from Perl. (Of course Perl has plenty to learn from Ruby and other languages too.)

Usability: Don't Make Me Think and a Bookmarklet

Hi! Steph here, former long-time End Point employee now blogging from afar as a software developer for Pinhole Press. While I’m no longer an employee of End Point, I’m happy to blog and share here.

I’m only about a quarter of the way into Don’t Make Me Think (Revised) by Steve Krug, but I can already tell it’s a winner. It’s a great (and quick) book about web usability, with both high level concepts and nitty gritty examples. I highly recommend it! Even if you aren’t interested in web usability but are a web developer, it’s still a quick read and would be invaluable to whomever you are coding for these days.

A Bookmarklet

The book inspired me to write a quick bookmarklet to demonstrate some high level concepts related to usability. Here’s the bookmarklet:

javascript:(function() {
  var possible = " ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";
  $('*:not(iframe)').contents().filter(function() {
     return this.nodeType == Node.TEXT_NODE && this.nodeValue.trim() != '';
  }).each(function() {
     var new_content = '';
     for(var i = 0; i<this.nodeValue.trim().length; i++) {
       new_content += possible.charAt(Math.floor(Math.random() * possible.length));
     this.nodeValue = new_content;

To add the bookmarklet to your browser, simply copy the code in as the location for a new bookmark (and name it anything you want). Note that this particular bookmarklet assumes jQuery is installed, so it may not work on all websites. Gist available here

What does it do?

In short, the bookmarklet converts readable text on the page to jibberish (random characters of the same length). Pictures are worth a thousand words here. Here are some example pages with the bookmarklet in action:

End Point home page.

End Point client list page.

Stance popup, with item in cart. product listing page.

CityPASS home page.

Why does this matter?

The bookmarklet provokes thought related to high level usability concepts, such as:

  • Is it clear which buttons are clickable?
  • Is the visual hierarchy clear?
  • What conventions does the user interface follow?
  • Users browsing behavior is often to hyperfocus and click on what they are looking for while ignoring other content entirely. Does the user interface aid or hinder that behavior?
  • How and what do images communicate on the page?

All of these ideas are great things to talk through when implementing user interface changes or rolling out an entirely new website. And if you are interested in learning more, visit Steve Krug’s website.

Bucardo replication from Postgres to sqlite and mariadb using pgbench

While Bucardo is known for doing "multi-master" Postgres replication, it can do a lot more than simple "master to master" replication (better known as "source to source" replication). As people have been asking for simple Bucardo Bucardo 5 recipes based on pgbench, I decided to present a few here. Since Bucardo allows any number of sources and targets, I will demonstrate a source-source-source-target replication. Targets do not have to be Postgres, so let's also show that we can do source - MariaDB - SQLite replication. Because my own boxes are so customized, I find it easier and more honest when writing demos to start with a fresh system, which also allows you to follow along at home. For this example, I decided to fire up Amazon Web Services (AWS) again.

After logging in at, I visited the AWS Management Console, selected "EC2", clicked on "Launch Instance", and picked the Amazon Linux AMI (in this case, "Amazon Linux AMI 2015.03 (HVM), SSD Volume Type - ami-1ecae776"). Demos like this require very little resources, so choosing the smallest AMI (t2.micro) is more than sufficient. After waiting a couple of minutes for it to start up, I was able to SSH in and begin. The first order of business is always updating the box and installing some standard tools. After that I make sure we can install the most recent version of Postgres. I'll skip the initial steps and jump to the Major Problem I encountered:

$ sudo yum install postgresql94-plperl
Error: Package: postgresql94-plperl-9.4.4-1PGDG.rhel6.x86_64 (pgdg94)
           Requires: perl(:MODULE_COMPAT_5.10.1)
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Well, that's not good (and the "You could try" are useless in this case). Although all the other Postgres packages installed without a problem (postgresql94, postgresql94-server, and postgresql94-libs), there is a major incompatibility preventing Pl/Perl from working. Basically, the rpm was compiled against Perl version 5.10, but Amazon Linux is using 5.16! There are many solutions to this problem, from using perlbrew, to downgrading the system Perl, to compiling Postgres manually. However, this is the Age of the Cloud, so a simpler solution is to ditch this AMI and pick a different one. I decided to try a RHEL (Red Hat Enterprise Linux) AMI. Again, I used a t2.micro instance and launched RHEL-7.1 (AMI ID RHEL-7.1_HVM_GA-20150225-x86_64-1-Hourly2-GP2). As always when starting up an instance, the first order of business when logging in is to update the box. Then I installed some important tools, and set about getting the latest and greatest version of Postgres up and running:

$ sudo yum update
$ sudo yum install emacs-nox mlocate git perl-DBD-Pg

Checking the available Postgres version reveals, as expected, that it is way too old:

$ sudo yum list postgresql*-server
Loaded plugins: amazon-id, rhui-lb
Available Packages
postgresql-server.x86_64        9.2.13-1.el7_1        rhui-REGION-rhel-server-release

Luckily, there is excellent support for Postgres packaging on most distros. The first step is to find a rpm to use to get the "pgdg" yum repository in place. Visit and choose the latest version (as of this writing, 9.4). Then find your distro, and copy the link to the rpm. Going back to the AWS box, add it in like this:

$ sudo yum localinstall

This installs a new entry in the /etc/yum.repos.d/ directory named pgdg-94-redhat.repo. However, we want to make sure that we never touch the old, stale versions of Postgres provided by the native yum repos. Keeping it from appearing is as simple as finding out which repo it is in, and adding an exclusion to that repository section by writing exclude=postgres*. Finally, we verify that all yum searches for Postgres return only the 9.4 items:

## We saw above that repo was "rhui-REGION-rhel-server-release"
## Thus, we know which file to edit
$ sudo emacs /etc/yum.repos.d/redhat-rhui.repo
## At the end of the [rhui-REGION-rhel-server-releases] section, add this line:

## Now we can retry the exact same command as above
$ sudo yum list postgresql*-server
Loaded plugins: amazon-id, rhui-lb
Installed Packages
postgresql94-server.x86_64        9.4.4-1PGDG.rhel7        pgdg94

Now it is time to install Postgres 9.4. Bucardo currently needs to use Pl/Perl, so we will install that package (which will also install the core Postgres packages for us). As we are going to need the pgbench utility, we also need to install the postgresql-contrib package.

$ sudo yum install postgresql-plperl postgresql-contrib

This time it went fine - and Perl is at 5.16.3. The next step is to start Postgres up. Red Hat has gotten on the systemd bandwagon, for better or for worse, so gone is the familiar /etc/init.d/postgresql script. Instead, we need to use systemctl. We will find the exact service name, enable it, then try to start it up:

$ systemctl list-unit-files | grep postgres
## postgresql-9.4.service                      disabled

$ sudo systemctl enable postgresql-9.4
ln -s '/usr/lib/systemd/system/postgresql-9.4.service' '/etc/systemd/system/'
$ sudo systemctl start postgresql-9.4
Job for postgresql-9.4.service failed. See 'systemctl status postgresql-9.4.service' and 'journalctl -xn' for details.

As in the pre-systemd days, we need to run initdb before we can start Postgres. However, the simplicity of the init.d script is gone (e.g. "service postgresql initdb"). Poking in the systemd logs reveals the solution:

$ sudo systemctl -l status postgresql-9.4.service
postgresql-9.4.service - PostgreSQL 9.4 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.4.service; enabled)
   Active: failed (Result: exit-code) since Wed 2015-08-03 10:15:25 EDT; 1min 21s ago
  Process: 11916 ExecStartPre=/usr/pgsql-9.4/bin/postgresql94-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Aug 03 10:15:25 ip- systemd[1]: Starting PostgreSQL 9.4 database server...
Aug 03 10:15:25 ip- postgresql94-check-db-dir[11916]: "/var/lib/pgsql/9.4/data/" is missing or empty.
Aug 03 10:15:25 ip- postgresql94-check-db-dir[11916]: Use "/usr/pgsql-9.4/bin/postgresql94-setup initdb" to initialize the database cluster.
Aug 03 10:15:25 ip- postgresql94-check-db-dir[11916]: See %{_pkgdocdir}/README.rpm-dist for more information.
Aug 03 10:15:25 ip- systemd[1]: postgresql-9.4.service: control process exited, code=exited status=1
Aug 03 10:15:25 ip- systemd[1]: Failed to start PostgreSQL 9.4 database server.
Aug 03 10:15:25 ip- systemd[1]: Unit postgresql-9.4.service entered failed state.

That's ugly output, but what can you do? Let's run initdb, start things up, and create a test database. As I really like to use Postgres with checksums, we can set the environment variables to pass that flag to initdb. After that completes, we can startup Postgres.

$ sudo PGSETUP_INITDB_OPTIONS=--data-checksums /usr/pgsql-9.4/bin/postgresql94-setup initdb
Initializing database ... OK

$ sudo systemctl start postgresql-9.4

Now that Postgres is up and running, it is time to create some test databases and populate them via the pgbench utility. First, a few things to make life easier. Because pgbench installs into /usr/pgsql-9.4/bin, which is certainly not in anyone's PATH, we will put it in a better location. We also want to loosen the Postgres login restrictions, and reload Postgres so it takes effect:

$ sudo ln -s /usr/pgsql-9.4/bin/pgbench /usr/local/bin/
$ sudo sh -c 'echo "local all all trust" > /var/lib/pgsql/9.4/data/pg_hba.conf'
$ sudo systemctl reload postgresql-9.4

Now we can create a test database, put the pgbench schema into it, and then give the pgbench_history table a primary key, which Bucardo needs in order to replicate it:

$ export PGUSER=postgres
$ createdb test1
$ pgbench -i --foreign-keys test1
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
creating tables...
100000 of 100000 tuples (100%) done (elapsed 0.10 s, remaining 0.00 s).
set primary keys...
set foreign keys...
$ psql test1 -c 'alter table pgbench_history add column hid serial primary key'

We want to create three copies of the database we just created, but without the data:

$ createdb test2
$ createdb test3
$ createdb test4
$ pg_dump --schema-only test1 | psql -q test2
$ pg_dump --schema-only test1 | psql -q test3
$ pg_dump --schema-only test1 | psql -q test4

Next up is installing Bucardo itself. We shall grab version 5.4.0 from the git repository, after cryptographically verifying the tag:

$ git clone
Cloning into 'bucardo'...
$ cd bucardo
$ gpg --keyserver --recv-keys 2529DF6AB8F79407E94445B4BC9B906714964AC8

$ git tag -v 5.4.0
object f1f8b0f6ed0be66252fa203c20a3f03a9382cd98
type commit
tag 5.4.0
tagger Greg Sabino Mullane  1438906359 -0400

Version 5.4.0, released August 6, 2015
gpg: Signature made Thu 06 Aug 2015 08:12:39 PM EDT using DSA key ID 14964AC8
gpg: please do a --check-trustdb
gpg: Good signature from "Greg Sabino Mullane "
gpg:                 aka "Greg Sabino Mullane (End Point Corporation) "
gpg: WARNING: This key is not certified with a trusted signature!
gpg:          There is no indication that the signature belongs to the owner.
Primary key fingerprint: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8

$ git checkout 5.4.0

Before Bucardo can be fully installed, some dependencies must be installed. What you need will depend on what your particular OS already has. For RHEL 7.1, this means a few things via yum, as well as some things via the cpan program:

$ sudo yum install perl-Pod-Parser perl-Sys-Syslog perl-Test-Simple perl-ExtUtils-MakeMaker cpan
$ echo y | cpan
$ (echo o conf make_install_make_command "'sudo make'"; echo o conf commit) | cpan
$ cpan boolean DBIx::Safe

## Now we can install the Bucardo program:
$ perl Makefile.PL
$ make
$ sudo make install

## Setup some directories we will need
$ sudo mkdir /var/run/bucardo /var/log/bucardo
$ sudo chown $USER /var/run/bucardo /var/log/bucardo

## Install the Bucardo database:
$ bucardo install ## hit "P" twice

Now that Bucardo is ready to go, let's teach it about our databases and tables, then setup a three-source, one-target database sync (aka multimaster or master-master-master-slave)

$ bucardo add db A,B,C,D dbname=test1,test2,test3,test4
Added databases "A","B","C","D"

$ bucardo add all tables relgroup=bench
Creating relgroup: bench
Added table public.pgbench_branches to relgroup bench
Added table public.pgbench_tellers to relgroup bench
Added table public.pgbench_accounts to relgroup bench
Added table public.pgbench_history to relgroup bench
New tables added: 4

$ bucardo add all sequences relgroup=bench
Added sequence public.pgbench_history_hid_seq to relgroup bench
New sequences added: 1

$ bucardo add sync btest relgroup=bench dbs=A:source,B:source,C:source,D:target
Added sync "btest"
Created a new dbgroup named "btest"

$ bucardo start
Checking for existing processes
Starting Bucardo

Time to test that it works. The initial database, "test1", should have many rows in the pgbench_accounts table, while the other databases should have none. Once we update some of the rows in the test1 database, it should replicate to all the others. Changes in test2 and test3 should go everywhere as well, because they are source databases. Changes made to the database test4 should stay in test4, as it is only a target.

$ psql test1 -xtc 'select count(*) from pgbench_accounts'
count | 100000

$ for i in {2,3,4}; do psql test$i -xtc 'select count(*) from pgbench_accounts'; done
count | 0
count | 0
count | 0

## We want to "touch" these four rows to make sure they replicate out:
$ psql test1 -c 'UPDATE pgbench_accounts set aid=aid where aid <= 4'

$ for i in {2,3,4}; do psql test$i -xtc 'select count(*) from pgbench_accounts'; done
count | 4
count | 4
count | 4

$ for i in {1,2,3,4}; do psql test$i -xtc "update pgbench_accounts set abalance=$i*100 where aid=$i"; done
$ psql test1 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |        0
$ psql test2 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |        0
$ psql test3 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |        0
$ psql test4 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |      400

What happens if we change aid '4' on one of the sources? The local changes to test4 will get overwritten:

$ psql test1 -c 'update pgbench_accounts set abalance=9999 where aid = 4'
$ psql test4 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |      100
   2 |      200
   3 |      300
   4 |     9999

Let's create one more sync - this time, we want to replicate our Postgres data to a MariaDB and a SQLite database. (Bucardo can also do systems like Oracle, but getting it up and running is NOT an easy task for a quick demo like this!). The first step is to get both systems up and running, and provide them with a copy of the pgbench schema:

## The program 'sqlite3' is already installed, but we still need the Perl module:
$ sudo yum install perl-DBD-SQLite

## MariaDB takes a little more effort
$ sudo yum install mariadb-server ## this also (surprisingly!) installs DBD::MySQL!

$ systemctl list-unit-files | grep maria
mariadb.service                             disabled
$ sudo systemctl enable mariadb
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/'
$ sudo systemctl start mariadb

$ sudo mysql
mysql> create user 'ec2-user'@'localhost' identified by 'sixofone';
mysql> grant all on *.* TO 'ec2-user'@'localhost';
mysql> quit

## Store the MariaDB / MySQL password so we don't have to keep entering it:
$ cat > ~/.my.cnf 
password = sixofone

Now we can create the necessary tables for both. Note that SQLite does not allow you to change a table's structure once it has been created, so we cannot use the MySQL/Postgres way of using ALTER TABLE after the fact to add the primary keys. Knowing this, we can put everything into the CREATE TABLE statement. This schema will work on all of our systems:

CREATE TABLE pgbench_accounts (
    aid      integer NOT NULL PRIMARY KEY,
    bid      integer,
    abalance integer,
    filler   character(84)
CREATE TABLE pgbench_branches (
    bid      integer NOT NULL PRIMARY KEY,
    bbalance integer,
    filler   character(88)
CREATE TABLE pgbench_history (
    hid    integer NOT NULL PRIMARY KEY,
    tid    integer,
    bid    integer,
    aid    integer,
    delta  integer,
    mtime  datetime,
    filler character(22)
CREATE TABLE pgbench_tellers (
    tid      integer NOT NULL PRIMARY KEY,
    bid      integer,
    tbalance integer,
    filler   character(84)

$ mysql
msyql> create database pgb;
mysql> use pgb
pgb> ## add the tables here
pgb> quit

$ sqlite3 pgb.sqlite
sqlite> ## add the tables here
sqlite> .q

Teach Bucardo about these new databases, then add them to a new sync. As we do not want changes to get immediately replicated, we set this sync to "autokick off". This will ensure that the sync will only run when it is manually started via the "bucardo kick" command. Since database C is also part of another Bucardo sync and may get rows written to it that way, we need to set it as a "makedelta" database, which ensures that the replicated rows from the other sync are replicated onwards in our new sync.

## Teach Bucardo about the MariaDB database
$ bucardo add db M dbname=pgb type=mariadb user=ec2-user dbpass=fred
Added database "M"

## Teach Bucardo about the SQLite database
$ bucardo add db S dbname=pgb.sqlite type=sqlite
Added database "S"

## Create the new sync, replicating from C to our two non-Postgres databases:
$ bucardo add sync abc relgroup=bench dbs=C:source,M:target,S:target autokick=off
Added sync "abc"
Created a new dbgroup named "abc"

## Make sure any time we replicate to C, we create delta rows for the other syncs
$ bucardo update db C makedelta=on
Changed bucardo.db makedelta from off to on

$ bucardo restart
Creating /var/run/bucardo/fullstopbucardo ... Done
Checking for existing processes
Removing file "/var/run/bucardo/fullstopbucardo"
Starting Bucardo

For the final test, all changes to A, B, or C should end up on M and S!

$ for i in {1,2,3,4}; do psql test$i -xtc "update pgbench_accounts set abalance=$i*2222 where aid=$i"; done

$ psql test4 -tc 'select aid, abalance from pgbench_accounts where aid <= 4 order by aid'
   1 |     2222
   2 |     4444
   3 |     6666
   4 |     8888

$ sqlite3 pgb.sqlite 'select count(*) from pgbench_accounts'

$ mysql pgb -e 'select count(*) from pgbench_accounts'
| count(*) |
|        0 |
$ bucardo kick abc 0
Kick abc: [1 s] DONE!

$ sqlite3 pgb.sqlite 'select count(*) from pgbench_accounts'

$ mysql pgb -e 'select count(*) from pgbench_accounts'
| count(*) |
|        3 |

$ sqlite3 pgb.sqlite 'select aid,abalance from pgbench_accounts where aid <=4 order by aid'

$ mysql pgb -e 'select aid,abalance from pgbench_accounts where aid <=4 order by aid'
| aid | abalance |
|   1 |     2222 |
|   2 |     4444 |
|   3 |     6666 |

Excellent. Everything is working as expected. Note that the changes from the test4 database were not replicated onwards, as test4 is not a source database. Feel free to ask any questions in the comments below, or better still, on the Bucardo mailing list.

Streaming Replication time lag monitoring added to check_postgres

Clocks at Great Northern in Manchester, UK
I almost let this one sneak past! Guess I need to do some lag monitoring on myself. About a month or so ago, a new version of check_postgres was released, and that includes a bit of code I wrote. While the patch has been available in the git checkout for a while, now that it's in the official release and will start appearing in repos (if it hasn't already) it's probably worth writing up a quick note describing its reasoning and usage.

What's the feature? Time-based replication monitoring in the hot_standby_delay action. This was something that had been a long-standing item on my personal TODO list, and happened to scratch the itch of a couple of clients at the time.

Previously it would only take an integer representing how many bytes of WAL data the master could be ahead of a replica before the threshold is crossed:

check_hot_standby_delay --dbhost=master,replica1 --critical=16777594

This is certainly useful for, say, keeping an eye on whether you're getting close to running over your wal_keep_segments value. Of course it can also be used to indicate whether the replica is still processing WAL, or has become stuck for some reason. But for the (arguably more common) problem of determining whether a replica is falling too far behind determining what byte thresholds to use, beyond simply guessing, isn't easy to figure out.

Postgres 9.1 introduced a handy function to help solve this problem: pg_last_xact_replay_timestamp(). It measures a slightly different thing than the pg_last_xlog_* functions the action previously used. And it's for that reason that the action now has a more complex format for its thresholds:

check_hot_standby_delay --dbhost=master,replica1 --critical="16777594 and 5 min"

For backward compatibility, of course, it'll still take an integer and work the same as it did before. Or alternatively if you only want to watch the chronological lag, you could even give it just a time interval, '5 min', and the threshold only takes the transaction replay timestamp into account. But if you specify both, as above, then both conditions must be met before the threshold activates.

Why? Well, that gets in to bit about the measurement of slightly different things. As its name implies, pg_last_xact_replay_timestamp() returns the timestamp of the last transaction it received and replayed. That's fine if you have a database cluster that's constantly active 24 hours a day. But not all of them are. Some have fluctuating periods of activity, perhaps busy during the business day and nearly idle during the night. In other words, if the master isn't processing any transactions, that last transaction timestamp doesn't change.

Then there's the other end of the scale. With the SSD's/high speed disk arrays a master server may in a short interval process more transaction data than it can send over a network wire. For example, we have a system that runs an ETL process between two local databases on a master server, and generates a ton of transaction log data in a short amount of time. However even if it has many megabytes of WAL data to transmit, the replicas never get any more than a handful of seconds behind and soon catch up.

Both conditions on their own are fine. It's when both conditions are simultaneously met, when the replica is behind in both transaction log and it hasn't seen a chronologically recent transaction, that's when you know something is going wrong with your replication connection.

Naturally, this updated check also includes the chronological lag metric, so you can feed that into Graphite, or some other system of choice. Just make sure you're system handles the new metric; our Nagios system seemed to ignore it until the RRD data for the check was cleared and recreated.

Oh, and make sure your clocks are in sync. The timestamp check executes only on the replica, so any difference between its clock and the master's can show up as skew here. ntpd is an easy way to keep everything mostly synchronized, but if you really want to be sure, check_postgres also has a timesync action.

Not so popular JavaScript/HTML functionalities

There are multiple things in a front-end developer work that can be easily fixed by a native browser functions. Based on experience, JavaScript learning path for a big part of front-end developers is not perfect. Therefore, I will give you some examples, for some people as a reminder, for others as a new thing to think about. It is always good to learn, right?


W3C definition

It lets you use built-in browser functions as you wish from JavaScript, simple functions to do simple things. execCommand is very useful when you work with text ranges selected by a user. It's also popular that it comes together with a contentEditable HTML element attribute.

Practical examples

  • You want to have redo button in your application, so when user will click on it, the latest changes will be discarded.
    // after running just this, the latest user changes will be discarded (text input changes)
    execCommand('redo', false, null);
  • You want to remove text after the cursor position.
    // after running this script one character after the cursor 
    // position in a current element will be removed
    execCommand('forwardDelete', false, null);

There are more than 30 functions (look here) that can be used with a limited amount of code. You need to be careful though, keep on testing, multiple browsers have different implementations, but will be unified in the future.


W3C definition

I want you to make a small experiment. On the current page please open developer console (Firebug or DevTools) and try to run this code:

document.designMode = 'on';

Have you noticed anything? What has happened now is that the whole page is editable now, you can edit any paragraph of this page, any header, any link. It's not so practical anymore as you can't run this command on any element, only on the Document object, but it's cool to know that stuff like this exists. Every HTML element has a contentEditable attribute set now with a value set to true.


W3C definition

"contentEditable" attribute can be set to any HTML document element. What it does is it makes element editable or not. By default, every HTML element is not editable, you can easily change it by setting contentEditable attribute value to true like this:

document.getElementById('word-open').contentEditable = 'true';

If you will again run this script on the current page you will be able to edit big slogan on the left side of this text, well, only the "OPEN" word. This can be extremely useful when you need to build an online rich text editor.


W3C definition

Navigator object is not standardized yet by W3C but it's supported by around 90% of the global browsers (known and popular browsers not supporting it are IE8 and Opera Mini). It contains multiple client browser information. For example:

// will return a list of supported/installed browser plugins, you can 
// check if the user has the Flash software installed, or to check if user uses Ad-block plugin

// will return a geolocation object, you can track client geo position

// will return a browser engine name, useful when you need to check it instead 
// of a browser name


I recommend everyone interested in the front-end development to go through W3C standardization documents, read it chapter by chapter. There are resources not widely popular but very helpful.

How To: Big Beautiful Background Video

One pretty common request for every web developer is to "please, make our Stone age website look sleek and modern". Well, no more head scratching about the meaning of "sleek" or "modern" (or "the Stone age" for some?). In times of the crisp and stunning visuals there's no better way to make an impression than to use a big beautiful background video on the home page.

Paired with some impressive infinite scroll which I already covered here and a nice full-screen image gallery (which I will cover later), it will definitely help to bring your website up to date.

Lucky for us, there is a very popular library called BigVideo.js which is based on another well known library videojs, which is a wrapper around HTML5 <video> tag.

Converting the video.

To ensure the cross browser support, it's best to supply the desired video in several formats. The most common formats to use are mp4, ogg and webm. Here is the browser support chart to give you a better idea of why it's so important to use more than one format on your page.

There are a lot of ways to convert the file, but since I'm not particularly good with compression settings or codecs, I'm using the following easy workflow:

  • Upload the video to Vimeo or YouTube.
    There is even a handy 'Share' setting straight from Final Cut for us, Apple users.
  • Go to Vimeo/YouTube and download it from there.
    This way I'm leveraging these web services' optimized and perfected compressing algorithms for web and also getting the smallest file size possible without too much quality loss. The target file should ideally be less than 3MB, otherwise it will slow down your browser, especially Firefox with Firebug installed.
  • The last step is to generate webm and ogg with Firefogg

You may find another process that works best for you, but this is what works for me.

Using BigVideo.js to display video

We will need to include the libraries:

<script src="//"></script>
<script src=""></script>
<script src=""></script>

And the following javascript:

var BV = new $.BigVideo({
controls: false,
forceAutoplay: true, 
container: $('#video')});
{ type: "video/webm", src: "" },
{ type: "video/mp4", src: "" }
], {ambient: true});

Please, note the "ambient:true" setting. This setting does the trick of playing the video in the background.

Mobile and Tablet Support

The sad truth is that the video backgrounds are not supported on touch devices, because HTML5 does not allow autoplay there. Instead there will be a "play" button underneath your content and the user will need to click on it to activate the ambient video. Not so ambient anymore, right? The best option for now is to use a full screen image instead of the video as described here.

Hope you enjoyed the blog post. Let me know your thoughts!

Converting from Pivotal Tracker to Trello for project management

For larger client projects, I find it helpful to maintain a list of tasks, with the ability to re-order, categorize, and mark tasks as completed. Add in the ability to share this list with coworkers or project owners, and you have a recipe for a better record and task-list for development.

I had been using Pivotal Tracker for this purpose, but I found a lot of its features were too complicated for a small team. On the simpler side, Trello offers project "boards" that meet many needs for project management. Plus, you can accomplish a lot with the free level of Trello.

No import

After being convinced that switching from Pivotal to Trello was the right move for my current project, I was dismayed to find that Trello offers no Import functionality, at least that I could find for front-end users. (They do have an API, but I didn't relish taking time to learn this.) I could easily export my Pivotal project, but how to get those tasks into Trello cards?

One idea

In my search of Trello for an import feature, I found a feature called Email-to-board. Trello provides a custom email address for each Board you create, allowing you to send emails to this address, containing information for a new Trello card. (This email address is unique for each board, containing random letters and numbers, so only the Board owner can use it.)

What if I wrote a quick script that processed a Pivotal CSV export file, and sent an email to Trello for each row (task) in the file? The script might send out quite a few emails, but would it work? Time to try it.

Perl to the rescue

I started cooking up a simple Perl script to test the idea. With the help of some CPAN modules to easily process the CSV file and send the emails, I landed on something that worked. After running the script, each row in the CSV export became an email to my Trello board, containing the item's title, description, estimate (difficulty level), and list of tasks required to complete it.

The script should work for most exports from Pivotal Tracker, and I have published it to my GitHub account, in case it is helpful for others who decide to move to Trello.

Find the pivotal2trello script on GitHub.

If you happen to try it, let me know if you experience any problems, or have any suggestions!