ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > 数据库开发技术 > openEuler 22.03-LTS 搭建PostgreSQL数据库服务器

openEuler 22.03-LTS 搭建PostgreSQL数据库服务器

原创 数据库开发技术 作者：shuchaoyang 时间：2022-12-23 07:30:00 0 删除 编辑

1 软件介绍2 配置环境2.1 关闭防火墙并取消开机自启动2.2 修改SELINUX为disabled2.3 创建组和用户2.4 创建数据盘2.4.1 方法一：在root权限下使用fdisk进行磁盘管理2.4.1 方法二：在root权限下使用LVM进行磁盘管理2.5 数据目录授权3 安装、运行和卸载3.1 安装3.2 运行3.2.1 初始化数据库3.2.2 启动数据库3.2.3 登录数据库3.2.4 配置数据库账号密码3.2.5 退出数据库3.2.6 停止数据库3.3 卸载4 管理数据库角色4.1 创建角色创建角色示例4.2 查看角色查看角色示例4.3 修改角色4.3.1 修改用户名4.3.2 修改用户示例4.3.3 修改用户密码4.3.4 修改角色密码示例4.4 删除角色删除角色示例4.5 角色授权角色授权示例4.6 删除用户权限删除用户权限示例5 管理数据库5.1 创建数据库创建数据库示例5.2 选择数据库选择数据库示例5.3 查看数据库查看数据库示例5.4 删除数据库删除数据库示例5.5 备份数据库备份数据库示例5.6 恢复数据库恢复数据库示例


1 软件介绍

PostgreSQL的架构如图1所示，主要进程说明如表1所示。

图 1 PostgreSql架构

表 1 PostgreSql中的主要进程说明

2 配置环境

说明：以下环境配置仅为参考示例，具体配置视实际需求做配置

2.1 关闭防火墙并取消开机自启动

说明：测试环境下通常会关闭防火墙以避免部分网络因素影响，视实际需求做配置。

  1. 在root权限下停止防火墙。

    # systemctl stop firewalld

    例如示例命令如下：

    [root@superman-21 ~]# systemctl stop firewalld  
    [root@superman-21 ~]#

  2. 在root权限下关闭防火墙。

    # systemctl disable firewalld

    说明： 执行disable命令关闭防火墙的同时，也取消了开机自启动。

    例如示例命令如下：

    [root@superman-21 ~]# systemctl disable firewalld
    Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
    Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
    [root@superman-21 ~]#

