投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

How to change PostgreSQL database name ?

• 数据库

今天需要对一测试数据库进行更改数据库名, 操作过程中遇到一点小插曲，做下记录。

查看数据库列表

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# /l  
 List of databases  
 Name | Owner | Encoding | Collation | Ctype | Access privileges  
-----------+----------+----------+-----------+-------+-----------------------  
postgres | postgres | UTF8 | C | C |  
skytmp | skytmp | UTF8 | C | C |  
template0 | postgres | UTF8 | C | C | =c/postgres +  
 | | | | | postgres=CTc/postgres  
template1 | postgres | UTF8 | C | C | =c/postgres +  
 | | | | | postgres=CTc/postgres  
test | test | UTF8 | C | C |  
(5 rows)

目标：将数据库 skytmp, 更名为 skytmp_test。

尝试对数据库 skytmp 更名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
postgres=# /h alter database;  
Command: ALTER DATABASE  
Description: change a database  
Syntax:  
ALTER DATABASE name [ [ WITH ] option [ ... ] ]
where option can be:
 CONNECTION LIMIT connlimit
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner
ALTER DATABASE name SET TABLESPACE new_tablespace
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }  
ALTER DATABASE name SET configuration_parameter FROM CURRENT  
ALTER DATABASE name RESET configuration_parameter  
ALTER DATABASE name RESET ALL

postgres=# alter database skytmp rename to skytmp_test;  
ERROR: database "skytmp" is being accessed by other users  
DETAIL: There are 49 other session(s) using the database.

备注：当对数据库进行更名时， pg抛出了以上 Error, 根据 Error 内容, 很容易看懂意思是当前有 49 个 session 还连着数据库。

查看数据库应用进程

查看 PostgreSQL 进程, 果然还有很多应用进程，如下：

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
[postgres@db-192-168-173-63](mailto:postgres@db-192-168-173-63)-> ps -ef | grep post  
postgres 18942 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41538) idle  
postgres 18943 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41539) idle  
postgres 18944 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41540) idle  
postgres 18945 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41541) idle  
postgres 18946 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41542) idle  
postgres 18947 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41543) idle  
postgres 18948 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41544) idle  
postgres 18949 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41545) idle  
postgres 18950 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41546) idle  
postgres 18951 20802 0 08:54 ? 00:00:00 postgres: skytmp skytmp xxxx.xxx.xx.xx(41547) idle  
root 19088 19059 0 09:05 pts/1 00:00:00 su - postgres  
postgres 19089 19088 0 09:05 pts/1 00:00:00 -bash  
root 19209 19181 0 09:09 pts/2 00:00:00 su - postgres  
postgres 19210 19209 0 09:09 pts/2 00:00:00 -bash  
postgres 19273 19089 0 09:12 pts/1 00:00:00 psql -h 127.0.0.1  
postgres 19318 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49122) idle  
postgres 19319 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49124) idle  
postgres 19320 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49125) idle  
postgres 19321 20802 0 09:16 ? 00:00:14 postgres: skytmp skytmp xxxx.xxx.xx.xx(49127) idle  
postgres 19322 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49128) idle  
postgres 19323 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49129) idle  
postgres 19324 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49130) idle  
postgres 19325 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49131) idle  
postgres 19326 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49132) idle  
postgres 19327 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49133) idle  
postgres 19328 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49134) idle  
postgres 19329 20802 0 09:16 ? 00:00:14 postgres: skytmp skytmp xxxx.xxx.xx.xx(49135) idle  
postgres 19330 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49136) idle  
postgres 19331 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49137) idle  
postgres 19332 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49138) idle  
postgres 19333 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49139) idle  
postgres 19334 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49140) idle  
postgres 19335 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49141) idle  
postgres 19336 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49142) idle  
postgres 19337 20802 0 09:16 ? 00:00:14 postgres: skytmp skytmp xxxx.xxx.xx.xx(49143) idle  
postgres 19338 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49144) idle  
postgres 19339 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49145) idle  
postgres 19340 20802 0 09:16 ? 00:00:14 postgres: skytmp skytmp xxxx.xxx.xx.xx(49146) idle  
postgres 19341 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49147) idle  
postgres 19342 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49148) idle  
postgres 19343 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49149) idle  
postgres 19344 20802 0 09:16 ? 00:00:15 postgres: skytmp skytmp xxxx.xxx.xx.xx(49150) idle  
postgres 19345 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49151) idle  
postgres 19346 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49152) idle  
postgres 19347 20802 0 09:16 ? 00:00:05 postgres: skytmp skytmp xxxx.xxx.xx.xx(49153) idle  
root 19428 19210 0 09:24 pts/2 00:00:00 su - postgres  
postgres 19429 19428 0 09:24 pts/2 00:00:00 -bash  
postgres 19829 20802 1 09:46 ? 00:00:01 postgres: postgres postgres 127.0.0.1(18376) idle  
postgres 19836 19429 0 09:48 pts/2 00:00:00 ps -ef  
postgres 19837 19429 0 09:48 pts/2 00:00:00 grep post  
postgres 20802 1 0 Mar24 ? 00:01:18 /opt/pgsql/bin/postgres -D /opt/pgdata/1921/pg_root  
postgres 20803 20802 0 Mar24 ? 00:00:00 postgres: logger process  
postgres 20805 20802 0 Mar24 ? 00:04:33 postgres: writer process  
postgres 20806 20802 0 Mar24 ? 00:03:11 postgres: wal writer process  
postgres 20807 20802 0 Mar24 ? 00:08:11 postgres: stats collector process  
postgres 23570 20802 0 May20 ? 00:00:27 postgres: skytmp skytmp xxxx.xxx.xx.xx(36165) idle  
postgres 23624 20802 0 May20 ? 00:00:17 postgres: skytmp skytmp xxxx.xxx.xx.xx(60897) idle  
postgres 23625 20802 0 May20 ? 00:00:24 postgres: skytmp skytmp xxxx.xxx.xx.xx(60898) idle  
postgres 23626 20802 0 May20 ? 00:00:23 postgres: skytmp skytmp xxxx.xxx.xx.xx(60899) idle  
postgres 23627 20802 0 May20 ? 00:00:30 postgres: skytmp skytmp xxxx.xxx.xx.xx(60900) idle  
postgres 23628 20802 0 May20 ? 00:00:14 postgres: skytmp skytmp xxxx.xxx.xx.xx(60901) idle  
postgres 23629 20802 0 May20 ? 00:00:11 postgres: skytmp skytmp xxxx.xxx.xx.xx(60902) idle  
postgres 23630 20802 0 May20 ? 00:00:19 postgres: skytmp skytmp xxxx.xxx.xx.xx(60903) idle

于是通知开发人员，停应用。

停应用后更改数据库名称

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
postgres=# alter database skytmp rename to skytmp_test;  
ALTER DATABASE

postgres=# /l  
 List of databases  
 Name | Owner | Encoding | Collation | Ctype | Access privileges  
-------------+----------+----------+-----------+-------+-----------------------  
postgres | postgres | UTF8 | C | C |  
skytmp_test | skytmp | UTF8 | C | C |  
template0 | postgres | UTF8 | C | C | =c/postgres +  
 | | | | | postgres=CTc/postgres  
template1 | postgres | UTF8 | C | C | =c/postgres +  
 | | | | | postgres=CTc/postgres  
test | test | UTF8 | C | C |  
(5 rows)

停应用后， 数据库更名成功。

原创文章，作者：506227337，如若转载，请注明出处：https://blog.ytso.com/236398.html

赞 (0)
0 0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论