Tablespace symlink error after changing data_directory location in PSQL 9.4

Question

Problem: After changing the 'data_directory' location in PSQL 9.4 (Windows Server 2012 R2) an error is thrown to the user when: (a) trying to connect to the PSQL server using PgAdmin or (b) when trying to list the tablespaces \db using the SQL Shell. In both cases the error is the following:

ERROR: could not read symbolic link "pg_tblspc/53605151": Invalid Argument.

Although the error message persists even after restarting the PSQL server it does not seem to affect the users' ability to ETL using the database.

Background: I followed the instructions suggested at the following link https://stackoverflow.com/questions/22596301/how-to-change-postgresql-data-directory to change the 'data_directory'.

Actions taken: I tried to remove the symbolic link using the following command using my system administrator account.:

rmdir ...\pg_tblspc\53605151

After the symlink is removed the error thrown changes slightly to the following:

ERROR: could not read symbolic link "pg_tblspc/53605151": No such file or directory

Any insights would be more than welcome on how to fix this.

Answer 1

Postgres tablespaces are a symbolic link in the pg_tblspc directory. That link must point to a valid place for the tablespace files.

Please, make a link again, pointing to the right place.

You can see the tablespaces locations on psql client with the \db command or running the query bellow:

SELECT 
  spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

See the documentation for more details.

Answer 2

Probably symbolic links from numeric directories to "named" directories was broken when you copy data.

Content of numeric directories must be identical to named directories. Then temporary move numeric directories to for example "old" subdir and create new symbolic links used mklink /d /j <numeric dir> <named dir>

Check is all ok with SQL query:

SELECT 
 spcname AS "Name",
 pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
 pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;

Answer 3

Also, even when the symbolic link is valid, if it is relative as below, it will fail with that same error message.

tablespace directory "../pg_tblspc-17255" does not exist

postgres@fra-pg-02:~/9.6/main/pg_tblspc$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 18 Feb  5 16:36 pg_tblspc-17255 -> ../pg_tblspc-17255
postgres@fra-pg-02:~/9.6/main/pg_tblspc$ ls -lL
total 0
drwx------ 3 postgres postgres 30 May  2  2017 pg_tblspc-17255