0

Now do I move a tablespace on a windows installation of postgres?

This thread shows how to do it on unix, not windows.

The tablespace was originally created with this command...

CREATE TABLESPACE tabspace2 OWNER theowner LOCATION 'e:/pgdata2/fdb';

The command ALTER TABLESPACE allows the name to be edited, but I need to change the file location.

Work from the previous thread, I can get the oid from

SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tabspace2';

Digging through my postgres installation I've found this folder where the bottom level foldername matches the oid c:\pg_data\14\data\pg_tblspc\57620. The icon for this folder has a little blue arrow in the bottom left corner - indicating (I think) that the folder is a link. I believe that this links to the location on e: that was specified when the table space was created.

The trail starts to run cold here, I think windows links can only be dropped or created (not edited), so how can I move the tablespace? It's currently populated with live data, so I'm trying to establish the correct methodology before I start fiddling. Any help appreciated.

ConanTheGerbil
  • 1,303
  • 5
  • 31
  • 50

1 Answers1

0

I am not a Windows user, so please test before use, but that should be (via cmd.exe):

cd C:\pg_data\14\data\pg_tblspc
rmdir 57620
mkdir E:\the\new\location
move E:\pgdata2\fdb E:\the\new\location\fdb
mklink /j 57620 E:\the\new\location\fdb

It is important that the server is shut down while you do that.

Essentially, just change the destination of the symbolic link 57620.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90