【PostgreSQL】入门学习笔记

前言：

以下内容为前几天在备考PostgreSQL入门考试时候做的笔记，经过了全职的两天的奋战与实验，并最终顺利通过了PCA初级认证考试。现在把我学习的笔记分享给大家，文中有对应的思维导图图片可供查看，内容与后面正文文本一致。另外，由于SQL语句部分比较基础，基本上会一门数据库就都会，所以此处部分省略掉不做过多记录了。

以下，enjoy：

# PostgreSQL

## PostgreSQL的发展历程

### 始于1986年的Postgres项目

### 1994年，新增了SQL语言解释器，Postgres95诞生

### 1996年，更名 PostgreSQL,版本号从6.0开始

## PostgreSQL的安装

### yum安装

– https://www.postgresql.org/download/linux/redhat/

– yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

– yum install -y postgresql14-server

– /usr/pgsql-14/bin/postgresql-14-setup initdb

– systemctl enable postgresql-14

– systemctl start postgresql-14

– 其他操作

– 关闭selinux

– setenforce 0

– sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/g’ /etc/selinux/config

– 关闭防火墙

– systemctl status firewalld.service

– systemctl stop firewalld.service

– systemctl disable firewalld.service

– 修改环境变量 /etc/profile

– export PATH=/usr/pgsql-14/bin/:$PATH

– source /etc/profile

### rpm安装

– https://yum.postgresql.org/rpmchart/

– postgresql14

– postgresql14-contrib

– postgresql14-libs

– postgresql14-server

– rpm -ivh postgresql14*

### 源码的安装

– wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz

tar xf postgresql-13.3.tar.gz

– 重要的依赖：readline,flex,bison

– yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc openssl-devel

– ./configure && make && make install | install-world

– 编译

– cd postgresql-xx/

– ./configure –prefix=/XXX/postgresql –with-openssl

– gmake world && gmake install-world

– 授权

– chown -R postgres. /XXX/postgresql

– 环境变量 /etc/profile

– export PATH=/XXX/postgresql/bin:$PATH

export PGDATA=/XXX/postgresql/data

– source /etc/profile

– 初始化

– su – postgres

initdb -D $PGDATA

– 启动

– pg_ctl -D $PGDATA start

– 使用系统管理

– 修改: /usr/lib/systemd/system/postgresql-xx.service

– [Unit]

Description=PostgreSQL database server

After=network.target

[Service]

Type=forking

User=postgres

Group=postgres

Environment=PGPORT=5432

Environment=PGDATA=/XXX/postgresql/data/

OOMScoreAdjust=-1000

ExecStart=/XXX/postgresql/bin/pg_ctl start -D ${PGDATA} -s -o “-p ${PGPORT}” -w -t 300

ExecStop=/XXX/postgresql/bin/pg_ctl stop -D ${PGDATA} -s -m fast

ExecReload=/XXX/postgresql/bin/pg_ctl reload -D ${PGDATA} -s

TimeoutSec=300

[Install]

WantedBy=multi-user.target

– 重新加载：systemctl daemon-reload

– 使用systemctl启动

– systemctl start postgresql-xx.service

systemctl enable postgresql-xx.service

### 二进制的安装

– EDB

## PostgreSQL的安装(其他)

### 初始化命令

– 运行initdb创建数据库实例：

– $ su – postgres

– $ initdb -D

– 指定数据目录。没有指定，将使用环境变量PGDATA

– $ initdb -U

– 指定数据库超级用户名字

– -E

– 指定数据库字符编码

– -n

– 错误后不清理文件

– -W

– 初始化时给数据库设置密码

– -x

– 预写日志目录位置

– –wal-segsize=XXX

– 指定 WAL 段大小(单位 M)，默认是 16M，最大 1G

### 启动和关闭

– system管理方式

– systemctl start postgresql-xx

systemctl enable postgresql-xx

systemctl status postgresql-xx

systemctl stop postgresql-xx

– postgresql自带的命令

– pg_ctl start

pg_ctl stop

pg_ctl status

– pg_ctl -D $PGDATA stop -m smart

pg_ctl -D $PGDATA stop -m fast

pg_ctl -D $PGDATA stop -m immediate

