• centos7系统下postgresql15离线安装，卸载

    1. 创建postgres用户

    1. #[root@VMTest postgresql16]# useradd -g postgres postgres
    2. [root@VMTest postgresql16]# useradd postgres

    插曲：在线YUM安装

    在线安装参考: PostgreSQL: Linux downloads (Red Hat family)

    2. 下载并安装离线rpm包

    2.1 从postgresql官网下载相应rpm离线安装包:

    注意：

    centos7只支持 rhel-7字眼的目录。

    下载地址: Index of /pub/repos/yum/15/redhat/rhel-7.12-x86_64/

    2.2 需要下载的安装包有:

    1. libzstd-1.5.2-1.el7.x86_64.rpm
    2. postgresql15-15.5-1PGDG.rhel7.x86_64.rpm
    3. libicu-50.2-4.el7_7.x86_64.rpm(可能需要)
    4. postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm
    5. postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm
    6.  
    7. #其中, libicu-50.1.2-17.el7.x86_64.rpm的下载地址是
    8. #https://centos.pkgs.org/7/centos-x86_64/libicu-50.1.2-17.el7.x86_64.rpm.html
    9. # http://mirror.centos.org/centos/7/os/x86_64/Packages/libicu-50.2-3.el7.x86_64.rpm
    10. http://mirror.centos.org/centos/7/os/x86_64/Packages/libicu-50.2-4.el7_7.x86_64.rpm
    11.  
    12. # 下载
    13. $. wget https://koji.xcp-ng.org/kojifiles/packages/zstd/1.5.2/1.el7/x86_64/libzstd-1.5.2-1.el7.x86_64.rpm
    14.  
    15. $. wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.12-x86_64/postgresql15-15.5-1PGDG.rhel7.x86_64.rpm
    16.  
    17. $. wget http://mirror.centos.org/centos/7/os/x86_64/Packages/libicu-50.2-4.el7_7.x86_64.rpm
    18.  
    19. $. wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.12-x86_64/postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm
    20.  
    21. $. wget https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-7.12-x86_64/postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm

    2.3 安装下载的离线rpm包

    将上述下载的rpm包上传到centos7上, 并使用rpm -ivh 进行安装：

    1. [root@VMTest ~]# rpm -ivh libzstd-1.5.2-1.el7.x86_64.rpm
    2. [root@VMTest ~]# rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm
    3. # 安装lib库
    4. [root@VMTest ~]# rpm -ivh postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm
    5. # 安装客户端
    6. [root@VMTest ~]# rpm -ivh postgresql15-15.5-1PGDG.rhel7.x86_64.rpm
    7. # 安装服务端  
    8. [root@VMTest ~]# rpm -ivh postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm
    9.  
    10. #ps:如果要卸载的话, 先停止postges服务，然后执行下面的命令即可
    11. [root@VMTest ~]# rpm -qe postgresql15-libs-15.5-1PGDG.rhel7.x86_64.rpm
    12. [root@VMTest ~]# rpm -qe postgresql15-15.5-1PGDG.rhel7.x86_64.rpm
    13. [root@VMTest ~]# rpm -qe postgresql15-server-15.5-1PGDG.rhel7.x86_64.rpm

    3. 初始化数据库

    1. [root@VMTest ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
    2. Initializing database ... OK

    4. 设置开机启动，启动数据库

    1. [root@VMTest ~]# systemctl enable postgresql-15 --now  #设置开机启动
    2. [root@VMTest ~]# systemctl status postgresql-15 
    3. [root@VMTest ~]# systemctl start postgresql-15   #启动postgresql
    4.  
    5. #查看启动效果
    6. 方法一:
    7. [root@VMTest ~]# ps -ef | grep postgre
    8.  
    9. #方法二：
    10. [root@VMTest ~]# systemctl status postgresql-15

    5. 移动数据库到指定目录

    5.0 前提条件

    1. # 移动前须先执行命令:
    2.   1. mkdir -p /data/pgsql/  
    3.   2. chown postgres:postgres -R /data/pgsql
    4.   3. systemctl stop postgresql-15

    5.1 移动目录

    1. [root@VMTest ~]# mv /var/lib/pgsql/15/* /data/pgsql/
    2. [root@VMTest ~]# chown -R postgres:postgres /data/pgsql/
    3. [root@VMTest ~]# chmod 700 /data/pgsql/data -R

    5.2 修改配置文件

    1. #a.修改指定的数据目录
    2. [root@VMTest ~]# vi /usr/lib/systemd/system/postgresql-15.service 
    3. #修改Environment=PGDATA=/var/lib/pgsql/15/data/为
    4.   Environment=PGDATA=/data/pgsql/data/
    5.  
    6. #b.修改数据目录
    7. [root@VMTest ~]# vi /data/pgsql/data/postgresql.conf
    8. #修改data_directory:
    9.  data_directory = '/data/pgsql/data'
    10.  
    11. 其它配置修改:
    12. log_directory = 'log'  
    13. max_connections = 100

    5.3 重新加载配置文件，重启数据库

    1. [root@VMTest ~]# systemctl daemon-reload
    2. [root@VMTest ~]# systemctl restart postgresql-15
    3. [root@VMTest ~]# ps -ef | grep postgres  #确认启动成功
    4. [root@VMTest ~]# systemctl status postgresql-15

    6. 修改密码

    1. [root@VMTest ~]# su postgres
    2. [postgres@VMTest root]$ psql
    3. could not change directory to "/root": Permission denied
    4. psql (11.1)
    5. Type "help" for help.
    6.  
    7. postgres=# 
    8.  
    9. #------------------------------------------------------
    10. # 执行命令
    11. postgres=# ALTER ROLE postgres WITH PASSWORD '123abc';
    12. # 退出
    13. postgres=# \q

    7. 修改授权

    7.1 设置远程连接

    1. #修改1， ps：认证方式解释见附录
    2. # [postgres@VMTest root]$ vi /var/lib/pgsql/15/data/pg_hba.conf
    3. [postgres@VMTest root]$ vi /data/pgsql/data/pg_hba.conf
    4. # IPv4 local connections: 
    5. #host    all             all             127.0.0.1/32            ident
    6. host    all             all             0.0.0.0/0                 md5
    7.  
    8. #new
    9. host    all             all             0.0.0.0/0               trust
    10.  
    11. #修改2
    12. # [root@vmfiend01 ~]# vi /var/lib/pgsql/15/data/postgresql.conf
    13. [root@vmfiend01 ~]# vi /data/pgsql/data/postgresql.conf
    14. #修改listen_addresses 
    15. listen_addresses = '*'
    16. #有需求修改port
    17. #port = 5432 
    18.  
    19. #重启数据库
    20. [root@VMTest ~]# systemctl restart postgresql-15

    8. 创建数据库

    8.1 创建数据库

    1. [root@VMTest ~]# su postgres
    2. [postgres@VMTest root]$ psql
    3. postgres=# create database databasename;

    8.2 查看数据库列表

    postgres=# \l

    8.3 进入数据库

    postgres=# \c databasename;

    8.4 进入数据库后，查看数据库

    postgres=# \d

    8.5 创建模式schema

    deepcogni=# create schema test1;

    8.6 navicat连接postgresql数据库

    navicat连接时会遇到以下问题, 解决方式如下: 
    1. 问题:
    2.     错误: 字段“datlastsysoid“不存在（Navicat报错）
    3.  
    4. 说明:    
    5.     查阅一下资料，Postgres 15 从表中删除了 datlastsysoid 字段pg_database，因此 Navicat 15.0.29 或 16.1 之前的任何版本在查找此已弃用字段时都会引发此错误。
    6.  
    7. 解决:
    8.     方式一:
    9.         要解决此问题，请升级到最新的 Navicat 15.0.29 或 16.1 及更高版本（可能需要新的许可证）
    10.         
    11.     方式二(use):
    12.         打开 Navicat 文件夹（通常在 C：\Program Files\PremiumSoft\Navicat…） 下），
    13.         找到 libcc.dll 并创建此文件的备份。
    14.         在任何十六进制编辑器中打开此文件，如果需要，您可以使用在线工具，例如 https://hexed.it/。
    15.         在文件中搜索“SELECT DISTINCT datlastsysoid”，并将其替换为“SELECT DISTINCT dattablespace”。
    16.         将文件保存在原始位置。如果遇到任何安全问题，请将其另存为“.txt”文件，然后将其重命名为“.dll”。

    9. 常用的数据库命令

    1. [root@VMTest ~]# su postgres
    2. [postgres@VMTest root]$ psql  #进入postgresql命令行
    3.  
    4. postgres=# \q   # 退出 or: exit;
    5. postgres=# \l   # 列出所有库
    6. postgres=# \du  # 列出所有用户, 指令显示用户和用户的用户属性
    7. postgres=# \d   # 列出库下所有表
    8. postgres=# \di  # 查看索引
    9. postgres=# dt   # 列举表, 相当于mysql的show tables
    10. postgres=# \dn  # 查看模式schema列表
    11. postgres=# \dn+ # 查看模式schema列表详情
    12.  
    13. postgres=# \z 或 \dp  # 显示用户访问权限
    14. postgres=# \c dbname  # 切换数据库,相当于mysql的use dbname
    15. postgres=# \d tblname # 查看表结构, 相当于desc tblname,show columns from tbname
    16.  
    17. postgres=# DROP DATABASE phone; # 删除数据库
    18.  
    19. # 指令查看全部可设置的管理权限
    20. postgres=# \h CREATE ROLE 
    21.  
    22. # 显示所有可设置的访问权限 
    23. postgres=# \h GRANT 
    24.  
    25.  
    26. # 创建表(大小写均可)
    27. deepcogni=# create table test(id integer not null primary key);
    28.  
    29. # 添加表的字段
    30. deepcogni=# alter table phone add column phone_number character varying(11) not null;
    31.  
    32. # 重命名一个表
    33. deepcogni=# alter table [表名A] rename to [表名B]; 
    34.  
    35. # 删除一个表
    36. deepcogni=# drop table [表名];

    9.1 查看版本信息 

    1. # 1.查看客户端版本
    2. [root@BC-VM-d20d ~]# psql --version
    3.  
    4. # 2.查看服务器端版本
    5. # 2.1 查看详细信息
    6. postgres=# select version();
    7.  
    8. # 2.2 查看版本信息
    9. postgres=# show server_version;
    10.  
    11. # 2.3 查看数字版本信息包括小版号
    12. postgres=# SHOW server_version_num;
    13. # 或
    14. postgres=# SELECT current_setting('server_version_num');
    15.  
    16. # 3.注意事项
    17. postgres=# SELECT current_setting(‘server_version_num’);
    18.  
    19. # 返回类型为text，如果需要可以转换为interger:
    20. postgres=# SELECT current_setting('server_version_num')::integer;

    10. 用户和权限管理

    10.1 登录

    方式1: 指定参数登录.

    1. #参数含义: -U指定用户 -d要连接的数据库 -h要连接的主机 -W提示输入密码。
    2. psql -U username -d database_name -h host -W
    3.  
    4. #use -- 使用linux当前用户登录连接到'pgtest'数据库
    5. [postgres@VMTest root]$ psql -d pgtest
    6. psql (15.5)
    7. Type "help" for help.
    8.  
    9. pgtest=#

    方式2: 切换到postgres同名用户后登录

    1. # 当不指定参数时psql使用操作系统当前用户的用户名, 作为postgres的登录用户名和要连接的数据库名。
    2. # 所以在PostgreSQL安装完成后可以通过以下方式登录, 此时连接的是postgres数据库, 且登录用户也是postgres。
    3. [root@VMTest ~]# su postgres
    4. [postgres@VMTest root]$ psql

    10.2 创建用户

    方式1: 在系统命令行中使用createuser命令中创建

    createuser username

    方式2: 在PostgresSQL命令行中使用CREATE ROLE指令创建

    CREATE ROLE rolename;

    方式3: 在PostgresSQL命令行中使用CREATE USER指令创建

    CREATE USER username;

    CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE ROLE没有。

    \du 指令显示用户和用户的用户属性

    10.3 创建用户时设定用户属性

    基本语法格式

    CREATE ROLE role_name WITH optional_permissions;

    示例:在创建用户时设定登录权限。

    1. CREATE ROLE username WITH LOGIN;
    2. \h CREATE ROLE #指令查看全部可设置的管理权限

    10.4 修改用户属性

    修改权限的命令格式

    ALTER ROLE username WITH attribute_options;

    修改用户权限--示例

    1. # 赋予登录权限
    2. ALTER ROLE david LOGIN
    3.  
    4. # 禁止登录
    5. ALTER ROLE username WITH NOLOGIN
    6.  
    7. # 赋予创建角色的权限
    8. postgres=# ALTER ROLE pg_test_user_4 WITH CREATEROLE
    9.  
    10. # 修改密码
    11. postgres=# ALTER ROLE pg_test_user_4 WITH PASSWORD '654321';
    12.  
    13. # 设置角色的有效期
    14. postgres=# ALTER ROLE pg_test_user_4 VALID UNTIL 'JUL 7 14:00:00 2012 +8'; 
    15.  
    16. #其它 root: role name
    17. postgres=# ALTER ROLE root SUPERUSER;
    18. postgres=# ALTER ROLE root Superuser;
    19. postgres=# ALTER ROLE root Createrole;
    20. postgres=# ALTER ROLE root CreateDB;
    21. postgres=# ALTER ROLE root INHERIT;
    22. postgres=# ALTER ROLE root CONNECTION;
    23. deepcogni=# ALTER ROLE root BypassRLS;
    24.  
    25. postgres=# ALTER ROLE name RENAME TO newname;
    26.  
    27. ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }  
    28. ALTER ROLE name RESET configuration_parameter描述  
    29. ALTER ROLE 修改一个数据库角色的属性。

    ALTER ROLE语句简介：

    1. ALTER ROLE  
    2. 名称  
    3. ALTER ROLE -- 修改一个数据库角色  
    4. 语法  
    5. ALTER ROLE name [ [ WITH ] option [ ... ] ]  
    6.   
    7. 这里的 option 可以是：  
    8.       
    9.       SUPERUSER | NOSUPERUSER  
    10.     | CREATEDB | NOCREATEDB  
    11.     | CREATEROLE | NOCREATEROLE  
    12.     | CREATEUSER | NOCREATEUSER  
    13.     | INHERIT | NOINHERIT  
    14.     | LOGIN | NOLOGIN  
    15.     | CONNECTION LIMIT connlimit  
    16.     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'  
    17.     | VALID UNTIL 'timestamp'   
    18.   
    19. ALTER ROLE name RENAME TO newname  
    20.   
    21. ALTER ROLE name SET configuration_parameter { TO | = } { value | DEFAULT }  
    22. ALTER ROLE name RESET configuration_parameter描述  
    23. ALTER ROLE 修改一个数据库角色的属性。

    10.5 设置访问权限

    语法格式如下:

    GRANT permission_type ON table_name TO role_name;

    实例:

    1. #赋予demo_role demo表的update权限
    2. GRANT UPDATE ON table_name TO role_name; 
    3.  
    4. #赋予demo_role所有表的SELECT权限
    5. GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role;

    特殊符号:ALL代表所访问权限，PUBLIC代表所有用户

    1. # 赋给用户所有权限
    2. GRANT ALL ON table_name TO role_name; 
    3.  
    4. # 将SELECT权限赋给所有用户, 大写的PUBLIC是一个特殊的关键字，代表了all users
    5. GRANT SELECT ON table_name TO PUBLIC;
    1. \z或\dp  指令显示用户访问权限。
    2. \h GRANT 显示所有可设置的访问权限

    10.6 撤销用户访问权限

    语法格式如下:

    1. REVOKE permission_type ON table_name FROM user_name;
    2. # 其中 permission_type 和 table_name 含义与 GRANT 指令中相同。

    用户组

    在postgres中用户实际上是role，同时组也是role。 包含其他role的role就是组。

    创建组示例:

    1. CREATE ROLE temporary_users;
    2. GRANT temporary_users TO demo_role;
    3. GRANT temporary_users TO test_user;

    切换ROLE

    1. # 切换到role_name用户
    2. SET ROLE role_name;
    3.  
    4. #切换回最初的role 
    5. RESET ROLE;

    INHERIT权限：该属性使组成员拥有组的所有权限

    ALTER ROLE test_user INHERIT;

    10.7 删除用户和组

    1. DROP ROLE role_name;
    2. DROP ROLE IF EXISTS role_name;

    11 附录

    认证方式说明:

    1. 1. trust
    2. 无条件地允许连接。这种方法允许任何可以与PostgreSQL数据库服务器连接的用户以他们期望的
    3. 任意PostgreSQL数据库用户身份登入，而不需要口令或者其他任何认证。
    4.  
    5. 2. reject
    6.  
    7. 无条件地拒绝连接。这有助于从一个组中"过滤出"特定主机，例如一个reject行可以阻塞一个
    8. 特定的主机连接，而后面一行允许一个特定网络中的其余主机进行连接。
    9.  
    10. 3. md5
    11. 要求客户端提供一个双重 MD5 加密的口令进行认证。
    12.  
    13. 4. password
    14. 要求客户端提供一个未加密的口令进行认证。
    15. 因为口令是以明文形式在网络上发送的，所以我们不应该在不可信的网络上使用这种方式。
    16.  
    17. 5. gss
    18. 用 GSSAPI 认证用户。只对TCP/IP 连接可用。
    19.  
    20. 6. sspi
    21. 用 SSPI 来认证用户。只在Windows 上可用。
    22.  
    23. 7. ident
    24. 通过联系客户端的 ident 服务器获取客户端的操作系统名，并且检查它是否匹配被请求的数据库用户名。
    25. Ident 认证只能在 TCIP/IP 连接上使用。当为本地连接指定这种认证方式时，将用 peer 认证来替代。
    26.  
    27. 8. peer
    28. 从操作系统获得客户端的操作系统用户，并且检查它是否匹配被请求的数据库用户名。这只对本地连接可用。
    29.  
    30. 9. ldap
    31. 使用LDAP服务器认证。
    32.  
    33. 10. radius
    34. 用 RADIUS 服务器认证。
    35.  
    36. 11. cert
    37. 使用 SSL 客户端证书认证。
    38.  
    39. 12. pam
    40. 使用操作系统提供的可插入认证模块服务（PAM）认证。
    41.  
    42. 13. bsd
    43. 使用由操作系统提供的 BSD 认证服务进行认证。
  • 相关阅读:
    Oracle/PLSQL: Soundex Function
    每日面试1题-如何防止CDN防护被绕过
    第九章《搞懂算法:决策树是怎么回事》笔记
    快速构建Spring boot项目
    AQS之基础分析 (一)
    前端代码生成
    图库 | 图存储的基础概念
    腾讯网关TGW基础原理入门
    Postgresql systemctl设置开机自启动
    分享68个ASP.NET源码总有一个是你想要的
  • 原文地址：https://blog.csdn.net/justlpf/article/details/134515251