2.2 修改SELINUX为disabled

  1. 永久关闭，在root权限下修改配置文件。

    # sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux

    说明：

    重启后生效。

    例如示例命令如下：

    [root@superman-21 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
    [root@superman-21 ~]# 
    [root@superman-21 ~]# cat /etc/sysconfig/selinux 

    # This file controls the state of SELinux on the system.
    # SELINUX= can take one of these three values:
    #     enforcing - SELinux security policy is enforced.
    #     permissive - SELinux prints warnings instead of enforcing.
    #     disabled - No SELinux policy is loaded.
    SELINUX=disabled
    # SELINUXTYPE= can take one of these three values:
    #     targeted - Targeted processes are protected,
    #     minimum - Modification of targeted policy. Only selected processes are protected. 
    #     mls - Multi Level Security protection.
    SELINUXTYPE=targeted


    [root@superman-21 ~]#

  2. 临时关闭

    # setenforce 0

    说明：

    即时生效，但重启后会自动开启。

    例如示例命令如下：

    [root@superman-21 ~]# setenforce 0
    setenforce: SELinux is permissive
    [root@superman-21 ~]#

2.3 创建组和用户

说明：服务器环境下，为了系统安全，通常会为进程分配单独的用户，以实现权限隔离。本章节创建的组和用户都是操作系统层面的，不是数据库层面的。

  1. 在root权限下创建MySQL用户（组）。

    # groupadd postgres
    # useradd -g postgres postgres

    例如示例命令如下：

    [root@superman-21 ~]# groupadd postgres
    [root@superman-21 ~]# 
    [root@superman-21 ~]# useradd -g postgres postgres
    [root@superman-21 ~]#

  2. 在root权限下设置MySQL用户密码。

    # passwd postgres

    需要重复输入密码（根据实际需求设置密码）。

    例如示例命令如下：

    [root@superman-21 ~]# passwd postgres
    更改用户 postgres 的密码 。
    新的密码： 
    重新输入新的密码： 
    passwd：所有的身份验证令牌已经成功更新。
    [root@superman-21 ~]#

2.4 创建数据盘

说明：

  • 进行性能测试时，数据目录使用单独硬盘，需要对硬盘进行格式化并挂载，参考方法一或者方法二

  • 非性能测试时，在root权限下执行以下命令，创建数据目录即可。然后跳过本小节：# mkdir /data

2.4.1 方法一：在root权限下使用fdisk进行磁盘管理

  1. 创建分区（以/dev/sdb为例，根据实际情况创建）

    # fdisk /dev/sdb

  2. 输入n，按回车确认。

  3. 输入p，按回车确认。

  4. 输入1，按回车确认。

  5. 采用默认配置，按回车确认。

  6. 采用默认配置，按回车确认。

  7. 输入w，按回车保存。

    例如示例命令如下：

    [root@superman-21 ~]# fdisk /dev/sdb

    欢迎使用 fdisk (util-linux 2.37.2)。
    更改将停留在内存中，直到您决定将更改写入磁盘。
    使用写入命令前请三思。

    设备不包含可识别的分区表。
    创建了一个磁盘标识符为 0x9ac1f07b 的新 DOS 磁盘标签。

    命令(输入 m 获取帮助)：n
    分区类型
       p   主分区 (0 primary, 0 extended, 4 free)
       e   扩展分区 (逻辑分区容器)
    选择 (默认 p)：p
    分区号 (1-4, 默认  1): 1
    第一个扇区 (2048-41943039, 默认 2048): 
    最后一个扇区，+/-sectors 或 +size{K,M,G,T,P} (2048-41943039, 默认 41943039): 

    创建了一个新分区 1，类型为“Linux”，大小为 20 GiB。

    命令(输入 m 获取帮助)：w
    分区表已调整。
    将调用 ioctl() 来重新读分区表。
    正在同步磁盘。

    [root@superman-21 ~]#

  8. 创建文件系统（以xfs为例，根据实际需求创建文件系统）

    # mkfs.xfs /dev/sdb1

    例如示例命令如下：

    [root@superman-21 ~]# mkfs.xfs /dev/sdb1
    meta-data=/dev/sdb1              isize=512    agcount=4, agsize=1310656 blks
             =                       sectsz=512   attr=2, projid32bit=1
             =                       crc=1        finobt=1, sparse=1, rmapbt=0
             =                       reflink=1    bigtime=0 inobtcount=0
    data     =                       bsize=4096   blocks=5242624, imaxpct=25
             =                       sunit=0      swidth=0 blks
    naming   =version 2              bsize=4096   ascii-ci=0, ftype=1
    log      =internal log           bsize=4096   blocks=2560, version=2
             =                       sectsz=512   sunit=0 blks, lazy-count=1
    realtime =none                   extsz=4096   blocks=0, rtextents=0
    [root@superman-21 ~]#

  9. 创建“/data”文件夹以供操作系统挂载分区使用。

    # mkdir /data

    例如示例命令如下：

    [root@superman-21 ~]# mkdir /data    
    [root@superman-21 ~]#

  10. 执行命令编辑“/etc/fstab”使重启后自动挂载数据盘。

    # echo "/dev/sdb1 /data                       xfs    defaults        1 1" >> /etc/fstab

    例如示例命令如下：

    [root@superman-21 ~]# cat /etc/fstab 

    #
    # /etc/fstab
    # Created by anaconda on Mon Nov 28 13:29:03 2022
    #
    # Accessible filesystems, by reference, are maintained under '/dev/disk/'.
    # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
    #
    # After editing this file, run 'systemctl daemon-reload' to update systemd
    # units generated from this file.
    #
    /dev/mapper/vg00-lv_root /                       ext4    defaults        1 1
    UUID=70c4f6e0-20be-46f8-93ba-97c8083bc37a /boot                   ext4    defaults        1 2
    /dev/mapper/vg00-lv_swap none                    swap    defaults        0 0
    [root@superman-21 ~]# 
    [root@superman-21 ~]# echo "/dev/sdb1 /data                       xfs    defaults        1 1" >> /etc/fstab
    [root@superman-21 ~]# 
    [root@superman-21 ~]# cat /etc/fstab 

    #
    # /etc/fstab
    # Created by anaconda on Mon Nov 28 13:29:03 2022
    #
    # Accessible filesystems, by reference, are maintained under '/dev/disk/'.
    # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
    #
    # After editing this file, run 'systemctl daemon-reload' to update systemd
    # units generated from this file.
    #
    /dev/mapper/vg00-lv_root /                       ext4    defaults        1 1
    UUID=70c4f6e0-20be-46f8-93ba-97c8083bc37a /boot                   ext4    defaults        1 2
    /dev/mapper/vg00-lv_swap none                    swap    defaults        0 0
    /dev/sdb1 /data                       xfs    defaults        1 1
    [root@superman-21 ~]#

  11. 挂载数据盘。

    # mount -a

    例如示例命令如下：

    [root@superman-21 ~]# df -h
    文件系统                  容量  已用  可用 已用% 挂载点
    devtmpfs                  4.0M     0  4.0M    0% /dev
    tmpfs                     1.7G   12K  1.7G    1% /dev/shm
    tmpfs                     677M  9.1M  668M    2% /run
    tmpfs                     4.0M     0  4.0M    0% /sys/fs/cgroup
    /dev/mapper/vg00-lv_root   41G  2.9G   36G    8% /
    tmpfs                     1.7G     0  1.7G    0% /tmp
    /dev/sda1                 974M  249M  659M   28% /boot
    [root@superman-21 ~]# 
    [root@superman-21 ~]# mount -a
    [root@superman-21 ~]# 
    [root@superman-21 ~]# df -h
    文件系统                  容量  已用  可用 已用% 挂载点
    devtmpfs                  4.0M     0  4.0M    0% /dev
    tmpfs                     1.7G   12K  1.7G    1% /dev/shm
    tmpfs                     677M  9.1M  668M    2% /run
    tmpfs                     4.0M     0  4.0M    0% /sys/fs/cgroup
    /dev/mapper/vg00-lv_root   41G  2.9G   36G    8% /
    tmpfs                     1.7G     0  1.7G    0% /tmp
    /dev/sda1                 974M  249M  659M   28% /boot
    /dev/sdb1                  20G  175M   20G    1% /data
    [root@superman-21 ~]#

2.4.1 方法二：在root权限下使用LVM进行磁盘管理

说明：此步骤需要安装镜像中的lvm2相关包，步骤如下：

  1. 配置本地yum源，详细信息请参考《openEuler 22.03-LTS 搭建repo服务器》。如果已经执行，则可跳过此步。

  2. 在root权限下执行命令安装lvm2。# yum install lvm2

  1. 创建物理卷（sdb为硬盘名称，具体名字以实际为准）。

    # pvcreate /dev/sdb

    例如示例命令如下：

    [root@superman-21 ~]# pvcreate /dev/sdb
      Physical volume "/dev/sdb" successfully created.
    [root@superman-21 ~]#

  2. 创建物理卷组（其中vg_data为创建的卷组名称，具体名字以实际规划为准）。

    # vgcreate vg_data /dev/sdb

    例如示例命令如下：

    [root@superman-21 ~]# vgcreate vg_data /dev/sdb
      Volume group "vg_data" successfully created
    [root@superman-21 ~]#

  3. 创建逻辑卷（其中20G为规划的逻辑卷大小，具体大小以实际情况为准；lv_data为创建的逻辑卷的名字，具体名称以实际规划为准。)。

    # lvcreate -L 20476M -n lv_data vg_data

    例如示例命令如下：

    [root@superman-21 ~]# lvcreate -L 20476M -n lv_data vg_data 
      Logical volume "lv_data" created.
    [root@superman-21 ~]#

  4. 创建文件系统。

    # mkfs.xfs /dev/vg_data/lv_data

    例如示例命令如下：

    [root@superman-21 ~]# mkfs.xfs /dev/vg_data/lv_data
    meta-data=/dev/vg_data/lv_data   isize=512    agcount=4, agsize=1310464 blks
             =                       sectsz=512   attr=2, projid32bit=1
             =                       crc=1        finobt=1, sparse=1, rmapbt=0
             =                       reflink=1    bigtime=0 inobtcount=0
    data     =                       bsize=4096   blocks=5241856, imaxpct=25
             =                       sunit=0      swidth=0 blks
    naming   =version 2              bsize=4096   ascii-ci=0, ftype=1
    log      =internal log           bsize=4096   blocks=2560, version=2
             =                       sectsz=512   sunit=0 blks, lazy-count=1
    realtime =none                   extsz=4096   blocks=0, rtextents=0
    [root@superman-21 ~]#

  5. 创建“/data”文件夹以供操作系统挂载分区使用。

    # mkdir /data

    例如示例命令如下：

    [root@superman-21 ~]# mkdir /data    
    [root@superman-21 ~]#

  6. 执行命令编辑“/etc/fstab”使重启后自动挂载数据盘。

    # echo "/dev/vg_data/lv_data /data                       xfs    defaults        1 1" >> /etc/fstab

    例如示例命令如下：

    [root@superman-21 ~]# cat /etc/fstab 

    #
    # /etc/fstab
    # Created by anaconda on Mon Nov 28 13:29:03 2022
    #
    # Accessible filesystems, by reference, are maintained under '/dev/disk/'.
    # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
    #
    # After editing this file, run 'systemctl daemon-reload' to update systemd
    # units generated from this file.
    #
    /dev/mapper/vg00-lv_root /                       ext4    defaults        1 1
    UUID=70c4f6e0-20be-46f8-93ba-97c8083bc37a /boot                   ext4    defaults        1 2
    /dev/mapper/vg00-lv_swap none                    swap    defaults        0 0
    [root@superman-21 ~]# 
    [root@superman-21 ~]# echo "/dev/vg_data/lv_data /data                       xfs    defaults        1 1" >> /etc/fstab
    [root@superman-21 ~]# 
    [root@superman-21 ~]# cat /etc/fstab 

    #
    # /etc/fstab
    # Created by anaconda on Mon Nov 28 13:29:03 2022
    #
    # Accessible filesystems, by reference, are maintained under '/dev/disk/'.
    # See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info.
    #
    # After editing this file, run 'systemctl daemon-reload' to update systemd
    # units generated from this file.
    #
    /dev/mapper/vg00-lv_root /                       ext4    defaults        1 1
    UUID=70c4f6e0-20be-46f8-93ba-97c8083bc37a /boot                   ext4    defaults        1 2
    /dev/mapper/vg00-lv_swap none                    swap    defaults        0 0
    /dev/vg_data/lv_data /data                       xfs    defaults        1 1
    [root@superman-21 ~]#

  7. 挂载数据盘。

    # mount -a

    例如示例命令如下：

    [root@superman-21 ~]# df -h
    文件系统                  容量  已用  可用 已用% 挂载点
    devtmpfs                  4.0M     0  4.0M    0% /dev
    tmpfs                     1.7G   12K  1.7G    1% /dev/shm
    tmpfs                     677M  9.1M  668M    2% /run
    tmpfs                     4.0M     0  4.0M    0% /sys/fs/cgroup
    /dev/mapper/vg00-lv_root   41G  2.9G   36G    8% /
    tmpfs                     1.7G     0  1.7G    0% /tmp
    /dev/sda1                 974M  249M  659M   28% /boot
    [root@superman-21 ~]# 
    [root@superman-21 ~]# mount -a
    [root@superman-21 ~]# 
    [root@superman-21 ~]# df -h
    文件系统                     容量  已用  可用 已用% 挂载点
    devtmpfs                     4.0M     0  4.0M    0% /dev
    tmpfs                        1.7G   12K  1.7G    1% /dev/shm
    tmpfs                        677M  9.1M  668M    2% /run
    tmpfs                        4.0M     0  4.0M    0% /sys/fs/cgroup
    /dev/mapper/vg00-lv_root      41G  2.9G   36G    8% /
    tmpfs                        1.7G     0  1.7G    0% /tmp
    /dev/sda1                    974M  249M  659M   28% /boot
    /dev/mapper/vg_data-lv_data   20G  175M   20G    1% /data
    [root@superman-21 ~]#

