第七周作业-PostgreSQL

一、PostgreSQL与MySQL优劣势总结

PostgreSQL	MySQL
开源软件，使用免费，有其他开源软件适配使用	数据库引擎非开源
采用多进程，并发高时性能优异	采用多线程，低并发性能优异，并发高时无法充分利用CPU性能
有强大的查询优化器，支持复杂查询，速度快	对复杂查询处理较弱，查询速度较慢
提供如窗口函数、表分区等高级功能	功能较为简单，缺乏高级功能
支持大量主流开发语言和SQL语法标准	仅支持少量的SQL语法标准

二、PostgreSQL安装介绍

2.1,二进制安装

PostgreSQL: Downloadshttps://www.postgresql.org/download/        访问PostgreSQL官网查找服务器对应的数据库安装包，按照官方提供的命令运行即可完成PostgreSQL安装和启用（如下列）：

#sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
#sudo dnf -qy module disable postgresql
#sudo dnf install -y postgresql14-server
#sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
#sudo systemctl enable postgresql-14
#sudo systemctl start postgresql-14

2.2,源码编译安装

PostgreSQL: File Browserhttps://www.postgresql.org/ftp/source/        访问PostgreSQL官网查找服务器对应的源码安装包下载至本地

#安装PostgreSQL软件

#安装编译依赖包

[root@Rocky-test1 postgresql-14.0]# yum -y install gcc make readline-devel zlib-devel

[root@Rocky-test1 ~]# tar xvf postgresql-14.0.tar.gz

#创建PostgreSQL专用的用户账号

[root@Rocky-test1 ~]# useradd -s /bin/bash -m -d /home/postgres postgres

[root@Rocky-test1 ~]# echo -e 'Wlm@123\nWlm@123' | passwd postgres

#创建PostgreSQL软件存放位置

[root@Rocky-test1 ~]# mkdir /home/apps/

#编译安装PostgreSQL至指定位置

[root@Rocky-test1 postgresql-14.0]# ./configure --prefix=/home/apps/pgsql
[root@Rocky-test1 postgresql-14.0]# make -j 2 world
[root@Rocky-test1 postgresql-14.0]# make install-world

#安装数据库实例

#创建PostgreSQL的数据库数据存放位置
[root@Rocky-test1 postgresql-14.0]# mkdir -p /home/pgsql/data
[root@Rocky-test1 postgresql-14.0]# chown -R postgres.postgres /home/pgsql/data

#配置环境变量，方便使用

[root@Rocky-test1 postgresql-14.0]# vim /etc/profile.d/pgsql.sh

#!/bin/bash
export PGHOME=/home/apps/pgsql
export PATH=$PGHOME/bin/:$PATH
export PGDATA=/home/pgsql/data
export PGUSER=postgres
export MANPATH=/home/apps/pgsql/share/man:/$MANPATH
[root@Rocky-test1 postgresql-14.0]# . /etc/profile.d/pgsql.sh

#切换至专业账号初始化PostgreSQL数据库

[root@Rocky-test1 postgresql-14.0]# su - postgres

#初始化数据库，配置环境变量后可以不加“-D 路径”

[postgres@Rocky-test1 ~]$ initdb -D /home/pgsql/data

#启动PostgreSQL数据库

[postgres@Rocky-test1 ~]$ pg_ctl -l logfile start

#实现PostgreSQL开机自启动

#创建service文件

[postgres@Rocky-test1 ~]$exit

[root@Rocky-test1 ~]# vim /lib/systemd/system/postgresql.service

#/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSql database server
After=network.target

[Service]
User=postgres
Group=postgres

ExecStart=/home/apps/pgsql/bin/postmaster -D /home/pgsql/data
ExecReload=/bin/kill -HUP

[Install]
WantedBy=multi-user.target

#设置开机自启动

[root@Rocky-test1 ~]# systemctl enable --now postgresql.service

