PostgreSQL数据库使用技巧

1.log日志配置
PostgreSQL的系统日志和报错日志等，默认是直接输出了，没有写入到文件，如果你希望能查看这些日志，需要修改postgresql.conf，如下：

log_destination = ’stderr’
logging_collector = on
log_directory = ‘pg_log’
log_min_duration_statement = 5000

上面这些参数的意思就是，把pgsql的日志到输出到pgsql数据目录的pg_log目录下，同时超过5000毫秒的操作将记录。

 
2.用户认证和监听

PostgreSQL的用户认证是通过pg_hba.conf来设置，默认都是trust，即本地帐户能直接登录。显然不安全，你可修改如下：

# “local” is for Unix domain socket connections only
local   all         all                                 md5
# IPv4 local connections:
host    all         all         127.0.0.1/32            md5
host    all         all         192.168.1.0/24          md5

上面这些参数的意思是，本地连接pgsql需要密码md5认证，192.168.1.xx 网段允许连接，密码也需要通过md5认证。
如果需要从其他客户端连接，还需要修改postgresql.conf的liston_addresses参数，这个参数和oracle的监听的作用类似。
listen_addresses = ‘*’

3.密码文件

通过2设置成md5验证后，虽然在安全方面有了提高，但是同时会造成一些麻烦，比如你的shell脚本需要使用psql能直接连接，这种情况你可以使用密码文件。通过在用户的根目录下，创建一个.pgpass文件。并将权限设置为0600。

文件的格式如下：
hostname:port:database:username:password

4.psql常用容易混淆的命令

通过psql登录到PostgreSQL，有些命令可能和其他数据库有些区别，这里和mysql对比下，介绍几个你很可能搞混的命令：
pgsql             mysql
/c                use
/q                quit
/d                desc

5.系统参数配置

PostgreSQL在安装的时候，并没有要求修改系统默认配置，那是因为其默认的内存等参数很小，不会超过系统的默认配置。例如你加大shared_buffers等参数的值时，基本都会超过默认系统参数。这时你需要修改这些配置
编辑 /etc/sysctl.conf，加入以下内容：
kernel.shmall = 3145728
kernel.shmmax = 12884901888
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
存盘退出后运行这个指令检查是否设置正确：
#sysctl -p

6.过程语言
PostgreSQL的存储过程和函数统一就叫function，当然传统意思上存储过程和函数的区别也就是，一个必须返回值，一个不需要。
目前在标准的 PostgreSQL 发布里有四种过程语言可用：
PL/pgSQL，
PL/Tcl，
PL/Perl，
PL/Python。
默认是没有安装的，例如你需要安装
CREATE [TRUSTED] [PROCEDURAL] LANGUAGE ‘language-name’
    HANDLER handler_function_name
    [VALIDATOR validator_function_name] ;

7.autovacuum

vacuum命令类似于Mysql里的optimize等命令，当对大表进行这样的操作，需要注意对业务的影响，应该是在业务比较空闲的时候进行。
从 PostgreSQL 8.1 开始，系统带有一个额外可选的 autovacuum 守护进程，用于自动执行 VACUUM 和 ANALYZE 命令。可以在postgresql.conf里进行配置。

8.获得对象创建语句

Oracle：
select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'OWNER’) from dual;

Mysql：
show create OBJECT OBJECT_NAME；

PostgreSQL获得对象创建语句比较折腾，需要分别处理。
8.1 表
貌似只能用 pg_dump 导出来，例如导出test数据库的t1表的结构
pg_dump -s -U test -t t1

8.2 过程和函数
pg_get_functiondef(oid)

8.3 触发器
pg_get_triggerdef(oid)

8.4 索引
pg_get_indexdef(oid)

8.5 视图
pg_get_viewdef(oid)

oid可以通过相应的系统表查出来，例如function可在pg_proc查到，注意oid是个隐藏列。

9.database、schema、objects

在PostgreSQL里的database和Mysql里的database不一样，它有点和Oracle的实例类似，每个database之间是独立的，在database和object之间

还有个schema。每个database可以创建多个schema,每个shcema又可以创建多个object。下列简单列出之间的关系：

PostgreSQL: database?shcma?object
Mysql     : database?object
Oracle    : instance?schema?object 

10.pg_dump和pg_dumpall

这2个工具的区别在于一个是备份单个database，一个备份所有的database。用惯了mysqldump，pg_dumpall很容易被忽略，为何不把这2个工具集成到一个呢？

总的来说，个人觉得PostgreSQL很多方面和Oracle是比较相似的，特别多进程的方式，相比Mysql来说要健壮的多。不过国内的PostgreSQL应用貌似很少，相关中文的资料也不多。一般来说java对应oracle,php对应mysql，python对应postgresql，是否能说python在国内的普及还是很不够呢？