PostgreSQL备忘

Learning PostgreSQL

1. PostgreSQL简介

"POSTGRES pioneered many concepts that only became available in some commercial database systems much later. " Quoted From PostgreSQL's Document.

PosgreSQL是一个拥有悠久历史的关系数据库系统，其最早的历史可以追述到1977年BSD的Ingres项目。到1986年到由Michael Stonebraker领导的由美国国防部高级项目研究所(DARPA)、美国陆军研究所(APO)和美国国家自然科学基金等资助的POSTGRES项目开始，发展到1994年的Postgres95，直到现在的PostgreSQL。近几十年来PostgreSQL一直沿着其既定的roadmap向前发展。

PostgreSQL是目前最强大的自由软件关系数据库管理系统。首先，PostgreSQL 的特性覆盖了SQL-2/SQL-92和SQL-3；其次，它包括了目前世界上最丰富的数据类型的支持；另外，PostgreSQL 全功能的自由软件数据库，很长时间以来，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统、数据完整性检查等先进特性的唯一一种自由软件的数据库管理系统；并且，PostgreSQL在索引类型的支持、其他类型对象的支持（存储过程、数据域、游标、触发器、函数和外部调用等）和数据表分区类型支持（http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html）处于领先地位。

最关键的是PostgreSQL拥有一支非常活跃的开发队伍，目前稳定的提交代码人员近50人，而且在众多黑客的努力下，PostgreSQL的各方面的质量日益提高。并且活跃的开发社区使PostgreSQL的文档和相关支持非常到位，在遇到问题并清楚的描述给PostgreSQL的邮件列表，通常会在几小时之内就能得到满意的回复。

2. PostgreSQL系统特性介绍

2.1. PostgreSQL本身的系统限制

单个数据库最大尺寸	无限制，目前已知有超过32TB的案例
单个表的最大尺寸	32TB
一行记录最大尺寸	400GB
一个表里最大记录行数	无限制
一个表里最大列数	250-1600，与列类型有关
一个表里最大索引个数	无限制

2.2. 关系数据库特性支持

复杂查询
外键
触发器
试图
事务完整性
多版本并行控制

2.3. 可扩展性

基于PosgreSQL的开放性和完善的API接口，很容易对其加入自定义的一些东西：
新的数据类型
新的函数
新的运算符
聚合函数
索引类型
操作数据库的过程语言

2.4. 完善的周边软件支持

2.4.1. 图形化管理工具:

* pgAdmin III - 跨平台管理工具
* PhpPgAdmin - 基于web的PostgreSQL管理工具

2.4.2. 应用程序访问数据库接口:

* DBD::Pg - Perl Driver
* JDBC - JDBC Driver
* libpqxx - C++ API
* Npgsql - .Net Data Provider
* ODBCng - ODBC Driver
* PgOleDb - OLE-DB Driver
* pgtclng - Next generation interface for TCL
* Pgtcl - Tcl binding library
* Pgin - Tcl interface to PostgreSQL written in Tcl
* psqlODBC - ODBC Driver
* PyGreSQL - Python interface for PostgreSQL
* psycopg - Another Python interface for PostgreSQL
* postgresql-sdbc - OpenOffice.org PostgreSQL Driver

2.4.3. Server-side Procedural Languages:

* PL/Java - Java procedural language using in-process JNI-based integration
* PL/php - PHP-based procedural language
* PL/psm - ANSI SQL compliant language for stored procedures based on current plpgsql environment.
* pl/R - Procedural language based on the R statistical language
* pl-ruby - Ruby based procedural language
* PL/scheme - Schema based procedural language
* PL/sh - procedural language handler for PostgreSQL that allows you to write stored procedures in a shell of your choice.

2.4.4. 附加软件支持:

* OpenFTS (Open Source Full Text Search Engine) - 提供在线索引和相关度排名基于PostgreSQL的全文搜索引擎。
* PL/proxy - 由PL language实现的PostgreSQL分区支持
* Slony1 - 主从库复制系统
* PGCluster - 提供主从复制和负载均衡的PostgreSQL集群方案
* PostGIS - 支持GIS的PostgreSQL
* pgpool-II - 提供连接池、复制、负载均衡和并行查询

2.4.5. 运行报表:

* TrafficObjects - Fetches data from remote databases (currently PostgreSQL and Oracle) and stores it internally in PostgreSQL. This data is then used to create real-time, streaming charts.
更多的第三方支持见： http://pgfoundry.org/

3. PostgreSQL安装配置

3.1 FreeBSD 6.2上安装PostgreSQL 8.3.6

Step 1. 更新FreeBSD Ports树，可能耗时较长

	#portsnap fetch && portsnap extract
	

Step 2. 进入PosgreSQL 8.3.6的ports目录下，并配置安装选项

	#cd /usr/ports/databases/postgresql83-server && make config
	

选择 NLS OPTIMIZED_CFLAGS THREADSAFE INDATE
Step 3. Ports编译安装

	#make all install clean
	

Step 4. 配置参数 编辑/etc/rc.conf.local，加入以下内容

	postgresql_enable="YES"
	postgresql_data="/usr/local/pgsql/data"
	postgresql_flags="-w -s -m fast"
	postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C -W -A password"
	postgresql_class="postgres"
	