[root@Rocky-test1 ~]# su - postgres
[postgres@Rocky-test1 ~]$ psql
psql (14.0)
Type "help" for help.

postgres=#

三、PostgreSQL服务管理pg_ctl和psql命令总结

3.1,pg_ctl命令

pg_ctl    start                         #启动数据库服务

pg_ctl    stop                         #停止数据库服务

pg_ctl    status                      #查看数据库服务状态

pg_ctl    reload                      #重新加载数据库配置

pg_ctl    restart                      #重新启动数据库服务

3.2,psql命令

psql  -U              #指定连接的用户名

psql  -d              #指定连接的数据库名

psql  -l               #显示数据库名

psql  -h              #指定连接数据库服务器的hostname

psql  -p              #指定连接的端口

psql  -W             #指定密码

四、PostgreSQL数据库结构总结

实例（instance）	由PostgreSQL软件生成的数据文件目录
数据库（database）	一个PostgreSQL数据库服务管理多个数据库
模式（schema）	一个数据库可以创建多个模式空间用于分隔不同业务，默认安装public模式
对象（object）	包括视图(view)，索引(index)，表(table)，函数(function)等
对象（filed）	表内的行（row）和列（column）

五、PostgreSQL远程连接实现

#修改用户密码

[postgres@Rocky-test1 ~]$ psql
psql (14.0)
Type "help" for help.

postgres=# alter user postgres with password 'Wlm@123';
ALTER ROLE

#开启数据库远程配置

[postgres@Rocky-test1 ~]$ vim /home/pgsql/data/postgresql.conf

listen_addresses = '0.0.0.0'  

[postgres@Rocky-test1 ~]$ vim /home/pgsql/data/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             10.0.0.0/24             md5
[postgres@Rocky-test1 ~]$ pg_ctl reload
server signaled
 

#远程连接测试（远程主机需提前安装postgresql）

[root@Rocky-test2 ~]# psql -Upostgres -h 10.0.0.151 -p5432
Password for user postgres:
psql (14.2, server 14.0)
Type "help" for help.

六、 总结库，模式，表的添加、删除操作和信息查看语句

#help帮助的用法

postgres=# \h                                       #psql命令的查询
Available help:
  ABORT                            CREATE FOREIGN DATA WRAPPER      DROP ROUTINE
  ALTER AGGREGATE                  CREATE FOREIGN TABLE             DROP RULE
postgres=# \?                                       #sql命令的帮助
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab

#设置显示信息格式

postgres=# \x                                        #查询结果竖着显示，再次执行关闭命令
Expanded display is on.
postgres=# \timing on                           #开启命令执行时长提示
Timing is on.

#常用命令

postgres=# create database testdb;  
CREATE DATABASE

postgres=# select oid,datname from pg_database;                #查询数据库存放目录路径
  oid  |  datname
-------+-----------
 13025 | postgres
 16384 | testdb
     1 | template1
 13024 | template0
(4 rows)
postgres=# drop database testdb ;
DROP DATABASE

postgres=# \dn                                        #列出所有schema
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres

postgres=# \l                                             #列出所有数据库
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres=# \c                                #查看当前连接信息
You are now connected to database "postgres" as user "postgres".
postgres=# \c testdb                      #连接数据库
You are now connected to database "testdb" as user "postgres".
testdb=# \conninfo                        #查看详细连接信息
You are connected to database "testdb" as user "postgres" via socket in "/tmp" at port "5432".

testdb=# create table tb1 (id serial primary key, name char(10));   
CREATE TABLE
testdb=# insert into tb1 (name) values ('xiaoming');
INSERT 0 1
testdb=# select * from tb1;
 id |    name
----+------------
  1 | xiaoming

testdb=# \d                                #列出所有表，视图，序列
             List of relations
 Schema |    Name    |   Type   |  Owner
--------+------------+----------+----------
 public | tb1        | table    | postgres
 public | tb1_id_seq | sequence | postgres

