I have a postgres 9.3 database in a windows installation. The complete installation is on a raid installed.
We wanted to replace that raid with new drives since the old ones started to die. We copied all data from the raid to a new drive with the file explorer (wasn't me...), then replaced all drives and created a new raid, put back on all the old data and changed the drive letter back to the old one. Thinking that everything would work as normal.
Sadly not with postgres. The copying with file explorer destroyed all symlinks in the pg_tblspc folder. They arent symlinks anymore, they are just empty folders. So I have now 147 broken symlinks...
I checked how the symlinks working and compared to a working postgres installation. I found out that every table has an OID. For each OID there are 2 symlinks in that folder. The symlinks have an increased number. For example, the OID of a table is 17530, then I have 2 symlinks 17538 and 17541. These pointing towards the data folder with the database name. In the folder with the database name I have 2 folders again. One of these symlinks pointing to one of these folders.
The increased numbers are always so same. So its always OID +8 and +11 (on other working installation its always +4 and +7).
All the table management is done by a program (FTK). So if you do stuff in there, its creating/deleting/updating the databases and tables for you. I think that its always 2 folders in there is because FTK is doing that in that way.
My question is now: Can I just manually create these symlinks? And then everything should work? Or is there maybe a function from postgres, where I can point to the "new" folder and it recreates the symlinks? It looks like the symlinks are managed by postgres itself. But so far I couldnt find anything about a repair function
Mistake number one was not to take a backup. Mistake number two was to create 147 tablespaces. But let's no dwell on that. What can you do?
First, facts. In the directory pg_tblspc is one symbolic link per tablespace, not per table. The name of the symbolic link is the object ID of the tablespace. You can find the object IDs with
SELECT oid, spcname FROM pg_tablespace;
Then you have to figure out what directory belong to which tablespace. There i no help for that, since that is the information that got lost. Once you know the path for a tablespace:
change to the pg_tblspc directory
run
ln -s /path/to/tablespace 12345
where 12345 is the OID of the tablespace.
Needless to say, PostgreSQL must be shut down when you do that. Once you have re-created the symbolic links for all tablespaces, you should be good.