Postgres Pro
Downloads
Home   >   mailing lists

Thread: Tablespaces

Tablespaces

From
Illya
Date:
Hi list, 

I have a large amount of data in a number of databases on a separate internal drive, under the tablespace  e_drive which normally sits in location E:\postgis. This setup has been been working happily for years. Yesterday I became unable to connect to any of the databases in the e_drive tablespace. I connected to the cluster and ran \db and lo the location is now changed to C for some unknown reason (I haven't upgraded or anything like that). 


​    Name    |  Owner   |  Location
------------+----------+------------
 e_drive    | theowner | C:\postgis​


​I realise that I can't use ALTER TABLESPACE for this, so can I delete the tablespace e_drive and recreate it with the correct location and everything will be fine? 

Or am I going to have to use pg_dumpall and hope I can dump the data out.....​


​Thanks in advance, ​

​Ills​

Re: Tablespaces

From
Alvaro Herrera
Date:
Illya wrote:
> Hi list,
> 
> I have a large amount of data in a number of databases on a separate
> internal drive, under the tablespace  e_drive which normally sits in
> location E:\postgis. This setup has been been working happily for years.
> Yesterday I became unable to connect to any of the databases in the e_drive
> tablespace. I connected to the cluster and ran \db and lo the location is
> now changed to C for some unknown reason (I haven't upgraded or anything
> like that).
> 
> 
> ​    Name    |  Owner   |  Location
> ------------+----------+------------
>  e_drive    | theowner | C:\postgis​

As I understand, the location is stored in symlinks in your data
directory only, so to fix it it should work to stop the server, change
the symlink to point to the right drive, then restart.

Probably it was Windows itself that updated the symlink when it saw that
the destination drive E: no longer existed.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services