介绍
PGSQL
- 开源
- 关系型数据库
- 底层基于C实现
- BDS协议
版本选择
- 如果为了稳定的运行，推荐使用12.x版本
- 如果想体验新特性，推荐使用14.x版本
PGSQL允许跨版本升级，而且没有什么大问题
PGSQL国外使用的比较多，国内以MySQL为主
很多国产数据库基于PGSQL做的二次封装：比如华为GaussDB还有腾讯的Tbase等等
PGSQL因为开源，有很多数据迁移的工具，可以快速的从MySQL，SQLServer，Oracle平转到PGSQL中，比如pgloader
对比MySQL
| MySQL | PGSQL |
|---|---|
| 数据类型不够丰富 | 数据类型丰富 |
| 不支持序列概念，Sequence | 有序列的概念的 |
| 性能优化监控工具不是很多，定位问题的成本是比较高 | 插件丰富 |
| 主从复制没有一个官方的同步策略，同步问题难以解决。 | 支持主从复制的同步操作，可以实现数据的0丢失，PostgreSQL的MVCC实现和MySQL不大一样，一行数据会存储多个版本。最多可以存储40亿个事务版本 |
安装
推荐Linux，不推荐Windows安装
Linux的版本尽量使用7.x版本，最好是7.6或者是7.8版本
# 下载PGSQL的rpm包
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装客户端
sudo yum install postgresql12
# 安装服务端（可选）
sudo yum install -y postgresql12-server
# 数据库初始化并开机自启
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12
# 配置防火墙
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --reload
配置
安装成功，默认创建用户：postgres
配置和数据路径：/var/lib/pgsql/12/data
postgreSQL的核心文件，都属于postgres用户
远程连接
PostgreSQL默认情况下不支持远程连接
- MySQL给mysql.user追加用户，一般是采用grant的命令
- PostgreSQL要基于配置文件修改，才能制定用户是否可以远程连接
1、修改配置文件 ../data/pg_hba.conf，（用户级）
# 第一块
local：代表本地连接，host代表可以指定连接的ADDRESS
# 第二块
database编写数据库名，如果写all，代表所有库都可以连接
# 第三块
user编写连接的用户，可以写all，代表所有用户
# 第四块
address代表那些IP地址可以连接
# 第五块
method加密方式，这块不用过多关注，直接md5
# 允许任意地址的全部用户连接所有数据库
host all all 0.0.0.0/0 md5
2、修改配置配置在postgresql.conf（服务级）
默认PGSQL只允许localhost连接，修改为*，允许所有地址连接，或者指定放行的地址
重启服务生效
sudo systemctl restart postgresql-12
日志
默认情况下，只保存7天的日志，循环覆盖
# 代表日志是开启的。
logging_collector = on
# 日志存放的路径，默认放到当前目录下的log里
log_directory = 'log'
# 日志的文件名，默认是postgresql为前缀，星期作为后缀
log_filename = 'postgresql-%a.log'
# 默认一周过后，日志文件会被覆盖
log_truncate_on_rotation = on
# 一天一个日志文件
log_rotation_age = 1d
# 一个日志文件，没有大小限制
log_rotation_size = 0
基本命令
postgresql数据管理系统使用命令方式有两种:
\1. 内部命令,以反斜线开始 \ ,如: \l 显示所有数据库
\2. 标准SQL命令,以分号;或 \g 结束,可以使用多行
连接参数
# 切换用户并连接 - 在postgres用户下
su postgres
psql -U postgres # 快速登录(全部使用默认设置)
# 切换用户并连接 - 非postgres用户下
psql -h 127.0.0.1 -p 5432 -U postgres # 默认打开postgres数据库
psql -h 127.0.0.1 -p 5432 -d dba -U postgres # 打开dba数据库
Password for user postgres:
# 设置用户密码
ALTER USER postgres WITH PASSWORD 'postgres'
# 数据库命令
\l # 列出所有数据库
\c database # 选择database库
\dt # 查看所有表
\c interface # 切换数据库
select * from apps limit 1; # 查看某个库中某个表的记录
\encoding # 显示字符集
\q # 退出
\d table # 查看table表结构
\h # 显示 SQL 命令的说明
\h select # 精细显示SQL命令中的select命令的使用方法
\? # 显示 pgsql 命令的说明 (pgsql内部命令)
\password postgres # 重新设置用户postgres的密码,然后需要 \q退出后才生效
\du # 显示所有用户
\conninfo # 显示当前数据库和连接信息
\e # 进入记事本sql脚本编辑状态(输入批命令后关闭将自动在命令行中执行)
\di # 查看索引(要建立关联)
\prompt [文本] 名称 # 提示用户设定内部变数
\encoding [字元编码名称] # 显示或设定用户端字元编码
# 可以将存储过程写在文本文件中aaa.sql,然后在psql状态下:
\i aaa.sql # 将aaa.sql导入(到当前数据库)
\df # 查看所有存储过程（函数）
\df+ name # 查看某一存储过程
select version(); # 获取版本信息
select usename from pg_user; # 获取系统用户信息
drop User username # 删除用户
# 可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：
pg_dump drupal>/opt/Postgresql/backup/1.bak
SELECT pg_size_pretty(pg_database_size('mydatabase')); # 查看数据库占用磁盘空间大小
\help
General
\copyright show PostgreSQL usage and distribution terms
\g [FILE] or ; execute query (and send results to file or |pipe)
\h [NAME] help on syntax of SQL commands, * for all commands
\q quit psql
Query Buffer
\e [FILE] [LINE] edit the query buffer (or file) with external editor
\ef [FUNCNAME [LINE]] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
\db[+] [PATTERN] list tablespaces
\dc[S] [PATTERN] list conversions
\dC [PATTERN] list casts
\dd[S] [PATTERN] show comments on objects
\ddp [PATTERN] list default privileges
\dD[S] [PATTERN] list domains
\det[+] [PATTERN] list foreign tables
\des[+] [PATTERN] list foreign servers
\deu[+] [PATTERN] list user mappings
\dew[+] [PATTERN] list foreign-data wrappers
\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions
\dF[+] [PATTERN] list text search configurations
\dFd[+] [PATTERN] list text search dictionaries
\dFp[+] [PATTERN] list text search parsers
\dFt[+] [PATTERN] list text search templates
\dg[+] [PATTERN] list roles
\di[S+] [PATTERN] list indexes
\dl list large objects, same as \lo_list
\dL[S+] [PATTERN] list procedural languages
\dn[S+] [PATTERN] list schemas
\do[S] [PATTERN] list operators
\dO[S+] [PATTERN] list collations
\dp [PATTERN] list table, view, and sequence access privileges
\drds [PATRN1 [PATRN2]] list per-database role settings
\ds[S+] [PATTERN] list sequences
\dt[S+] [PATTERN] list tables
\dT[S+] [PATTERN] list data types
\du[+] [PATTERN] list roles
\dv[S+] [PATTERN] list views
\dE[S+] [PATTERN] list foreign tables
\dx[+] [PATTERN] list extensions
\l[+] list all databases
\sf[+] FUNCNAME show a function's definition
\z [PATTERN] same as \dp
Formatting
\a toggle between unaligned and aligned output mode
\C [STRING] set table title, or unset if none
\f [STRING] show or set field separator for unaligned query output
\H toggle HTML output mode (currently off)
\pset NAME [VALUE] set table output option
(NAME := {format|border|expanded|fieldsep|footer|null|
numericlocale|recordsep|tuples_only|title|tableattr|pager})
\t [on|off] show only rows (currently off)
\T [STRING] set HTML <table> tag attributes, or unset if none
\x [on|off] toggle expanded output (currently off)
Connection
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
connect to new database (currently "postgres")
\encoding [ENCODING] show or set client encoding
\password [USERNAME] securely change the password for a user
\conninfo display information about current connection
Operating System
\cd [DIR] change the current working directory
\timing [on|off] toggle timing of commands (currently off)
\! [COMMAND] execute command in shell or start interactive shell
Variables
\prompt [TEXT] NAME prompt user to set internal variable
\set [NAME [VALUE]] set internal variable, or list all if no parameters
\unset NAME unset (delete) internal variable
Large Objects
\lo_export LOBOID FILE
\lo_import FILE [COMMENT]
\lo_list
\lo_unlink LOBOID large object operations
用户操作
构建用户
# 区别就是create user默认有连接权限，create role没有，不过可以基于选项去设置
CREATE USER 名称 [ [ WITH ] 选项 [ ... ] ]
create role 名称 [ [ WITH ] 选项 [ ... ] ]
构建超级管理员用户
create user root with SUPERUSER PASSWORD 'root';
此时不能登录，该用户没有数据库，需要创建这个用户所属数据库
create database root;
psql -h 192.168.11.32 -p 5432 -U root -W # 再去登录
权限操作
可以看到PGSQL一个数据库中有多个schema，在每个schema下都有自己的相应的库表信息，权限粒度会比MySQL更细一些
在PGSQL中，权限的管理分为很多层：
server、cluster、tablespace级别：这个级别一般是基于pg_hba.conf去配置
database级别：通过命令级别操作，grant
namespace、schema级别：用的不多
对象级别：通过grant命令去设置
后面如果需要对database或者是对象级别做权限控制，直接基于grant命令去操作即可
\help grant # 查看grant命令
数据类型
PGSQL支持的类型特别丰富，大多数的类型和MySQL都有对应的关系
其他
删除重复记录
在PostgreSQL删除重复记录的表中table加一列rownum字段(id为table表中的主键) ，类型设置为serial类型即可，然后执行sql语句：
delete from deltest where rownum not in(
select max(rownum) from deltest
);
最后删除列rownum即可
图形化界面安装
图形化界面可以连接PGSQL的很多，Navicat（收费）
也可以直接使用PostgreSQL官方提供的图形化界面pgadmin（免费）
python连接脚本
import psycopg2
try:
# 数据库连接参数
params = {
"host": "127.0.0.1", # 数据库服务器地址
"database": "intelligence", # 数据库名称
"user": "postgres", # 默认用户
"password": "123456", # 默认用户的密码
"port": "5432" # PostgreSQL的默认端口
}
query = """
SELECT i.ip, ii.found_time, ii.update_time, t.name, t.threat_level
FROM ip i
JOIN ip_intelligence ii ON i.id = ii.ip_id
JOIN intelligence_tag it ON ii.id = it.intelligence_id
JOIN tag t ON it.tag_id = t.id
LIMIT 10;
"""
# 建立连接
conn = psycopg2.connect(**params)
# 创建游标对象
cur = conn.cursor()
# 执行SQL查询
cur.execute(query)
# 获取查询结果
results = cur.fetchall()
# 打印结果
for row in results:
# print(f"IP: {row[0]}, Found Time: {row[1]}, Update Time: {row[2]}, Name: {row[3]}, Threat Level: {row[4]}")
# datetime.datetime格式转换为时间戳格式
print(f"IP: {row[0]}, Found Time: {int(row[1].timestamp())}, Update Time: {int(row[2].timestamp())}, Name: {row[3]}, Threat Level: {row[4]}")
except (Exception, psycopg2.DatabaseError) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# 关闭数据库连接
if conn is not None:
conn.close()
