PostgreSQL基本使用（1）数据库操作

九皇叔叔

已于 2024-03-16 08:56:31 修改

阅读量165

点赞数 1

分类专栏： PostgreSQL 文章标签：数据库 postgresql

于 2023-08-17 21:31:34 首次发布

本文链接：https://blog.csdn.net/CharlesYuangc/article/details/132351099

版权

PostgreSQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

数据库操作

登录/退出
数据库操作
扩展

登录/退出

登录数据库的格式如下

# 注意：-U 是大写，其他字母均小写
psql -h 服务器 -U 用户名 -d 数据库  -p 端口地址

说明：

若 -d 参数不添加，默认数据库为 postgres

若需退出，使用 \q

postgres=# 
postgres=# \q
[root@localhost ~]#

数据库操作

创建数据库

创建数据库SQL如下

postgres=# create database mydb;
CREATE DATABASE
postgres=#

查看数据库

数据库创建完成之后，若想查看当前有哪些数据库？
使用 \l （L字母的小写）

postgres=# \l
                                    List of databases
   Name    |   Owner   | Encoding |  Collate   |   Ctype    |     Access privileges      
-----------+-----------+----------+------------+------------+----------------------------
 db_test   | fid_test  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/fid_test              +
           |           |          |            |            | fid_test=C*T*c*/fid_test
 dbtest    | fid_test2 | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/fid_test2             +
           |           |          |            |            | fid_test2=C*T*c*/fid_test2
 mydb      | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 postgres  | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 template0 | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres               +
           |           |          |            |            | postgres=CTc/postgres
 template1 | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres               +
           |           |          |            |            | postgres=CTc/postgres
 test      | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
(7 rows)

postgres=#

当前所使用的数据库是哪个？
注意上面的 =# 这个前面的 postgres，即代表当前数据库。

切换数据库

若想切换到刚刚创建的 mydb
使用 \c

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=# 
mydb=#

删除数据库

删除数据库命令如下：

-- 删除数据库
postgres=# drop database mydb
postgres-# 
-- 查看当前还有哪些数据库
postgres-# \l
                                    List of databases
   Name    |   Owner   | Encoding |  Collate   |   Ctype    |     Access privileges      
-----------+-----------+----------+------------+------------+----------------------------
 db_test   | fid_test  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/fid_test              +
           |           |          |            |            | fid_test=C*T*c*/fid_test
 dbtest    | fid_test2 | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/fid_test2             +
           |           |          |            |            | fid_test2=C*T*c*/fid_test2
 mydb      | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 postgres  | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
 template0 | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres               +
           |           |          |            |            | postgres=CTc/postgres
 template1 | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres               +
           |           |          |            |            | postgres=CTc/postgres
 test      | postgres  | UTF8     | zh_CN.utf8 | zh_CN.utf8 | 
(7 rows)

postgres-#

扩展

删库报错

1、删除数据库时，出现报错；如下：

postgres=# drop database mydb;
ERROR:  database "mydb" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=#

上述报错，可看出：是因为 mydb 数据库当前正在被使用，故无法删除。
Q：如何解决？
A：Kill 连接 mydb 的会话，然后再次删库 mydb 即可，步骤如下：

-- 查询会话
SELECT
	pid,
	usename,
	datname,
	application_name,
	client_addr,
	client_hostname,
	client_port,
	STATE,
	query 
FROM
	pg_stat_activity 
WHERE
	datname = 'mydb';
	
-- kill 会话，此处的 3089 是上面 select 语句中的 pid 编号.
SELECT pg_terminate_backend(3089);

在这个 SQL 查询中，每个字段的含义如下：

pid：表示 PostgreSQL 进程的 ID，即会话的进程 ID。
usename：表示正在执行会话的用户名。
datname：表示正在访问的数据库名称。
application_name：表示正在执行会话的应用程序的名称。
client_addr：表示客户端的 IP 地址。
client_hostname：表示客户端的主机名。
client_port：表示客户端连接的端口号。
state：表示会话的状态，例如 active（活动）、idle（空闲）等。
query：表示正在执行的查询，如果会话正在执行查询，则会显示查询的文本；如果会话没有在执行查询，则为 null。

在这里插入图片描述

-- 实际操作时，完整步骤如下：
-- kill 会话.
postgres=# select pg_terminate_backend(3089);
 pg_terminate_backend 
----------------------
 t
(1 row)

-- 再次尝试删除数据库.
postgres=# drop database mydb;
DROP DATABASE

-- 再次查看数据库，发现 mydb 已经被删除.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb01    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 mydb02    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(6 rows)

postgres=#

kill 会话.

除了使用 pg_terminate_backend 函数之外，还有其他一些方法可以终止 PostgreSQL 中的会话。其中一个方法是使用 pg_cancel_backend 函数。

这两个函数的区别在于：

pg_terminate_backend 函数会立即终止指定会话的执行，并且会中断正在执行的查询。这相当于强制终止该会话。
pg_cancel_backend 函数会发送一个取消请求给指定的会话，但不会立即终止会话。如果正在执行的查询可以取消，它将被取消，否则会继续执行。这相当于请求取消该会话的执行。

以下是使用 pg_cancel_backend 函数的示例：

SELECT pg_cancel_backend(3089);

这将向进程 ID 为 3089 的会话发送一个取消请求。如果查询可以取消，并且正在执行，它将被取消。

选择使用 pg_terminate_backend 还是 pg_cancel_backend 取决于你的需求。通常情况下，如果你需要立即终止会话，可以使用 pg_terminate_backend。如果只是希望尝试取消会话的执行，可以使用 pg_cancel_backend。

锁账号

在实际使用时，一般是应用程序连接数据库，而且应用程序一般也会有重连的机制。也就说：kill 会话之后，应用程序还是会很快再次和数据库建立连接的，那此时还是不能删除数据库的。
Q：如何解决？
A：先锁住账号，使其不能登录数据库；然后再 kill 已经建立的连接会话。

kill 会话，上述已经说过；此处仅讲解如何锁住账号。例如：账号 fid_test

在 PostgreSQL 中，要查看用户（或角色）的状态，可以查询系统表 pg_user 或 pg_roles，具体取决于PostgreSQL 版本。通常，大多数用户和角色相关信息都在 pg_roles 中。

以下是查询用户状态的示例：

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolconnlimit, rolvaliduntil
FROM pg_roles
WHERE rolname = 'fid_test';

在这个 SQL 查询中，每个字段的含义如下：

rolname：角色（或用户）的名称。
rolsuper：如果为 true，则表示角色是超级用户，拥有系统中的所有特权。
rolinherit：如果为 true，则表示角色可以继承其父角色的权限。
rolcreaterole：如果为 true，则表示角色可以创建其他角色。
rolcreatedb：如果为 true，则表示角色可以创建数据库。
rolcanlogin：如果为 true，则表示角色允许登录到数据库系统。如果为 false，则表示该角色不能登录。
rolreplication：如果为 true，则表示角色允许进行流复制。
rolconnlimit：指定角色的并发连接数限制。如果为 -1，则表示没有限制。
rolvaliduntil：指定角色的有效期限。这是一个时间戳，指示角色的有效期限。如果没有指定，则为 null。