Below are the steps for moving a tablespace to a new file system/mount point in postgres.

EXAMPLE:

TABLESPACE_NAME – > proddb_ts
TABLESPACE_OID – 19847
NEW MOUNT POINT – > /new_fs/data

1. Get the tablespace details and existing path:


postgres=#  select * from pg_tablespace;

  oid  |  spcname   | spcowner |                        spcacl                        | spcoptions
-------+------------+----------+------------------------------------------------------+------------
  1663 | pg_default |       10 |                                                      |
  1664 | pg_global  |       10 |                                                      |
 19847 | prodb_ts   |       10 | {enterprisedb=C/enterprisedb,devraj=C*/enterprisedb} |
(3 rows)

postgres=# \db+
                                           List of tablespaces
    Name    |    Owner     |   Location    |      Access privileges      | Options |  Size  | Description
------------+--------------+---------------+-----------------------------+---------+--------+-------------
 proddb_ts   | enterprisedb | /old_fs/data | enterprisedb=C/enterprisedb+|         | 28 GB  |
            |              |               | devraj=C*/enterprisedb      |         |        |
 pg_default | enterprisedb |               |                             |         | 84 MB  |
 pg_global  | enterprisedb |               |                             |         | 784 kB |
(3 rows)

Currently the tablespace path is /old_fs/edb

2. Stop the postgres instance:

NOTE – > Here i am using edb postgres , so used edb-as-12 service name, you can use pg_ctl stop command also.


[rootedb]# service edb-as-12 status

INFO: [PID: 58076]
INFO: [CMD: /usr/edb/as12/bin/edb-postgres]
MSG:  [edb-as-12 is running]
INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log for details]

[rootedb]# service edb-as-12 stop

INFO: [PID: 58076]
INFO: [CMD: /usr/edb/as12/bin/edb-postgres]

Stopping edb-as-12                                         [  OK  ]

MSG:  [edb-as-12 stopped]

INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log for details]

3. Move the directoty to the new path:


-bash-4.1$ mv /old_fs/data /new_fs/data



4.Now Do the relinking:


-bash-4.1$ echo $PGDATA
/var/lib/edb/as12/data

-bash-4.1$ cd $PGDATA/pg_tblspc

-bash-4.1$ ls -ltr
total 0
lrwxrwxrwx 1 enterprisedb enterprisedb 13 Jun 10 16:24 19847 -> /old_fs/data
-bash-4.1$ pwd
/var/lib/edb/as12/data/pg_tblspc


--- Relink command 

-bash-4.1$ ls -fs /new_fs/data 19847
/dmdata06/edb:
total 12
4 ..  4 .  4 edb

19847:
total 8
4 ..  0 edb  4 .


-bash-4.1$ ls -ltr
total 0
lrwxrwxrwx 1 enterprisedb enterprisedb 13 Oct 15 16:22 19847 -> /new_fs/data

5. Star the postgres instance:


[root@edb]#  service edb-as-12 start

Starting edb-as-12                                         [  OK  ]

INFO: [PID: 34285]
INFO: [CMD: /usr/edb/as12/bin/edb-postgres -D /var/lib/edb/as12/data -p 5444]
MSG:  [edb-as-12 started]

INFO: [Please see service script file /var/log/edb/as12/edb-as-12_script.log for details]



psql (12.3.4)
Type "help" for help.

Now check the tablespace information:



postgres=# \db+
                                             List of tablespaces
    Name    |    Owner     |     Location      |      Access privileges      | Options |  Size  | Description
------------+--------------+-------------------+-----------------------------+---------+--------+-------------
 proddb_ts  | enterprisedb | /new_fs/data      | enterprisedb=C/enterprisedb+|         | 28 GB  |
            |              |                   | devraj=C*/enterprisedb      |         |        |
 pg_default | enterprisedb |                   |                             |         | 84 MB  |
 pg_global  | enterprisedb |                   |                             |         | 784 kB |
(3 rows)