2.5 数据目录授权

在root权限下修改目录权限。

# chown -R postgres:postgres /data

例如示例命令如下：

[root@superman-21 ~]# chown -R postgres:postgres /data
[root@superman-21 ~]#


3 安装、运行和卸载

3.1 安装

  1. 配置本地yum源，详细信息请参考《openEuler 22.03-LTS 搭建repo服务器》

  2. 清除缓存。

    # dnf clean all

    例如示例命令如下：

    [root@superman-21 ~]# dnf clean all
    36 files removed
    [root@superman-21 ~]#

  3. 创建缓存。

    # dnf makecache

    例如示例命令如下：

    [root@superman-21 ~]# dnf makecache
    OS                                                                                             1.6 MB/s | 3.4 MB     00:02    
    everything                                                                                     6.5 MB/s |  16 MB     00:02    
    EPOL                                                                                           4.3 MB/s | 2.6 MB     00:00    
    debuginfo                                                                                      5.4 MB/s | 3.9 MB     00:00    
    source                                                                                         3.2 MB/s | 1.7 MB     00:00    
    update                                                                                         9.2 MB/s |  29 MB     00:03    
    Metadata cache created.
    [root@superman-21 ~]#

  4. 在root权限下安装PostgreSQL服务器。

    # dnf install postgresql-server

    例如示例命令如下：

    [root@superman-21 ~]# dnf install postgresql-server
    Last metadata expiration check: 0:00:57 ago on 2022年12月22日 星期四 20时17分30秒.
    Dependencies resolved.
    ===============================================================================================================================
     Package                             Architecture             Version                           Repository                Size
    ===============================================================================================================================
    Installing:
     postgresql-server                   x86_64                   13.3-7.oe2203                     update                   5.4 M
    Installing dependencies:
     postgresql                          x86_64                   13.3-7.oe2203                     update                   1.6 M

    Transaction Summary
    ===============================================================================================================================
    Install  2 Packages

    Total download size: 7.0 M
    Installed size: 29 M
    Is this ok [y/N]: y
    Downloading Packages:
    (1/2): postgresql-13.3-7.oe2203.x86_64.rpm                                                     3.2 MB/s | 1.6 MB     00:00    
    (2/2): postgresql-server-13.3-7.oe2203.x86_64.rpm                                              3.4 MB/s | 5.4 MB     00:01    
    -------------------------------------------------------------------------------------------------------------------------------
    Total                                                                                          4.4 MB/s | 7.0 MB     00:01     
    retrieving repo key for update unencrypted from http://repo.openeuler.org/openEuler-22.03-LTS/OS/x86_64/RPM-GPG-KEY-openEuler
    update                                                                                          23 kB/s | 2.1 kB     00:00    
    Importing GPG key 0xB25E7F66:
     Userid     : "private OBS (key without passphrase) <defaultkey@localobs>"
     Fingerprint: 12EA 74AC 9DF4 8D46 C69C A0BE D557 065E B25E 7F66
     From       : http://repo.openeuler.org/openEuler-22.03-LTS/OS/x86_64/RPM-GPG-KEY-openEuler
    Is this ok [y/N]: y
    Key imported successfully
    Running transaction check
    Transaction check succeeded.
    Running transaction test
    Transaction test succeeded.
    Running transaction
      Preparing        :                                                                                                       1/1 
      Installing       : postgresql-13.3-7.oe2203.x86_64                                                                       1/2 
      Running scriptlet: postgresql-server-13.3-7.oe2203.x86_64                                                                2/2 
      Installing       : postgresql-server-13.3-7.oe2203.x86_64                                                                2/2 
      Running scriptlet: postgresql-server-13.3-7.oe2203.x86_64                                                                2/2 
    /usr/lib/tmpfiles.d/postgresql.conf:1: Line references path below legacy directory /var/run/, updating /var/run/postgresql → /run/postgresql; please update the tmpfiles.d/ drop-in file accordingly.

      Verifying        : postgresql-13.3-7.oe2203.x86_64                                                                       1/2 
      Verifying        : postgresql-server-13.3-7.oe2203.x86_64                                                                2/2 

    Installed:
      postgresql-13.3-7.oe2203.x86_64                            postgresql-server-13.3-7.oe2203.x86_64                           

    Complete!
    [root@superman-21 ~]#

  5. 查看安装后的rpm包。

    # rpm -qa | grep postgresql

    例如示例命令如下：

    [root@superman-21 ~]# rpm -qa | grep postgresql
    postgresql-13.3-7.oe2203.x86_64
    postgresql-server-13.3-7.oe2203.x86_64
    [root@superman-21 ~]#

