Database►PostgreSQL

PostgreSQL 必知必会

• 世界上最先进的开源数据库
• 设计灵活(extensibility ) ，可定制(customization)
• 支持ANSI/ISO 兼容的 SQL
• 已有30多年的发展历史
– University POSTGRES (1986-1993)
– Postgres95 (1994-1995)
– PostgreSQL (1996-2019)
• 有活跃在全球范围内的社区支持
– Support Mailing Lists
• http://www.postgresql.org/community/lists/
• 中文社区
– http://www.postgres.cn

安装 ：4种安装方式(1)

• 在Linux安装PostgreSQL
① 二进制安装包安装
• EDB不再为PostgreSQL 11及更高版本提供Linux安装程序(windows版本继续提供)，推荐用户使用源码编译安装
• 更早版本可参照 https://www.enterprislnxdb.com/downloads/postgres-postgresql-downloads
② 通过yum安装
• 1）安装指南：https://www.postgresql.org/download/linux/redhat/
• 2） 安装RPM仓库

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

• 3）安装PG
yum search postgresql – 得到相关列表
yum install -y postgresql14-14.4 postgresql14-contrib-14.4 postgresql14-libs-14.4 postgresql14-server-14.4
yum install -y postgresql14 postgresql14-contrib postgresql14-libs postgresql14-server (安装 14的最新子版本)
• 干净卸载 yum -y remove postgresql;rpm -e –nodeps rpm -qa | grep postgresql （一键删除所有的包）
③ RPM安装
下载位置https://yum.postgresql.org/rpmchart/
或者：https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-7-x86_64/
postgresql14 - PostgreSQL client programs and libraries
postgresql14-contrib - Contributed source and binaries distributed with PostgreSQL
postgresql14-libs - The shared libraries required for any PostgreSQL clients
postgresql14-server - The programs needed to create and run a PostgreSQL server

rpm -ivh postgresql13*

安装 ：4种安装方式(2)

④ 通过源码编译安装
• 重要的依赖：readline,flex,bison
• 三部曲：./configure && make && make install | install-world
• 源码安装的优势

安装：初始化数据库

– 修改 postgresql.conf 中的监听和端口，确保为每个实例分配了唯一端口
• listen_addresses = ‘*’ # what IP address(es) to listen on;
• port = 5436 # (change requires restart)
– 重启数据库

数据库启动与关闭

• 启停
– 使用 pg_ctl ( 推荐 )
• Start $PGHOME/pg_ctl –D $PGDATA start
• Reload $PGHOME/pg_ctl –D $PGDATA reload
• Stop $PGHOME/pg_ctl –D $PGDATA –m smart | fast | immediate stop
• Restart $PGHOME/pg_ctl –D $PGDATA –m smart | fast | immediate restart

– -m smart 等待客户端断开连接
– -m fast 回滚未完成的事务，断开客户端连接 （默认方式 )
– -m immediate 强行终止进程，数据库没有干净的关闭

– 使用Service方式 (el7)
• /etc/init.d/postgresql-14.x start | stop | restart | reload
• Service postgresql-14.x start | stop | restart | reload

– 使用systemctl （el7）
• systemctl start | stop | restart | relaod postgresql-14.x.service

• 启停注意事项
– 启动和停止时，尽量一边观察日志一边操作 （eg. $ tailf logfile.csv）
– 可以通过预先设置环境变量，减少出错及方便日常操作
• PATH 必须指向正确的 bin 目录.
• PGDATA 必须指向正确的数据库实例目录.
• PGPORT 必须指向正确的数据库实例运行的端口.
• 修改 .bash_profile或/etc/profile文件设置变量

• 前台启动
– postgres -D $PGDATA
• 前台变后台启动
– postgres -d $PGDATA >logfile 2>&1 &

为什么要配置变量？

Postgres,pg_ctl都会读取$PGDATA变量，所以要设置好这个变量

安装与运行：注意事项

OS 用户不能是root或具有操作系统管理权限的帐号

进行容量估算和规划
• 存储空间及读写能力
• 内存、CPU、网卡带宽
– 安装集群需要确认OS用户uid和gid
• 检查uid和gid

1
2
id postgres
uid=1000(postgres) gid=1000(postgres) groups=1000(postgres)

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

1
2
groupmod -g 1000 postgres
usermod -u 1000 -g 1000 postgres

– 配置NTP服务器(now,current_date,current_time使用的都是系统data时间)
• 方法1）配置NTP服务器 ： server cn.pool.ntp.org iburst | server time1.aliyun.com iburst
• 方法2）从NTP服务器更新系统时间：ntpdate cn.pool.ntp.org
http://www.pool.ntp.org/zone/cn

– 在Linux上作为一个后台进程 ，在Windows上作为一个服务
• Linux中查看postgres进程
– ps -aux | grep postgres
– netstat -natp|grep post （查看进程占用的端口号）
– netstat -anpl|grep post （查看进程占用的端口号）
– netstat -ano | grep PGSQL （查看进程占用的端口号）
– ss -tnlp | grep post（查看进程占用的端口号）
• 查看进程相关信息的命令：
– lsof –p pid
– strace -o output.txt -T -tt -e trace=all -p 28979 (strace能帮助你追踪到一个程序所执行的系统调用)
– 所有的PostgreSQL 的进程以及数据文件属于一个OS的用户
• 修改目录或文件属主：chown
– OS 用户和数据库用户没有关系 (un-related )
• OS 用户不能是root或具有操作系统管理权限的帐号
• 在 Windows 上只支持NTFS(只有NTFS下才支持建立表空间) .
– 环境变量说明
• $PGHOME 指向PG可执行程序的路径（安装路径）
• $PGDATA 指向数据库目录

登录数据库

• psql 

1
2
3
su - postgres
psql -Upostgres -d postgres  -h 10.0.0.61 -p 6632
psql -h tar1 -p 5432 -d zdb -U pgccc –W

• -U ：以哪个用户登录
• -W：强制输入密码
• -h ：主机名（IP地址）
• -p ：端口号
• -d ：登录哪个数据库

