On Wed, Aug 20, 2014 at 7:23 PM, Bruce Momjian <bruce@xxxxxxxxxx> wrote:
On Mon, Aug 18, 2014 at 12:15:57PM -0700, Craig James wrote:
>
>
> On Mon, Aug 18, 2014 at 11:59 AM, Craig James <cjames@xxxxxxxxxxxxxx> wrote:
>
> I did pg_upgrade from 8.4.17 to 9.3.5. The upgrade claimed it was
> successful. However, when I start Postgres 9.3.5, I get an error message
> for every one of the roughly 250 databases:
>
> LOG: could not open tablespace directory "pg_tblspc/16828/
> PG_9.3_201306121": No such file or directory
> LOG: could not open tablespace directory "pg_tblspc/16523/
> PG_9.3_201306121": No such file or directory
> LOG: could not open tablespace directory "pg_tblspc/16768/
> PG_9.3_201306121": No such file or directory
> LOG: could not open tablespace directory "pg_tblspc/16715/
> PG_9.3_201306121": No such file or directory
> ... and so forth
>
> My Postgres looks like this
>
> /data/postgres/main - the PGDATA directory
> /data/postgres/tablespaces - where most of the data live
>
>
> OK, I figured this out: pg_upgrade didn't follow symbolic links. Each of the
> missing files pointed to /data/postgres/tablespaces, but the actual directory
> was in /data/postgres-8.4/tablespaces. When I replaced /data/postgres/
> tablespaces with a symbolic link to /data/postgres-8.4/tablespaces, everything
> worked.
>
> Once everything is in order and I'm convinced 9.3.5 is working, I'll shut down,
> remove the symlink, and move the tablespaces directory to the new location.
Uh, I am not totally clear what happened here. I am unclear how
pg_upgrade would have moved the files yet the new server would not
start. Did you do anything between the completion of pg_upgrade and
trying to start the new server?
It's complicated. My situation is probably not relevant to anyone else. Here is the history, which explains the how this occurred.
For consistency with our older systems, we built the database with PGDATA = /postgres, even though it was actually in /data/postgres. /postgres was a symlink.
Similarly, our tablespaces were specified as /postgres/tablespaces/xxx, but in fact they were in /data/postgres/tablespaces/xxx (where xxx is the tablespace name).
Prior running pg_upgrade, I renamed /data/postgres to /data/postgres-8.4 and updated the symlink /postgres. I verified that 8.4 still ran with no problems.
pg_upgrade ran with no complaints. The new directory was /data/postgres-9.3.
Since I wasn't going to run 8.4 any more, I updated the symlink /postgres to point to /data/postgres-9.3. Unfortunately, pg_upgrade hadn't created /data/postgres-9.3/tablespaces, since it thought they were in /postgres/tablespaces:
/data/postgres-9.3/main/pg_tblspc/nnnnn -> /postgres/tablespaces/xxx/
where "nnnnn" is the ID of the tablespace and "xxx" was the original tablespace name. When I tried to start 9.3, I got the errors shown in my original email.
To fix it, I simply moved /data/postgres-8.4/tablespaces to /data/postgres-9.3/tablespaces.
One scary side effect: if I had run the cleanup script that pg_upgrade produced, it would have deleted my entire database since /data/postgres-8.4/tablespaces was NOT hard linked into the new /data/postgres-9.3 directory.
Craig
--
Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
