PostgreSQL Move Tablespaces to other location

The customer has a request that they need to change the PGDATA location and then the PostgreSQL tablespace needs to be changed to the new location.

Step 1. Stop Postgres

[postgres@pdb10a ~]$ pg_ctl stop -D $PGDATA
waiting for server to shut down.... done
server stopped

Step 2. edit parameter PGDATA to new location

[postgres@pdb10a ~]$ vim ~/.bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

export PGDATA=/PG/PGDATA
export PGHOME=/usr/pgsql-14
export PATH=$PATH:$PGHOME/bin

Step 3. change directory to new $PGDATA/pg_tblspc location

[postgres@pdb10a ~]$ cd $PGDATA/pg_tblspc
[postgres@pdb10a pg_tblspc]$ ls -l
total 0
lrwxrwxrwx. 1 postgres postgres 24 Apr  6 15:04 16454 -> /PRI/PGTBS/PBBBBBDB
lrwxrwxrwx. 1 postgres postgres 22 Apr  6 15:04 16455 -> /PRI/PGTBS/PAAADB

Step 4. replace link to new location

[postgres@pdb10a pg_tblspc]$ ln -fs /PG/PGTBS/PBBBBBDB 16454
[postgres@pdb10a pg_tblspc]$ ln -fs /PG/PGTBS/PAAADB 16455
[postgres@pdb10a pg_tblspc]$ ls -l
total 0
lrwxrwxrwx. 1 postgres postgres 20 Apr 14 14:52 16454 -> /PG/PGTBS/PBBBBBDB
lrwxrwxrwx. 1 postgres postgres 18 Apr 14 14:52 16455 -> /PG/PGTBS/PAAADB

Step 5. Start Postgres

[postgres@pdb10a pg_tblspc]$ pg_ctl start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started

Step 6. Check Tablespace mapping to new location

[postgres@pdb10a pg_tblspc]$ psql
psql (14.2)
Type "help" for help.

postgres=# \db+
                                         List of tablespaces
    Name    |   Owner   |       Location       | ... |  Size   | Description
------------+-----------+----------------------+-----+---------+-------------
 pbbbdb     | pxxxxxmgr | /PG/PGTBS/PBBBBBDB   | ... | 8425 kB |
 pg_default | postgres  |                      | ... | 33 MB   |
 pg_global  | postgres  |                      | ... | 576 kB  |
 paaadb     | pxxxxxmgr | /PG/PGTBS/PAAADB     | ... | 8577 kB |
(4 rows)


Ref:

Moving Tablespaces in PostgreSQL

張貼留言

0 留言