参数配置： 配置基础

• 配置文件的位置
– $PGDATA/postgresql.conf
• 开启参数include_dir = ‘conf.d’可使用$PGDATA/.conf.d/custom.conf
• 开启参数include_if_exists = ‘exists.conf’并存在可使用$PGDATA/exists.conf
• 开启参数#include = ‘special.conf’可使用$PGDATA/special.conf
• 数据库启动时，会读取该文件
• 可手工编辑
– $PGDATA/postgresql.auto.conf
• 保存ALTER SYSTEM修改后的参数( ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, Values
set with ALTER SYSTEM will be effective after the next server configuration reload)
• 不要手动修改它
• postgresql.auto.conf 的值覆盖postgresql.conf 的值（Settings in postgresql.auto.conf override those in postgresql.conf. ）
– $PGDATA/recovery.conf（pg12已经不存在这个文件，recovery.conf 配置文件中的参数合并到 postgresql.conf）
– $PGDATA/pg_hba.conf
– $PGDATA/pg_ident.conf(记录操作系统用户和数据库用户映射关系)
• postgresql.conf 配置文件结构
– #注释
– key = value
• 支持的参数值类型
– 布尔、整数、浮点数、字符串、枚举
– include指令（允许嵌套）

Oracle配置文件与PG配置文件对应关系

pfile: 初始化参数文件（Initialization Parameters Files），
pfile 默认的名称为“init+例程名.ora”文件路径: /data/app/oracle/product/。
该文件可以使用任何文本编辑工具打开。

spfile: 服务器参数文件（Server Parameter Files），从Oracle 9i版本开始，Oracle 引入了服务器参数文件（spfile）。它的默认名称为“spfile+例程名.ora”文件路径: /data/app/oracle/product/。
与 pfile 不同，spfile 不能直接通过文本编辑器编辑，其中的参数进行修改时，只能通过 SQL 命令在线修改。

全局参数修改

• 修改参数的方法
– 通过linux命令修改配置文件vim,echo,sed
– 通过ALTER SYSTEM命令修改全局配置:
– 启动时设置（不推荐，除非进入单用户模式）
• postgres -c configparameter=newvalue
• 如何查看参数
– 查询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-14.x reload; (el6)
– systemctl reload service.postgresql-14.x (el7)

非全局参数

• 设置和重置Database级别的配置
– ALTER DATABASE name SET configparameter { TO | = } { value | DEFAULT }
– ALTER DATABASE name RESET configuration
• 设置和重置Session级别的配置
– 通过SET命令设置当前Session的配置
• SET configparameter { TO | = } { value | ‘value’ | DEFAULT }
• SET configparameter TO DEFAULT;
– 更新pg_settings视图
• UPDATE pg_settings SET setting = new_value WHERE name = ‘configparameter’;
• UPDATE pg_settings SET setting = reset_val WHERE name = ‘configparameter’;
– 使用set_config函数更新会话配置
• SELECT set_config(‘configparameter’,new_value,false);
• Role级别的配置
– ALTER ROLE name IN DATABASE database_name SET configparameter { TO | = } { value | DEFAULT }
– ALTER ROLE name IN DATABASE database_name RESET configparameter

PG防火墙

pg_hba格式

• 由pg_hba.conf决定
– 哪些主机可以连接数据库实例
– 客户端使用什么连接方式和认证方式
• 连接方式(TYPE)：local,host,hostssl,hostnossl
• 认证方式：trust, reject, md5, password, scram, gss, sspi, ident, peer, pam, ldap, radius or cert
– 哪个数据库用户可以使用它
– 允许这个用户使用哪些数据库
– find / -name “postgresql.conf”
• 七种格式
– # TYPE DATABASE USER ADDRESS METHOD
– local database user auth-method [auth-options]
– host database user address auth-method [auth-options]
– hostssl database user address auth-method [auth-options]
– hostnossl database user address auth-method [auth-options]
– host database user IP-address IP-mask auth-method [auth-options]
– hostssl database user IP-address IP-mask auth-method [auth-options]
– hostnossl database user IP-address IP-mask auth-method [auth-options]

连接方式

• local匹配使用Unix域套接字的连接。
– 如果没有TYPE为local的条目则不允许通过Unix域套接字连接
• host匹配使用 TCP/IP建立的连接，同时匹配SSL和非SSL连接
– 缺省安装只监听本地环回地址localhost的连接，不允许使用TCP/IP远程连接，启用远程连接需要修改postgresql.conf中的listen_addresses参数。
• hostssl匹配必须是使用SSL的TCP/IP连接
– 客户端和服务器端都安装OpenSSL
– 编译PostgreSQL的时候指定configure参数–with-openssl打开SSL支持
– 在postgresql.conf中配置ssl = on
• hostnossl只匹配使用非SSL的TCP/IP连接

认证方法 （1）

• trust
– 无条件地允许连接。这种方法允许任何可以与PostgreSQL数据库服务器连接的用户以他们期望的任意PostgreSQL数据库用户身份登入,而不需要口令或者其他任何认证。
• reject
– 无条件拒绝连接。常用于从一个组中“过滤出”特定主机,例如一个reject行可以阻塞一个特定的主机连接,而后面一行允许一个特定网络中的其余主机进行连。
• md5
– md5认证方式为双重md5加密

认证方法（2）

• password口令认证
– password指明文密码，所以不能在非信任网络使用password方式(Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks)。
• peer
– 从操作系统获得客户端的操作系统用户,并且检查它是否匹配被请求的数据库用户名。这只对本地连接可用。
• scram-sha-256
– Perform SCRAM-SHA-256 authentication to verify the user’s password.。
– 其它认证方式
– https://www.postgresql.org/docs/current/static/auth-methods.html

认证方法 （3）