3.2 运行

3.2.1 初始化数据库

注意：此步骤在postgres用户下操作。

  1. 切换到已创建的PostgreSQL用户。

    # su - postgres

    例如示例命令如下：

    [root@superman-21 ~]# su - postgres


    Welcome to 5.10.0-60.18.0.50.oe2203.x86_64

    System information as of time:  2022年 12月 22日 星期四 20:21:50 CST

    System load:    0.99
    Processes:      151
    Memory used:    6.4%
    Swap used:      0%
    Usage On:       7%
    IP address:     192.168.0.21
    IP address:     192.168.122.1
    Users online:   1
    To run a command as administrator(user "root"),use "sudo <command>".
    [postgres@superman-21 ~]$

  2. 初始化数据库，其中命令中的/usr/bin是命令initdb所在的目录。

    $ initdb -D /data/

    例如示例命令如下：

    [postgres@superman-21 ~]$ initdb -D /data/
    属于此数据库系统的文件宿主为用户 "postgres".
    此用户也必须为服务器进程的宿主.
    数据库簇将使用本地化语言 "zh_CN.UTF-8"进行初始化.
    默认的数据库编码已经相应的设置为 "UTF8".
    initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
    缺省的文本搜索配置将会被设置到"simple"

    禁止为数据页生成校验和.

    修复已存在目录 /data 的权限 ... 成功
    正在创建子目录 ... 成功
    选择动态共享内存实现 ......posix
    选择默认最大联接数 (max_connections) ... 100
    选择默认共享缓冲区大小 (shared_buffers) ... 128MB
    selecting default time zone ... Asia/Shanghai
    创建配置文件 ... 成功
    正在运行自举脚本 ...成功
    正在执行自举后初始化 ...成功
    同步数据到磁盘...成功

    initdb: 警告: 为本地连接启用"trust"身份验证
    你可以通过编辑 pg_hba.conf 更改或你下次
    执行 initdb 时使用 -A或者--auth-local和--auth-host选项.

    成功。您现在可以用下面的命令开启数据库服务器：

        /usr/bin/pg_ctl -D /data/ -l 日志文件 start

    [postgres@superman-21 ~]$