testdb=# \d tb1                          #查看tb1表结构      
                               Table "public.tb1"
 Column |     Type      | Collation | Nullable |             Default
--------+---------------+-----------+----------+---------------------------------
 id     | integer       |           | not null | nextval('tb1_id_seq'::regclass)
 name   | character(10) |           |          |
Indexes:
    "tb1_pkey" PRIMARY KEY, btree (id)

testdb=# \dt                        #列出public的schema的所有表名（相当于show tables）
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | tb1  | table | postgres

testdb=# select pg_total_relation_size('tb1');                     #查看表大小
 pg_total_relation_size
------------------------
                  24576

#查询文件路径

testdb=# select oid,datname from pg_database where datname = 'testdb'; 
  oid  | datname
-------+---------
 16385 | testdb
(1 row)

Time: 0.714 ms
testdb=# select relid from pg_stat_all_tables where relname = 'tb1';
 relid
-------
 16403
(1 row)

Time: 3.930 ms

testdb=# insert into tb1 values (2,'xiaohng');
INSERT 0 1
testdb=# update tb1 set name='xiaohong' where id=2;
UPDATE 1
testdb=# select * from tb1;
 id |    name
----+------------
  1 | xiaoming
  2 | xiaohong

testdb=# delete from tb1 where id=2;
DELETE 1
testdb=# create index idx_name on tb1(name);
CREATE INDEX
testdb=# drop index idx_name;
DROP INDEX
testdb=# \db                                        #列出所有表空间
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |

#查看系统信息

testdb=# select version();                             #显示数据库版本
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit

testdb=# select pg_postmaster_start_time();                #显示数据库启动时间
   pg_postmaster_start_time
-------------------------------
 2024-05-23 10:41:03.514521+08

testdb=# show timezone;                                #显示时区
   TimeZone
---------------
 Asia/Shanghai

testdb=# select now();                                      #显示当前时间
              now
-------------------------------
 2024-05-24 13:33:21.985654+08

testdb=# select user;                                        #显示用户
   user
----------
 postgres

testdb=# show max_connections;                    #显示最大连接数
 max_connections
-----------------
100

七、PostgreSQL用户和角色总结

用户(user)：是连接数据库的实体，每个用户是唯一的，默认具有登录和管理数据库权限

角色(role)：是一组用户或其他角色的集合，默认不可以登录

#常用用户管理命令

testdb=# create user wang with password 'Wlm@123';
CREATE ROLE
testdb=# alter user wang with password '123@Wlm';
ALTER ROLE
#远程登录测试

[root@Rocky-test2 ~]# psql -Uwang -h 10.0.0.151 -d testdb
Password for user wang:
psql (14.2, server 14.0)
Type "help" for help.

testdb=>
testdb=# drop user wang;
DROP ROLE
#角色管理命令使用方法与用户相同，仅需用role取代user

create role li with password 'Li@123';

alter role li with login;

drop role li;

八、PostgreSQL实例：为数据库添加新库和新库管理员

#添加mage用户，magedu模式，准备zabbix库，配置mage用户的默认模式magedu，要求mage用户给zabbix库有所有权限

postgres=# create database zabbix;
CREATE DATABASE
postgres=# \c zabbix
You are now connected to database "zabbix" as user "postgres".
zabbix=# create schema magedu;
CREATE SCHEMA
zabbix=# create user mage with password 'mage@123';
CREATE ROLE
zabbix=# alter schema magedu owner to mage;
ALTER SCHEMA
zabbix=# grant all privileges on database zabbix to mage;
GRANT

[root@Rocky-test2 ~]# psql -Umage -h 10.0.0.151 -d zabbix
Password for user mage:
psql (14.2, server 14.0)
Type "help" for help.
zabbix=> \dn
  List of schemas
  Name  |  Owner
--------+----------
 magedu | mage
 public | postgres

九、PostgreSQL进程结构总结

十、PostgreSQL数据目录结构总结