– smart 等待客户端断开连接(执行之后会特别慢)

– fast 回滚未完成的事务，断开客户端连接(推荐用法)

– immediate 强行终止进程，数据库没有干净的关闭

– 启动\停止有关日志

– vim /var/lib/pgsql/xx/data/log/postgresql-Mon.log

### 注意事项

– 关闭防火墙

– systemctl status firewalld.service

– systemctl stop firewalld.service

– systemctl disable firewalld.service

– ntp时间同步

– 服务端配置

– yum -y install ntp

– 修改配置文件 /etc/ntp.conf

– 给本机权限:

restrict 127.0.0.1

restrict ::1

– 例如授权10.0.0.0网段上所有的机器允许从ntp服务器上查询和同步时间:

restrict 10.0.0.0 mask 255.255.255.0 nomodify notrap

– 增加时间服务器列表：

0.asia.pool.ntp.org

0.cn.pool.ntp.org

time.nist.gov

server 0.asia.pool.ntp.org iburst

server 1.asia.pool.ntp.org iburst

server 2.asia.pool.ntp.org iburst

server 3.asia.pool.ntp.org iburst

– 当外部时间不可用时，使用本地时间:

server 127.127.1.0 iburst

fudge 127.127.1.0 stratum 10

– 设置开机自启动

– systemctl enable ntpd

systemctl start ntpd

systemctl enable ntpdate

systemctl start ntpdate

– 查看ntp情况

– ntpq -p

– remote: NTP主机的IP或主机名称；

最左边是 + 表示目前正在起作用的上层NTP；如果是 * 表示这个也连接上了，不过是作为次要联机的NTP主机

– refid: 参考上一层NTP主机的地址

– st: stratum阶层

– t: 连接类型

– u：单播(unicast)

– l：本地(local)

– m: 多播(multicast)

– b: 广播(broadcast)

– when: 这个时间之前刚刚做过时间同步

– poll: 在几秒之后进行下次更新

– reach: 已经向上层NTP服务器要求更新的次数

– delay: 网络传输过程中的延迟时间

– offset: 时间补偿的结果

– jitter: Linux系统时间和Bios硬件时间的差异时间

– 与硬件时间进行同步

– hwclock -w

– 测试

– ntpstat

– 客户端配置

– yum -y install ntp ntpdate

– 方法一

– 重启服务以使配置生效,之后大概要等10分钟左右,才会同步成功

– echo “server ip” >/etc/ntp.conf

systemctl enable ntpd

systemctl restart ntpd

hwclock -w

– 方法二

– systemctl enable ntpdate

/usr/sbin/ntpdate -u ip

hwclock -w

crontab -e

10 23 * * * (/usr/sbin/ntpdate -u ip && /sbin/hwclock -w)&>/var/log/ntpdate.log

– 如果配置主从，尽量保持uid和gid一致

– 检查uid和gid

– id postgres

– 通过usermod,groupmod修改OS用户uid和gid

– groupmod -g 1000 postgres

usermod -u 1000 -g 1000 postgres

– 关闭selinux

– setenforce 0

– sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/g’ /etc/selinux/config

– psql参数的使用

– 参数说明

– -U

– 以哪个用户登录

– -W

– 强制输入密码

– -h

– 主机名/IP地址

– -p

– 端口号

– -d

– 登录哪个数据库

– 查看版本信息

– psql –version

select version();

## PostgreSQL的配置

### 参数的修改

– 文件位置：$ PGDATA/postgresql.conf

– include_if_exists = ‘xxx.conf’

– 开启参数，并存在可使用的 $PGDATA/xxx.conf 文件才会进行加载

– include_dir = ‘conf.d’

– 加载目录下的参数文件,$PGDATA/.conf.d/xxx.conf

– include = ‘xxx.conf’

– 无论参数文件是否存在都会加载 $PGDATA/xxx.conf

– 数据库启动时，会读取该文件，可手动修改

– postgresql.auto.conf

– alter system修改后的参数配置，会覆盖postgresql.conf的值

– recovery.conf

– pg12已经不存在这个文件，已经将此文件的参数合并到了postgresql.conf

– pg_hba.conf 客户端认证配置文件(PG防火墙)

– 第一列 TYPE(连接方式)

