【云原生进阶之数据库技术】第三章-PostgreSQL-管理-2.2-运维操作

2 PostgreSQL运维管理

2.1 登入登出

#登入postgres用户
[root@client ~]# su - postgres
#psql -h 服务器 -U 用户名 -d 数据库 -p 端口
-bash-4.2$ psql -U dbuser -d demo -h 127.0.0.1 -p 5432

-bash-4.2$ psql        #链接数据库，默认用户和数据库都是postgres
#相当于系统用户postgres以同名数据库用户的身份登录数据库，这时不用输入密码的，如果一切正常，系统提示符会变成postgres=# ，表示这时已经进入了数据库控制台

#登出
postgres=# \q或exit

2.2 数据库操作

2.2.1 查看psql命令列表

?

2.2.2 数据库相关操作

1.查看有哪些数据库（postgres是该数据库的默认用户）

        切换su - postgres用户(直接在终端输入psql也可实现)，在postgres用户下输入\l

2.连接指定服务器上的数据库

psql -h IP地址或数据库名 -p 端口 -U 用户名 -d 数据库名

3.创建名字为mydb的数据库

CREATE DATABASE mydb

4.查看所有数据库

\l

5.退出数据库

\q

6.切换到名字为mydb的数据库

\c mydb

7.删除mybd数据库

drop database mybd

2.2.3 备份与恢复PostgreSQL数据库

        备份格式：*.bak、*.sql、*.tar

1.将数据库mydb备份到当前目录（.bak或.sql格式）

pd_dump mydb > mydb.bak

2.将数据库mydb备份到/tmp目录（.bak或.sql格式）

pd_dump -U 用户名 -f /tmp/mydb.bak mydb

3.将数据库mydb备份为指定格式

pd_dump -U 用户名 -F t -f /tmp/mydb.tar mydb

4.恢复*.bak或*.sql的备份数据库，以下命令代表将mydb.sql的备份数据库恢复到mydb数据库

psql -U 用户名 -f /tmp/mydb.sql mydb

5.恢复自定义格式的备份数据库

pg_restore -U 用户名 -d mydb /tmp/mydb.tar

备注：在恢复备份数据库之前需要新建空白数据库

2.2.4 其他创建操作库的方法

1.在bash环境中创建

2.在Navicat软件中创建

2.3 如何备份PostgreSQL数据库

        如果在生产环境中使用PostgreSQL，请务必采取预防措施以确保用户的数据不会丢失。

2.3.1 单数据库

        PostgreSQL提供了pg_dump实用程序来简化备份单个数据库的过程。必须以对要备份的数据库具有读取权限的用户身份运行此命令：

        以postgres用户身份进行登录

[root@client ~]# su - postgres

        通过运行以下命令将数据库的内容转存到文件中。替换dbname为要备份的数据库的名称。

-bash-4.2$ pg_dumo 数据库名 > 自定义名.bak

        生成的备份文件*.bak可以使用scp传输到另一台主机也可以存储在本地以供之后使用。使用psql恢复数据库：

-bash-4.2$ psql 数据库名 < 自定义名.bak

        备份格式有几种选择：

1. bak：压缩二进制格式
2. sql：明文转储
3. tar：tarball

        在默认情况下，PostgreSQL将忽略备份过程中发生的任何错误，这可能导致备份不完整，要防止这种情况，可以使用-1选项运行pg_dump命令。这会将整个备份过程视为单个事务，将在发生错误时阻止部分备份。

2.3.2 所有数据库

        由于pg_dump一次只创建一个数据库的备份，因此他不会存储有关数据库角色或其他集群范围配置的信息，要存储此信息并同时备份所有数据库，可以使用pg-dumpall。

创建配置文件：

-bash-4.2$ pg_dumpall > pg_backup.bak

        从备份还原所有数据库：

-bash-4.2$ psql -f pg_backup.bak postgres

##备份数据库
##导出数据库保存为……
$ pg_dump -U 用户名 -f 备份文件 库名 
##导出数据库中某表保存为……
$ pg_dump -U postgres -f 备份文件 -t 表名 库名
##导出数据库以tar格式压缩保存为……
$ pg_dump -U postgres -F t -f 备份文件 库名 

##恢复数据库
##恢复备份文件到指定库
$ psql -U postgres -f 备份文件 库名
##从pg_dump创建的备份文件中恢复数据库，用于恢复由pg_dump转储的任何非纯文本格式中的数据库。
#pg_restore --
##恢复备份文件到指定数据库
$ pg_restore -U 用户名 -d 库名 备份文件

2.4 PostgreSQL用户管理

        在postgresql里没有区分用户和角色的概念，create user是create role的别名，这两个命令几乎是完全相同的，唯一的区别就是create user命令创建的用户默认带有login属性，而create role命令创建的用户不带login属性。

