PostgreSQL 8.4 on RHEL 4: Teaching an old dog new tricks
By David Christensen
May 19, 2010
So a client has been running a really old version of PostgreSQL in production for a while. We finally got the approval to upgrade them from 7.3 to the latest 8.4. Considering the age of the installation, it should come as little surprise that they had been running a similarly ancient OS: RHEL 4.
Like the installed PostgreSQL version, RHEL 4 is ancient—5 years old. I anticipated that in order to get us to a current version of PostgreSQL, we’d need to resort to a source build or rolling our own PostgreSQL RPMs. Neither approach was particularly appealing.
While the age/decrepitude of the current machine’s OS came as little surprise, what did come as a surprise was that there were supported RPMs available for RHEL 4 in the community Yum RPM repository (modulo your architecture of choice).
In order to get things installed, I followed the instructions for installing the specific yum repo. There were a few seconds where I was confused because the installation command was giving a “permission denied” error when attempting to install the 8.4 PGDG rpm as root. A little brainstorming and a lsattr later revealed that a previous administrator, apparently in the quest for über-security, had …
database postgres redhat
PostgreSQL switches to Git
By Greg Sabino Mullane
May 19, 2010
Looks like the Postgres project is finally going to be bite the bullet and switch to git as the canonical VCS. Some details are yet to be hashed out, but the decision has been made and a new repo will be built soon. Now to lobby to get that commit-with-inline-patches list to be created…
database git postgres
Finding the PostgreSQL version — without logging in!
By Greg Sabino Mullane
May 17, 2010
Metasploit used the error messages given by a PostgreSQL server to find out the version without actually having to log in and issue a “SELECT version()” command. The original article is at http://blog.metasploit.com/2010/02/postgres-fingerprinting.html and is worth a read. I’ll wait.
The basic idea is that because version 3 of the Postgres protocol gives you the file and the line number in which the error is generated, you can use the information to figure out what version of Postgres is running, as the line numbers change from version to version. In effect, each version of Postgres reveals enough in its error message to fingerprint it. This was a neat little trick, and I wanted to explore it more myself. The first step was to write a quick Perl script to connect and get the error string out. The original Metasploit script focuses on failed login attempts, but after some experimenting I found an easier way was to send an invalid protocol number (Postgres expects “2.0” or “3.0”). Sending a startup packet with an invalid protocol of “3.1” gave me back the following string:
E|SFATALC0A000Munsupported frontend protocol 3.1:
server supports 1.0 to 3.0 …database postgres security
Thrillist Buys JackThreads
By Jon Jensen
May 14, 2010
We were excited to learn yesterday that “flash sale” site JackThreads was acquired by Thrillist. Congratulations!
End Point has provided technical assistance for the members-only JackThreads store in numerous ways: provisioning hosting, setting up automated development environments, improving performance for scalability, integrating with third-party systems, and various development projects.
Though the businesses and technology are completely unrelated, End Point has also helped develop and support the growth of Backcountry.com’s several trailblazing “one deal at a time” (ODAT) sites, starting with SteepandCheap.com.
The “flash sale” business model is a lot of fun and opens up new opportunities for retail and technical innovation alike and we look forward to more work in this area.
ecommerce clients
Continuing an interrupted git-svn clone
By David Christensen
May 13, 2010
I’ve run into the issue before when using git-svn to clone a large svn repo; something interrupts the transfer, and you end up having to restart the git-svn clone process again. Attempting to git-svn clone from a partially transferred svn clone directory results in error messages from git-svn, and it’s not immediately clear what you need to do to pick the process back up from where you left off.
In the past I’ve just blown away the partially-transferred repo and started the clone over, but that’s a waste of time and server resources, not to mention extremely frustrating, particularly if you’re substantially into the clone process.
Fortunately, this is not necessary; just go into your partially retrieved git-svn repo and execute git-svn fetch. This continues fetching the svn revisions from where you left off. When the process completes, you will have empty directory with just the .git directory present. Looking at git status shows all of the project files deleted (oh noes!), however this is just misdirection. At this point, you just need to issue a git reset –hard to check out the files in the HEAD commit.
More illustratively:
$ git svn clone …git
PostgreSQL template databases to restore to a known state
By Greg Sabino Mullane
May 12, 2010
Someone asked on the mailing lists recently about restoring a PostgreSQL database to a known state for testing purposes. How to do this depends a little bit on what one means by “known state”, so let’s explore a few scenarios and their solutions.
First, let’s assume you have a Postgres cluster with one or more databases that you create for developers or QA people to mess around with. At some point, you want to “reset” the database to the pristine state it was in before people starting making changes to it.
The first situation is that people have made both DDL changes (such as ALTER TABLE … ADD COLUMN) and DML changes (such as INSERT/UPDATE/DELETE). In this case, what you want is a complete snapshot of the database at a point in time, which you can then restore from. The easiest way to do this is to use the TEMPLATE feature of the CREATE DATABASE command.
Every time you run CREATE DATABASE, it uses an already existing database as the “template”. Basically, it creates a copy of the template database you specify. If no template is specified, it uses “template1” by default, so that these two commands are equivalent:
CREATE DATABASE foobar;
CREATE DATABASE foobar …database postgres testing
Using PostgreSQL Hooks
By Josh Tolley
May 12, 2010
PostgreSQL is well known for its extensibility; users can build new functions, operators, data types, and procedural languages, among others, without having to modify the core PostgreSQL code. Less well known is PostgreSQL’s extensive set of “hooks”, available to the more persistent coder. These hooks allow users to interrupt and modify behavior in all kinds of places without having to rebuild PostgreSQL.
Few if any of these hooks appear in the documentation, mostly because the code documents them quite well, and anyone wanting to use them is assumed already to be sufficiently familiar with the code to find the information they’d need to use one. For those interested in getting started using hooks, though, an example can be useful. Fortunately, the contrib source provides one, in the form of passwordcheck, a simple contrib module that checks users’ passwords for sufficient strength. These checks include having a length greater than 8 characters, being distinct from the username, and containing both alphabetic and non-alphabetic characters. It can also use CrackLib for more intense password testing, if built against the CrackLib code.
In general, these hooks consist of global …
postgres
Tail_n_Mail does Windows (log file monitoring)
By Greg Sabino Mullane
May 9, 2010
I’ve just released version 1.10.1 of tail_n_mail.pl, the handy script for watching over your Postgres logs and sending email when interesting things happen.
Much of the recent work on tail_n_mail has been in improving the parsing of statements in order to normalize them and give reports like this:
[1] From files A to Q Count: 839
First: [A] 2010-05-08T05:10:46-05:00 alpha postgres[13567]
Last: [Q] 2010-05-09T05:02:27-05:00 bravo postgres[19334]
ERROR: duplicate key violates unique constraint "unique_email_address"
STATEMENT: INSERT INTO email_table (id, email, request, token) VALUES (?)
[2] From files C to E (between lines 12523 of A and 268431 of B, occurs 6159 times)
First: [C] 2010-05-04 16:32:23 UTC [22504]
Last: [E] 2010-05-05 05:04:53 UTC [23907]
ERROR: invalid byte sequence for encoding "UTF8": 0x????
HINT: This error can also happen if the byte sequence does not
match the encoding expected by the server, which is controlled
by "client_encoding".
## The above examples are from two separate instances, the first
## of which has the "find_line_number" option turned off
However, I’ve only ever used tail_n_mail on Linux-like systems, so it will …
database monitoring postgres