3.2.2 启动数据库

  1. 启动PostgreSQL数据库。

    $ pg_ctl -D /data/ -l /data/logfile start

    例如示例命令如下：

    [postgres@superman-21 ~]$ pg_ctl -D /data/ -l /data/postgres.log start
    等待服务器进程启动 .... 完成
    服务器进程已经启动
    [postgres@superman-21 ~]$

  2. 确认PostgreSQL数据库进程是否正常启动。

    $ ps -ef | grep postgres

    命令执行后，打印信息如下图所示，PostgreSQL相关进程已经正常启动了。

3.2.3 登录数据库

登录数据库。

$ psql -U postgres

说明：初次登录数据库，无需密码。

例如示例命令如下：

[postgres@superman-21 ~]$ psql -U postgres
psql (13.3)
输入 "help" 来获取帮助信息.

postgres=#

3.2.4 配置数据库账号密码

登录后，设置postgres密码。

postgres=# alter user postgres with password '123456';

例如示例命令如下：

postgres=# alter user postgres with password '123456';
ALTER ROLE
postgres=#

3.2.5 退出数据库

执行\q退出数据库。

postgres=# \q

例如示例命令如下：

postgres=# \q
[postgres@superman-21 ~]$

3.2.6 停止数据库

停止PostgreSQL数据库。

$ pg_ctl -D /data/ -l /data/logfile stop

例如示例命令如下：

[postgres@superman-21 ~]$ pg_ctl -D /data/ -l /data/postgres.log stop
等待服务器进程关闭 .... 完成
服务器进程已经关闭
[postgres@superman-21 ~]$

3.3 卸载

  1. 在postgres用户下停止数据库。

    $ pg_ctl -D /data/ -l /data/logfile stop

    例如示例命令如下：

    [postgres@superman-21 ~]$ pg_ctl -D /data/ -l /data/postgres.log stop
    等待服务器进程关闭 .... 完成
    服务器进程已经关闭
    [postgres@superman-21 ~]$

  2. 在root用户下执行dnf remove postgresql-server卸载PostgreSQL数据库。

    # dnf remove postgresql-server

    例如示例命令如下：

    [root@superman-21 ~]# dnf remove postgresql-server
    Dependencies resolved.
    ===============================================================================================================================
     Package                             Architecture             Version                          Repository                 Size
    ===============================================================================================================================
    Removing:
     postgresql-server                   x86_64                   13.3-7.oe2203                    @update                    22 M
    Removing unused dependencies:
     postgresql                          x86_64                   13.3-7.oe2203                    @update                   6.2 M

    Transaction Summary
    ===============================================================================================================================
    Remove  2 Packages

    Freed space: 29 M
    Is this ok [y/N]: y
    Running transaction check
    Transaction check succeeded.
    Running transaction test
    Transaction test succeeded.
    Running transaction
      Preparing        :                                                                                                       1/1 
      Running scriptlet: postgresql-server-13.3-7.oe2203.x86_64                                                                1/1 
      Running scriptlet: postgresql-server-13.3-7.oe2203.x86_64                                                                1/2 
      Erasing          : postgresql-server-13.3-7.oe2203.x86_64                                                                1/2 
      Running scriptlet: postgresql-server-13.3-7.oe2203.x86_64                                                                1/2 
      Erasing          : postgresql-13.3-7.oe2203.x86_64                                                                       2/2 
      Running scriptlet: postgresql-13.3-7.oe2203.x86_64                                                                       2/2 
      Verifying        : postgresql-13.3-7.oe2203.x86_64                                                                       1/2 
      Verifying        : postgresql-server-13.3-7.oe2203.x86_64                                                                2/2 

    Removed:
      postgresql-13.3-7.oe2203.x86_64                            postgresql-server-13.3-7.oe2203.x86_64                           

    Complete!
    [root@superman-21 ~]#


