Re: pg_upgrade & tablespaces

On 12/27/2013 01:00 PM, Joseph Kregloh wrote:
>
> Postgres is going to /usr/local/pgsql/data/drupal___dbspace/ to look
> for the 9.0 files instead of
> /usr/local/pgsql_90/data/__drupal_dbspace/ and is trying to copy
> them as 9.3 versions into the new default location which has the
> same path. Since the new
> /usr/local/pgsql/data/drupal___dbspace/PG_9.0_201008051 is empty it
> is failing.
>
>
> That is exactly what is going on. I think what I am going to end up
> doing is:

I am not sure that is going to work.

>
> - Leaving 9.0 in the default location, this way it will successfully
> complete PG upgrade.

So you will have 9.3 installed in /opt correct?

> - Uninstall 9.0
> - Manually move the user created tablespaces into the 9.3 data folder

The 9.0 tablespaces correct? Why, this after the upgrade they are no
longer of use to the 9.3 installation and cannot be used by it?

> - Reinstall 9.3 to go into the default location, right now its installed
> in /opt using the PREFIX

Now 9.3 is in /usr/local/ correct?

> - Move the 9.3 data folder into the default location.

Same problem, different direction:) The 9.3 tablespaces in pg_tablespace
will be looking back at the old /opt location which does not exist

> - Cleanup the old 9.0 folders

>
> Then in theory it should start right up.
>
> I would assume that if the user created tablespaces were created outside
> of the /data folder then this would not be an issue. But again, I am not
> the DBA, I clean up after everybody else.

Well the idea behind user created tablespaces is to spread the data load
across filesystems/disks. So, yes it is generally best practice not to
put them in the default PGDATA directory.

>
> Thanks for all your help Adrian.
>
> -Joseph

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com