Skip site navigation (1) Skip section navigation (2)

pg_upgrade tablespace directory problem

From: <admin(at)jptechpro(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: pg_upgrade tablespace directory problem
Date: 2010-11-22 22:20:54
Message-ID: 20101122152054.3323e16d52a379b845c98eb3f1fa2b0c.40613a2200.wbe@email00.secureserver.net (view raw or flat)
Thread:
Lists: pgsql-novice
<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.&nbsp; 
</span></font></div>
<div class="MsoNormal" style="font-size:12pt;"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">&nbsp;</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&nbsp; 
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&nbsp; 
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;">&nbsp;</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;">&nbsp;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;">&nbsp;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;">&nbsp;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;">&nbsp;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;">&nbsp;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;">&nbsp;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;">&nbsp;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;">&nbsp;</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;">&nbsp;</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;">&nbsp;</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;">&nbsp;</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?&nbsp; 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>


Attachment: unknown_filename
Description: text/html (5.3 KB)

pgsql-novice by date

Next:From: Josh KupershmidtDate: 2010-11-22 23:44:28
Subject: Re: Change Data Capture
Previous:From: richard terryDate: 2010-11-22 20:59:06
Subject: Re: Help with date query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group