如果要使所有可提供有效密码的用户均能从以 100 开头的地址连接到 PostgreSQL 实例中的任何数据库，则需要向 pg_hba.conf 文件
添加与与下面的行内容类似的行：
host all all 100.0.0.0/8 md5
上述示例针对 IPV4 地址。如果需要配置数据库群集以接受具有 IPV6 地址的计算机的连接，那么格式应如下所示：

host all all fe60::61f9:1253:c522:9b92%10/128 md5
要允许所有具有以 fe60 开始的地址的计算机进行连接，应向 pg_hba.conf 文件添加以下行：
host all all fe60::0000:0000:0000:0000%10/8 md5

如果您的 PostgreSQL 数据库集群安装在具有 IPV6 地址的计算机上，而您想要使具有 IPV4 地址的计算机与其连接，则需要在
pg_hba.conf 文件中取消以下行的注释：
host all all ::1/128 md5

允许IP地址为10.10.56.17的所有用户可以通过MD5的密码验证方式连接主机上所有的数据库

TYPE DATABASE USER ADDRESS METHOD

host all all 10.10.56.17/32 md5

即表示允许地址为 10.10.56.17 的用户 pgtest通过 MD5方式 加密的密码方式连接主机上的 test 数据库
TYPE DATABASE USER ADDRESS METHOD
host test pgtest 10.10.56.17/32 md5

即表示允许 任意iP 通过用户名为 pgtest 和md5的 密码 验证方式连接主机上 test 的数据库
TYPE DATABASE USER ADDRESS METHOD
host test pgtest 0.0.0.0/0 md5

用最安全的认证(A)

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

1
2
3
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 ;

MD5破解网站：https://cmd5.com/default.aspx?hashtype=md5&answer=mtu5mdu3oty1mdi%3d
查看现在的加密方法

1
postgres=# show password_encryption;

修改加密方法

1
2
3
postgres=# alter system set password_encryption = 'scram-sha-256';
postgres=# show password_encryption;
postgres=# select pg_reload_conf();

用最安全的认证(B)

修改密码

1
postgres=# alter user foo password  'Xzzp20088';

The password_encryption parameter decides how the ALTER USER statement will encrypt the password
修改pg_hba.conf 本地连接的method方法为scram-sha-256
TYPE DATABASE USER ADDRESS METHOD
“local” is for Unix domain socket connections only
host all all 0.0.0.0/0 scram-sha-256

1
postgres=# select pg_reload_conf();

远程登录

1
psql -h 10.0.0.61 -p 5433 -d postgres -U foo -W

有一个插件，可以用于加强口令复杂度管理
passwordcheck
http://www.postgresql.org/docs/current/static/passwordcheck.html

客户端工具

pgAdmin 4

– https://www.pgadmin.org
– yum / apt install pgadmin
• other
– Navicat , DBeaver ….

• psql - 最好的客户端工具
– 连接数据库：psql -h localhost -p 5432 database_name
– 获得psql的帮助：?
– 获得语法的帮助：\h STATEMENT
– 在shell中执行命令：psql -c “STATEMENT”
– 通过psql执行sql文件：psql < f.sql

psql的使用

\l 查看有哪些数据库
\c 用于切换数据库
\d 显示每个匹配关系（表，视图，索引，序列）的信息
\d [ pattern ] +
\d 后面跟一个表名，表示显示表结构定义
\d 后跟一个索引名，显示索引的信息
\d 后面跟一个视图名，显示视图信息
\timing on 显示SQL执行的时间
\timing off 关闭计时功能
\dn 列出所有的schema
\db 显示所有的表空间
\du\dg 列出所有的角色或者用户
\dp 显示权限分配情况
\x 行列互换显示
\set AUTOCOMMIT off将自动提交功能关闭

数据库管理

• 创建数据库(2种方法)

  1. Using CREATE DATABASE, an SQL command.
    postgres=# CREATE DATABASE LNXDB;
  2. Using creatlnxdb a command-line executable.
    [postgres@tar1 pgdata]$ createdb LNXDB

• 登录数据库
psql LNXDB

• 删除数据库

  1. Using CREATE DATABASE, an SQL command.
    postgres=# DROP DATABASE LNXDB;
    (可能有错，试分析并解决)
  2. Using creatlnxdb a command-line executable.
    [postgres@tar1 pgdata]$ dropdb LNXDB

体系结构初探

数据库集群

（ Database Cluster ）的概念*
• 一个数据库集群（database cluster）=一个数据库实例（简称“实例”）
• 通过两种方式访问集群：
– 数据目录的位置
– 端口号
• 一个服务器可以管理多个数据库实例
• PostgreSQL多实例和MySQL多实例
• 进程树(pstree -p 13524)

• 进程查看
ps -ef |grep postgres

Table: background processes.
process description reference
background writer 进程将shared buffer pool中的脏数据写到磁盘，检查点总能触发这个进程
checkpointer 检查点会触发产生这个进程
autovacuum launcher 为vacuum process周期性的调用autovacuum work processes
WAL writer 周期性的从wal buffer刷新数据到磁盘
statistics collector 收集统计信息进程，比如pg_stat_activity 和pg_stat_database的数据
logging collector (logger) 将错误信息写入到日志
archiver 将日志归档的进程

故障排查

• 查看操作系统错误日志
/var/log/message 系统启动后的信息和错误日志，是Linux中最常用的日志之一

• 查看数据库错误日志
查看文件$PGDATA/log

常见故障

1、Initdb Failed Due to Directory ‘Invalid Permission’
$ initdb -D data running bootstrap script …
FATAL: data directory “data” has invalid permissions [1885]
DETAIL: Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
child process exited with exit code 1 initdb: removing data directory “data”

2、Is the PostgreSQL Server Running Locally and Accepting?
$ psql postgres
psql:
error: could not connect to server: could not connect to server: No such file or directory
Is the server running locally and accepting connections on Unix domain socket
“/var/run/postgresql/.s.PGSQL.5432”?

3、FATAL: no pg_hba.conf entry for host

4、no space left on device

以下是针对您提供的 PostgreSQL 常见故障的解析及解决方案：

  1. Initdb Failed Due to Directory ‘Invalid Permission’

