Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have installed PG 9.2 on my machine. I have a database cluster that was created about a year ago, using initdb, using postgreSQL version 8.4.

The (8.4) data cluster consumes several Gb and so its on its own separate hard disk. I instructed the old 8.4 server to use the correct data location by passing it a -D /path/to/data/folder when the server was started.

My question is this:

I have a (v8.4) database cluster (containing about 10 databases) located at /some/path/to/datadir

I have a freshly installed postgreSQL 9.2 server.

How do I upgrade the cluster (and its databases), so that the new server can use the cluster - bearing in mind that I want the data to remain in the current data location (a physically separate device mapped to '/').

I tried running the server against the old cluster file by modifying the configuration file, and I (unsurprisingly), got the following error:

 * Starting PostgreSQL 9.2 database server                                                                                                     * The PostgreSQL server failed to start. Please check the log output:
2012-11-18 23:50:31 GMT FATAL:  database files are incompatible with server
2012-11-18 23:50:31 GMT DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.2.1.

So how can I upgrade an 8.4 cluster (in a specified data directory) to be used by a 9.2 server?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

It appears you have an 8.4 database that isn't managed by pg_wrapper, and have installed 9.1 from Ubuntu packages and allowed it to create a pg_wrapper managed cluster in the default location, which isn't where you want the DB.

pg_upgradecluster from pg_wrapper isn't suitable for this, since you hand-initdb'd the old cluster.

If the 9.1 cluster doesn't have any data of value in it yet, drop it, destroying all its data:

pg_dropcluster 9.2 main

Then re-create it in the desired location. Assuming your mounted HD's root is /mydata, you'd:

pg_createcluster -d /mydata/pgdata_9.2_main 9.2 main

Now dump the old database and load it into the new one. Start both 8.4 and 9.1 on different ports; I'll assume 8.4 is on 5432 (default) and 9.2 is on 5433. Something like this should copy the dump from one directly into the other. Make sure you use the 9.1 versions of pg_dumpall and psql:

sudo -u postgres pg_dumpall -p 5432 | sudo -u postgres psql -p 5433

... but personally I'd do a pg_dumpall --globals-only using the 9.2 pg_dumpall against the 8.4 DB, followed by individual pg_dumps of each database in the old cluster (again using the 9.1 pg_dump). I'd then restore the globals dump followed by each individual dump into the new cluster.


Alternately, you can drop the pg_wrapper managed 9.2 cluster, not re-create it, and follow the instructions for pg_upgrade to do a binary upgrade from the 8.4 to the 9.2 DB. This will create a DB that is not managed by pg_wrapper. You can either convert it to be managed by pg_wrapper by moving and symlinking postgresql.conf and pg_hba.conf, or you can just manage it like you did 8.4, using your own init scripts.

share|improve this answer
    
Do I need to edit my /etc/postgresql/9.2/main/postgresql.conf file so that when postgres server is started, it automatically uses the correct data location?. BTW, I am using 9.2 now not 9.1. I suppose everything you said above holds true (after swapping 9.1 with 9.2)? –  Homunculus Reticulli Nov 19 '12 at 0:23
    
@HomunculusReticulli If you use pg_createcluster to create it where you want it, you don't have to edit the config, it's created correct. If you move the cluster you must edit the configuration to reflect the correct data directory. –  Craig Ringer Nov 19 '12 at 1:00
    
ok, thanks for your help. :) –  Homunculus Reticulli Nov 19 '12 at 1:04

Don't know about 2) and 3) (I don't use Ubuntu).

But to migrate the data from 8.4 to 9.1 pg_dump.

Assuming that that 8.4 runs on port 5432 and 9.1 runs on port 5433 something like the following will copy all the data from 8.4 to 9.1:

/usr/lib/postgresql/9.1/bin/pg_dump -p 5432 -U -c -C postgres my_db | /usr/lib/postgresql/9.1/bin/psql -p 5433 

This assumes that your current user can log on to both databases.

