Re: pg_upgrade error

Hi Bruce,

I was able to resolve this error, with following steps:

I was able to perform following steps and pg_upgrade was successful after that, I only tried with federator.

1. Move directory contents of 16388 to /opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.0_201008051
2. Remove directory /opt/symcor-foa/pgsql/data/pg_tblspc/16388/PG_9.0_201008051 and /opt/symcor-foa/pgsql/data/pg_tblspc/16388
3. Create symlink ln -s /opt/symcor-foa/pgsql/data/pg_tblspc/federator 16388
4. Start postgresql instance
5. Check federator and query data
6. Stop postgresql instance
7. Run pg_upgrade using steps in the documents by setting environment variables.
8. Pg_upgrade was successful.

Do you think, any problem with data integrity due to this method? I did some system testing and it looks fine, but I wanted to check with postgresql admin team to verify if this steps are valid.

Thanks,
Yogi

-----Original Message-----
From: Yogi Barot
Sent: Tuesday, February 09, 2016 1:47 PM
To: 'Bruce Momjian'
Cc: 'pgsql-admin(at)postgresql(dot)org'
Subject: RE: [ADMIN] pg_upgrade error

Hi Bruce,

Can someone help?

I am still getting pg_upgrade error when upgrade from postgersql 9.0.19 to 9.5

There is no symlinks, data is copied to same directory as old server and both clusters has same oid, same locate, upgrade check runs fine, but upgrade fails with following error:

Here is the error message:

linking "/opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.0_201008051/20014217/11838" to "/opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.5_201510051/16401/2613"

error while creating link for relation "pg_catalog.pg_largeobject" ("/opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.0_201008051/20014217/11838" to "/opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.5_201510051/16401/2613"): No such file or directory

Can you please help to make upgrade successful, It is TB of data and we don't have any other way to do upgrade. At the end of this project we will be upgrading 800TB of data.

Thanks,
Yogi

-----Original Message-----
From: Yogi Barot
Sent: Tuesday, December 08, 2015 11:33 AM
To: 'Bruce Momjian'
Cc: 'pgsql-admin(at)postgresql(dot)org'
Subject: RE: [ADMIN] pg_upgrade error

Hi Bruce,

On another cluster, I fixed the old cluster tablespace location by running bellow statement.

update pg_tablespace set spclocation='/opt/symcor-foa/data/pg_tblspc/federator' where spcname='federator'; select * from pg_tablespace

, but it still fails with same error. There is no symlink in this cluster.

/opt/symcor-foa/data/pg_tblspc/federator/PG_9.0_201008051/17055/11838
linking "/opt/symcor-foa/data/pg_tblspc/federator/PG_9.0_201008051/17055/11838" to "/opt/symcor-foa/data/pg_tblspc/federator/PG_9.4_201409291/16423/13013"
error while creating link for relation "pg_catalog.pg_largeobject" ("/opt/symcor-foa/data/pg_tblspc/federator/PG_9.0_201008051/17055/11838" to "/opt/symcor-foa/data/pg_tblspc/federator/PG_9.4_201409291/16423/13013"): No such file or directory

Thanks,
Yogi

-----Original Message-----
From: Bruce Momjian [mailto:bruce(at)momjian(dot)us]
Sent: Monday, December 07, 2015 4:05 PM
To: Yogi Barot
Cc: 'pgsql-admin(at)postgresql(dot)org'
Subject: Re: [ADMIN] pg_upgrade error

On Mon, Dec 7, 2015 at 05:44:06PM +0000, Yogi Barot wrote:
> Hi,
>
>
>
> I am doing pg upgrade from postgresql 9.0.19 to postgresql 9.4 CE version.
>
>
>
> Database size is 300GB.
>
> Pg_upgade check runs fine but pg_upgrade fails with following error:
>
>
>
> linking
> "/opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.0_201008051/
> 10349572/11838" to "/opt/symcor-foa/pgsql/data/pg_tblspc/federator/
> PG_9.4_201409291/16434/13013"
>
>
>
> error while creating link for relation "pg_catalog.pg_largeobject"
> ("/opt/ symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.0_201008051/10349572/11838" to "
> /opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.4_201409291/16434/13013"):
> No such file or directory
>
> Failure, exiting
>
>
>
> How can I resolve this or resume upgrade?
>
> The directory
> "/opt/symcor-foa/pgsql/data/pg_tblspc/federator/PG_9.0_201008051/
> 10349572/11838” does not exist in old cluster, how can I change or
> remove reference to this directory?

My guess is that you have a mismatch between the symbolic links used in data/pg_tblspc/ and pg_tablespace.spclocation.
pg_tablespace.spclocation. was removed in Postgres 9.2, but in older releases you could have moved the tablespace without updating that field, causing pg_upgrade failures.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +

CONFIDENTIALITY WARNING
This communication, including any attachments, is for the exclusive use of addressee and may contain proprietary and/or confidential information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

AVERTISSEMENT RELATIF À LA CONFIDENTIALITÉ
Ce message, ainsi que les pièces qui y sont jointes, est destiné à l’usage exclusif de la personne à laquelle il s’adresse et peut contenir de l’information personnelle ou confidentielle. Si le lecteur de ce message n’en est pas le destinataire, nous l’avisons par la présente que toute diffusion, distribution, reproduction ou utilisation de son contenu est strictement interdite. Veuillez avertir sur-le-champ l’expéditeur par retour de courrier électronique et supprimez ce message ainsi que toutes les pièces jointes.