首页 > 其他分享 >第七周

第七周

时间：2024-03-04 13:12:09浏览次数：21  
标签：第七 数据库 pgsql 进程 pg db1 postgres

一、总结pg和mysql的优劣势。
特性 MySQL PostgreSQL
实例 通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。 通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。
数据库 数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。 数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。
数据缓冲区 通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。
数据库连接 客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。 客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。
身份验证 MySQL 在数据库级管理身份验证。 基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证
加密 可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。 可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。
审计 可以对 querylog 执行 grep。 可以在表上使用 PL/pgSQL 触发器来进行审计。
查询解释 使用 EXPLAIN 命令查看查询的解释计划。 使用 EXPLAIN 命令查看查询的解释计划。
备份、恢复和日志 InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。 在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。
JDBC 驱动程序 可以从 参考资料 下载 JDBC 驱动程序。 可以从 参考资料 下载 JDBC 驱动程序。
表类型 取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。 支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。
索引类型 取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。 支持 B-树、哈希、R-树和 Gist 索引。
约束 支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。 支持主键、外键、惟一、非空和检查约束。
存储过程和用户定义函数 支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。 没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。
触发器 支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。 支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。
系统配置文件 my.conf Postgresql.conf
数据库配置 my.conf Postgresql.conf
客户机连接文件 my.conf pg_hba.conf
XML 支持 有限的 XML 支持。 有限的 XML 支持。
数据访问和管理服务器 OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片 myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎） mysql —— 命令行工具 MySQL Administrator —— 客户机 GUI 工具 Vacuum —— 回收未使用的空间 Analyze —— 更新查询优化器所使用的统计数据 psql —— 命令行工具 pgAdmin —— 客户机 GUI 工具
并发控制 支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。 支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

MySQL相对于PostgreSQL的劣势：

MySQL PostgreSQL
最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。 BSD协议，没有被大公司垄断。
对复杂查询的处理较弱，查询优化器不够成熟 很强大的查询优化器，支持很复杂的查询处理。
只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。 都支持
性能优化工具与度量信息不足 提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。
InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。 不存在这个问题。
大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。 不存在这个问题
表增加列，基本上是重建表和索引，会花很长时间。 表增加列，只是在数据字典中增加表定义，不会重建表
存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱 除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。也支持用C语言写存储过程。
不支持Sequence。 支持
不支持函数索引，只能在创建基于具体列的索引。不支持物化视图。 支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。
执行计划并不是全局共享的, 仅仅在连接内部是共享的。 执行计划共享
MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。 都 支持
不支持用户自定义类型或域(domain) 支持。
对于时间、日期、间隔等时间类型没有秒以下级别的存储类型 可以精确到秒以下。
身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。 支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证
不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多 有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。
Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master. 有丰富的开源cluster软件支持。
explain看执行计划的结果简单。 explain返回丰富的信息。
类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复 DDL也是有事务的。

PostgreSQL主要优势：

　　1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了，是商业数据库，不开放。而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。
　　2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。

　　3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostgreSQL做二次开发的

　　4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。

二、总结pg二进制安装和编译安装。
# 二进制安装
dnf install -y 下载链接
 
# 禁用内置的postgresql
dnf -qy module disable postgresql
dnf install -y postgresql12-server
 
# 初始化数据库
/usr/pgsql-12/bin/postgresql-12-setup initdb
 
# 启动服务
systemctl enable --now postgresql-12
 
# 验证成功
sodu -u postgresql psql -c "SELECT version();"
su - postgres
 
# 源码安装
# 安装依赖包
rocky·：yum -y install gcc make readline-devel zlib-devel
 
Ubuntu: 
apt update
apt -y install gcc make readline-devel zlib-devel
 
https://www.postgresql.org/ftp/source/右键单击，复制链接
wget +链接
[root@localhost ~]# wget https://ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.gz
 
解压
[root@localhost ~]# tar xf postgresql-14.2.tar.gz
 
 cd postgresql-14.2
 [root@localhost postgresql-14.2]# cat INSTALL |less
 Short Version
 
    ./configure
    make
    su
    make install
    adduser postgres
    mkdir /usr/local/pgsql/data
    chown postgres /usr/local/pgsql/data
    su - postgres
    /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
    /usr/local/pgsql/bin/createdb test
    /usr/local/pgsql/bin/psql test
 