– local

– 使用Unix域套接字的连接，如果没有TYPE为local的条目则不允许通过Unix域套接字连接

– host

– 匹配使用TCP/IP建立的连接，同时匹配SSL和非SSL连接

– hostssl

– 匹配必须是使用SSL的TCP/IP进行连接。

– 配置hostssl的3个条件

– 户端和服务端都安装openssl

– 编译时要指定 –with-openssl 打开ssl支持

– 在postgresql.conf中配置ssl = on

– hostnossl

– 只匹配使用非SSL的TCP/IP连接

– 第二列 DATABASE(目标数据库)

– 标识该行设置对哪个数据库生效

– 第三列 USER (目标用户)

– 标识该行设置对哪个数据库用户生效

– 第四列 ADDRESS (访问来源)

– 标识该行设置对哪个IP地址或IP地址段生效

– 第五列 METHOD (认证方式)

– reject

– 无条件拒绝连接

– md5 或 password

– 双重md5加密和明文加密

– scram-sha-256

– postgresql10中新增最安全的加密方式

– 查看没有用SCRAM加密口令的用户

– create user foo password ‘foopassword’;

– select usename,passwd from pg_shadow where passwd not like ‘SCRAM%’ or passwd is null;

– select usename,passwd from pg_shadow ;

– trust

– 无条件的允许连接

– cert

– 使用SSL客户端证书认证

– peer

– 本地操作系统的当前用户名和数据库的用户名一致时，可以直接使用此用户名登录而不需要密码

– ident

– 用户映射文件

– 查看当前的加密方法

– postgres=# show password_encryption;

– 修改加密方法

– postgres=# alter system set password_encryption = ‘scram-sha-256’;

postgres=# show password_encryption;

postgres=# select pg_reload_conf();

– 修改密码

– postgres=# alter user foo password ”Xzzp2008 ‘;

– 远程登录

– psql -h 192.168.1.221 -p 5433 -d postgres -U foo -W

– 加强口令复杂度管理插件

– passwordcheck

– http://www.postgresql.org/docs/current/static/passwordcheck.html

– pg_ident.conf 客户端认证映射文件

### 数据库相关命令

– 查看参数

– 查询pg_settings系统表

– SELECT name,setting FROM pg_settings where name ~ ‘xxx’;

– select name,setting,unit,short_desc from pg_settings where name like ‘work_mem%’;

– SELECT current_setting(name);

– SELECT current_setting(‘work_mem’);

– 通过show 命令查看

– show all

– 参数生效几种方式

– SELECT pg_reload_conf();

– pg_ctl -D $PGDATA reload;

– /etc/init.d/postgresql-11.x reload; (el6)

– systemctl reload service.postgresql-11.x (el7)

– 子主题 3

### 数据库管理

– 客户端工具

– pgAdmin

– https://www.pgadmin.org

– yum / apt install pgadmin

– psql

– 连接数据库

– psql -h localhost -p 5432 database_name

– 获得psql的帮助

– \?

– 获得语法的帮助

– \h STATEMENT

– 在shell中执行命令

– psql -c “STATEMENT”

– 通过psql执行sql文件

– psql < f.sql

– 其他

– \l 查看有哪些数据库

– \c 用于切换数据库

– \d 显示每个匹配关系(表，视图，索引，序列)的信息

– \d 后面跟一个表名，表示显示表结构定义

– \d 后跟一个索引名，显示索引的信息

– \d 后面跟一个视图名，显示视图信息

– \timing on 显示SQL执行的时间

– \timing off 关闭计时功能

– \dn 列出所有的schema

– \db 显示所有的表空间

– \du\dg 列出所有的角色或者用户

– \dp 显示权限分配情况

– \x 行列互换显示

– \set AUTOCOMMIT off 将自动提交功能关闭

## 体系结构

### 内存结构

– shared_buffers

– 共享内存

– work_mem

– 当使用order by或distinct操作对元组仅从排序时会使用这部分内存

– wal_buffer

– wal缓存

### 进程结构

– 查看进程

– –ps –ef |grep post

– background writer

– 进程将shared buffer pool中的脏数据写到磁盘，检查点总能触发这个进程

– checkpointer

