DBA Hub

📋Steps in this guide1/5

How to move a tablespace to new directory in postgres - DBACLASS DBACLASS

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=# <strong>select * from pg_tablespace; </strong> oid | spcname | spcowner | spcacl | spcoptions -------+------------+----------+-----------------------------

postgresql configurationintermediate
by PostgreSQL
14 views
1

Overview

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
2

Section 2

1. Get the tablespace details and existing path: Currently the tablespace path is /old_fs/edb 2. Stop the postgres instance:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
postgres=#  <strong>select * from pg_tablespace;
</strong>
  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=# <strong>\db+</strong>
                                           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)
3

Section 3

NOTE – > Here i am using edb postgres , so used edb-as-12 service name, you can use pg_ctl stop command also. 3. Move the directoty to the new path:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[rootedb]#<strong> service edb-as-12 status
</strong>
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]# <strong>service edb-as-12 stop
</strong>
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]

-bash-4.1$ <strong>mv /old_fs/data /new_fs/data

</strong>
4

Section 4

4.Now Do the relinking: 5. Star the postgres instance:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-bash-4.1$ <strong>echo $PGDATA</strong>
/var/lib/edb/as12/data

-bash-4.1$ <strong>cd $PGDATA/pg_tblspc</strong>

-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$ <strong>ln -fs /new_fs/data 19847</strong>
/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

[root@edb]#  <strong>service edb-as-12 start
</strong>
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.
5

Section 5

Now check the tablespace information:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
postgres=# <strong>\db+</strong>
                                             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)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!