Upgrading Postgres on Ubuntu

Since I had a post on how to update Postgres on OS X, I thought I might as well create one for Ubuntu for my own reference, as well.

First of all, take a look at this page to see how you can use the official Postgres Apt packages. It’s important to note that if you use these packages (maybe the ones from Canonical, too, but I’ve never used those), the data directory for Postgres will be put in different locations, and the old version will continue to start if you use the /etc/init.d script. Each version will start on a different port, beginning at 5432 (the default) for the oldest version.

Be sure to change the version numbers below:

sudo apt-get install -y postgresql-$new postgresql-server-dev-$new postgresql-contrib-$new postgresql-client-$new postgresql-doc-$new
sudo -u postgres psql -p 5432 -c "select version()" #make sure your old version is still on port 5432
sudo -u postgres psql -p 5433 -c "select version()" #make sure the new version is on port 5433
sudo -u postgres pg_dumpall -p 5432 > ~/pg_backup.sql #default home path is /var/lib/postgresql
sudo -u postgres psql -p 5433 < ~/pg_backup.sql
sudo -u postgres psql -p 5433 -l #make sure your databases were created successfully
sudo apt-get remove -y postgresql-$old postgresql-server-dev-$old postgresql-contrib-$old postgresql-client-$old postgresql-doc-$old
sudo /etc/init.d/postgresql restart
sudo -u postgres psql -c "select version()" #make sure the new version is on port 5432

Upgrading Brew-Installed PostgreSQL on OSX

Here’s the tl;dr: If you want to see the full instructions, look further down:

new_pg=9.3.3 #CHANGE THIS!!
psql postgres -c "select version()" #make sure you've still got the old version running
pg_dumpall > pg_backup.sql
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/postgres.old
sudo brew switch postgresql $new_pg
initdb /usr/local/var/postgres -E utf8
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
createdb #run this if you get an error when trying to import the dump below
psql < pg_backup.sql
psql postgres -c "select version()" #make sure you've got the new version running
psql -l #see if all of your databases are still there
vacuumdb --all --analyze-only #optional
sudo brew cleanup postgresql #this will remove the older versions brew installed
sudo rm -rf /usr/local/var/postgres.old #if you're feeling really confident / don't care

Somehow, whenever I sit down to do some tinkering on my computer, it seems like a new version of PostgreSQL is ready to be installed via Brew. Since I always forget what the steps are to upgrade between major versions of Postgres, I figured I’d throw it into a blog post. Mostly for my own pleasure, but others might find it useful, as well.

First, you realize that Brew is installing the new version, and you panic! “What about my data!?” you might say. Don’t worry, the old version is still running. You can verify this by opening psql, and running select version(). You should see your old version. We need to dump your databases so that you can restore them to your new version:

pg_dumpall > pg_backup.sql

Now, we need to stop the old version, move the old cluster to a safe place (in case we need to revert for some reason), tell brew we want to use the new version as the default, and start the new one. It sounds complicated, but it’s not:

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
sudo mv /usr/local/var/postgres /usr/local/var/postgres.old
sudo brew switch postgresql 9.3.3
initdb /usr/local/var/postgres -E utf8
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Now, restore the dump into the new version:

psql < pg_backup.sql

If you get an error saying that the database (usually named the same as your login) could not be found, just crate it, using the following command:


You should now be good to go! As a bonus, if you get this error (which I did)

➜ ~ psql
dyld: Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
Referenced from: /usr/local/bin/psql
Reason: image not found
[1] 39772 trace trap psql

You either need to install readline from brew, or it needs to be re-linked:

sudo brew install readline
sudo brew link --force readline

If you had to install readline, then you will need to re-install postgres:

sudo brew reinstall postgresql

SELECT * Analogy

The DBAs at the company I work for sent out a mass email this morning “reminding” developers that using SELECT * is a bad idea. One of my friends, who usually asks me about database questions was confused about one part of the email:

If implementing these changes for an existing application, the percentage decreases will be off the time to process columns and not necessarily off the total currently used by the SQL statement.

And I came up with what I thought was a pretty good analogy:

Let’s pretend the database table is a grocery store, and you need to buy ingredients so you can bake a cake.

“SELECT *” would be like taking every single product in the store home and then figuring out which ingredients you actually need to make a cake.

Selecting individual columns would be taking your recipe with you and getting only the ingredients you need.

It still might take you a while to go through the store, especially if it’s one of those huge mega-marts (a.k.a, a really big query with lots of joins) to find the ingredients you need, but only getting the products you need will make things a lot faster when you get home.

Moving Between Two FreeBSD Servers

Due to various reasons, I’ve had to move the VPS that hosts my website and other things two different times now, and I’ve compiled a basic list of things you need to do to create a complete copy of the old server to the new one.

When you perform these steps, the it would be best to disable as many services as you can on the old server (so that some services doesn’t write a new file while you’re copying things over).

This is, of course a basic list of things to do, but it has done me good thusfar. There will be a few times where you may have to “go it on your own” (looking at KERNCONFs, here), but you should be able to figure that out yourself using Online Documentation.

