This article explains how to move a database to a new tablespace in postgres. This is same as changing the default tablespace for a postgres database.   For this activity we have make sure that no application sessions are connecting to that database. We can say this activity will need some downtime.

Objective – We will move the database prod_crm from pg_default tablespace to crm_tblspc;

 Check existing tablespace details:

prod_crm=# \l+ prod_crm
                                                        List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU | Access privileges |  Size   | Tablespace  | Description
-----------+--------------+----------+-------------+-------------+-----+-------------------+---------+-------------+-------------
 prod_crm | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |                   | 6677 MB | pg_default |
(1 row)

Alter database command

prod_crm=# alter database prod_crm set tablespace crm_tblspc;
ERROR:  cannot change the tablespace of the currently open database
prod_crm=#

Above error is because, we are connecting to the same database for which we are altering. We should issue the command by connecting to a different database.

-- Connect a different database 
prod_crm=# \q
-bash-4.2$ psql -d postgres -p 5432
Password for user enterprisedb:
psql.bin (11.8.15)
Type "help" for help.

postgres=#  alter database prod_crm set tablespace crm_tblspc;
ERROR:  database "prod_crm" is being accessed by other users
DETAIL:  There are 11 other sessions using the database.

New error says, the few sessions are using the database. So we need kill them.

Find the sessions using db:

postgres=# select usename,pid,application_name from  pg_stat_activity where datname='prod_crm';
  usename   |  pid  |    application_name
------------+-------+------------------------
 PROD_CRM | 15225 | PostgreSQL JDBC Driver
 PROD_CRM | 15283 | PostgreSQL JDBC Driver
 PROD_CRM | 15285 | PostgreSQL JDBC Driver
 PROD_CRM | 15286 | PostgreSQL JDBC Driver
 PROD_CRM | 15287 | PostgreSQL JDBC Driver
 PROD_CRM | 15290 | PostgreSQL JDBC Driver
 PROD_CRM | 15291 | PostgreSQL JDBC Driver
 PROD_CRM | 15292 | PostgreSQL JDBC Driver
 PROD_CRM | 15298 | PostgreSQL JDBC Driver
 PROD_CRM | 15300 | PostgreSQL JDBC Driver
 PROD_CRM | 15303 | PostgreSQL JDBC Driver
(11 rows)

As all the sessions are from PROD_CRM, so we need to kill the all the sessions of that user.

Kill the sessions for user PROD_CRM

 
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where usename='PROD_CRM';
 pg_terminate_backend
----------------------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(11 rows)

alternatively you can kill the sessions individually using below command.

select pg_terminate_backend(pid) from pg_stat_activity where pid=’15225′

postgres=#  alter database prod_crm set tablespace crm_tblspc;
ALTER DATABASE
postgres=#

prod_crm=# \l+ prod_crm
                                                        List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU | Access privileges |  Size   | Tablespace  | Description
-----------+--------------+----------+-------------+-------------+-----+-------------------+---------+-------------+-------------
 prod_crm | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |                   | 6677 MB | crm_tblspc |
(1 row)