The full path to pg_dump is necessary to ensure that the dump is taken using the target version.

Of course you can also use an intermediate file if you prefer that. More details are in the manual: http://www.postgresql.org/docs/9.1/static/backup.html

Using the pg_dump binary from the new version is intentional.

From the manual:

It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL

share|improve this answer
    
Are you sure you have not made a typo?. You appear to use the 9.1 binary twice (but with different ports) in your command. Is this correct? –  Homunculus Reticulli Nov 18 '12 at 18:30
    
@HomunculusReticulli: yes that's correct. You have to do the dump reload using the new version. –  a_horse_with_no_name Nov 18 '12 at 18:56
    
I think there is an implicit assumption about the location of the database file in your command. How would I update the pg_dump command to dump a db stored in a custom location? (can't find that in the docs). –  Homunculus Reticulli Nov 18 '12 at 19:50
    
@HomunculusReticulli: the dump produces a SQL file which is directly piped to psql. There is no intermediate file. The "database location" is defined by the configuration of the PostgreSQL installation (in postgresql.conf or a commandline parameter respectively) –  a_horse_with_no_name Nov 18 '12 at 20:19
    
Yes, I want to know how to pass the cluster (data) directory in the command line. I have set the PGDATA envvar, but dosen't seem to have any effect. At this rate, I think I'll probably just scrap the old database and start from scratch - as painful as that will be, its likely to be quicker than the hoops I'm having to jump through ... –  Homunculus Reticulli Nov 18 '12 at 20:35

Ubuntu uses pg_wrapper to manage multiple PostgreSQL installations on a system.

Before doing anything more, make a backup of your databases.

You can use pg_upgradecluster -n 9.1 8.4 main to upgrade your 8.4 cluster to 9.1. When the upgrade completes (it'll do a dump and reload), you can pg_stopcluster 8.4 main and make sure you can still access all your data on the new cluster. Then edit postgresql.conf in the new cluster to change the port to the default 5432 and, when you're happy everything's fine, pg_dropcluster the old cluster.

Note that pg_upgradecluster does not use pg_upgrade. It does a dump and reload. For big databases you may want to manage the upgrade manually according to the PostgreSQL documentation on upgrades, using pg_upgrade or dumps. Ubuntu's odd packaging of PostgreSQL confuses pg_upgrade so the procedure in the PostgreSQL documentation won't match exactly.

See the man pages for the above-referenced commands, and less /usr/share/doc/postgresql-common/README.Debian.gz

share|improve this answer
    
Did you notice that my data directory is not in 'main' ? –  Homunculus Reticulli Nov 18 '12 at 20:11
    
@HomunculusReticulli No, I didn't, but you can have any datadir location while still using pg_wrapper; see pg_createcluster --help. So: Did you manually initdb the new cluster? Are you using Ubuntu packages for 9.1 and if not, how did you install it? –  Craig Ringer Nov 18 '12 at 23:01
    
I have removed 8.4 binaries from my machine completely, as I am quickly running out of both time and patience. I will readup on pg_createcluster as you suggest. If that doesn't help, I'll simply can the old db and recreate from scratch ... painful option, but needs must. –  Homunculus Reticulli Nov 18 '12 at 23:34
    
ok, this is the current situation. I have only one PG binaries on my machine (PG v9.2). Default datadir=/var/lib/postgresql/9.2/main. My old (8.4) database cluster resides on /mydata/pgdata. I want to use /mydata/pgdata since it is a new HD I bought purposely for storing this large dataset. It takes about 2 weeks to recreate the database, so obviously, I would like to avoid recreating from scratch. So how can I get the 8.4 database upgraded to the new (9.2) db?. To answer your question, yes I run initdb (about a year ago) to create the 8.4 cluster. –  Homunculus Reticulli Nov 18 '12 at 23:40
    
pg_createcluster --help gives me Unknown option: help. Not the best of starts, but I'll keep motoring on :/ –  Homunculus Reticulli Nov 18 '12 at 23:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.