– 检查点会触发产生这个进程

– autovacuum launcher

– autovacuum的守护进程，为vacuum process周期性的调用autovacuum work processes

– autovacuum的作用

– 删除或重用无效元组的磁盘空间

– 更新数据统计信息，保证执行计划更优

– 更新visibility map，加速index-only scans

– 避免XID回卷造成的数据丢失

– WAL writer

– 周期性的从wal buffer刷新数据到磁盘

– statistics collector

– 收集统计信息进程，比如pg_stat_activity 和pg_stat_database的数据。(表和索引进行了多少次插入，更新，删除操作，磁盘块读写次数及行的读写次数)

– logging collector (logger)

– 将错误信息写入到日志

– archiver

– 将日志归档的进程

– postgremaster

– 监听

### 数据库集群概念

– 一个数据库集簇(database cluster)=一个数据库实例(简称“实例”)

– 每个数据库实例由数据库目录组成，目录中包含了所有的数据文件和配置文件

– 不同的实例可以通过两种方式引用

– 数据目录的位置

– 端口号

– 一个服务器可以管理多个数据库实例

### 物理结构

– 数据目录

– base

– 表和索引文件存放目录

– global

– 影响全局的系统表存放目录

– pg_commit_ts

– 事务提交时间戳数据存放目录

– pg_stat

– 统计子系统信息永久文件

– pg_wal

– 事务日志(预写日志)

– 相关文件

– PG_VERSION

– 版本号文件

– pg_hba.conf

– 客户端认证控制文件

– postgresql.conf

– 参数文件

– postgresql.auto.conf

– 参数文件，只保存ALTER SYSTEM命令修改的参数

– postmaster.opts

– 记录服务器最后一次启动时使用的命令行参数

– pg_ident.conf

– 控制postgresql用户名映射文件

– postmaster.pid

– 记录数据库进程编号、PGDATA、端口等

## 数据库故障排查

### 查看操作系统错误日志

– /var/log/message 系统启动后的信息和错误日志，是Red Hat Linux中最常用的日志之一

### 查看数据库错误日志

– 查看文件$PGDATA/log

默认情况下，一天产生一个日志

## SQL入门

### DDL

– 数据定义语言，用来定义库和表

### DML

– 数据操作语言

### DCL

– 数据控制语言

### DQL

– 数据查询语言

### 数据类型

– 字符类型

– 数字类型

– 日期/时间类型

– 范围类型

– 布尔类型

– 相关约束

### 其他

## 数据库备份相关

### 物理备份

– 热备

– 全量 pg_basebackup

– 备份命令：

pg_basebackup -h ip -D /XXX/backup -P -p 5432 -U postgres

– 恢复命令:

cd /xxx/backup/

tar xf base.tar.gz -C ../data

– 结合时间点进行恢复

– 创建归档目录

– mkdir -p /XXX/archive

– chown -R postgres. /XXX/

– 开启归档，并重启数据库

– wal_level = ‘replica’

– wal_level参数说明

– minimal

– 记录wal最少，记录数据库异常关闭需要恢复的wal外，其它操作都不记录

– replica

– 在minimal的基础上还支持wal归档、复制和备库中启用只读查询等操作所需的wal信息

– logical

– 记录wal日志信息最多，包含了支持逻辑解析(10版本的新特性，逻辑复制使用这种模式)所需的wal，此参数包含了minimal和replica所有的记录

– archive_mode = ‘on’

– archive_command = ‘cp %p /XXX/archive/%f’

– 重启数据库

– pg_ctl restart

– 执行全量备份

– pg_basebackup -D /xxx/backup -P -p 5432 -U postgres

– 如果此时的数据有变化，也可以执行增量备份：

pg_receivewal -D /xxx/backup/ -p 5432

– 启动备份实例，进行数据恢复

– vim postgresql.conf

recovery_target_time = ‘2022-04-17 16:00:00.00000+08’

restore_command=’cp /xxx/archive/%f %p’

recovery_target_action = ‘promote’

– 创建备库标签

– touch recovery.signal

– 启动数据库

– pg_ctl -D /xxx/backup start

– 关闭读模式

– select pg_wal_replay_resume();

– 结合还原点进行恢复

– 创建归档目录