The long version is the rest of this document.
 
mkdir /apps
mkdir /apps/pgsql
 [root@localhost postgresql-14.2]# ./configure --prefix=/apps/pgsql
不包含文档和其他模块
[root@localhost postgresql-14.2]# make -j 2 world
不包括安装文档
[root@localhost postgresql-14.2]# make install-world
创建用户
useradd -s /bin/bash -m -d /home/postgres postgres
修改密码
echo -e '111111\n111111' |passwd postgres
 
创建目录
[root@localhost postgresql-14.2]# mkdir -pv /pgsql/data/
修改所有者
[root@localhost postgresql-14.2]# chown -R postgres. /pgsql/data/
[root@localhost postgresql-14.2]# ll -d /pgsql/data/
drwxr-xr-x. 2 postgres postgres 6 Mar  9 12:07 /pgsql/data/
 
# 配置环境变量
vim /etc/profile.d/pgsql.sh
export PGHOME=/apps/pgsql
export PATH=$PGHOME/bin/:$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MANPATH=/apps/pgsql/share/man:$MANPATH
让他生效：
[root@localhost postgresql-14.2]# . /etc/profile.d/pgsql.sh
 
切换用户
[root@localhost postgresql-14.2]# su - postgres
直接initdb也可以
[postgres@localhost ~]$ initdb -D /pgsql/data
 
# 启动
[postgres@localhost ~]$ pg_ctl -l logfile start
 
# 配置开机自启
[root@localhost ~]# vim /lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target
 
[Service]
User=postgres
Group=postgres
 
ExecStart=/apps/pgsql/bin/postmaster -D /pgsql/data
ExexReload=/bin/kill -HUP
 
[Install]
WantedBy=multi-user.target
 
[root@localhost ~]# systemctl daemon-reload
[root@localhost system]# systemctl enable --now  postgresql.service
三、总结pg服务管理相关命令 pg_ctl 和pgsql命令选项及示例和不同系统的初始化操作

pg_ctl命令管理

  • 初始化PostgreSQL数据库实例
  • 启动、终止和重启PostgreSQL数据库服务
  • 查看PostgreSQL数据库服务的状态
  • 让数据库实例重新读取配置文件，允许给一个指定的PostgreSQL进程发送信号
  • 控制standby服务器为可读写
  • 在window平台下允许为数据库实例注册或取消一个系统服务

psql常用命令选项
\h 查看sql语法
? 命令行操作的帮助
Tab键可以补全目录
\i sql文件 执行sql文件
\l 列出所有的数据库
\dn 列出所有schema
\db 显示所有表空间（pg中的表空间是一个目录，表放在表空间相当于将表的数据文件放到该目录之下）
\d 查看当前schema 中所有的表、视图、序列
\d+ 同上，但是多额外信息，下面几个命令都有带+版，能看到更详细的信息
\d schema.obj 查看对象结构（相当于desc）
\dt 只显示匹配的表
\di 只显示匹配的索引
\ds 只显示序列
\dv 只显示视图
\df 只显示函数
\sf 函数名 查看函数定义
\du 或 \dg 列出数据库中所有角色或用户（pg中是一样的）
\dp 或 \z 表名 显示表的权限分配情况
\x 横纵显示切换 （类似mysql \G）
\dT+ 显示扩展类型相关属性及描述
\q 退出pg命令行
\z 或 \dp 表名 显示表的权限分配情况
\timing 显示执行时间
\watch 5 每五秒循环执行sql语句
\c dbname 切换数据库
\conninfo 查看连接信息
\echo 字符串 输出一行信息
\encoding 字符集名 设置客户端字符集
\set [NAME [VALUE]] 设置内部变量，不加参数则列出内部变

四、总结pg数据库结构组织

实例：一个PostgreSQL对应一个安装的数据目录$PGDATA,即一个instance实例。
数据库：一个PostgreSQL数据库服务下可以管理多个数据库，当应用连接到一个数据库时，一般只能访问这个数据库中的数据，而不能访问其他数据库中的内容。
模式：一个数据库可以创建多个不同的名称空间即Schema，用于分隔不同的业务数据。
表和索引：一个数据库可以有多个表和索引。
行和列：每张表中有很多列和行数据。

