Updating PostgreSQL 9.1 to 9.3 in Ubuntu
This was written a while back on Roon.io, and now 9.4 is out with better JSON support.
Probably somewhat similar to this. I would reference this gist as a starting point.
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
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.
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
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
sudo apt-get remove postgresql-9.1 # change port to 5432 sudo vim /etc/postgresql/9.3/main/postgresql.conf sudo service postgresql start
Hopefully you got through that smoothly. Now for the final part, We are going to enable some of the more common extensions.
hstoreis a key-value storage data type inside postgres.
uuid-osspis a Universal Unique Identifier (UUID) data type. Note the quotes that are required for this one.
tcnstands 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
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!
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.