错误信息：

1
2
3
4
5
6
$ initdb -D data
running bootstrap script ... 
FATAL:  data directory "data" has invalid permissions [1885] 
DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750). 
child process exited with exit code 1 
initdb: removing data directory "data"

错误原因：
PostgreSQL 数据目录的权限设置不正确，initdb 需要确保目录具有特定的权限（0700 或 0750）。这个错误通常发生在您设置 PostgreSQL 数据目录时权限不正确。

解决方案：

  1. 检查并修改数据目录权限。使用以下命令：

    1
    chmod 700 /path/to/data  # 或者 chmod 750

  2. 确保 PostgreSQL 用户拥有访问和写入数据目录的权限：

    1
    chown -R postgres:postgres /path/to/data

  3. 然后重新运行 initdb 命令。

  1. Is the PostgreSQL Server Running Locally and Accepting Connections?

错误信息：

1
2
3
4
5
$ psql postgres
psql: 
error: could not connect to server: could not connect to server: No such file or directory 
Is the server running locally and accepting connections on Unix domain socket 
"/var/run/postgresql/.s.PGSQL.5432"?

错误原因：
此错误通常表示 PostgreSQL 服务器没有运行，或者没有在正确的 Unix 域套接字上监听。

解决方案：

  1. 检查 PostgreSQL 服务是否正在运行。运行以下命令：

    1
    systemctl status postgresql

    或者：

    1
    service postgresql status

    如果服务没有运行，可以通过以下命令启动：

    1
    systemctl start postgresql

    或者：

    1
    service postgresql start

  2. 确保 PostgreSQL 配置文件 postgresql.conf 中的 unix_socket_directories 参数指向正确的目录。如果没有设置或者设置不正确，可以在配置文件中调整它：

    1
    unix_socket_directories = '/var/run/postgresql'
  1. FATAL: no pg_hba.conf entry for host

错误信息：

1
FATAL: no pg_hba.conf entry for host

错误原因：
PostgreSQL 的 pg_hba.conf 文件用于控制哪些主机可以连接到数据库以及如何进行身份验证。如果没有正确配置该文件，客户端会收到此错误。

解决方案：

  1. 编辑 pg_hba.conf 文件（通常位于 PostgreSQL 数据目录中）：

    1
    sudo nano /var/lib/pgsql/data/pg_hba.conf

  2. 确保在文件中添加了适当的条目以允许客户端连接。例如，允许本地连接：

    1
    2
    3
    # 允许所有本地用户连接到所有数据库
    host    all             all             127.0.0.1/32            md5
    host    all             all             ::1/128                 md5

  3. 修改后保存并重新加载 PostgreSQL 配置：

    1
    sudo systemctl reload postgresql
  1. no space left on device

错误信息：

1
no space left on device

错误原因：
此错误通常意味着 PostgreSQL 数据目录所在的磁盘已经没有剩余空间。由于 PostgreSQL 要写入日志文件、事务日志等，这会导致数据库无法正常运行。

解决方案：

  1. 检查磁盘空间：

    1
    df -h

    确保数据存储目录所在的磁盘有足够的空间。如果空间不足，您可以采取以下措施：

    • 清理不必要的日志文件，临时文件等。
    • 将 PostgreSQL 数据目录移动到空间较大的磁盘。

  2. 如果是数据库日志占用大量空间，您可以考虑调整日志文件的保留策略或手动清理旧的日志文件。

SQL快速上手

SQL Basic(1) SQL

• 语法
– 标识符、关键字、常量的转义
• 在系统表中查询所有关键字
– select word from pg_get_keywords();
• 用单引号标示字符串常量
– 例如：’foo’
• 在字符串前写字母E (大写或者小写)的方法声明C风格的转义字符串常量
– 例如：SELECT E’\tfoo’;将返回“__foo”
– 例如：SELECT E‘\’;将返回“\”，请问：SELECT ‘\’;返回什么？
• Dollar-quoted字符串常量
– $$这里\有个’单引号$$
– $tag$这里是一’个标\签的内容$tag$（标签大小写敏感）
• 位串常量
– 以B开头加单引号包括的0和1组成位串常量，例如：SELECT B‘1001’::int;返回9
– 以X开头加十六进制数组成，请问：SELECT X‘0B’::int;返回什么？

• 值表达式
– 字段引用，例如：correlation.columnname
– 位置参数，例如：$1表示函数的第一个参数
– 下标，例如：mytable.arraycolumn[4]
– 字段选择，例如：mytable.mycolumn
– 函数调用，例如：abs(x);

SQL Basic(2) CREATE TABLE

• 语法
CREATE TABLE table_name (
col_1 data_type,
col_2 data_type, …
col_n data_type
);
• 示例
CREATE TABLE departments(
department_idinteger,
name varchar(50)
);

SQL Basic(3) DROP TABLE

• 使用 DROP 命令
• 删除一个表:
– 语法:
DROP TABLE [ IF EXISTS ] name [, …] [ CASCADE]

SQL Basic(4) Insert

• 语法
INSERT INTO table [ ( column [, …] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT }
[, …] ) | query }
• 示例
INSERT INTO departments (department_id, name)
VALUES (1, ‘Development’);

SQL Basic(5) Multi Value INSERT

• 语法
INSERT INTO table [ ( column [, …] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT }
[, …] ) | query }
• 示例
INSERT INTO emp (empno, ename, job) VALUES
(2,’JOHN’, ‘MANAGER’),
(3, ‘MARY’, ‘CLERK’),
(4, ‘HARRY’, ‘MANAGER’);

SQL Basic(6) Simple SELECT

• 语法
SELECT column_1, column_2 , … column_n
FROM table
WHERE condition
ORDER BY column_list
• 示例
SELECT department_id, name
FROM departments
WHERE department_id = 1
ORDER BY name;

SQL Basic(7) 列和表别名(Aliases)

• 可以提高复杂SQL的可读性
• 可以减少输入量
• 示例:
SELECT ename, dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SQL Basic(8) UPDATE