4 管理数据库角色

4.1 创建角色

可以使用CREATE ROLE语句或createuser来创建角色。createuser是对CREATE ROLE命令的封装，需要在shell界面执行，而不是在数据库界面。

CREATE ROLE rolename [ [ WITH ] option [ ... ] ];
createuser rolename

其中：

  • rolename：角色名。

  • option为参数选项，常用的有：

    • SUPERUSER | NOSUPERUSER：决定一个新角色是否为"超级用户"，若未指定，则默认为NOSUPERUSER，即不是超级用户。

    • CREATEDB | NOCREATEDB：定义一个角色是否能创建数据库，若未指定，则默认为NOCREATEDB，即不能创建数据库。

    • CREATEROLE | NOCREATEROLE：决定一个角色是否可以创建新角色，若未指定，则默认为NOCREATEROLE，即不能创建新角色。

    • INHERIT | NOINHERIT：决定一个角色是否"继承"它所在组的角色的权限。一个带有 INHERIT 属性的角色可以自动使用已经赋与它直接或间接所在组的任何权限。若未指定，则默认为INHERIT。

    • LOGIN | NOLOGIN：决定一个角色是否可以登录，一个拥有LOGIN属性的角色可以认为是一个用户，若无此属性的角色可以用于管理数据库权限，但是并不是用户，若未指定，则默认为NOLOGIN。但若创建角色是使用的是CREATE USER而不是CREATE ROLE，则默认是LOGIN属性。

    • [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'：设置角色的密码，密码只对那些拥有 LOGIN 属性的角色有意义。ENCRYPTED | UNENCRYPTED表示是否对密码进行加密，若未指定，则默认为ENCRYPTED，即加密。

    • VALID UNTIL 'timestamp'：角色的密码失效的时间戳，若为指定，则表示密码永久有效。

    • IN ROLE rolename1：列出一个或多个现有的角色，新角色rolename将立即加入这些角色，成为rolename1的成员。

    • ROLE rolename2：列出一个或多个现有的角色，它们将自动添加为新角色rolename的成员，即新角色为"组"。

要使用这条命令，必须拥有 CREATEROLE 权限或者是数据库超级用户。

创建角色示例

创建一个可以登录的角色roletest1。

postgres=# CREATE ROLE roletest1 LOGIN;
CREATE ROLE
postgres=#

创建一个密码为123456的角色roletest2。

postgres=# CREATE ROLE roletest2 WITH LOGIN PASSWORD '123456';
CREATE ROLE
postgres=#

创建角色名为roletest3的角色。。

[postgres@superman-21 ~]$ createuser roletest3
[postgres@superman-21 ~]$

4.2 查看角色

可以使用SELECT语句或psql的元命令\du查看角色。

SELECT rolename FROM pg_roles;
\du

其中：rolename：角色名。

查看角色示例

查看所有角色名。

postgres=# SELECT rolname from pg_roles;
          rolname          
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
 roletest1
 roletest2
 roletest3
(12 行记录)

postgres=#

查看现有角色。

postgres=# \du
                             角色列表
 角色名称  |                    属性                    | 成员属于 
-----------+--------------------------------------------+----------
 postgres  | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 roletest1 |                                            | {}
 roletest2 |                                            | {}
 roletest3 |                                            | {}

postgres=#

4.3 修改角色

4.3.1 修改用户名

可以使用ALTER ROLE语句修改一个已经存在的角色名。

ALTER ROLE oldrolername RENAME TO newrolename;

其中：

  • oldrolername：旧的角色名。

  • newrolename：新的角色名。

4.3.2 修改用户示例

将角色名roletest1修改为roletest4。

postgres=# ALTER ROLE roletest1 RENAME TO roletest4;
ALTER ROLE
postgres=#

4.3.3 修改用户密码

可以使用ALTER ROLE语句修改一个角色的登录密码。

ALTER ROLE rolename PASSWORD 'password'

其中：

  • rolename：角色名。

  • password：密码。

4.3.4 修改角色密码示例

将roletest2的密码修改为456789。

postgres=# ALTER ROLE roletest2 WITH PASSWORD '456789';
ALTER ROLE
postgres=#

4.4 删除角色

可以使用DROP ROLE语句或dropuser来删除角色。dropuser是对DROP ROLE命令的封装，需要在shell界面执行，而不是在数据库界面。

DROP ROLE rolename;
dropuser rolename

其中：rolename：角色名。

删除角色示例

删除roletest2角色。

postgres=# DROP ROLE roletest2;
DROP ROLE
postgres=#

删除roletest3角色。。

[postgres@superman-21 ~]$ dropuser roletest3
[postgres@superman-21 ~]$

4.5 角色授权

可以使用GRANT语句来对角色授权。

对角色授予表的操作权限：

GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

对角色授予序列的操作权限：

GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

对角色授予数据库的操作权限：

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE databasename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

对角色授予函数的操作权限：

GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

对角色授予过程语言的操作权限：

GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

对角色授予模式的操作权限：

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]  