五、实现pg远程连接。输入密码和无密码登陆
打开postgresql.conf配置文件，修改监听地址为所有地址
postgres@ubuntu200406:~$ vim $PGDATA/postgresql.conf
...
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
...
重启服务
postgres@ubuntu200406:~$ pg_ctl restart
监听端口的ip已经改过来了
postgres@ubuntu200406:~$ ss -ntl
State        Recv-Q       Send-Q             Local Address:Port               Peer Address:Port       Process       
LISTEN       0            4096               127.0.0.53%lo:53                      0.0.0.0:*                        
LISTEN       0            128                      0.0.0.0:22                      0.0.0.0:*                        
LISTEN       0            244                      0.0.0.0:5432                    0.0.0.0:*                        
 
打开pg_hba.conf配置文件，添加远程连接信息
postgres@ubuntu200406:~$ vim /pgsql/data/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
重启服务
postgres@ubuntu200406:~$ pg_ctl restart
 
先修改postgres用户密码
db1=# alter user postgres with password '123456';
ALTER ROLE
 
现在就可以坐在rocky这台机器上远程连接ubuntu机器上的db1数据库了，要输入密码
[15:30:41 postgres@rocky88 ~]$psql -h 192.168.31.179 db1 postgres
Password for user postgres:
psql (14.2)
Type "help" for help.
 
db1=#
 
 
要实现自动化连接，可以把用户名密码提前写在文件中
[15:49:30 postgres@rocky88 ~]$vim .pgpass
192.168.31.179:5432:db1:postgres:123456
修改文件权限
15:51:48 postgres@rocky88 ~]$chmod 600 .pgpass
[15:52:22 postgres@rocky88 ~]$ll .pgpass
-rw------- 1 postgres postgres 40 Nov 22 15:51 .pgpass
此时远程连接就不需要输入密码了
[15:52:43 postgres@rocky88 ~]$psql -h 192.168.31.179 db1 postgres
psql (14.2)
Type "help" for help.
 
db1=#
六、总结库，模式，表的添加和删除操作。表数据的CURD。同时总结相关信息查看语句。
数据库的添加和删除

创建数据库可以使用SQL语句create database 实现,也可以利用createdb 命令创建数据库

createdb 是一个 SQL命令 CREATE DATABASE 的封装

#远程连接创建db2数据库
[16:24:53 root@rocky88 ~]# createdb -h 192.168.31.179 -U postgres db2
#另一台机器查看一下db2有没有创建成功
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
db2       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
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
(5 rows)
#创建数据库db2
postgres=# create database db2;
ERROR:  database "db2" already exists
#删除数据库db2
postgres=# drop database db2;
DROP DATABASE
模式的添加和删除

一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其它对象，包括数据类型函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突; 比如，schema1和schema2都可以包含一个名为test的表

#创建模式
db1=# create schema n83_sch;
CREATE SCHEMA
#查看模式
db1=# \dn
  List of schemas
  Name   |  Owner   
---------+----------
n83_sch | postgres
public  | postgres
(2 rows)
#删除模式
db1=# drop schema n83_sch;
表的添加和删除
#创建表tb1
db1=# create table tb1 (id serial primary key,name text);
CREATE TABLE
#查看表
db1=# \dt
        List of relations
Schema | Name | Type  |  Owner   
--------+------+-------+----------
public | t1   | table | postgres
public | t2   | table | postgres
public | tb1  | table | postgres
(3 rows)
#删除表tb1
db1=# drop table tb1;
表数据的CURD，即insert，update，delete，select四条语句
#创建testdb库
db1=# create database testdb;
CREATE DATABASE
#使用testdb库
db1=# \c testdb
You are now connected to database "testdb" as user "postgres".
#创建tb1表
testdb=# create table tb1 (id serial,name varchar(10));
CREATE TABLE
#查看tb1表结构
testdb=# \d tb1
                                   Table "public.tb1"
