Postgres 9.1 to 9.2 upgrade guide for Ubuntu 12.04

gistfile1.sh

sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2 postgresql-server-dev-9.2 postgresql-contrib-9.2

sudo su -l postgres
psql -d template1 -p 5433
CREATE EXTENSION IF NOT EXISTS hstore;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
service postgresql stop
/usr/lib/postgresql/9.2/bin/pg_upgrade -b /usr/lib/postgresql/9.1/bin -B /usr/lib/postgresql/9.2/bin -d /var/lib/postgresql/9.1/main/ -D /var/lib/postgresql/9.2/main/ -O "-c config_file=/etc/postgresql/9.2/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.1/main/postgresql.conf"

logout # logout postgresql back to previous user

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

This is perfect. Worked without any hiccups on production. Thanks!

This is perfect. Worked without any hiccups on production. Thanks!

Thanks it worked perfectly fine!

Thanks it worked perfectly fine!

Flawless! Thank you!

Flawless! Thank you!

Thank you!

Thank you!

Great. Thank you!

Great. Thank you!

Thanks!

Thanks!

This line doesn’t work

CREATE EXTENSION IF NOT EXISTS hstore;

It gives this error message:

template1=# CREATE EXTENSION IF NOT EXISTS hstore;
ERROR:  could not open extension control file "/usr/share/postgresql/9.2/extension/hstore.control": No such file or directory
template1=#

This line doesn’t work

CREATE EXTENSION IF NOT EXISTS hstore;

It gives this error message:

template1=# CREATE EXTENSION IF NOT EXISTS hstore;
ERROR:  could not open extension control file "/usr/share/postgresql/9.2/extension/hstore.control": No such file or directory
template1=#

Thank you!

Thank you!

thanks!

thanks!

This no longer work on 12.04 LTS.

E: Package 'postgresql-9.2' has no installation candidate

This no longer work on 12.04 LTS.

E: Package 'postgresql-9.2' has no installation candidate

This worked fine on ubuntu 12.04 :)

This worked fine on ubuntu 12.04 :)

Worked well for upgrading 9.0 to 9.3, too.

Worked well for upgrading 9.0 to 9.3, too.

Can any one explain me the what line no. 10 do?

Can any one explain me the what line no. 10 do?

worked well for upgrading 9.1 to 9.3 on ubuntu server 12.04 LTS
Thanks a lot

worked well for upgrading 9.1 to 9.3 on ubuntu server 12.04 LTS
Thanks a lot

How do I get 9.3?

How do I get 9.3?

Very much thanks Sir!

Very much thanks Sir!

Thank you :)

Thank you :)

Worked great for upgrading 9.2 -> 9.3 also

@ntucker Follow these directions to upgrade PostgreSQL 9.3 on Ubuntu http://wiki.postgresql.org/wiki/Apt

Worked great for upgrading 9.2 -> 9.3 also

@ntucker Follow these directions to upgrade PostgreSQL 9.3 on Ubuntu http://wiki.postgresql.org/wiki/Apt

You should be looking into using pg_upgradecluster. It's much easier than this.

You should be looking into using pg_upgradecluster. It's much easier than this.

Worked perfect for 9.1 -> 9.3
Thanks!

Worked perfect for 9.1 -> 9.3
Thanks!

👍

👍

A bit easier way:

Add apt.postgresql.org repository by running this script: http://anonscm.debian.org/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh

sudo apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2 postgresql-server-dev-9.2
su postgres
pg_ctlcluster 9.1 main stop
pg_ctlcluster 9.2 main stop
pg_dropcluster 9.2 main
pg_upgradecluster 9.1 main
pg_ctlcluster 9.2 main start
CTRL-D to quit su postgres
sudo apt-get remove postgresql-9.1

A bit easier way:

Add apt.postgresql.org repository by running this script: http://anonscm.debian.org/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh

sudo apt-get install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2 postgresql-server-dev-9.2
su postgres
pg_ctlcluster 9.1 main stop
pg_ctlcluster 9.2 main stop
pg_dropcluster 9.2 main
pg_upgradecluster 9.1 main
pg_ctlcluster 9.2 main start
CTRL-D to quit su postgres
sudo apt-get remove postgresql-9.1

The gist worked fine to upgrade from 9.1 to postgresql 9.3!
Thanks a lot!

The gist worked fine to upgrade from 9.1 to postgresql 9.3!
Thanks a lot!

Awesome, thanks.

Awesome, thanks.

Thanks a lot! You saved my time!

Thanks a lot! You saved my time!

I did it this way (for 9.3), in an automated fashion.

https://gist.github.com/ibussieres/11262268

I did it this way (for 9.3), in an automated fashion.

https://gist.github.com/ibussieres/11262268

Thanks @ibussieres. Upgraded to 9.3.

Thanks @ibussieres. Upgraded to 9.3.

Perfect! Works like a charm.

Perfect! Works like a charm.

Thanks!

Worked great...until I got to PostGIS. Check out this post for details on dealing with the simultaneous postgres 9.1->9.3, postgis 1.5->2.x upgrade: http://sorokine.blogspot.com/2013/11/upgrading-postgresql-91postgis-15-to.html

Thanks!

Worked great...until I got to PostGIS. Check out this post for details on dealing with the simultaneous postgres 9.1->9.3, postgis 1.5->2.x upgrade: http://sorokine.blogspot.com/2013/11/upgrading-postgresql-91postgis-15-to.html

Thanks @ibussieres. It worked... Awesome

Thanks @ibussieres. It worked... Awesome

Instructions for upgrading from 9.3 to 9.4: https://gist.github.com/dideler/60c9ce184198666e5ab4

Instructions for upgrading from 9.3 to 9.4: https://gist.github.com/dideler/60c9ce184198666e5ab4