Log in

No account? Create an account
Not a *nix Admin - Multiplayer vi [entries|archive|friends|userinfo]
Tomas Gallucci

counter customisable

[ flavors | Meta Profile ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

[Links:| Value for Value Politics Tech Reads ]

Not a *nix Admin [Sep. 10th, 2009|02:53 am]
Tomas Gallucci
[Tags|, , , , , , ]
[music |Vanilla Fudge - Bang Bang]

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)


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


if [ -d $dirName ] ; then
	cd $dirName
	mkdir $dirName
	cd $dirName

dbs=`psql -l |grep program |awk '{ print $1}'`

for dbname in $dbs;
	echo $dbname
	pg_dump -Fc -f "$dbname.backup" "$dbname"


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?

[User Picture]From: ehowton
2009-09-10 09:33 am (UTC)
Can you do it without looking past the cut?

I wouldn't be much of a unix sysadmin if I couldn't:

for x in `psql -l | grep program | awk {'print $1'}` ; do pg_dump $x > outfile ; done
(Reply) (Thread)
[User Picture]From: schpydurx
2009-09-10 10:32 am (UTC)
I originally started wanting to use a for each look but I couldn't find what I was looking for. Granted, I don't script on a regular basis. The thing that killed me wasn't the logic (outside of what was for me thinking outside the box to make the "magic" work on the line in question) it was the syntax as I haven't scripting in quite some time. I had to google everything to make sure I had a properly written script. I was struggling with parens at one point, hence the reason I did my assignment and then looped over that variable. Thanks to your insight, I can now refactor my script so it's more readable.

Also, I meant to allude to the last section of this post and the ensuing comments in this post. I saw what I did yesterday as history repeating itself.
(Reply) (Parent) (Thread)
[User Picture]From: ehowton
2009-09-10 10:56 am (UTC)
I was surprised that link didn't lead to the entry on when you melted your brand new mouse.
(Reply) (Parent) (Thread)
[User Picture]From: schpydurx
2009-09-10 10:59 am (UTC)
I hate you.
(Reply) (Parent) (Thread)