• 语法
UPDATE [ ONLY ] table SET column = {expression | DEFAULT}
[,…]
[ FROM fromlist ]
[ WHERE condition ]
• 示例
UPDATE departments SET name=’DEVELOPMENT’
WHERE department_id=1;

SQL Basic(9) DELETE

• 语法
DELETE FROM [ ONLY ] table [ WHERE condition ]
• 示例
DELETE FROM departments
WHERE department_id = 2;

TRUNCATE 快速清空表中数据

SQL Basic(10) 使用SQL Functions

• 可以在 SELECT 语句以及WHERE 中使用
• 包括
– String Functions
– Format Functions
– Date & Time Fuctions
– Aggregate Functions
• 示例
SELECT lower(name)
FROM departments;
SELECT * FROM departments
WHERE lower(name) = ‘development’;

数据类型

数据类型分类

PostgreSQL 拥有大量的内置的数据类型用来存储不同类型的数据。
• 标准类型(Standard)
– Boolean and Logic
– Strings - char(n), varchar(n), varchar2(n) and text
– Numbers - integer, floating point, numeric, number
– Date/time - timestamp(), date, time(), interval(), datetime
• 扩展类型(Extended)
– Geometric - point, line, box, etc
– Network - inet, cidr, macaddr
– Bit - bit, bit varying
• 数组和符合类型(Arrays and Composite types)
• 系统类型(System types)

Boolean

• Postgres 提供了标准的SQL类型boolean。 boolean 只能有如下两种状态中的一种: “true” or “false”. 第三种, “unknown”, 可以用SQL null 值表示.
• TRUE, ‘t’, ‘true’, ‘y’, ‘yes’, ‘1’
• FALSE, ‘f’, ‘false’, ‘n’, ‘no’, ‘0’
• 使用关键字 TRUE 和 FALSE 是首选的(它是SQL兼容的( SQL-compliant)).

逻辑和比较操作符

• AND, OR, 和 NOT
• <, >, <=, >=, =,和<> 或者 != 所有都返回boolean值.
• BETWEEN和NOT BETWEEN
• IS NULL 是唯一标准的方式查看是否为空. IS NOT NULL 是相反的操作符。
• NULL != NULL ! NULL = anything 将返回 NULL
• 将 transform_null_equals 设置为 on
就会使得
x = NULL 转换为 x IS NULL
a IS DISTINCT FROM b 等价于 a != b，除非 a 或 b 有一个为空，那就返回TRUE。如果a和b
都是 null, 他将返回FALSE. 这将使null的比较变的更简单。
IS TRUE, IS NOT TRUE, 等等…

条件表达式

• SQL中的 CASE 表达式是一种常见的条件表达式，同其他语言的
if/else语句很相似:
• CASE WHEN condition THEN result
CASE expression
WHEN value THEN result
[WHEN …]
[ELSE result]
END

• COALESCE() – 返回第一个非空值(non-null)
COALESCE(desc, short_desc, ‘(none)’)

• NULLIF(a, b) – 与 coalesce 相反,
如果 a = b 则返回NULL，否则返回 a
NULLIF(a, ‘(none)’)

Strings

• Postgres中有3中主要的文本类型： char(n), varchar(n) 和text.
• char(n) 和 varchar(n) 大多遵循 ANSI 标准
– 如果给定的一个string比 n characters (not bytes)长，将会报错，除非超出的全部为空格，或者你显式地标注(casting)，在这种情况下这个字符串将会被截断(truncated) (感谢, ANSI SQL)
– char(n)末尾的空格，在两个 char(n) 比较时将被忽略掉，在转换为其他string类型时也会被去掉。
– 和其他许多数据库不一样， 在Postgres中，char(n)不存储为fixed-sized。对它的处理和
varchar(n)很像，只是需要被填补(being padded) 。
• char(n) 和 varchar(n) - cont’d
– char() 或者 char 等同于 char(1) ，和 “char”不同
– varchar()或者 varchar等同于 一个没有长度限制的varchar. 这是 Postgres 的扩展(extension)。
– 所有的存储为 “变长 (varlena)”类型 – 后面详述

模式匹配

(Pattern Matching) - LIKE
1
2
• string [NOT] LIKE pattern [ESCAPE escape-character] (also ~~ and !~~)
• ILIKE - case Insensitive LIKE (~~*, !~~*)

• “_” – 单字符通配符 (思考“?”)
• “%” – 多字符通配符 (思考 “*”)
• 匹配整个字符串; 使用 ‘%abc%’ 来查询字符串中任何位置的’abc’
• 使用 escape-character (通常用“\”)来将一个字面上的(不作为通配符)_或%加入到 pattern中

(Pattern Matching) - RegEx

• 正则表达式(Regular Expression (或者 regex)) 是一个模式匹配(pattern
matching)/文本处理(text processing)的语言
• string ~ regex – 如果 string 匹配上 regex则返回TRUE, 大小写敏感
• “*” -大小写不敏感
• “!” 和 “!~*” 如果 string 没有匹配上 regex则返回TRUE
• 不匹配上整个字符串

1
2
3
4
5
6
SELECT 'PostgreSQL' ~ 'gre';

 ?column? 
----------

 t
(Pattern Matching) - SIMILAR TO

• string [NOT] SIMILAR TO pattern [ESCAPE escape-character]
• 和 LIKE 操作相同, 只不过 pattern 是 ANSI SQL regex
• “_” and “%” instead of “.” and “.”
• “.” 不是一个特殊的字符
• “|”, “”, “+”, “()”, 和 “[]” 在正则表达式中操作
• “{}” 不支持

字符串函数

•ascii
•bit_length
•btrim
•char_length or character_length
•chr
•concat
•convert
•decode
•encode
•initcap
•length
•rpad
•rtrim
•split_part
•strpos
•substr
•substring
•to_ascii
•to_hex
•translate
•trim
•upper
•lower
•lpad,
•ltrim
•md5,
•octet_length
•overlay
•pg_client_encoding
•position
•quote_ident
•quote_literal
•repeat
•replace

  • General

