Using tablespaces to move data files from PDATA to another mount point

For each database in the cluster there is a subdirectory within $PGDATA/base, named after the database's OID in pg_database, and all related files for that database reside under that particular directory.

If you want to move a database directory from base directory to some other directory, then you can create a new tablespace and assign that tablespace as default to the database to which you want to move the other location.

First of all let’s check how many databases are present in the cluster and what are the default tablespaces for them.

edb=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges | Size | Tablespace | Description 
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 47 MB | pg_default | 
pem | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 13 MB | pg_default | 
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 13 MB | pg_default | default administrative connection database
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +| 13 MB | pg_default | unmodifiable empty database
| | | | | | enterprisedb=CTc/enterprisedb | | | 
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +| 13 MB | pg_default | default template for new databases
| | | | | | enterprisedb=CTc/enterprisedb | | | 
(5 rows)

Now lets find out the OID’s for respective database.

edb=# select oid, datname from pg_database;
oid | datname 
16221 | postgres
16222 | edb
1 | template1
16220 | template0
16393 | pem
(5 rows)

Check the location of the OID which you want to move. In this case we are moving EDB database and the OID is 16222:

[enterprisedb@snvm001 ~]$ cd $PGDATA
[enterprisedb@snvm001 data]$ 
[enterprisedb@snvm001 data]$ cd base/
[enterprisedb@snvm001 base]$ ls -lrt
total 64
drwx______. 2 enterprisedb enterprisedb 8192 Jan 10 2021 16220
drwx______. 2 enterprisedb enterprisedb 8192 Oct 27 2021 1
drwx______. 2 enterprisedb enterprisedb 8192 May 17 03:30 16221
drwx______. 2 enterprisedb enterprisedb 8192 May 17 03:30 16393
drwx______. 2 enterprisedb enterprisedb 12288 May 30 04:01 16222
[enterprisedb@snvm001 base]$ du -sh 16222
48M 16222

Now create a new tablespace with the location where you want place the database files:

[enterprisedb@snvm001 ~]$ pwd
/home/enterprisedb
[enterprisedb@snvm001 ~]$ mkdir edb_database

[enterprisedb@snvm001 ~]$ psql postgres
Welcome to ENTERPRISEDB! 

sql (12.5.6)
Type "help" for help.

edb=# create tablespace abc location '/home/enterprisedb/edb_database';
CREATE TABLESPACE
edb=# 

Assign default tablespace for edb database:

postgres=# alter database edb set tablespace abc;
ALTER DATABASE
postgres=# 
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU | Access privileges | Size | Tablespace | Description 
edb | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 47 MB | abc | 
pem | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 13 MB | pg_default | 
postgres | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | | 13 MB | pg_default | default administrative connection database
template0 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +| 13 MB | pg_default | unmodifiable empty database
| | | | | | enterprisedb=CTc/enterprisedb | | | 
template1 | enterprisedb | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | =c/enterprisedb +| 13 MB | pg_default | default template for new databases
| | | | | | enterprisedb=CTc/enterprisedb | | | 
(5 rows)

postgres=# 

Where the files has been created under the specified location:

[enterprisedb@snvm001 PG_12_201909212]$ ls -lrt
total 16
drwx______. 2 enterprisedb enterprisedb 12288 Jun 9 01:12 16222
[enterprisedb@snvm001 PG_12_201909212]$ du -sh *
48M 16222
[enterprisedb@snvm001 PG_12_201909212]$ pwd
/home/enterprisedb/edb_database/PG_12_201909212
[enterprisedb@snvm001 PG_12_201909212]$ 

We can see that all files have moved from the default location to the new location with the same size.