寻觅生命中的那一片浅草......

PostgreSQL简介

怎么用了PostgreSQL?

PostgreSQL,在国内应该是没有MySQL那么流行,但对它的介绍,通常是和Oracle比,我们也只在2个地方用了:

  1. Zabbix,一开始是MySQL,后来agent端出现很多unreachable,才用了PostgreSQL,但换数据库并没有解决问题,最后是采用了表分区才解决了
  2. 大数据,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

目前我无法说清楚这是什么东西,大概理解为是一种隔离机制吧,默认只有$userpublic,如果你的表有分区,那么你虽然在打印的时候可以看到,但你要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性能调校,一定不要看中文版
2018年九月
« 2月    
 12
3456789
10111213141516
17181920212223
24252627282930