[root@Rocky-test1 data]# pwd
/home/pgsql/data
[root@Rocky-test1 data]# ll
total 64
drwx------ 7 postgres postgres    67 May 24 15:29 base
drwx------ 2 postgres postgres  4096 May 27 08:51 global
drwx------ 2 postgres postgres     6 May 22 14:41 pg_commit_ts
drwx------ 2 postgres postgres     6 May 22 14:41 pg_dynshmem
-rw------- 1 postgres postgres  4821 May 23 10:31 pg_hba.conf
-rw------- 1 postgres postgres  1636 May 22 14:41 pg_ident.conf
drwx------ 4 postgres postgres    68 May 27 08:55 pg_logical
drwx------ 4 postgres postgres    36 May 22 14:41 pg_multixact
drwx------ 2 postgres postgres     6 May 22 14:41 pg_notify
drwx------ 2 postgres postgres     6 May 22 14:41 pg_replslot
drwx------ 2 postgres postgres     6 May 22 14:41 pg_serial
drwx------ 2 postgres postgres     6 May 22 14:41 pg_snapshots
drwx------ 2 postgres postgres     6 May 27 08:50 pg_stat
drwx------ 2 postgres postgres   105 May 27 10:13 pg_stat_tmp
drwx------ 2 postgres postgres    18 May 22 14:41 pg_subtrans
drwx------ 2 postgres postgres     6 May 22 14:41 pg_tblspc
drwx------ 2 postgres postgres     6 May 22 14:41 pg_twophase
-rw------- 1 postgres postgres     3 May 22 14:41 PG_VERSION
drwx------ 3 postgres postgres    60 May 22 14:41 pg_wal
drwx------ 2 postgres postgres    18 May 22 14:41 pg_xact
-rw------- 1 postgres postgres    88 May 22 14:41 postgresql.auto.conf
-rw------- 1 postgres postgres 28770 May 23 10:40 postgresql.conf
-rw------- 1 postgres postgres    54 May 27 08:50 postmaster.opts
-rw------- 1 postgres postgres    79 May 27 08:50 postmaster.pid

base目录	存储数据库中的所有表数据文件。每个数据库有一个对应的子目录
global目录	存储全局共享的系统数据文件，如系统表空间文件等
pg_commit_ts目录	存储提交时间戳文件，用于实现多版本并发控制
pg_dynshmem目录	存储动态共享内存文件，用于处理动态分配的共享内存
pg_hba.conf文件	存储PostgreSQL数据库的身份验证配置信息，用于控制用户访问权限
pg_ident.conf文件	存储PostgreSQL数据库的身份映射配置信息，用于将操作系统用户映射到数据库用户
pg_logical目录	存储逻辑复制相关的文件，用于实现逻辑复制功能
pg_multixact目录	存储多事务共享文件，用于实现并发控制
pg_notify目录	存储通知文件，用于实现异步通知功能
pg_replslot目录	存储复制插槽文件，用于实现流复制功能
pg_serial目录	存储序列化事务日志文件，用于实现流复制功能
pg_snapshots目录	存储快照文件，用于实现快照隔离级别功能
pg_stat目录	存储统计信息文件，包括表、索引等对象的统计信息
pg_stat_tmp目录	存储临时统计信息文件
pg_subtrans目录	存储子事务文件，用于实现并发控制
pg_tblspc目录	存储表空间链接文件
pg_twophase目录	存储两阶段提交事务文件
PG_VERSION文件	记录PostgreSQL数据库的版本号
pg_wal目录	存储WAL日志文件，用于实现数据库的持久性和恢复功能
pg_xact目录	提交日志commit log的目录
postgresql.auto.conf文件	与postgresql.auto.conf功能相同但是优先级更高，默认不配置该项
postgresql.conf文件	数据库实例的主配置文件，所有配置参数几乎都在此文件内
postmaster.opts文件	记录数据库启动时的命令行选项
postmaster.pid文件	数据库启动的主进程信息文件，包括PIG，PGDATA目录和主进程状态等