Column |         Type          | Collation | Nullable |             Default             
--------+-----------------------+-----------+----------+---------------------------------
id     | integer               |           | not null | nextval('tb1_id_seq'::regclass)
name   | character varying(10) |           |     
#插入数据
testdb=# insert into tb1 (name)values('zhu');
INSERT 0 1
testdb=# insert into tb1 (name)values('xu');
INSERT 0 1
#查看数据
testdb=# select * from tb1;
id | name
----+------
  1 | zhu
  2 | xu
(2 rows)  
#修改数据
testdb=# update tb1 set name='li' where id=2;
UPDATE 1
#删除数据
testdb=# delete from tb1 where id=2;
DELETE 1
七、总结pg的用户和角色管理。
创建用户和角色

在PostgreSQL中，角色与用户是没有区别的

用户和角色可以用来实现以下功能：

用来登录数据库实例.

管理数据库对象

#创建用户
db1=# create user user1 with PASSWORD '123';
CREATE ROLE
#创建角色
db1=# create role user2 with PASSWORD '123';
CREATE ROLE
#查看用户角色
db1=# \du
                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1     |                                                            | {}
user2     | Cannot login                                               | {}
#创建wang用户及密码
db1=# create user wang with PASSWORD '123';
CREATE ROLE
#用wang用户账号远程连接db1数据库
[22:13:29 root@rocky88 ~]# psql -h 192.168.31.179 -d db1 -U wang
Password for user wang:
psql (14.2)
Type "help" for help.
db1=> \c
You are now connected to database "db1" as user "wang".
#创建zhu角色及密码
db1=# create role zhu with PASSWORD '123';
CREATE ROLE
db1=# \du
                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1     |                                                            | {}
user2     | Cannot login                                               | {}
wang      |                                                            | {}
zhu       | Cannot login                                               | {}
#用zhu角色远程连接db1数据库，发现不能连接（角色账号默认不能登录）
[22:17:21 root@rocky88 ~]# psql -h 192.168.31.179 -d db1 -U zhu
Password for user zhu:
psql: error: connection to server at "192.168.31.179", port 5432 failed: FATAL:  role "zhu" is not permitted to log in
#创建li角色，允许登录
db1=# create role li with login PASSWORD '123';
CREATE ROLE
[22:17:28 root@rocky88 ~]# psql -h 192.168.31.179 -d db1 -U li
Password for user li:
psql (14.2)
Type "help" for help.
db1=> \c
You are now connected to database "db1" as user "li".
#创建管理员
db1=# create role admin with superuser login password '123456';
CREATE ROLE
[22:25:26 root@rocky88 ~]# psql -h 192.168.31.179 -d db1 -U admin
Password for user admin:
psql (14.2)
Type "help" for help.
db1=# \c
You are now connected to database "db1" as user "admin".
#管理员有权限创建数据库
db1=# create database db2;
CREATE DATABASE
db1=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
db2       | admin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
hellodb   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
#修改管理员密码
db1=# alter user admin with password '123';
ALTER ROLE
#修改权限，让zhu用户角色可以登录
db1=# alter user zhu with login;
ALTER ROLE
#取消登录权限，让管理员不能登录
db1=# alter user admin with nologin;
ALTER ROLE
db1=# \du
                                   List of roles
Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
admin     | Superuser, Cannot login                                    | {}
li        |                                                            | {}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1     |                                                            | {}
user2     | Cannot login                                               | {}
wang      |                                                            | {}
zhu       |                                                            | {}
八、添加mage用户，magedu模式，准备zabbix库，配置mage用户的默认模式magedu，要求mage用户给zabbix库有所有权限。
添加mage用户
create user mage;
 
添加magedu模式
create schema magedu;
 
添加zabbix库
create database zabbix;
 
配置mage用户的默认模式magedu
alter schema magedu owner to mage;
 
要求mage用户给zabbix库有所有权限
alter database zabbix owner to mage;
九、总结pgsql的进程结构，说明进程间如何协同工作的。

Postgresql使用多进程架构实现（Mysql为多线程），PG启动时会拉起守护进程，然后由守护进程fork相关的后台辅助进程。守护进程的另一个作用是监听客户端的连接请求，当client发起连接时，守护进程会fork服务进程来处理client发送过来的命令，每有一个连接，后台就会存在一个服务进程

img

  • *Postmaster 主进程*