Here we go! All these steps are stated as though you’re performing this on server 2, which is the one you’re moving to, unless noted otherwise.

#Install rsync
portsnap fetch
portsnap extract
cd /usr/ports/net/rsync
make install clean
#Copy users and groups over
scp -r root@server.one:/usr/home/* /usr/home/
scp root@server.one:/etc/passwd /etc/
scp root@server.one:/etc/group /etc/
scp root@server.one:/etc/master.passwd /etc/
#Copy /etc , /usr, /root, /var to new server
rsync -vrplogDtH root@server.one:/var/db/ /var/db
rsync -vrplogDtH --exclude 'bin' --exclude 'lib' --exclude 'lib32' --exclude 'libexec' root@server.one:/usr/ /usr
rsync -vrplogDtH root@server.one:/root/ /root
pwd_mkdb -p /etc/master.passwd 
#Update Kernel  ->  http://www.freebsd.org/doc/handbook/makeworld.html
csup /root/supfile
###check /usr/src/UPDATING
###check /etc/make.conf
#Update files in /etc
cd /usr/src/usr.sbin/mergemaster
./mergemaster.sh -p
#Drop to single-user mode
shutdown now
#From here, you should be able to hit enter to get a /bin/sh in recovery mode
fsck -p
mount -u /
mount -a -t ufs
swapon -a
adjkerntz -i  #If hw clock is set to local time.  `date` will show incorrect time and zone
#remove files from /usr/obj
cd /usr/obj
chflags -R noschg *
rm -rf *
#Save compile output
script /var/tmp/build.log
#Compile base system
cd /usr/src
make -j6 buildworld
#compile kernel  -- You should create your own KERNCONF here, or use GENERIC
make buildkernel KERNCONF=GALLY
make installkernel KERNCONF=GALLY
#Reboot the machine into single-user mode and ensure kernel works
shutdown -r now
### Option 4 at the FreeBSD boot loader
### Remount drives
fsck -p
mount -u /
mount -a -t ufs
swapon -a
#Install new system binaries if kernel is working correctly.
cd /usr/src
make installworld
#Run mergemaster again 
#New kernel and world are now installed.  Reboot into your new system
shutdown -h now
#Re-install ports
portmaster --list-origins > /root/installed-port-list  ## Perform this step on server 1
scp root@server.one:/root/installed-port-list /root/
portsnap fetch update
portmaster -y --clean-distfiles
portmaster --check-port-dbdir
portmaster -Faf
pkg_delete '*'
rm -rf /usr/local/lib/compat/pkg
#check /usr/local to make sure that only config files, etc are still installed
rm -rf /var/db/pkg
#install portmaster again
cd /usr/ports/ports-mgmt/portmaster && make install clean
portmaster -D --update-if-newer `cat /root/installed-port-list`
###  Do minor manual configurating.

Theme Change… Again!

I’m changing the theme for my site back to this older one.

Although I kinda liked the idea of the “quick posting” that the other one seemed to be going for, I think this theme is much more “blog-ish” and fits better overall.

In other blog updating news, you may now leave comments with an OpenID. If you don’t know what that means, read here. You can thank StackOverflow for making me really find OpenID useful. I hope you do, too.

Unrelated, but I there were some pretty sweet deals on Newegg for Black Friday (which makes me excited for Cyber Monday), some of which I took advantage of, like an 800w UPS for $119 and two 1.5TB hard drives for $59 each.

Fairly stoked about those.

A Little Creepy…

Below is an excerpt from my stats module for WordPress:

Last search terms

Date Time Terms Engine Result
November 3, 2010 19:24:07 razorback cardboard boat Yahoo page viewed
November 3, 2010 18:27:28 galador porn Google page viewed
November 3, 2010 03:31:52 derivatives made simple Google page viewed
November 3, 2010 03:31:49 derivatives made simple Google page viewed
November 2, 2010 14:11:12 derivatives math Google page viewed
November 2, 2010 02:00:04 derivatives made easy Google page viewed
November 1, 2010 14:14:59 gentoo update old installation Google page viewed
November 1, 2010 11:58:29 advice comes from Google page viewed
November 1, 2010 11:58:01 mah blog Google page viewed
November 1, 2010 10:21:02 gentoo emerge libtool file collision Google page viewed

I’m not sure what to think of that.

In unrelated news, I upgraded my MySQL to the new 5.5 RC. Haven’t noticed any extremely positive or negative performance gains, but my blog isn’t exactly super-stressing on the server. It does seem to have some nifty new features that I will eventually have to try out, though. :>

You Are my Muse…

So, I’m not sure how I haven’t gotten around to at least mentioning this on my blog, but holy crap.

I’ve knocked one more band off my “OMG, I TOTALLY, SRSLY, HAFTA SEE THEM” list. And, as you may surmise from the title, that band is Muse.

It was, as to be expected, truly spectacular. Probably one of the best concerts I’ve seen in my life. Incredible, to say the least.