The Correct way to Backup PostgreSQL

Begin a full time DBA can make you realize how important backups are. Enterprise database systems like Oracle and Microsoft's SQL Server provide excellent backup mechanisms for doing "live" backups of the databases that guarantee consistency and allow for point-in-time recovery.

In the open source world, however, we're left with old skoul methods of doing backups such as cold backups, or even worse, text-based "dumps" of the databases. I'll let you waste your time reading about how MySQL "recommends" you backup a database, and how PostgreSQL's pg_dump works. These methods are very resource intensive (creating a bunch of "insert" statements and formatting everything as plain text) and can even lock our real working processes (in the case of MySQL) while it is doing the backup!



Thankfully, the folks as PostgreSQL introduced Online-Backups in version 8.0. In my opinion, this is the single biggest advancement in this release of the database engine. It how handles it's backup is a more Oracle-before-RMAN fashion (which worked great for many many years for Oracle.) It basically works like this:

1. You setup the database instance to make use of, and keep historical "logs" (WAL for PostgreSQL, "arhive redo logs" for Oracle.) A contiguous chain of these logs, plus a full backup from some point in the past (more in this in a second) can be used to "replay" every transaction/change in your database. MySQL sort of implements this with it's binary logs, but it is not quite the same thing.


2. You tell the database how to backup these "logs"


3. You do a "full backup" of the database which is on-line, so no body is blocked from doing any work. In fact, the only work involved in this backup is really the I/O operations required to do a binary copy of the data files to a backup location.


4. Once you tell the database engine you are done with the backup, it will start (or continue) backing up the logs to a location of your choosing.

In the event of a failure or need for recovery you can take the full backups (step 3) and apply the saved logs (steps 1, 2 and 4) to recover your database. Even cooler then that, you can point-in-time recovery. Which essentially means that if you had somebody "accidentally" delete a bunch of data at 1:34:00 PM, you can restore the database up to 1:33:59 PM and get back the lost data!

I'll let you read more details on the official PostgreSQL backuppage about it, but I would highly recommend this method of backup and recovery to any PostgreSQL DBA that has critical data to take care of.

ServAdmin Open Sourced

I've decided to release a web-hosting application that I had built from scratch a couple of years ago, ServAdmin. You can access the SVN repository on Google code here: http://servadmin.googlecode.com/svn/trunk/

Some of the features:

• Single administrative panel to control any number of servers (unlimited)
• Written from scratch -- does not rely on 3rd party packages
• Written in about 95% PHP5 code.
• Secure communication between servers using stunnel.
  • This allows you to have servers spread across the Internet where communications between them will happen on open networks, and still maintain a secure environment.
• OS-independent - Right now I've only written the 'Linux' server pieces of code, but this could be easily applied to any operation system. It is designed in such a way that you simply have to write the layer for whatever OS you're concerned with.
• Controls MySQL, Apache, Bind and vpopmail to make a complete hosting environment.

If anybody out there is interested in picking up development, just let me know. It is really only a few polishes away from being a complete product.

Nozbe API and .Net

While browsing this very nice 5000+ Resources to Do Just About Anything Online blog entry, I can across a very nice "Getting things Done" site named Nozbe.

What I particularly liked about it was that it had a very simple API that you can work with. Basically, you make a specially formatted request and it sends results back on a JSON response.

JSON was likely chosen as the response type for a few reasons:

• You can make the requests via AJAX so it is very easy to integrate with web sites.
• You can easily use it with PHP5's very nice json_encode/decode functions.

I, however, wanted to be able to write a windows application so that I could quickly add tasks to my project lists without having to actually open a web browser. The ideal tool would be a simple icon-tray application that I would rick click on and say "add action" (actions are what Nozbe calls what I would call a task.)

I decided to start writing a .Net application to do just this. In .Net (C#, in my case) you have to have some external library to handle the JSON parsing -- because writing it yourself would be a pointless waste of time.

Thankfully, Json.Net has done just that for us. Thanks to this nice library and about 10 minutes of fiddling around, I was able to piece together a "proof of concept" application to show myself that want I wanted to do, could be done.

For now, I'll throw up my demo application for anybody whom wants to do download and extend. If I actually follow through on completing the application, I'll post the updated app as well.

Side note: I guess I better create an action item in Nozbe to finish the application! If only there were some Windows application that would allow me to do this very easily....

- Jon

Download Nozbe .Net API Integration Proof of Concept