​ 。它是整个数据库实例的主控制进程，负责启动和关闭该数据库实例。

​ 。实际上,使用pg ctl来启动数据库时，pg_ctl也是通过运行postgres来启动数据库的，只是它做0了一些包装,更容易启动数据库。

​ 。它是第一个PostgreSQL进程，此主进程还会fork出其他子进程，并管理它们。

​ 。当用户和PostgresQL建立连接时，首先是和Postmaster进程建立连接。首先，客户端会发出身份验证的信息给Postmaster进程，Postmaster进程根据消息中的信息进行身份验证判断如果验证通过，它会fork出一个会话子进程为这个连接服务。

​ 。当某个服务进程出现错误的时候，Postmaster主进程会自动完成系统的恢复。恢复过程中会9停掉所有的服务进程,然后进行数据库数据的一致性恢复,等恢复完成后，数据库又可以接受新的连接。

​ 。验证功能是通过配置文件pg hba.conf和用户验证模块来提供。0

​ 。postmaster 程序是指向postgres的软链接

  • BgWriter 后台写进程

​ 。为了提高插入、删除和更新数据的性能，当往数据库中插入或者更新数据时，并不会马上把数据持久化到数据文件中,而是先写入Buffer中

​ 。该辅助进程可以周期性的把内存中的脏数据刷新到磁盘中

  • WalWriter 预写式日志进程

​ 。 WAL是write ahead log的缩写，WAL log日版中称为xlog，相当于MySQL中Redo log

​ 。预写式日志是在修改数据之前，必须把这些修改操作记录到磁盘中，这样后面更新实际数据时，就不需要实时的把数据持久化到文件中了。即使机器突然宕机或者数据库异常退出，导致一部分内存中的脏数据没有及时的刷新到文件 中，在数据库重启后，通过读取WAL日志并把最后一部分WAL日志重新执行一遍，就能恢复到宕机时的状态了

​ 。WAL日志保存在pg_wal目录(早期版本为pg xlog)下。每个xlog 文件默认是16MB,为了满足恢0复要求，在pg_wal目录下会产生多个WAL日志，这样就可保证在宕机后，未持久化的数据都可以通过WAL日志来恢复，那些不需要的WAL日志将会被自动覆盖

  • Checkpointer 检查点进程

​ 。检查点(Checkpoints)是事务序列中的点，保证在该点之前的所有日志信息都更新到数据文件中。

​ 。在检查点时，所有脏数据页都冲刷到磁盘并且向日志文件中写入一条特殊的检查点记录。在发生崩溃的时候，恢复器就知道应该从日志中的哪个点 (称做 redo 记录) 开始做 REDO 操作，因为在该记录前的对数据文件的任何修改都已经在磁盘上了。在完成检查点处理之后，任何在redo记录之前写的日志段都不再需要，因此可以循环使用或者删除。在进行 WAL 归档的时候，这些日志在循环利用或者删除之前应该必须先归档保存

​ 。检查点进程(CKPT)在特定时间自动执行一个检查点,通过向数据库写入进程(BgWriter) 传递消1息来启动检查点请求

  • AutoVacuum 自动清理进程

​ 。执行delete操作时，旧的数据并不会立即被删除，在更新数据时，也不会在旧的数据上做更新，而是新生成一行数据。旧的数据只是被标识为删除状态，在没有并发的其他事务读到这些日数据时，它们才会被清除掉

​ 。autovacuum lanucher 负责回收垃圾数据的master进程如果开启了autovacuum的话，那么.postmaster会fork这个讲程

​ 。autovacuum worker 负责回收垃圾数据的worker进程,是lanucher进程fork出来的·

  • Pgstat 统计数据收集进程

​ 。此进程主要做数据的统计收集工作

​ 。收集的信息主要用于查询优化时的代价估算。统计的数据包括对一个表或索引进行的插入、删.除、更新操作，磁盘块读写的次数以及行的读次数等。

​ 。系统表pg statistic中存储了PgStat收集的各类统计信息

  • *PgArch 归档进程*

​ 。默认没有此进程,开启归档功能后才会启动archiver进程

​ 。WAL日志文件会被循环使用，也就是说WAL日志会被覆盖,利用PgArch进程会在覆盖前把WAL日志备份出来,类似于binlog,可用于备份功能

