怎么用了PostgreSQL？
PostgreSQL，在国内应该是没有MySQL那么流行，但对它的介绍，通常是和Oracle比，我们也只在2个地方用了：
- Zabbix，一开始是MySQL，后来agent端出现很多unreachable，才用了PostgreSQL，但换数据库并没有解决问题，最后是采用了表分区才解决了
- 大数据，CDH这个Hadoop发行版，默认使用的是PostgreSQL 8.4（2009年发行），有几个数据库连接 ，长期占用CPU达50%，花了点时间去看，但最终也没有解决。而且版本太旧，目前搜到的资料，都不太适用
安装
官方提供了各系统，各版本的rpm包，直接yum或者rpm即可
rpm -ivh https://download.postgresql.org/pub/repos/yum/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-3.noarch.rpm
yum -y install libxslt uuid
yum -y install postgresql93-server postgresql93-contrib postgresql93-libs postgresql93
上面的安装可能会提示证书有问题，此时可以去看看yum仓库的地址，然后手动wget回来安装即可
grep baseurl /etc/yum.repos.d/pgdg-93-centos.repo
初始化和连接
# 初始化数据，数据库文件目录在/var/lib/pgsql/9.3/data/
/etc/init.d/postgresql-9.3 initdb
# 启动
/etc/init.d/postgresql-9.3 start
连接
# 以系统用户连接
# 连接，切换到postgres，然后就可以直接连接了
su - postgres
psql
# 通过用户密码连接，只能通过环境变量指定密码
PGPASSWORD=xxx /usr/bin/psql -U zabbix
# 把连接信息写到文件
cat /root/.pgpass
localhost:5432:*:test_user:user_password
# 通过指定用户名即可连接
psql -U test_user
认证
它和MySQL一样，也是基于用户和主机进行认证的，配置文件如下，就是和数据在一起
# 修改此文件
# 需要以root身份进行reload：/etc/init.d/postgresql-9.3 reload
/var/lib/pgsql/9.3/data/pg_hba.conf
文件内容
# "local" is for Unix domain socket connections only
# 类型 数据库 用户 地址 认证方式
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host replication replica 192.168.8.198/32 md5
认证类型
local的话，就是不指定主机名或者指定主机名为localhost，它默认使用unix socket进行连接/tmp/.s.PGSQL.5432，5432会随着监听端口改变，例如改为7432，则socket文件名就会变成/tmp/.s.PGSQL.7432
其他的就是通过host:port这样的tcp方式访问
认证方式
数据库和用户没什么要说的，这里讲下认证方式
- ident，就是用PostgreSQL所在机器的系统用户，例如上面提到的切换到postgres用户，当系统用户和PostgreSQL里的用户名一致，就可以连接
- peer，和ident一样，只是它是在客户端系统进行识别
- md5，就是密码认证
- trust，只验证主机，不验证用户名和密码
- password，这个没去了解
使用
以下指令为输入psql进入PostgreSQL控制台输入
\?：
打印PostgreSQL帮助
\h：
打印SQL帮助
\c :
可以通过\c databasename 切换数据库
\l :
数据库列表的详细信息
\q :
退出数据库
\d或\dt:
该数据库下所有表的详细信息
\d tablename:
该表的表结构
\df
打印函数
\df+ partition_every_day
打印partition_every_day的信息
\du
列出所有用户
alter user postgres with password 'xxx'
修改用户名密码
以下指令在系统终端执行
# 创建数据库
createdb test_db
# 备份数据库，dumpall为备份所有库
pg_dump/pg_dumpall
# 备份结构
pg_dump --schema-only
# 执行sql文件
psql -f xxx.sql
schema
目前我无法说清楚这是什么东西，大概理解为是一种隔离机制吧，默认只有$user和public，如果你的表有分区，那么你虽然在打印的时候可以看到，但你要drop分区的时候，则会提示找不到
# 显示Schema的搜索路径
SHOW search_path;
# 从上面的输出可以看出，默认没有partitions，我们加入，如果不加入，在drop table的时候，就会提示does not exist
SET search_path TO "$user",partitions,public;
# 可以drop了，CASCADE的作用是把关联的触发条件删除，这触发条件，其实就是什么数据应该插入此分区的，现在已经是2017了，自然不会有2015的数据需要插入，所以可以放心删除
drop table history_uint_2015_01_11 CASCADE;
# 当然也可以不用设置，直接加partitions前缀
drop table partitions.history_uint_2015_01_11 CASCADE;
性能
- shared_buffers，缓存最近查询的数据页，设置为系统内存的25%，但不应该超过8G
- effective_cache_size，单次查询可用缓存，如果是专门的PostgreSQL服务器，设置为系统总内存一半
- work_mem，用于排序、表扫描、哈希关联等操作的最大内存量，如果是多用户并发进行简单查询，那么此设置很小也没有问题，如果执行复杂查询，可以调大
- maintenance_work_mem，用于vaccum（回收内存和磁盘空间）操作的内存总量。不应该大于1G。
一些工具
pgAdmin
是PostgreSQL官方的图形界面管理客户端
- 版本3可以管理PostgreSQL 8.4
- 版本4可以管理PostgreSQL 9.x
pgsniff
从网络流量中提取SQL，测试支持9.3版本，8.4版本不支持
# 最后一个-，意思是输出到终端
pgsniff -d eth1 -l -
还有一个叫pgshark，没测试
书籍
- PostgreSQL 9 Admin Cookbook，有中文版
- PostgreSQL即学即用
- PostgreSQL 9.0性能调校，一定不要看中文版