• ||连接两个字符串
• greatest(value[, …]), least(value[, …]) – 返回最大最小值
• lower(), upper() – 将字符串变为小写或大写
• length() – 返回字符串中字符的个数. 参见 char_length(), character_length(), octet_length() 和 bit_length()

  • Pad

    • lpad, rpad(string, length[, fill])
    使用填充字符串 “fill”，在左边或右边填充一个字符串到
    固定的长度”length” (填充字符串fill缺省为空格).如果填
    充后的字符串比”length”约束的长，则会截断。

    1
    2
    3
    4
    5
    6
     - SELECT rpad('pad', 8, 'xyz');
       rpad   

    ----------

     padxyzxy

    • Trim

    • ltrim, rtrim, btrim(string[, characters])
    删除字符串左边或右边或两边的characters字符
    (默认是空格)

    1
    2
    3
    4
    SELECT btrim('xyxtrimyyx', 'xy');
     btrim 
    -------
     trim

  • Position

• position(substring in string)
substring 在 string中的位置。参见 strpos().

1
2
3
4
5
6
7
SELECT position('DB' in 
'EnterpriseDB');

 position 
----------

       11

  • Substring

    • substring(string[ from start][ for run])
    • substring(string from pattern)
    • substring(string from pattern for escape)
    根据指定位置返回子串, POSIX regex pattern, or SQL regex pattern. 参
    见 substr().

    1
    2
    3
    4
    5
    6
    - SELECT substring('EnterpriseDB' from 11 for 2);
       substring 

    -----------

     DB

    数值类型(Numbers)

    • Integers
    – int/integer/int4, smallint/int2, bigint/int8
    – bigint 依赖于编译器的支持，因此不一定能在所有的平台上运行。
    – bigint, int2, int4 and int8 是 Postgres扩展
    • Serial
    – 不是一个真正的类型 - 只不过为创建一个 sequence并把它作为列的默认类型提供方便
    – 可以定义列 NOT NULL
    – serial, bigserial, serial4, serial8

    CREATE TABLE s(s serial);
    NOTICE: CREATE TABLE will create implicit sequence “s_s_seq” for serial
    column “s.s”
    \d s
    …
    s | integer | not null default nextval(‘s_s_seq’::regclass)

数值类型(Numbers)

• Floating point
– real, double precision
– 它依赖于编译器，CPU, 和 OS 支持, 在不同的安装中细节会有不同
– real 通常有 6 位精度，范围至少在 1E-37 和 1E+37之间
– double通常有 15 位精度，范围至少在 1E-307 和 1E+308之间
– 特殊值 : Infinity, -Infinity, NaN
–SQL标准表示法float(p)用于声明一个的数值类型，它以P（二进制位表示的最低可接受精度，在7.4以前，P是一个十进制的位）。P取值在1到24之间表示一个real的精度，在25到53之间表示double的精度。如果有其他值将会返回错误。
– 如果没有定义float的precision，等价于 double的precision

数值类型(Numbers)

• Numeric
– 允许是任意 precision (所有位的个数) 和 scale (小数位数)
– 可以存储多达1000位的数值，但非常慢
– numeric(p, s) 定义了最大的 precision (须 > 0) 和最大的 scale (须 >= 0)
– numeric(p)是一个scale为0的 numeric
– 如果在numeric中没有定义任何的 precision 或者 scale，那他将允许存储任意precision 和 scale的数值。这种形式不会将输入数值强制转化成任何特定精度的值
– 如果一个要存储的数值的scale比声明的scale大， 那么系统将尝试四舍五入该数值到指定的scale。如果小数点左边的数据位数超过了声明的precision减去scale ， 那么抛出一个错误。
– 数据在物理存储时其前后不带任何的0
– 允许 NaN

数值类型操作符 - DON’T EVER DO THIS

• +, -, *, /, % (also mod()), ^ (also power())
• |/ (prefix) square root (also sqrt())
• ||/ (prefix) cube root (also cbrt())
• !! (prefix), ! – factorial
• @ (prefix) - absolute value (also abs())

1
2
3
4
SELECT |/ 25, 5 !, @ -5;
 ?column? | ?column? | ?column? 
----------+----------+----------
        5 |      120 |        5

数值函数

• ceil/ceiling, floor
• exp (exponential), ln, log
• greatest, least
• random, setseed
• round, truncate
• sign
• to_number
• degrees(radians), radians(degrees)
• cos, acos, sin, asin
• cot (cotangent), tan, atan
• atan2(x, y) = atan(x/y)

日期/时间(Date/Time)

• 可能会以 integers 或者 double 的精度存储
• timestamp[(p)] with time zone (timestamptz)
• timestamp[(p)] without time zone (timestamp)
– p - 0 to 6 digits of fractional seconds
– + interval
– interval, timestamp
• 特殊值
– now – 当前事务开始时间
– today, tomorrow, yesterday - 那天的midnight
– epoch - unix epoch (midnight, Jan. 1, 1970)

• Date
– 当添加了 timestamp,即假设 midnight
– 特殊值: now, today/tomorrow/yesterday, epoch
– + int (days), interval, time (returns timestamp)
– - int (days), interval, date (returns int)
• time[(p)] with time zone (timetz)
• time[(p)] without time zone (time)
– p - 0 to 6 for 如果使用integer存储，p为0-6；如果使用double存储，p为0-10
– 特殊值: now, allballs (00:00:00.00 UTC)
– + interval, date (returns timestamp)
– - interval, time

• interval[(p)]
– 接受输入中的单位(Units)(如day，minutes)
‘1 day 3 minutes’ = ‘1 00:03:00‘
– 单位(Units)和ago共同使用代表负数
‘1 day ago’ = ‘-1 days -00:03:00‘
– “冒号” 默认为 hour:minute
‘1 1:30’ = ‘1 day 01:30:00‘
– 没有单位就默认为秒
‘12345’::interval = ‘03:25:45‘
– 在内部存储为月，日，秒的形式，因为一个月内的天数是变化的。
– +/- timestamp, date, time, interval
– 可以乘以或除以某个数
5 * ‘2 minutes’::interval = 00:10:00
‘10 minutes’::interval / 3 = 00:03:20