​ 。PostgreSQL从8.X版本开始提供了PITR( Point-In-Time-Recovery)技术，即就是在对数据库0进行过一次全量备份后，该技术将备份时间点后面的WAL日志通过归档进行备份，将来可以使用数据库的全量备份再加上后面产生的 WAL 日志，即可把数据库向前恢复到全量备份后的任意一个时间点的状态

  • SysLogger 系统日志进程

​ 。默认没有此进程,配置文件 postgresgl.conf 设置参数logging collect设置为"on"时， 主进程才会启动SysLogger辅助进程

​ 。它从Postmaster主进程、所有的服务进程以及其他辅助进程收集所有的stderr输出，并将这0些输出写入到日志文件中

  • startup 启动进程

​ 。用于数据库恢复的进程

  • Session 会话进程

​ 。每一个用户发起连接后，一旦验证成功,postmaster进程就会fork一个新的子进程负责连接此用户。

​ 。通常表现为进程形式: postgres postgres [local] idle0

十、总结pgsql的数据目录中结构，说明每个文件的作用，并可以配上一些示例说明文件的作用

img

数据库数据存放在环境变量PGDATA指向数据目录。这个目录是在安装时指定的，所以在安装时需要指定一个合适的目录作为数据目录的根目录，而且，每一个数据库实例都要有一个对应的目录。目录的初始化是使用initdb来完成的。

初始化完成后，PGDATA数据目录下就会生成三个配置文件postgresgl.conf、pg_hba.conf、pg_ident.conf

postgresql.conf #数据库实例的主配置文件，基本上所有的配置参数都在此文件中。*pg_hba.conf* #认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息

*pg_ident.conf* #认证方式ident的用户映射文件。

此外在PGDATA目录下还会生成如下一些子目录：

*base* #默认表空间的目录,每个数据库都对应一个base目录下的子目录,每个表和索引对应一个独立文件

[root@ubuntu200406 ~]#*ls $PGDATA/base*

1 12973 12974 16384 16418 16450 16497 16498 16499

*global* #这个目录对应pg_globa1表空间，存放实例中的共享对象

[root@ubuntu200406 ~]#*ls $PGDATA/global*

1213 1214_vm 1260_vm 1262_fsm 2397 2694 2847 3592 4176 4183 6001 pg_control

1213_fsm 1232 1261 1262_vm 2671 2695 2964 3593 4177 4184 6002 pg_filenode.map

1213_vm 1233 1261_fsm 2396 2672 2697 2965 4060 4178 4185 6100 pg_internal.init

1214 1260 1261_vm 2396_fsm 2676 2698 2966 4061 4181 4186 6114

1214_fsm 1260_fsm 1262 2396_vm 2677 2846 2967 4175 4182 6000 6115

*pg_clog* #存储事务提交状态数据

*pg_bba.conf* #数据库访问控制文件

*pg_log* #数据库系统日志目录，在查询一些系统错误时就可查看此目录下日志文件。(根据配置定义,可能没有这个目录)

*pg_xact* #提交日志commit log的目录,pg 9之前叫pg_clog

*pg_multixact* #共享行锁的事务状态数据

*pg_notify* #异步消息相关的状态数据pg_seria] #串行隔离级别的事务状态数据 *pg_snapshots* #存储执行了事务snapshot导出的状态数据pg_stat_tmp #统计信息的临时文件

*pg_subtrans* #子事务状态数据

*pg_stat* #统计信息的存储目录。关闭服务时,将pg_stat_tmp目录中的内容移动至此目录实现保存

pg_stat_tmp #统计信息的临时存储目录。开启数据库时存放统计信息

*pg._tblsp* #存储了指向各个用户自建表空间实际目录的链接文件pg_twophase#使用两阶段提交功能时分布式事务的存储目录

*pg_wal* #WAL日志的目录，早期版一本目录为pg_xlog

*PG_VERSION* #数据库版本

*postmaster.opts* #记录数据库启动时的命令行选项

*postmaster.pid* #数据库启动的主进程信息文件，包括PID，SPGDATA目录,数据库启动时间，监听端口,socket文件路径，临听地址,共享内存的地址信息(ipsc可查看),主进程状态

