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,devusr=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 |
| | | devusr=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$ ln -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 |
| | | devusr=C*/enterprisedb | | |
pg_default | enterprisedb | | | | 84 MB |
pg_global | enterprisedb | | | | 784 kB |
(3 rows)
