Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Upgrade PostgreSQL 9.1 to 9.3 on Ubuntu 12.04
echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.3 postgresql-server-dev-9.3 postgresql-contrib-9.3 -y
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS hstore;'"
sudo su - postgres -c "psql template1 -p 5433 -c 'CREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";'"
sudo su - postgres -c "service postgresql stop"
sudo su - postgres -c '/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"'
sudo apt-get remove postgresql-9.1 -y
sudo sed -i "s:5433:5432:g" /etc/postgresql/9.3/main/postgresql.conf
sudo service postgresql restart
@railsfactory-kumaresan

This comment has been minimized.

Copy link

commented Aug 22, 2014

Awesome ...It worked Great !

@pinglamb

This comment has been minimized.

Copy link

commented Aug 27, 2014

I found that this command has error.

sudo su - postgres -c '/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"'

because it tries to run:

"/usr/lib/postgresql/9.3/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/postgresql/9.3/main/" -o "-p 50432 -b -c autovacuum_multixact_freeze_max_age=2000000000  -c synchronous_commit=off -c fsync=off -c full_page_writes=off-c config_file=/etc/postgresql/9.3/main/postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/postgresql'" start

You can see this error in log:

waiting for server to start....FATAL:  parameter "full_page_writes" requires a Boolean value

Therefore I updated it to be like this:

sudo su - postgres -c '/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"'
@woto

This comment has been minimized.

Copy link

commented Oct 6, 2014

Cool! Thanks!

@Sunno

This comment has been minimized.

Copy link

commented Oct 9, 2014

I lost my data -_-

@geoom

This comment has been minimized.

Copy link

commented Oct 27, 2014

good ! thnks @ibussieres and @pinglamb

@cwjenkins

This comment has been minimized.

Copy link

commented Nov 7, 2014

Yes, thank you @ibussieres and @pinglamb... ran into the same issue and the updated command resolved it!

@galch99

This comment has been minimized.

Copy link

commented Jan 27, 2015

Only the install user can be defined in the new cluster
Failure, exiting
9.2 to 9.4
how to fix ?

@toxaq

This comment has been minimized.

Copy link

commented Jan 27, 2015

Thanks to @ibussieres for the script and @pinglamb for the fix.

@GarPit

This comment has been minimized.

Copy link

commented Feb 20, 2015

Awesome! Helped me to upgrade from 9.2 to 9.3

@soniaprevost

This comment has been minimized.

Copy link

commented Jul 15, 2015

supercool !

@arturmartins

This comment has been minimized.

Copy link

commented Nov 12, 2015

Amazing! Helped me to upgrade from 9.2 to 9.3

@lokesh-webonise

This comment has been minimized.

Copy link

commented Dec 28, 2015

Worked for me :)

@edib

This comment has been minimized.

Copy link

commented Nov 23, 2016

I tried it to upgrade 9.3 to 9.6, and it worked! Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.