对角色授予表空间的操作权限：

GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] TO { rolename | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

将角色rolename1的成员关系赋予角色rolename2：

GRANT rolename1 [, ...] TO rolename2 [, ...] [ WITH ADMIN OPTION ]

其中：

  • SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、USAGE、CREATE、CONNECT、TEMPORARY、TEMP、EXECUTE、ALL [ PRIVILEGES ]：用户的操作权限，ALL [ PRIVILEGES ]表示所有的权限，PRIVILEGES 关键字在 PostgreSQL 里是可选的，但是严格的 SQL 要求有这个关键字。

  • ON字句：用于指定权限授予的对象。

  • tablename：表名。

  • TO字句：用来指定被赋予权限的角色。

  • rolename、rolename1、rolename2：角色名。

  • groupname：角色组名。

  • PUBLIC：表示该权限要赋予所有角色，包括那些以后可能创建的用户。

  • WITH GRANT OPTION：表示权限的接收者也可以将此权限赋予他人，否则就不能授权他人。该选项不能赋予给PUBLIC。

  • sequencename：序列名。

  • databasename：数据库名。

  • funcname ( [ [ argmode ] [ argname ] argtype [, …] ] )：函数名及其参数。

  • langname：过程语言名。

  • schemaname：模式名。

  • tablespacename：表空间名。

  • WITH ADMIN OPTION：表示成员随后就可以将角色的成员关系赋予其它角色，以及撤销其它角色的成员关系。

角色授权示例

对roletest4授予数据库database1的CREATE权限。

postgres=# GRANT CREATE ON DATABASE postgres TO roletest4;
GRANT
postgres=#

对所有用户授予表t1的所有权限。

postgres=# GRANT ALL PRIVILEGES ON TABLE t1 TO PUBLIC; 
GRANT
postgres=#

4.6 删除用户权限

可以使用REVOKE语句来撤销以前赋予一个或多个角色的权限。

撤销角色对表的操作权限：

REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...]

撤销角色对序列的操作权限：

REVOKE [ GRANT OPTION FOR ] { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]  

撤销角色对数据库的操作权限：

REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE databasename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

撤销角色对函数的操作权限：

REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

撤销角色对过程语言的操作权限：

REVOKE [ GRANT OPTION FOR ] { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

撤销角色对模式的操作权限：

REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]  

撤销角色对表空间的操作权限：

REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] FROM { rolename | GROUP groupname | PUBLIC } [, ...] [ CASCADE | RESTRICT ]

删除rolename2的rolename1的成员关系：

REVOKE [ ADMIN OPTION FOR ] rolename1 [, ...] FROM rolename2 [, ...] [ CASCADE | RESTRICT ]

其中：

  • GRANT OPTION FOR：表示只是撤销对该权限的授权的权力，而不是撤销该权限本身。

  • SELECT、INSERT、UPDATE、DELETE、REFERENCES、TRIGGER、USAGE、CREATE、CONNECT、TEMPORARY、TEMP、EXECUTE、ALL [ PRIVILEGES ]：用户的操作权限，ALL [ PRIVILEGES ]表示所有的权限，PRIVILEGES关键字在 PostgreSQL里是可选的，但是严格的SQL 要求有这个关键字。

  • ON字句：用于指定撤销权限的对象。

  • tablename：表名。

  • FROM字句：用来指定被撤销权限的角色。

  • rolename、rolename1、rolename2：角色名。

  • groupname：角色组名。

  • PUBLIC：表示撤销隐含定义的、拥有所有角色的组，但并不意味着所有角色都失去了权限，那些直接得到的权限以及通过一个组得到的权限仍然有效。

  • sequencename：序列名。

  • CASCADE：撤销所有依赖性权限。

  • RESTRICT：不撤销所有依赖性权限。

  • databasename：数据库名。

  • funcname ( [ [ argmode ] [ argname ] argtype [, …] ] )：函数名及其参数。

  • langname：过程语言名。

  • schemaname：模式名。

  • tablespacename：表空间名。

  • ADMIN OPTION FOR：表示传递的授权不会自动收回。

删除用户权限示例

对roletest4授予数据库postgres的CREATE权限。

postgres=# GRANT CREATE ON DATABASE postgres TO roletest4; 
GRANT
postgres=#

对所有用户授予表pg_proc的所有权限。

postgres=# GRANT ALL PRIVILEGES ON TABLE pg_proc TO PUBLIC;
GRANT
postgres=#