角色属性	说明
login	只有具有login属性的角色可以用作数据库链接的初始角色名
superuser	超级用户
createdb	创建数据库权限
createrole	允许创建或删除其他普通用户角色（超级用户除外）
replication	做流复制时用到的一个用户属性，一般单独设定
password	在登录时要求指定密码时才会起作用，跟客户端的链接认证方式有关
inherit	用户组对组员的一个继承标志，成员可以继承用户组的权限特性

2.5 控制台常用命令总结

命令	说明
\password	设置当前密码
\q	退出
\h	查看sql命令的解释，如\h select
?	查看psql命令列表
\l	列出所有数据库
\c [database_name]	链接其他数据库
\d	列出当前数据库的所有表格
\d [table_name]	列出某一张表格的结构
\du	列出所有用户

2.6 数据表操作

2.6.1 数据类型

        创建表格时每列都必须使用数据类型，PostgreSQL中主要有三类数据类型：

• 数值数据类型
• 字符串数据类型
• 日期/时间数据类型

2.6.1.1 常用数值类型

名字	存储长度	描述	范围
smallint	2字节	小范围整数	-32768到32767
int(integer)	4字节	常用的整数	-2147483648到2147483647
bigint	8字节	大范围整数	-9223372036854775808到9223372036854775807
decimal	可变长	用户指定的精度，精确	小数点前131072位；小数点后16383位
numeric	可变长	用户指定的精度，精确	小数点前131072位；小数点后16383位
real	4字节	可变精度，不精确	6位十进制数字精度
double	8字节	可变精度，不精确	15位十进制数字精度

2.6.1.2 字符串类型包括

名字	描述
char(size)	固定长度字符串，size规定了需存储的字符数，由右边的空格补齐
varchar	可变长度字符串，size规定了需存储的字符数
text	可变长度字符串

2.6.1.3 日期/时间类型

名字	描述
timestamp	日期和时间
data	日期，无时间
time	时间

2.6.1.4 其他数据类型

        其他数据类型还有布尔值boolean（false或true），货币数额（money）和几何数据等。

2.6.2 表管理相关操作

（1）查看数据库中的表

\d

（2）查看表的具体信息

\d 表名

（3）创建表

#命令：

CREATE TABLE 表名(字段1 字段1的数据类型,字段2 字段2的数据类型,字段3 字段3的数据类型,···);

（4） 删除某个表

drop table 表名;

（5）在表中每次插入一条数据

命令：

INSERT INTO 表名 (字段1,字段2,字段3,···) VALUES (字段1的值,字段2 的值,字段3的值,···);

（6）查询表中的所有数据

SELECT * FROM 表名;

（7）查询表中特定条件的数据记录

SELECT * FROM 表名 WHERE 字段名 = 字段值;

(8)统计表中的所有记录总数

SELECT COUNT(*) AS "RECORDS" FROM 表名;

(9)更新表中某个ID的某个字段的值

UPDATE 表名 SET 字段名=字段更新的值 WHERE ID =ID 号;

(10) 同时更新表中某个ID的多个字段的值

UPDATE 表名 SET 字段1=字段1更新的值,字段2=字段2更新的值 WHERE ID =ID号;

(11)同时更行表中多个字段的值（不区分ID）

UPDATE 表名 SET 字段1=字段1更新的值,字段2=字段2更新的值;

(12)按特定条件删除表中的数据

DELETE FROM 表名 WHERE 字段名=字段值;

(13)查看当前时间

SELECT CURRENT_TIMESTAMP;

（14）找出表中某个字段为空/不为空的记录

SELECT * FROM 表名 WHERE 字段 IS NOT NULL; SELECT * FROM 表名 WHERE 字段 IS NULL;

（15）从表的某行开始获取N条数据，一般通过该命令实现分页功能

        以下语句表示：从表t_host的0行开始获取20条数据。

SELECT "t1"."id", "t1"."create_at", "t1"."update_at", "t1"."sn", "t1"."ip", "t1"."ipmi", "t1"."ipmi_user", "t1"."ipmi_password", "t1"."card_type", "t1"."power_status", "t1"."stage", "t1"."task_id", "t1"."create_by", "t1"."update_by" 
FROM "t_host" AS "t1" LIMIT 20 OFFSET 0

参考链接

进阶数据库系列（一）：PostgreSQL 基础入门与安装 - 知乎

PostgreSQL数据库-CSDN博客

PGSQL(PostgreSQL)数据库基础篇：PostgreSQL 的 主要优点 、 劣势 、体系结构 、核心功能 、安装教程。-CSDN博客

PostgreSQL 数据库介绍与使用 - 知乎

PostgreSQL数据库_数据库_单纯的一比-CSDN开发云