标签：第七,数据库,pgsql,进程,pg,db1,postgres
From： https://www.cnblogs.com/meishijia8/p/18051583

相关文章

  • 《程序是怎样跑起来的》第七章观后感
    在《程序是怎样跑起来的》的第七章中，我们进入了计算机科学的一个至关重要的领域——操作系统的核心概念与实现。这一章节的内容对于理解整个计算机系统有着举足轻重的作用，它揭示了计算机系统运作的基石。首先，本章深入探讨了操作系统的基本功能，包括进程管理、内存管理、文件系统以......
  • C++第七节课 new开辟空间 delete释放空间
    #include<iostream>usingnamespacestd;//C中开辟空间的方式所有的返回值都是void*///int*p=(int*)malloc(sizeof(int))///malloc在堆上开辟空间并没有进行初始化//////int*pa=(int*)calloc(1,sizeof(int));///calloc在堆上开辟空间是有初始化的......
  • 《程序是怎么跑起来的》第七章
    《程序是怎么跑起来的》这本书的第七章主要探讨了计算机程序的运行环境，以及程序从源代码到最终执行的过程。这一章重点介绍了函数在计算机程序中的作用和实现方式，包括函数的定义、调用、返回值，以及参数传递的方式，如按值传递和按引用传递。此外，还讲解了局部变量和全局变量的概念，以......
  • 程序是怎样跑起来的第七章读书笔记
    第七章围绕程序是在和的环境中运行的来展开介绍7.1告诉我们运行环境等于操作系统加组件，操作系统和硬件是运行环境的重要组成部分不同的操作系统和硬件组合可能会导致，软件在不同的环境中表现出不同的行为。Windows操作系统在一定程度上解决了CPU以外的硬件差异问题使得软件在不同......
  • 阅读《程序是怎样运行的》第七章
    这本书的第七章呢主要讲程序是在何种环境中运行的。运行环境=操作系统+硬件。程序中包含着运行环境这一内容。大家手头若是有购买的应用软件的话，可以稍微观察一下它的安装包或者目录。通常在某个位置会写有“运行环境”这一项。例如，2007MicrosoftOfficeSystem(下文简称为Office2......
  • 《系统科学方法概论》第七章
    当我深入到《系统科学方法概论》的第七章时，复杂系统成为了主要的讨论对象。这一章不仅为我提供了对复杂系统的全新认识，也让我理解了系统科学在面对这类系统时的重要性和应用价值。首先，复杂系统是一个由多个相互关联、相互作用的元素组成的系统。这些元素之间可能存在直接的或间接......
  • 程序是怎样跑起来的 第七章
    第七章：第七章讲的是程序的运行环境，程序都是有相对应的运行环境来支撑的，运行环境是操作系统和硬件的综合。CPU只能解释其固有的机器语言，不同种类的Cup机器语言也不一样，在编写完源代码进行编译后得到本地代码，再由CPU来解析和运行本地代码。在使用Windows应用的功能时，是间接向windows......
  • 程序是怎样跑起来的第七章读后感
    阅读本章我了解了《程序是怎样跑起来的》第七章主要讲述了程序的运行环境，包括运行环境程序、操作系统、源代码等。程序的运行环境是操作系统和计算机硬件的组合，操作系统和硬件共同决定了应用的运行环境。而源代码和本地代码都各有各的好处。好的程序能被大家喜爱，说明其具有很大的......
  • 第七章
    程序是在何种环境中运行的1.CPU只能解释其自身固有的机器语言。不同的CPU能解释的机器语言的种类也是不同的。它们各自的机器语言是完全不同的。机器语言的程序称为本地代码(nativecode)。程序员用C语言等编写的程序，在编写阶段仅仅是文本文件。文本文件(排除文字编码的问题)......
  • 读后感_第七章
    第七章主要介绍了程序的运行环境。通过阅读这一章，我对程序的运行环境有了更深入的了解。在这一章中，我了解了操作系统的基本概念和功能，包括进程管理、内存管理、文件系统等。我学习了操作系统如何管理计算机资源，以及如何为程序提供运行环境。这些知识让我能够更好地理解和使用操作......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99