• Time zones
– timestamp with time zone 内部存储为 UTC, 没有原来的时区
(original time zone)
– time with time zone存储中带有原来的时区(original time zone)
– 没有日期的时区转换没有什么意义，使用带时区的time是不推荐的，最好使用 timestamp 来代替。
– 时区信息 (尤其是 daylight savings time) 变化频繁，因此保持当前的时间更新是最重要的。

日期/时间函数

• age - 减去参数，生成一个使用年、月、天的”符号化”的结果 

1
2
3
4
5
SELECT age('1/1/2006'::timestamp), 
age('10/15/2006'::timestamp,'1/1/2006'::timestamp); 
      age      |      age       
---------------+----------------
 9 mons 3 days | 9 mons 14 days

• date_part or extract - get subfield(day, month, week, etc)
• date_trunc -截断成指定的精度
• greatest, least
• isfinite - timestamp/interval 不是 +/- infinity
• justify_days – 调整间隔为 years, months, days的形式
• justify_hours -调整间隔为 days, hours的形式

• current_date, current_time[(p)], current_timestamp[(p)]
• now() – 事务开始的 date/time/timestamp with timezone
• localtime[(p)], localtimestamp[(p)] - 事务开始的 time/timestamp without timezone
• timeofday() – 当前时间 (不是事务开始时间). 返回text.

#### 数组(Arrays) • 任何基本类型 (不是组合类型或域) 可以用作数组，只要在类型后面加一个中括号 ([]) 。 创建一个二维数组如下: 
1
CREATE TABLE a(a int[][5]);
• PostgreSQL 不强制要求定义数组大小，甚至维数也可以不定义。所有的数组作为type[]. • Arrays 可以为 NULL, 但数组内的元素不能为null • ANSI 语法只支持一维数组，并且需要定义数组大小。但是，和其他数组一样，PostgreSQL对此没有特殊要求。 
1
CREATE TABLE a(a int ARRAY[5]);
**• 输入(Input)** – 通常形式是 “‘{ value 分隔符 value ... }'” – 单引号是必须的 (实际上连接成一个字符串) – 其内的值要么是一个常量，要么是一个数组 – 分隔符根据类型而定。 所有的内置类型使用 “,”，长方形类型(box)除外, 它使用 “;”。 – 输入值需要对应多维数组中的维数 
1
2
3
4
5
INSERT INTO a VALUES('{{1,2},{4,5,6}}');
ERROR:  multidimensional arrays must have array 
expressions with matching dimensions
INSERT INTO a VALUES('{{1,2,3},{4,5,6}}');
INSERT 0 1
**• 访问** – 通过下标访问 - SELECT a[2][2]; – 通过范围访问 - a[1:2][2] = '{{1,2},{4,5}}‘ • 如果没有写 ([2])，默认下界(Lower range)是1 • 查询数组下标以外的值返回为 NULL, 而不是错误: a[3][4] IS NULL = true **• 更新** – 全部赋值 - UPDATE a SET a = '{10,11}‘ – 部分赋值 - SET a[2][2:2] = '{{2},{5}}' #### 数组函数 • array_cat(array, array) – 连接两个数组 • array_prepend(element, array) – 在数组开头添加一个元素 • array_append(array, element) –在数组末尾添加一个元素 • 或者使用 ||: array[1,2] || array[3,4] = {1, 2, 3, 4} array[1,2] || array[[3,4]] = {{1, 2}, {3, 4}} 1 || array[2,3] = {1, 2, 3} array[1,2] || 3 = {1, 2, 3} • array_dims – 返回数组的维数，生成text结果 array_dims(array[[1,2],[3,4]]) = [1:2][1:2] #### 数组比较 • 值操作符 ANY(array) – SOME 是 ANY 的同意词 – 如果数组中有任一元素都满足比较条件则返回TRUE. 1 = ANY(array[1,2]) = TRUE 2 < ANY(array[1,2]) = FALSE – 如果任一结果返回 TRUE，则表达式返回TRUE。 • 值操作符 ALL(array) – 如果数组中所有的元素都满足比较条件则返回TRUE 1 = ALL(array[1,2]) = FALSE 0 < ALL(array[1,2]) = TRUE – 如果任一结果返回 FALSE ，则表达式返回FALSE 。 #### 数组函数 • array_lower, array_upper – 返回指定数组维数的下界或上界 array_upper(array[[1,2],[3,4],[5,6]], 2) = 2 • array_to_string – 使用字符串将数组元素连接起来 array_to_string(a, 'xy') = 1xy2xy3xy4xy5xy6 • string_to_array – 使用指定的分隔符将字符串分割为数组 string_to_array('1xy2xy3xy4xy5xy6','xy') = {1,2,3,4,5,6}

域(Domains)和组合类型(Composite types)

• 域(Domains)
– 是已存在的数据类型的别名，虽然可以额外定义约束
CREATE DOMAIN name [AS] type [DEFAULT expression]
[constraint …]
– 支持性不太好; plpgsql 不检查约束, 不能被使用到一些系统中…
• 组合类型(Composite types)
– 类似C语言的结构体(struct)

1
2
3
4
CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

• 注意 AS 是必须的，否则数据库会认为你将创建一个新的数据类型。

聚集函数

• 从一系列的输入值中计算出某个单一的值
• 忽略 NULLs (如果所有的输入是NULL则返回 NULL)
• avg, count, max, min, stddev, sum, variance
• bit_and, bit_or – 所有非空输入值按位”与/或”
• bool_and, every -如果所有输入值都是真，则为真，否则为假
• bool_or -如果至少有一个输入值为真，则为真，否则为假

设置返回集合函数

• 返回多行的函数；使用时和表类似
• generate_series(start, stop[, step]) -生成一个数值序列，从 start 到 stop，步长
为 step (默认为1)

