<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div class="Section1">
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">I have been testing pg_upgrade for
use migrating a PostgreSQL 8.4.2 cluster to PostgreSQL 9.0.1.
</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">There is only one database (named
alerting) in this cluster and it has three tablespaces
defined:</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">CREATE TABLESPACE alerting_data
OWNER postgres LOCATION
'/pgdata/5432/pg_tblspc/alerting_data';</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">CREATE TABLESPACE alerting_index
OWNER postgres LOCATION
'/pgdata/5432/pg_tblspc/alerting_index';</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">CREATE TABLESPACE alerting_text
OWNER postgres LOCATION
'/pgdata/5432/pg_tblspc/alerting_text';</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Following the directions in the
pg_upgrade documentation, I ran through the steps (I added step
6):</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 1. Performed a full backup with
pg_dumpall.</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 2. Stopped postmaster and all
backends.</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 3. Renamed old pgdata
directory.</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 4. Installed the new PostgreSQL
9.0.1 binaries</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 5. Ran initdb
(/usr/local/pgsql-9.0.1/bin/initdb -D
/pgdata/5432;)</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 6. I created all the existing (old)
tablespace directories and symlinks under the new pgdata
directory</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> 7. Switched to the postgres user
and ran pg_upgrade, using:</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">/usr/local/pgsql-9.0.1/bin/pg_upgrade
-d /pgdata/5432.old -D /pgdata/5432 -b /usr/local/pgsql-8.4.2/bin -B
/usr/local/pgsql-9.0.1/bin -l /tmp/pg_upgrade.log;</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">During pg_upgrade's "Restoring user
relation files" step, when it encounters the first alerting object, it errors
out with:</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">“error while copying
public.ix_alert_createdategmt(/pgdata/5432/pg_tblspc/alerting_index/16616/17783)
to
public.ix_alert_createdategmt(/pgdata/5432/pg_tblspc/alerting_index/PG_9.0_201008051/16407/17783):
No such file or directory”</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Is this a "bug" where pg_upgrade is
making an assumption about my use of version-specific subdirectories? Is there
a simple work-around (maybe changing our directory structure or alter tablespace
before the upgrade)?</span></font></div></div><div><br></div><div>Thanks for any help!</div></span></body></html>