5 管理数据库

5.1 创建数据库

可以使用CREATE DATABASE语句或createdb来创建角色。createrdb是对CREATE DATABASE命令的封装，需要在shell界面执行，而不是在数据库界面。

CREATE DATABASE databasename;
createdb databasename

其中：databasename为数据库名。

要使用这条命令，必须拥有CREATEDB权限。

创建数据库示例

创建一个数据库testdb1。

postgres=# CREATE DATABASE testdb1;
CREATE DATABASE
postgres=#

5.2 选择数据库

可以使用\c语句来选择数据库。

\c databasename;

其中：databasename为数据库名称。

选择数据库示例

选择testdb1数据库。

postgres=# \c testdb1;
您现在已经连接到数据库 "testdb1",用户 "postgres".
testdb1=#

5.3 查看数据库

可以使用\l语句来查看数据库。

\l;

查看数据库示例

查看所有数据库。

postgres=# \l
                                     数据库列表
   名称    |  拥有者  | 字元编码 |  校对规则   |    Ctype    |       存取权限        
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | roletest4=C/postgres
 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
 testdb1   | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
(4 行记录)

postgres=#

5.4 删除数据库

可以使用DROP DATABASE语句或dropdb来删除数据库。dropdb是对DROP DATABASE命令的封装，需要在shell界面执行，而不是在数据库界面。

注意：删除数据库要谨慎操作，一旦删除，数据库中的所有表和数据都会删除。

DROP DATABASE databasename;
dropdb databasename

其中：databasename为数据库名称。

DROP DATABASE会删除数据库的系统目录项并且删除包含数据的文件目录。

DROP DATABASE只能由超级管理员或数据库拥有者执行。

删除数据库示例

删除testdb1数据库。

postgres=# DROP DATABASE testdb1;
DROP DATABASE
postgres=#

5.5 备份数据库

可以使用pg_dump命令备份数据库，将数据库转储到一个脚本文件或其他归档文件中。

pg_dump [option]... [databasename] > outfile

其中：

  • databasename：数据库名称。如果没有声明这个参数，那么使用环境变量 PGDATABASE 。如果那个环境变量也没声明，那么使用发起连接的用户名。

  • outfile：数据库备份的文件。

  • option：pg_dump命令参数选项，多个参数之间可以使用空格分隔。常用的pg_dump命令参数选项如下：

    • -f，--file= filename ：指输出到指定的文件。如果忽略，则使用标准输出。

    • -d，--dbname= databasename ：指定转储的数据库。

    • -h，--host= hostname ：指定主机名。

    • -p，--port= portnumber ：指定端口。

    • -U，--username= username ：指定连接的用户名。

    • -W，--password：强制口令提示（自动）。

备份数据库示例

备份主机为127.0.0.1，端口为5432，postgres用户下的testdb2数据库到testdb2.sql中。

[postgres@superman-21 ~]$ pg_dump -h 127.0.0.1 -p 5432 -U postgres -W testdb2 > testdb2.sql
口令: 
[postgres@superman-21 ~]$

5.6 恢复数据库

可以使用psql命令恢复数据库。

psql [option]... [databasename [username]] < infile

其中：

  • databasename：数据库名称。如果没有声明这个参数，那么使用环境变量 PGDATABASE 。如果那个环境变量也没声明，那么使用发起连接的用户名。

  • username：用户名。

  • infile：pg_dump命令中的outfile参数。

  • option：psql命令参数选项，多个参数之间可以使用空格分隔。常用的psql命令参数选项如下：

    • -f，--file=filename：指输出到指定的文件。如果忽略，则使用标准输出。

    • -d，--dbname=databasename：指定转储的数据库。

    • -h，--host=hostname：指定主机名。

    • -p，--port=portnumber：指定端口。

    • -U，--username=username：指定连接的用户名。

    • -W，--password：强制口令提示（自动）。

psql命令不会自动创建databasename数据库，所以在执行psql恢复数据库之前需要先创建databasename数据库。

恢复数据库示例

将testdb2.sql脚本文件导入到主机为127.0.0.1，端口为5432，postgres用户下newdb数据库中。

[postgres@superman-21 ~]$ createdb newdb
[postgres@superman-21 ~]$ 
[postgres@superman-21 ~]$ psql -h 127.0.0.1 -p 5432 -U postgres -W -d newdb < testdb2.sql
口令：
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 行记录)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 0
COPY 0
[postgres@superman-21 ~]$


有什么吐槽或反馈意见，直接告诉我！

温馨提示：如出现二维码失效也可直接添加微信号：ysc13803862469。

原文作者：超哥的IT私房菜；如有侵权，请联系管理员删除。

0
0
上一篇： openEuler 22.03-LTS 管理网络
下一篇： openEuler 22.03-LTS 搭建MySQL数据库服务器
请登录后发表评论 登录
全部评论
shuchaoyang
专家姓名：杨书超
专注于IT系统集成、运维、架构二十年的IT职场老兵，拥有CCNA、OCA、OCP、PGCA、PGCE认证证书，公众号：超哥的IT私房菜，知识星球：超哥的IT私房菜，目前是多家IT厂商的咨询顾问。

注册时间：2021-08-27

  • 博文量
    552
  • 访问量
    303501

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593