September 10, 2009

• Not a *nix Admin

  Task: Given the following output of the command psql -l write a script that will automatically back up the databases with a filename format of dbname.backup

  program@library:~$ psql -l
          List of databases
     Name    |  Owner   | Encoding  
  -----------+----------+-----------
   andrew    | program  | SQL_ASCII
   ants      | postgres | SQL_ASCII
   burr      | program  | SQL_ASCII
   central   | program  | SQL_ASCII
   district  | program  | SQL_ASCII
   garfield  | program  | SQL_ASCII
   lincoln   | program  | SQL_ASCII
   postgres  | postgres | SQL_ASCII
   smith     | program  | SQL_ASCII
   template0 | postgres | SQL_ASCII
   template1 | postgres | SQL_ASCII
   wilson    | program  | SQL_ASCII
  (12 rows)
  
  program@library:~$

  Now at first, this doesn’t seem like to hard a thing to do. You should just be able pipe this command to awk, grab the first column and then script the rest, right? Well, there’s a few problems.

  First, look at the first couple of lines of output. See how that was designed to be more readable on the screen? Pipe that to auk and you’re just SOL because you’re not picking up the database names in the first column.

  The next problem is a bit more subtle: you don’t need the databases that are owned by postgres backed up because they don’t contain customer data. Those databases are there for reference, maintenance and other esoteric reasons that only the developers of both PostgreSQL and “Program, Inc.” know and fully understand.

  So if you were a unix admin, how would you go about solving this problem? Can you do it without looking past the cut?

  I couldn’t figure it out. Now granted, I don’t typically do anything more complicated then move files around and do some basic networking diagnostics via CLI, so when the following solution (in BIG BOLD LETTERS was supplied, I had to *facepalm* that I’d missed such a simple solution.

  Here’s the script that I came up with once I had the solution to my problem:

  #! /bin/bash
  
  dirName="4_1_6"
  
  if [ -d $dirName ] ; then
  	cd $dirName
  else
  	mkdir $dirName
  	cd $dirName
  fi
  
  dbs=`psql -l |grep program |awk '{ print $1}'`
  
  
  for dbname in $dbs;
  do
  	echo $dbname
  	pg_dump -Fc -f "$dbname.backup" "$dbname"
  done
  
  cd

  I was actually quite proud of myself. The key that my guru pointed out was that you could grep for something common. He initially chose SQL_ASCII but it was after he showed me how to make parsing work that I realized I could be more efficient and look for databases only owned by the user "program".

  dirName is designed to be some kind of representation of the version of the software. When I showed the guru I asked for help with the command, he was impressed with the script. Turns out there was a much more convoluted script floating around the building, but you had to be a sed guru, bash acrobat and speak in Tongues to comprehend what was happening. My script was much more elegant.

  The only criticism i got was that I could have made the directory be an argument to the command. I will go one step further and suggest that I could create a --d flag but also have a generic program_backup as a default value for the name of the directory.

  The cool thing about this script is that all I have to do is change the string I'm grepping for in the real script (obfuscated here to protect the guilty) because the particular server that I needed to upgrade did not have the databases owned by the "program" but rather our logon for the box. Outside of this one detail, I can now use this script to backup all databases on a customer's server on the command line where I can get realtime feedback instead of having to rely on a less-than-reliable web-interface that in older versions of the software required I back up the databases one at a time and would have put those databases in the directory where the automated backup go. This becomes an issue if for some reason the upgrade doesn't go right and I have to restore all databases. If the manual backups I made are in one directory, it's very easy to tell which backup belongs to which database and I can modify this script slightly to restore all databases.

  So what do you *nix people think? Good script? Bad script? Criticisms? Is this how YOU would have solved the problem?

  • 3:47 am
  • 14 Comments