postgres

Updating PostgreSQL 9.1 to 9.3 in Ubuntu

Bryan Robles

Dec 16, 2013 • 3 min read

This was written a while back on Roon.io, and now 9.4 is out with better JSON support.

Ubuntu

Probably somewhat similar to this. I would reference this gist as a starting point.

Bonus: OSX

I would recommend upgrading to that using Heroku's Postgres App and make sure you are using the correct path when running postgres related commands such as psql. to the correct path.

You may also need to point to this app when building the pg gem or other extenstions. See this Stack Overflow.

Legacy Post : Updating to Postgres 9.3 in Ubuntu

Dying for improved JSON data-types, better Foreign Data Wrapper Support, and an even more powerful LISTEN and NOTIFY? Fear no more. Today, I will be guiding you through upgrading PostgreSQL.

Normally, you would think a simple sudo apt-get update and sudo apt-get upgrade would suffice, but nope! Here, I'll share how I updated this site to run on v9.3. This should work in Ubuntu 12.04+ as is. Any other distros may vary.

Updating PostgreSQL

We are going to take this in chucks, just to simplify the process. I credit ivanvanderbyl's gist for most of this code, along with various PostgreSQL documentation.

Updating the Repository

Change into the apt-get sources directory and add the postgres repo into it (lines 1,2)
It is then going to grab the key to verify it and add it (3,4)

Then we do the normal update and upgrade (5,6)

 cd /etc/apt/sources.list.d
 echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> pgdg.list
 wget --quiet -O -https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
 sudo apt-key add -
 sudo apt-get update
 sudo apt-get upgrade

If your updates are getting kept back, you may need to run sudo apt-get dist-upgrade. I had to, but seems like not everyone runs into this problem. See this askubuntu question.

Migrating the Data

Now we are going to migrate over our configuration and data. Notice that the last command is pointing to files specific to version of postgres you are migrating to and from. Make sure to change the paths accordingly.

Sudo into the postgres user (1)
Stop postgres (2)

Use pg_update to move the configuration (the rest)

 sudo su -l postgres
 service postgresql stop
 
 # This is one huge command
 /usr/lib/postgresql/9.3/bin/pg_upgrade\ 
 -b /usr/lib/postgresql/9.1/bin\ 
 -B /usr/lib/postgresql/9.3/bin\ 
 -d /var/lib/postgresql/9.1/main/\ 
 -D /var/lib/postgresql/9.3/main/\ 
 -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf"\ 
 -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"

At this point, you should be done with the postgres user, so go ahead and logout to your original user.

To complete the process, you will need to remove the old version of PostgreSQL and change some configuration files. In /etc/postgresql/9.2/main/postgresql.conf, you will need to change port to 5432.

sudo apt-get remove postgresql-9.1
# change port to 5432
sudo vim /etc/postgresql/9.3/main/postgresql.conf
sudo service postgresql start

Extensions

Hopefully you got through that smoothly. Now for the final part, We are going to enable some of the more common extensions.

hstore is a key-value storage data type inside postgres.
uuid-ossp is a Universal Unique Identifier (UUID) data type. Note the quotes that are required for this one.
*tcn stands for Triggered Change Notifications. This allows you to notify listeners on changes to a table.

*Warning: I couldn't figure out if it is now in core, and if so, why this extension exists. It may be just for backward compatibility. Same goes for intarray.

	psql
	CREATE EXTENSION IF NOT EXISTS hstore;
	CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
    CREATE EXTENSION IF NOT EXISTS tcn;

You should be all set up and ready to go now. If you want to learn about more extensions, you can checkout Heroku's docs or go into psql and run select * from pg_available_extensions;

Now to feed your imagination, check these links out and have fun!

PostgreSQL Resources

Tuning a Brand New Postgres install - A quick and dirty post on how to quickly tune your new postgres instance for better performance.

What Can You Do With the JSON Data Type - A small tutorial showing off some of the functionality Postgres gives you when playing with JSON.

PGRest - A JSON Document Store running inside PostgreSQL that is compatible with MongoLab REST API and Firebase's real-time API.

Postgres NOTIFY for Cache Busting - A blog post by Chris Stucchio that talks about using Event Triggers in PostgreSQL to bust caches.