Step 5. 初始化PostgreSQL

	#/usr/local/etc/rc.d/postgresql initdb 
	

并输入superuser密码
Step 6. 启动PostgreSQL服务daemon

	#/usr/local/etc/rc.d/postgresql start
	

Step 7. 创建一个普通用户, test_user

	#createuser --no-superuser --no-createdb --no-createrole --login --connection-limit=200 --pwprompt --password -U pgsql test_user 
	

Step 8. 创建一个database, test_db，并把其属主设为test_user

	#createdb --encoding utf-8 --owner test_user -U pgsql test_db
	

Step 9. 通过psql访问test_db

	#psql -U test_user test_db
	

3.2 Debian/Ubuntu Linux安装PostgreSQL 8.3.6

Step 1. 升级apt

	#apt-get update
	

Step 2. 安装postgresql-8.3

	#apt-get install postgresql-8.3
	

Step 3. 建立db文件所在目录，并赋予权限给postgres用户

	#mkdir -p /var/db/postgresql
        #chown -R postgres:postgres /var/db/postgresql
	

Step 4. 修改/etc/postgresql/8.3/main/postgresql.conf，把data_directory、hda_file改为以下内容：

	data_directory = '/var/db/postgresql/'
	hba_file = '/etc/postgresql/8.3/main/pg_hba.conf'
	

Step 5. 修改/etc/postgresql/8.3/main/pg_hba.conf，使之只有以下三行：

	local   all         postgres                          ident sameuser
	local   all         all                               password
	host    all         all         127.0.0.1/32          password
	

Step 6. su为postgres用户身份

	# su root
	# su postgres
	

Step 7. 初始化数据库，并设定superuser密码

	# /usr/lib/postgresql/8.3/bin/initdb --encoding=utf-8 --locale=en_US.UTF-8 -W -A password --username=postgres -D /var/db/postgresql/
	

Step 8. 启动

	# su root
	# /etc/init.d/postgresql-8.3 start
	

Step 9. 创建test_user

	# createuser --no-superuser --no-createdb --no-createrole --login --connection-limit=200 --pwprompt --password -U postgres test_user
	

Step 10. 创建test_db数据库

	# createdb --encoding utf-8 --owner test_user -U postgres test_db
	

Step 10. 连接测试

	# psql -h localhost -U test_user test_db #在任意用户身份下
	

4. 服务器管理

4.1 数据库管理常用命令

4.2 数据库备份

4.2.1 pg_dump 备份

对于小数据量的数据可以采用pg_dump来进行完整的数据库备份，包括表结构和表内数据。需要注意的是pg_dump 工作的时候并不阻塞其它的对数据库的操作(但是会阻塞那些需要排它锁的操作，比如 VACUUM FULL)。

pg_dump -h localhost -U test_user test_db  | gzip > db-test.gz

从备份恢复

# createdb --encoding utf-8 --owner test_user -U pgsql test_db
# gunzip -c db-test_db.tar.gz  | psql -h localhost -U pgsql test_db

4.2.2 直接的文件系统备份

可以直接tar包拷贝或者文件系统的snapshot保存PostgreSQL用于存放数据库数据的文件。需要注意的是此时postgreSQL必须完全的关闭。

4.2.3 在线备份以及即时恢复(PITR)

5. 软件开发

6. SQL介绍

7. 数据库优化

7.1 系统优化

7.1.1 FreeBSD优化

Step 1. 使用最新的FreeBSD 7.0， 并采用ULE调度器

Step 2. 优化内核参数（假定机器为4GB物理内存运行于AMD 64版）

	# cat /boot/loader.conf
	kern.ipc.semmni=256
	kern.ipc.semmns=512
	kern.ipc.semmnu=256
	kern.ipc.shmmni=8192
	kern.maxproc=4500

	# cat /etc/sysctl.conf

	#512MB
	kern.ipc.shmmax=536870912
	#512MB/4096(page size)
	kern.ipc.shmall=131072
	kern.ipc.shm_allow_removed=1
	kern.ipc.shm_use_phys=1

	kern.ipc.semmap=256
	#kern.ipc.nmbclusters=131072
	kern.ipc.somaxconn=5000

	kern.maxfiles=65536
	kern.maxfilesperproc=10000

	vfs.ufs.dirhash_maxmem=4194304

	net.inet.tcp.sendspace=65535
	net.inet.tcp.recvspace=65535
	net.inet.udp.maxdgram=65535
	
	net.inet.tcp.keepinit=7500
	net.local.stream.sendspace=65535
	net.local.stream.recvspace=65535

	net.inet.tcp.msl=4000
	net.inet.icmp.icmplim=8
	net.inet.icmp.icmplim_output=1

	

Step 3. 打开UFS Softupdate（需要进入单用户模式操作）

	# tunefs -n enable /filesystem
	

7.1.3 GNU/Linux优化

7.1.4 系统硬件优化

7.1.1 GNU/Linux优化

8. 高可靠性话题

9. 集群

10. 参考资料

0. PostgreSQL官方网站：http://www.postgresql.org/

1. PostgreSQL Summary：http://en.wikipedia.org/wiki/PostgreSQL

2. Comparison of relational database management systems: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

3. PostgreSQL FAQ: http://www.postgresql.org/docs/faqs.FAQ.html

4. PostgreSQL Documentation: http://www.postgresql.org/docs/