PostgreSQL 数据库
PostgreSQL 数据库
PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)ORDBMS（对象关系数据库系统）是面向对象技术与传统的关系数据库相结合的产物
ORDBMS 术语
在我们开始学习 PostgreSQL 数据库前，让我们先了解下 ORDBMS 的一些术语：
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
- 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
- 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
- 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键：外键用于关联两个表。
- 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
- 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
postgres 用户管理
\du 查看数据库用户
创建新用户：
create user 用户名 with password '******';
postgres 常用操作
\h #查看所有的sql关键字
\? #命令行操作的帮助
\d #查看当前schema 中所有的表 （支持正则\d '*post*'）
\q #退出pg命令行
\d #schema.table 查看表的结构
\x #横纵显示切换
\dT+ #显示扩展类型相关属性及描述
\dx #显示已安装的扩展插件
\l #列出所有的数据库 （支持正则\l '*post*'）
\c #切换数据库
\timing #显示执行时间
查看存储过程
SELECT prosrc FROM pg_proc WHERE proname = 'ff_lan_geom';
查看建表语句
pg_dump -U username databasename -W -s -t tablename
-s, --schema-only 只转储模式, 不包括数据
-t, --table=TABLE 只转储指定名称的表
postgres 数据库备份
-F, --format=c|d|t|p output file format (custom, directory, tar,导出文件的格式 plain text (default))
-f, --file=FILENAME output file or directory name导出后保存的文件名
-s, --schema-only dump only the schema, no data只转储模式, 不包括数据(不导出数据)
-a, --data-only dump only the data, not the schema只导出数据，不包括模式
-t, --table=TABLE dump the named table(s) only只转储指定名称的表
-c, --clean clean (drop) database objects before recreating在重新创建之前，先清除（删除）数据库对象
1、备份数据库结构
su - postgres
pg_dump -Fc -s -f testdbschema.sql testdb（库）
2、备份数据库数据
su - postgres
pg_dump -Fc -a -f testdbdata.sql testdb
3、备份数据库结构和数据
su - postgres
pg_dump -Fc -f testdbschemadata.sql testdb
4、备份数据库中指定表结构
pg_dump -Fc -s -t citycode -f citycode_schema.sql testdb
5、备份数据库中指定表数据
pg_dump -Fc -a -t citycode -f citycode_data.sql testdb
6、备份数据库中指定表（结构和数据）
pg_dump -Fc -t citycode -f citycode_schemadata.sql testdb
postgres 恢复数据库
恢复一个SQL备份文件，如遇任何错误则立即停止恢复：
psql -U postgres --set ON_ERROR_STOP=ON -f myglobals.sql
--set ON_ERROR_STOP=ON
1、创建新数据库testdb
su - postgres
createdb testdb;
2、 恢复数据结构（only schema）
su - postgres
pg_restore -s -d testdb testdbschema.sql
3、恢复数据库数据（only data）
su - postgres
pg_restore -a -d testdb testdbdata.sql
4、恢复数据库结构和数据（schema and data）
su - postgres
pg_restore -d testdb testdbschemadata.sql
PostgreSQL 日志配置
#vim data/postgresql.conf
#### 3.1 连接设置
listen_addresses (string)
这个参数只有在启动数据库时，才能被设置。它指定数据库用来监听客户端连接的TCP/IP地址。默认是值是*,如果被设成localhost，表示数据库只能接受本地的客户端连接请求，不能接受远程的客户端连接请求。
#### 3.2 数据库运行日志配置参数
# 启动日志收集， 这是一个后台进程，抓取发送到stderr的日志消息，并会将他们重定向到日志文件。
logging_collector = on
# 日志输出路径，可以是自定义绝对路径或相对于数据目录 PGDATA 的相对路径
log_directory = 'log'
# 文件名，可以带上格式字符串
log_filename = 'postgresql-%a.log'
# 当生成新的文件时，文件名已存在，则覆盖同名旧文件名
log_truncate_on_rotation = on
#设置日志记录内容，log_statement：none, ddl, mod, and all 默认是 none
# None表示不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP语句，
# mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等
# all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句，但会对数据库性能产生较大影响，生产环境不建议配置此值
log_statement = mod
log_rotation_size (integer)
这个参数只能在postgresql.conf文件中被设置。它决定何时创建一个新的数据库日志文件。单位是KB。默认值是10240。如果一个日志文件写入的数据量超过log_rotation_size的值，数据库将创建一个新的日志文件。如果它的值被设为0，该参数将不起任何作用。
log_min_error_statement (string)
控制日志中是否记录导致数据库出现错误的SQL语句。合法的取值是DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING、ERROR、 LOG、FATAL和PANIC，每个级别都包含排在它后面的所有级别。默认值是ERROR。只有超级用户才能修改这个参数。
DEBUG1..DEBUG5 报告详细的调试信息。
INFO 报告用户可能需要的信息。
NOTICE 报告对用户有用的信息。
WARNING 报告警告信息。
ERROR 报告错误信息。
LOG 报告对数据库管理员有用的信息, 例如，检查点操作统计信息。
FATAL 报告导致当前会话被终止的错误信息。
PANIC 报告导致整个数据库被关闭的错误信息。
# 以上设置完成之后，可以记录到 update、drop 等，但是并不会记录 select 语句，加上 log_min_duration_statement = 0 才可以记录到select
log_min_duration_statement=0
Python 操作 PostgreSQL 数据库
安装Python模块
pip3 install python-psycopg2
pip3 install psycopg2-binary
连接
#!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database="testdb", user="postgres", password="pass123", host="127.0.0.1", port="5432")
print("Opened database successfully")