– mkdir -p /xxx/archive

chown -R postgres. /xxx/

– 开启归档，并重启数据库

– wal_level = ‘replica’

archive_mode = ‘on’

archive_command = ‘cp %p /xxx/archive/%f’

– 重启数据库

– pg_ctl restarat

– 执行全量备份

– pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres

– 如果有变化的数据，可以执行增量备份

– pg_receivewal -D /xxx/backup/ -p 5432

– 启动备份实例，进行数据恢复

– vim postgresql.conf

recovery_target_name = ‘huanyuan’

restore_command=’cp /xxx/archive/%f %p’

recovery_target_action = ‘promote’

– touch recovery.signal

– 启动数据库

– pg_ctl -D /xxx/backup start

– 关闭读模式

– select pg_wal_replay_resume();

– 结合事务进行恢复

– 创建归档目录

– mkdir -p /xxx/archive

chown -R postgres. /xxx/

– 开启归档，并重启数据库

– wal_level = ‘replica’

archive_mode = ‘on’

archive_command = ‘cp %p /xxx/archive/%f’

– 重启数据库

– pg_ctl restarat

– 执行全量备份

– pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres

– 如果有变化的数据，可以执行增量备份

– pg_receivewal -D /xxx/backup/ -p 5432

– 启动备份实例，进行数据恢复

– vim postgresql.conf

recovery_target_xid = ‘487’

restore_command=’cp /xxx/archive/%f %p’

recovery_target_action = ‘promote’

touch recovery.signal

– 启动数据库

– pg_ctl -D /xxx/backup start

– 关闭读模式

– select pg_wal_replay_resume();

– 结合LSN号码进行恢复

– 创建归档目录

– mkdir -p /xxx/archive

chown -R postgres. /xxx/

– 开启归档，并重启数据库

– wal_level = ‘replica’

archive_mode = ‘on’

archive_command = ‘cp %p /xxx/archive/%f’

– 重启数据库

– pg_ctl restarat

– 执行全量备份

– pg_basebackup -h ip -D /xxx/backup -P -p 5432 -U postgres

– 如果有变化的数据，可以执行增量备份

– pg_receivewal -D /xxx/backup/ -p 5432

– 启动备份实例，进行数据恢复

– vim postgresql.conf

recovery_target_lsn = ‘0/4011BF8’

restore_command=’cp /xxx/archive/%f %p’

recovery_target_action = ‘promote’

touch recovery.signal

– 启动数据库

– pg_ctl -D /xxx/backup start

– 关闭读模式

– select pg_wal_replay_resume();

– 冷备

### 逻辑备份

– 全库备份

– pg_dump

– 库级别备份

– 备份

– pg_dump -U postgres test >test.sql

– 恢复

– psql

create database test;

psql -U postgres test < test.sql

– 指定格式进行备份

– 备份

– pg_dump -Fc -U postgres test >test.dmp

– 恢复

– psql

create database test;

pg_restore -d test test.dmp

– 表级别备份

– 备份库下某个模式所有的表

– 备份

– pg_dump -U postgres -t ‘schema1.t*’ test >test.sql

– 恢复

– psql -U postgres test < test.sql

– 备份单个表

– 备份

– pg_dump -t 表名数据库名 >dump.sql

– 恢复

– create database 数据库名;

psql -U postgres 数据库名< dump.sql

– schema级别备份

– pg_dumpall

– 全库级别备份

– 备份

– pg_dumpall -U postgres > dumpall.sql

– 恢复

– psql -U postgres < dumpall.sql

– pg_dump VS pg_dumpall

– pg_dumpall 是一个用于写出(“转储”)一个数据库集群里的所有PostgreSQL 数据库到一个脚本文件的工具。

– pg_dumpall 调用pg_dump

– pg_dumpall 还转储出所有数据库公用的全局对象。这些信息目前包括数据库用户和组，以及适用于整个数据库的访问权限

– pg_dumpall 无法转储”大对象”，因为pg_dump无法把这样的对象转储到纯文本文件中。如果你的数据库里有大对象，那么你应该使用pg_dump 的非文本输出格式之一转储它们。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/120945.html

【PostgreSQL】入门学习笔记

相关推荐

发表回复