PostgreSQL Upgrade Confusion

carlosinfl · 02-13-2011, 08:20 AM

So I did an upgrade this weekend of my database server at work and failed to realize that I was doing a major upgrade from 8.4 to 9.0. Now when I start the Postgres daemon, I for some reason show to instances of the server starting up:

Code:

db1:~# /etc/init.d/postgresql start
Starting PostgreSQL 8.4 database server: main.
Starting PostgreSQL 9.0 database server: main.

I checked and verified that there are 8.4 & 9.0 configuration folders:

Code:

db1:/etc/postgresql# ls -l
total 8
drwxr-xr-x 3 postgres postgres 4096 Dec 28 13:19 8.4
drwxr-xr-x 3 postgres postgres 4096 Feb 12 10:17 9.0

Anyone know if I did something wrong or if this is normal behavior for doing a major upgrade on Debian? Do I need to migrate my 8.4 to 9.0 clusters and then purge all 8.4 data or am I suppose to leave both versions installed and running together?

So my question is why is my Debian server starting two different versions of the same application? Shouldn't it just start the new 9.0 or does it do this to avoid data loss from 8.4?

I checked to see what exactly's running:

Code:

db1:~# ps -ef | grep -i "postgres" | grep -v grep
postgres   750     1  0 10:17 ?        00:00:00 /usr/lib/postgresql/9.0/bin/postgres -D /var/lib/postgresql/9.0/main -c config_file=/etc/postgresql/9.0/main/postgresql.conf
postgres   752   750  0 10:17 ?        00:00:00 postgres: writer process                                                                                                    
postgres   753   750  0 10:17 ?        00:00:00 postgres: wal writer process                                                                                                
postgres   754   750  0 10:17 ?        00:00:00 postgres: autovacuum launcher process                                                                                       
postgres   755   750  0 10:17 ?        00:00:00 postgres: stats collector process                                                                                           
postgres  1862     1  0 11:19 ?        00:00:00 /usr/lib/postgresql/8.4/bin/postgres -D /var/lib/postgresql/8.4/main -c config_file=/etc/postgresql/8.4/main/postgresql.conf
postgres  1864  1862  0 11:19 ?        00:00:00 postgres: writer process                                                                                                    
postgres  1865  1862  0 11:19 ?        00:00:00 postgres: wal writer process                                                                                                
postgres  1866  1862  0 11:19 ?        00:00:00 postgres: autovacuum launcher process                                                                                       
postgres  1867  1862  0 11:19 ?        00:00:00 postgres: stats collector process

lqdummy · 02-13-2011, 03:28 PM

It can be surprising, but you are correct (and so is the software

): Debian does not auto-upgrade your database clusters. It's all explained, please see:
$ zless /usr/share/doc/postgresql-9.0/README.Debian.gz

in short, you need to do:
$ sudo -u postgres -s
$ pg_dropcluster 9.0 main --stop
$ pg_upgradecluster 8.4 main

Now, double check that the new cluster is working correctly before removing the old one!
$ pg_dropcluster 8.4 main

If you have other clusters, you need to upgrade them manually. Check with:
$ pg_lsclusters

You also need to manually remove postgres-8.4 when all databases are upgraded.

edit: what you are "supposed" to do is up to you: postgres-8.4 is the default in Squeeze, so it will probably be supported for another 4 years. You can choose to stick with 8.4, or migrate to 9.0. You can also keep current databases on 8.4, and create new databases on 9.0. But if you keep the default setup (i.e. the "main" cluster on both versions), you *will* have to rename/delete one of them when you decide to upgrade the 8.4 cluster.