1
2
3
4
5
6
7
8
SELECT * FROM generate_series(1,3) a;

 a 
---

 1
 2
 3
对象标识符类型Object Identifier Types (OID)

• 内部使用，用来标识对象
• 可以在用户定义的表中使用，但不推荐
• 别名类型对查找一个对象的OID非常方便。
– reg* ，其中 *可以是class, type, oper, operator, proc 或者procedure
– regoperator 和 regprocedure 接收参数类型

1
2
3
4
5
6
7
8
9
10
SELECT 'pg_catalog.abs'::regproc;
ERROR:  more than one function named "abs"
SELECT 'abs(int)'::regproc;
ERROR:  function "abs(int)" does not exist
SELECT 'abs(int)'::regprocedure;

 regprocedure 
--------------

 abs(integer)
约束(Constraints)

• 条件约束
(Check Constraints)
• 非空约束
(Not-Null Constraints)
• 唯一性约束
(Unique Constraints)
• 主键(Primary Keys)
• 外键(Foreign Keys)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE emp
(
  empno numeric(4) NOT NULL,
  ename varchar2(10),
  job varchar2(9),
  mgr numeric(4),
  hiredate datetime,
  sal numeric(7,2),
  comm numeric(7,2),
  deptno numeric(2),
  CONSTRAINT emp_pk PRIMARY KEY (empno),
  CONSTRAINT emp_ref_dept_fk FOREIGN KEY (deptno) 
    REFERENCES dept (deptno) ON UPDATE NO ACTION 
    ON DELETE NO ACTION,
  CONSTRAINT emp_sal_ck CHECK (sal > 0)
)

建立数据库用户

• 数据库用户和操作系统用户不同
• 用户可以通过SQL命令 “CREATE USER”或 “createuser” 工具创建
例如:

1
2
3
4
5
CREATE USER scott PASSWORD ‘tiger’;
CREATE USER scott_dba CREATEDB CREATEUSER;
CREATE USER scott_lnxdb VALID UNTIL ‘2013-03-
01’;
DROP USER scott CASCADE;

用户与角色有什么区别？

这张截图展示的是关于 PostgreSQL 数据库中 CREATE USER 和 CREATE ROLE 命令的说明。它的核心意思如下：

解释：

  • CREATE USER 现在是 CREATE ROLE 的别名。也就是说，CREATE USER 实际上是 CREATE ROLE 的简写。
  • 这两者的唯一区别在于：
    • 当命令是 CREATE USER 时，默认假定该用户可以登录（LOGIN），即用户会拥有登录权限。
    • 而当命令是 CREATE ROLE 时，默认假定该角色不能登录（NOLOGIN）。

主要内容：

  • CREATE USER 和 CREATE ROLE 都用于创建数据库角色。
  • 在使用 CREATE USER 时，创建的角色默认是可以登录的。
  • 在使用 CREATE ROLE 时，创建的角色默认是不能登录的，除非显式指定 LOGIN 选项。

总结：

  • CREATE USER 只是 CREATE ROLE 的一种语法简化，二者的功能相同，但默认行为有所不同。

操作权限赋予

对表的访问权限通过 GRANT 和 REVOKE SQL 命令赋予和回收。
例如:

1
2
3
4
5
GRANT UPDATE DELETE ON emp TO scott_temp;
GRANT UPDATE chgdesc ON jobhist TO scott;
GRANT ALL ON dept TO GROUP scott_lnxdb;
REVOKE UPDATE DELETE ON emp FROM scott_lnxdb;
GRANT USAGE ON SCHEMA postgres TO scott;

索引(Indexes)

• 索引是提高性能的常用的方法
• Postgres 支持多种索引类型:
– B-tree (default)
– Hash – 只有当 WHERE 语句包含一个简单的等号(”=”)时使用 (缺点: 需要创建的时间长, 不如B-tree好)
– Index on Expressions – 当某个表达式(expression)经常被使用以提高查询速度的时候使用。插入和更新操作
会变慢。
– Partial Index – 只对满足WHERE条件的行进行索引 (WHERE语句不能包含索引列)。 查询也必须使用partial 

1
2
3
4
5
index 使用的那个WHERE 语句。
CREATE INDEX <name> on <table> (<column>);
CREATE INDEX <name> ON <table> USING HASH (<column>);
CREATE INDEX <name> on <table>(expression(<column(s)>));
CREATE INDEX <name> ON <table> (<column>) WHERE <where clause>;

流复制维护生产案例

主备切换

1.备库执行pg_ctl promote 提升成主库
2.原主库执行pg_rewind拉齐和新主库之间的时间线
3.如果主库中wal日志被超时清理,就需要使用pg_basebackup重做备库

流复制经验谈 1

需求1：一个数据库中一部分表要求同步复制,一部分表要求异步复制。
当主库已配置参数synchronous_standby_names = standby01 或 * 时
会话执行：set synchronous_commit = off;
当前会话可以取消同步复制，改为异步复制。

流复制经验谈 2

延迟备库需求
多个备库情况下可以将其中某一个或者几个设置成延迟备库,预防数据在主
库被删除后,可以在延迟备库中找回。
recovery_min_apply_delay = 1min #延迟1分钟

流复制经验谈 3

同步备库模式,可用性与一致性兼顾。
synchronous_standby_names=’ANY 2 (s1,s2,s3)’

流复制经验谈 4

主库上创建表空间,备库宕机

流复制经验谈 5

主库上的WAL被覆盖,导致备库不可用。
1.复制槽
2.wal_keep_segment

流复制经验谈 6

pg_basebackup卡住
在备份的开始时，需要向从中拿去备份的服务器写一个检查点。尤其在没
有使用选项–checkpoint=fast时，这可能需要一点时间，在其间
pg_basebackup看起来处于闲置状态。
有可能是checkpoint的阶段卡住了
想要不等待checkpoint直接开始备份，可以加上参数 -c, –
checkpoint=fast|spread