DBA Hub

📋Steps in this guide1/4

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

To move a database to a new tablespace issue, alter database prod_crm set tablespace crm_tblspc; Before that make sure no sessions are connec

postgresql configurationintermediate
by PostgreSQL
12 views
1

Check existing tablespace details:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
prod_crm=# <span style="color: #ff0000;"><strong>\l+ prod_crm</strong></span>
                                                        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)
2

Alter database command

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. New error says, the few sessions are using the database. So we need kill them.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
prod_crm=# <span style="color: #ff0000;"><strong>alter database prod_crm set tablespace crm_tblspc;</strong></span>
ERROR:  cannot change the tablespace of the currently open database
prod_crm=#

-- 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=# <span style="color: #ff0000;"><strong> alter database prod_crm set tablespace crm_tblspc;</strong></span>
ERROR:  database "prod_crm" is being accessed by other users
DETAIL:  There are 11 other sessions using the database.
3

Find the sessions using db:

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
postgres=# <span style="color: #ff0000;"><strong>select usename,pid,application_name from  pg_stat_activity where datname='prod_crm';</strong></span>
  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)
4

Kill the sessions for user PROD_CRM

> alternatively you can kill the sessions individually using below command. select pg_terminate_backend(pid) from pg_stat_activity where pid=’15225′ alternatively you can kill the sessions individually using below command. select pg_terminate_backend(pid) from pg_stat_activity where pid=’15225′

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
postgres=# <span style="color: #ff0000;"><strong>select pg_terminate_backend(pid) from pg_stat_activity where usename='PROD_CRM';</strong></span>
 pg_terminate_backend
----------------------
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
 t
(11 rows)

postgres=#  <span style="color: #ff0000;"><strong>alter database prod_crm set tablespace crm_tblspc;</strong></span>
ALTER DATABASE
postgres=#

prod_crm=# <span style="color: #ff0000;"><strong>\l+ prod_crm</strong></span>
                                                        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 | <strong>crm_tblspc</strong> |
(1 row)

Comments (0)

Please to add comments

No comments yet. Be the first to comment!