Re: Upgrading from 9.0.11 to 9.3.5 on CentOS 6 (64 bit)

On 7/31/2014 11:09 PM, Phoenix Kiula wrote:
> I have Postgresql from a few years ago. That's 9.0.11.

you can upgrade to 9.0.18 painlessly. 9.1 or .2 or .3, not quite so
painless.

> During the vacuum it's basically crawling to its knees. While googling
> for this (it stops at "pg_classes" forever) I see Tom Lane suggested
> upgrading.

have you tried a vacuum full of the whole cluster, with your
applications shut down?

> So now I must. In doing so, can I follow these instructions?
> https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-a-centos-vps

those aren't upgrade instructions, those are first-time install
instructions.

> I want to make sure all my data remains exactly as it is, and the
> pgbouncer on top of PG (helps us a lot) also remains on the same port
> etc. Just want to confirm that whether I update via the RPM method, or
> the YUM method, that the settings in all the places will remain?

you will need to either pg_dumpall your old database 'cluster' and load
this into the new version, or use pg_upgrade, which is a fair bit
trickier but can do an in-place upgrade. if your databases aren't
much over a few dozen gigabytes, pg_dumpall is probably simpler than
pg_upgrade. if your databases are large, pg_dumpall -> psql restore may
take a LONG time, so the pg_upgrade process may be more efficient.

since you've never done this before, if you chose to go the pg_upgrade
route, BACKUP EVERYTHING BEFORE YOU START. it may take several tries to
get right.

> Ideally, I don't want to be linking new paths and so on as I see in
> online instructions on blogs. Many of them (e.g., the official post
> here -http://wiki.postgresql.org/wiki/FAQ#What_is_the_upgrade_process_for_PostgreSQL.3F
> ) also speak of "clusters". I don't have any, or is my PG basically
> one cluster?

in PG terminology, a 'cluster' is the set of databases in a single
instance of the postgres server, with a single $PGDATA directory. poor
choice of terms, 'instance' probably would have been more appropriate,
but its too late to change.

> Sorry for the noob question, but it would be great to get some simple
> to follow, step by step guidance. MySQL etc are so simple to upgrade!

mysql hasn't changed its core data formats in eons. but try to upgrade
from MyISAM to InnoDB, good luck.

--
john r pierce 37N 122W
somewhere on the middle of the left coast