Postgresql8.3数据库在linux编译安装说明

有些部分是我自己写的，有些摘抄的，感谢开源社区的支持

一，编译

安装软件包：

sudo apt-get install binutils cpp fetchmail flex gcc libarchive-zip-perl libc6-dev libcompress-zlib-perl libdb4.3-dev libpcre3 libpopt-dev lynx m4 make ncftp nmap perl perl-modules unzip zip zlib1g-dev autoconf automake1.9 libtool bison autotools-dev g++ build-essential

安装libncurses5-dev：

sudo apt-get install libncurses5-dev

 

编译安装：

adduser postgres
tar -vxf postgresql*.tar.gz
./configure --prefix=/usr/local/postgresql
make
make install
mkdir /usr/local/postgresql/data
chown postgres /usr/local/postgresql/data
chown -R postgres /usr/local/postgresql 
su  postgres

初始化数据库：
/usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data
运行数据库服务器：
/usr/local/postgresql/bin/postmaster -D /usr/local/postgresql/data>logfile 2>&1 &
创建一个数据库：
/usr/local/postgresql/bin/createdb test
在本机上运行客户端进行管理：
/usr/local/postgresql/bin/psql test

 

postgresql设置：

根据需要对Postgresql的配置文件Postgresql.conf进行修改，该文件在数据目录/usr/local/postgresql/data下，
比如你需要用局域网或通过互联网远程管理Postgresql进行管理，就需要把# listen_address=’localhost’改为
listen_address='*'，记住要去掉注释，这点用过MySQL的用户比较好理解，

接下来修改Postgresql的配置文件pg_hba.conf，该文件在数据目录/usr/local/postgresql/data下，在文件后面加一句：
host   all all  192.168.0.0/24   password

就是允许192.168.0.0网段的所有用户通过密码来访问数据库。
 
重新启动postgresql服务器：
/usr/local/postgresql/bin/pg_ctl stop -D /usr/local/postgresql/data

停止原来的服务器
/usr/local/postgresql/bin/postmaster -i -D /usr/local/postgresql/data >logfile 2>&1 &

二,Postgresql访问认证

安装postgresql是一件很简单的事，但是当我第一次安装了postgresql以后，它的安全设置曾经让我困扰过一阵，现在我将以我的经历为线索来讲述如何配置postgresql的访问认证，postgresql的版本是7.3.1，老一点的版本在配置文件上会有稍许不同，请自己注意区别。另外，在阅读中，请注意区分数据库用户和系统用户的区别，以免混淆。
postgresql的访问认证配置主要涉及到两个主要的配置文件：postgresql.conf和pg_hba.conf。
postgresql.conf包含了许多的选项，这些选项控制了postgresql.conf的方方面面，中间影响访问认证的选项是：

unix_socket_group
设置 Unix 域套接字的组所有人，（套接字的所有权用户总是启动 postmaster 的用户）与 UNIX_SOCKET_PERMISSIONS 选项一起使用可以给这种套接字类型增加额外的访问控制机制，缺省时是一个空字串，也就是使用当前用户的缺省的组， 这个选项只能在服务器启动时设置。

unix_socket_permissions
给 Unix 域套接字设置访问权限，Unix 域套接字使用通常的 Unix 文件系统权限集。可选的值可以是一个 chmod 和 umask 系统调用可以接受的数字模式。（要使用客户化的八进制格式，该数字必须以 0 （零）开头）
缺省权限是 0777，意即任何人都可以联接，合理的选则可能是0770 （只有用户和组， 参阅UNIX_SOCKET_GROUP）和 0700 （只有用户）。（请注意对于 Unix 套接字而言，实际上只有写权限有意义，而且 也没有办法设置或者取消读或执行权限）
这个选项只能在服务器启动时设置。

pg_hba.conf是设置访问认证的主要文件，格式为每条记录一行，每行指定一条访问认证。设定一条访问认证包含了7个部分：连接方式（type）、数据库（database）、用户名（user）、ip地址（ip-address）、子网掩码（ip-mask）、认证方法（authentication method）、认证配置（authentication-option），以下是这7个部分的详细说明：

连接方式（type）
连接方式共有三种：local、host、hostssl

local
这条记录匹配通过 Unix 域套接字进行的联接企图， 没有这种类型的记录，就不允许 Unix 域套接字的联接。

host
这条记录匹配通过 TCP/IP 网络进行的联接尝试，请注意，除非服务器是 带着 -i 选项或者打开了 postgresql.conf 里面的 tcpip_socket 配置参数集启动的，否则 TCP/IP 联接是被禁止掉的。

hostssl
这条记录匹配通过在 TCP/IP 上进行的 SSL 联接企图， host 记录可以匹配 SSL 和非 SSL 的联接企图， 但 hostssl 记录需要 SSL 联接。

数据库（database）
声明记录所匹配的数据库。值 all 表明该记录匹配所有数据库， 值 sameuser表示如果被请求的数据库和请求的用户同名，则匹配。 samegroup 表示请求的用户必须是一个与数据库同名的组中的成员。 在其他情况里，这就是一个特定的 PostgreSQL 的名字。我们可以通过用逗号分隔的方法声明多个数据库。 一个包含数据库名的文件可以 通过对该文件前缀 @ 来声明．该文件必需和 pg_hba.conf 在同一个目录。

用户名（user）
为这条记录声明所匹配的 PostgreSQL 用户，值 all 表明它匹配 于所有用户。否则，它就是特定 PostgreSQL 用户的名字，多个用户名可以通过用逗号分隔的方法声明，组名字 可以通过用 + 做组名字前缀来声明。一个包含用户名的文件可以 通过在文件名前面前缀 @ 来声明，该文件必需和 pg_hba.conf 在同一个目录。

ip地址（ip-address）
子网掩码（ip-mask）
这两个字段包含标准的点分十进制表示的 IP地址/掩码值。 （IP地址只能用数字的方式声明，而不能用域名或者主机名）它们俩放在一起，声明了这条记录匹配的客户机的 IP 地址。 准确的逻辑是：
(actual-IP-address xor IP-address-field) and IP-mask-field
对于要匹配的记录必需为零。
如果连接方式是host或者hostssl的话，这两项必须指定，否则可以不填。

认证方法（authentication method）
trust
无条件地允许联接，这个方法允许任何可以与PostgreSQL 数据库联接的用户以他们期望的任意 PostgreSQL 数据库用户身份进行联接，而不需要口令。

reject
联接无条件拒绝，常用于从一个组中"过滤"某些主机。

md5
要求客户端提供一个 MD5 加密的口令进行认证，这个方法是允许加密口令存储在pg_shadow里的唯一的一个方法。

crypt
类似 md5 方法，只是用的是老式的 crypt 加密认证， 用于 7.2 以前的客户端，对于 7.2 以及以后的客户端，我们建议使用 md5。

password
和"md5"一样，但是口令是以明文形式在网络上传递的，我们不应该在不安全的网络上使用这个方式。

krb4
用 Kerberos V4 认证用户，只有在进行 TCP/IP 联接的时候才能用。（译注：Kerberos，"克尔波洛斯"，故希腊神话冥王哈得斯的多头看门狗。Kerberos 是 MIT 开发出来的基与对称加密算法的认证协议和/或密钥 交换方法，其特点是需要两个不同用途的服务器，一个用于认证身份，一个用于通道两端用户的密钥交换。同时 Kerberos 对网络时间同步 要求比较高，以防止回放攻击，因此通常伴随 NTP 服务。）

krb5
用 Kerberos V5 认证用户．只有在进行 TCP/IP 联接的时候才能用。（译注：Kerberos V5 是上面 V4 的改良，主要是不再依赖 DES 算法， 同时增加了一些新特性。）

ident
获取客户的操作系统名（对于 TCP/IP 联接，用户的身份是通过与运行在客户端上的 ident 服务器联接进行判断的，对于本地联接，它是从操作系统获取的。） 然后检查一下，看看用户是否允许以要求的数据库用户进行联接， 方法是参照在 ident 关键字后面声明的映射。
/pg_ident.conf 如果你使用了 sameuser 映射，那么假设用户名 是相等的。如果没有声明这个关键字，则在  文件中找出映射名。如果这个文件里包含一条记录标识着ident提供的用户名 和请求的 PostgreSQL 用户名的映射， 那么联接被接受。
对于本地联接，只有在系统支持Unix域套接字信任证的情况下 才能使用（目前是 Linux， FreeBSD， NetBSD， 和 BSD/OS）。

pam
使用操作系统提供的可插入的认证模块服务 （Pluggable Authentication Modules） （PAM）来认证。


认证配置（authentication-option）
这个可选的字段的含义取决与选择的认证方法。


了解了以上的内容以后，我们可以开始设置自己的访问认证。和mysql一样，postgresql默认安装的访问认证是不安全的，当我第一次安装好我的postgresql以后，我发现只要输入`psql -U pgsql -d template1`就可以不需要任何密码的进入我的数据库，并且使用的是pgsql用户（数据库的最高权限），即使在我使用ALTER USER为我的数据库用户添加了密码以后情况也没有得到改善，经过一番查找，我发现我的pg_hba.conf内容如下：

local all all trust
host all all 0.0.0.0 0.0.0.0 trust

这说明无论在本地还是通过tcp/ip，任何人都可以不加任何限制的使用任何他想用的身份访问我的数据库，于是我为所有的访问都使用了md5认证方法，我的pg_hba.conf变为：

local all all md5
host all all 0.0.0.0 0.0.0.0 md5

事情似乎得到了解决，任何人想访问我的数据库都需要通过密码这一关，我的数据库安全了。但是当我有一天重新启动计算机的以后发现我的 postgresql并没有被正常的启动，在终端前我发现启动到postgresql服务启动脚本时，提示需要输入密码，原来在设置了md5认证方式以后我的pgsql需要密码才能够启动服务，为了不每次启动都跑到终端前去输入一次密码，我的pgsql用户的认证方法必须为trust：

local pgsql all trust
local all all md5
host all all 0.0.0.0 0.0.0.0 md5

在随后的检测中我发现，虽然通过网络得到了保护，但是如果在本地通过Unix域套接字进行连接的话，任何人都还是可以使用`psql -U pgsql -d template1`的方式轻松的进入我的数据库，这样做显而易见不是我想要的结果，现在我需要unix_socket_permissions的协助，当我把这个项设置为0700以后，就只有套接字的所有人，即系统用户pgsql可以使用套接字进行连接，另外如果我的数据库有几个管理员需要最高权限的话，0770也是一个选择，不过暂时只有我一个管理员，所以我选择了0700，现在我的pg_hba.conf改变为：

local pgsql all trust
host all all 0.0.0.0 0.0.0.0 md5

结合了unix_socket_permissions以后，现在只有系统用户pgsql可以无任何限制的连接（当然，伟大的root例外），并且我也不用每次启动的时候都跑到终端前去输入一次密码了，而其他人只能通过网络连接，即使登录到了本地计算机上，其他用户也只能使用`psql -U xx -d template1 -h 127.0.0.1`的方式连接，这种连接是受到密码认证的保护的，这一切正是我想要的。

以上就是我的设置过程，我们还可以使用pam等认证方法实现更加复杂的访问认证，不过那些内容已经不在本文的讨论范围以内了。

本文引用了来自http://laser.zhengmai.com.cn/翻译的postgresql中文手册中的内容，在此声明并表示感谢
三,postgresql备份存储

您是否遇到过因为不小心而删除了某个数据库表，或者是存放数据库的磁盘损坏了的情况呢？如果碰到这种情况，我相信您会觉得非常的沮丧，因为您努力了几个星期的工作成果可能就付之东流了。和任何包含珍贵数据的东西一样，PostgreSQL数据库也应该经常备份。如果您将数据存放在 PostgreSQL 表中，您就可以定时的进行数据库备份，从而避免发生上面的悲剧。PostgreSQL 自带有内置工具来执行备份工作，而且在系统出现损坏或是意外时，您可以通过这些工具进行“回滚”，并通过以前保存的快照将系统恢复到其初始的状态。


1. 综述;

PostgreSQL数据库从备份 PostgreSQL 数据有三种完全不同的方法：

SQL 转储

文件系统级别备份

在线备份

每种备份都有自己的优点和缺点，下面主要介绍SQl转储的方法;


2. 数据备份;

SQL 转储的方法是创建一个文本文件，这个文本里面都是 SQL 命令，当把这个文件回馈给服务器时，将重建与转储时状态一样的数据库。


2.1 pg_dump;

PostgreSQL 自带的内置备份工具叫做 pg_dump。这个工具是通过一系列的SQL语句读取某个指定的数据库并复制其中的内容，以它作为快照并用于日后的数据恢复。客户端到服务器端的连接是用于执行备份工作。这些备份文件就是前面所讲的快照并可以用于日后的数据恢复。而保证客户端与服务器端的连接是执行备份所必需的。这条命令的基本用法是：

pg_dump dbname > outfile

注：在进行上述工作之前，首先请确保您具有登录到服务器并访问您将要备份的数据库或表的权限。您可以通过使用PostgreSQL 命令行客户端——psql，进行服务器登录。在客户端中输入主机名（-h），用户名（-u）和密码（-p），以及数据库名，然后就可以校验您是否被授权访问。

使用 pg_dump 非常的简单——只需要在命令提示符后面输入将要导出的数据库名就可以进行备份工作了，如下例所示（根据您自己的安装路经更改PostgreSQL 路径）：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/pg_dump -D -h localhost -U xiaop(用户名) mydb(数据库名称) > mydb.bak

通过上面的命令就会创建一个名为 mydb.bak的文件，文件中记录了用于恢复数据库的SQL命令。

正如您所见，pg_dump 把结果输出到标准输出。 我们下面就可以看到这样做有什么好处。

pg_dump 是一个普通的 PostgreSQL 客户端应用（尽管是个相当聪明的东西。）这就意味着您可以从任何可以访问该数据库的远端主机上面进行备份工作。 但是请记住 pg_dump 不会以任何特殊权限运行。具体说来， 就是它必须要有您想备份的表的读权限，因此，实际上您几乎总是要成为数据库超级用户。

要声明 pg_dump 应该以哪个用户身份进行联接，使用命令行选项 -h host 和 -p port。缺省主机是本地主机或您的环境变量PGHOST声明的值。类似，缺省端口是环境变量PGPORT或（如果它不存在的话）编译好了的缺省值。（服务器通常有相同的缺省，所以还算方便。）

和任何其他 PostgreSQL 客户端应用一样， pg_dump 缺省时用与当前操作系统用户名同名的数据库用户名进行联接。要覆盖这个名字，要么声明 -U 选项， 要么设置环境变量PGUSER。 请注意 pg_dump 的联接也和普通客户应用一样要通过客户认证机制。

由 pg_dump 创建的备份在内部是一致的， 也就是说，在pg_dump运行的时候对数据库的更新将不会被转储。 pg_dump 工作的时候并不阻塞其他的对数据库的操作。 （但是会阻塞那些需要排它锁的操作，比如 VACUUM FULL。）

注：: 如果您的数据库结构依赖于 OID （比如说用做外键），那么您必须告诉 pg_dump 把 OID 也倒出来。 要倒 OID，可以使用 -o 命令行选项。 缺省时也不会转储"大对象"。如果您使用大对象，请参考 pg_dump 的命令手册页。


2.2 pg_dumpall;

如果您希望对整个系统中所有的数据库进行备份的话（而不是只对某一个数据库进行备份），您可以使用命令pg_dumpall 而不是pg_dump。执行这个命令可以对PostgreSQL 所能识别的所有的数据库（包括其自身的系统数据库）备份到一个文件中。下面给出了一个使用实例：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/pg_dumpall -D -h localhost -U xiaop(用户名) > all.bak

这样就会将localhost的所有数据库备份到all.bak文件中了;


2.3 计划任务;

为了保证您的备份时刻保持更新，您可以通过往cron table中加入pg_dump或者是pg_dumpall命令来定期执行备份工作。这里给出了两个cron entries的例子。第一个是在每天凌晨3点对test数据库进行备份，而第二个是在每个星期五的晚上9点对所有的数据库进行备份：

xiaop@xiaop-laptop:~$ 0 3 * * * /usr/lib/postgresql/8.2/bin/pg_dump -D -h localhost -U xiaop(用户名) mydb(数据库名称) > /home/xiaop/mydb.bak0 21 * *
xiaop@xiaop-laptop:~$ 5 /usr/lib/postgresql/8.2/bin/pg_dumpall -D -h localhost -U xiaop(用户名) > /home/xiaop/all.bak


3. 从转储中恢复


3.1 用pg_dump恢复;

从备份中恢复数据的工作比执行备份甚至更简单——您所要做的就是通过执行备份文件中的SQL命令来对数据库进行恢复。如果您是使用pg_dump对某一个数据库进行了备份，那么备份中就会有CREATE TABLE 的语句来对源表进行复制。当然，您首先要新创建一个空数据库来存放这些数据表。您可以使用createdb 这个工具来完成这一步工作，这个工具也是PostgreSQL 套件中的一部分：
xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/createdb mydb(数据库名称)

现在您就可以执行备份文件中的SQL命令来对数据库进行恢复了， pg_dump 生成的文本文件可以由 psql 程序读取。 从转储中恢复的常用命令格式是:

psql dbname < infile

如下例所示：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/psql -h localhost -U xiaop(用户名) -d mydb(数据库名称) < mydb.bak


3.2 用pg_dumpall恢复;

如果您是使用pg_dumpall对所有的数据库进行备份的，就没有必要先新建一个数据库，因为备份文件中已经包含了完成CREATE DATABASE工作的相关的调用。在这里，只需要在psql命令行客户端中输入对应的备份文件就可以了，而不需要指定目标数据库：

xiaop@xiaop-laptop:~$ /usr/lib/postgresql/8.2/bin/psql -h localhost -U xiaop(用户名 ) < all.bak

一旦数据恢复完成后，您就可以登录到服务器并查看到已恢复的数据。


3.3 ANALYZE;

一旦完成恢复，在每个数据库上运行 ANALYZE 是明智的举动， 这样优化器就有有用的统计数据了。您总是可以运行 vacuumdb -a -z 来 VACUUM ANALYZE 所有数据库；这个等效于手工运行 VACUUM ANALYZE;


4. 处理大数据库;


4.1 输出大数据库;

因为 PostgreSQL 允许表的大小大于您的系统允许的最大文件大小， 可能把表转储到一个文件会有问题，因为生成的文件很可能比您的系统允许的最大文件大。 因为 pg_dump 输出到标准输出，您可以用标准的 Unix 工具绕开这个问题：
使用压缩的转储. 使用您熟悉的压缩程序，比如说 gzip。

xiaop@xiaop-laptop:~$ pg_dump mydb(数据库名) | gzip > mydbBACK.gz


4.2 恢复大数据库;

用下面命令恢复：
xiaop@xiaop-laptop:~$ createdb mydbNEW(新数据库名)
xiaop@xiaop-laptop:~$ gunzip -c mydbBACK.gz | psql mydbNEW

或者
xiaop@xiaop-laptop:~$ cat mydbBACK.gz | gunzip | psql mydbNEW


4.3 使用 split;


4.3.1 分割;

split 命令允许您用下面的方法把输出分解成操作系统可以接受的大小。 有关split的用法可以在《文件的切分split和结合工具cat 介绍》中查询。比如，让每个块大小为 1 兆字节：
xiaop@xiaop-laptop:~$ pg_dump dbname | split -b 1m - filename


4.3.2 合并;

分割后可以用下面的命令恢复：
xiaop@xiaop-laptop:~$createdb dbname
xiaop@xiaop-laptop:~$cat filename* | psql dbname


5. 关于本文;

有关PostgreSQl数据库备份和恢复的另外两个方法“文件系统级别的备份”和“在线备份”，我们以后再讨论，本文大部分资料都是参照中文文档，目的是让兄弟们查找方便一些，详细的东西在中文文档都有，多谢各位弟兄们指点 ：）
四,影响postgresql性能的主要参数

本人现在开发的所有项目都使用postgresql，应用下来对它很是满意，现就影响postgresql性能的几个重要参数介绍如下，希望对PG的初学者有所帮助，如果你在实际应用中遇到什么问题，可给我留言，我们一起解决：

    PG的配置文件是数据库目录下的postgresql.conf文件，8.0以后的版本可支持K，M，G这样的参数，只要修改相应参数后重新启动PG服务就OK了。

 

    shared_buffers：这是最重要的参数，postgresql通过shared_buffers和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在shared_buffers中。通常设置为实际RAM的10％是合理的，比如50000(400M)

 

    work_mem： 在pgsql 8.0之前叫做sort_mem。postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem查不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置为实际RAM的2% -4%，根据需要排序结果集的大小而定，比如81920(80M)

 

    effective_cache_size：是postgresql能够使用的最大缓存，这个数字对于独立的pgsql服务器而言应该足够大，比如4G的内存，可以设置为3.5G(437500)

 

    maintence_work_mem：这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给maintence_work_mem大的内存，比如512M(524288)

 

    max_connections： 通常，max_connections的目的是防止max_connections * work_mem超出了实际内存大小。比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2％的内存，则会导致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系。

 

 

Python 连接 PostgreSQL

 

使用 PyGreSQL 来连接 PostgreSQL

PyGreSQL 是一个开放原始码的 Python 模组，让 Python 可以与 PostgreSQL 资料库连接。

PyGreSQL 分成两个模组，pg(classic" PyGreSQL interface） 和 pgdb（DB-API 2.0 compliant interface），以下說明为 pg 模组。


PyGreSQL 官方首页

下载页面

另外 import pg 需要 libpq.dll 这个档案，这个档案是在 PostgreSQL Client 中，所以要先安装 PostgreSQL Client 如果不想安装则到 PostgreSQL 官方下载 postgresql-8.2.3-1-binaries-no-installer.zip，然后解开来，把 libpq.dll, comerr32.dll, libintl-2.dll, libiconv-2.dll, krb5_32.dll 复制到 C:/WINDOWS/system32/ 资料夹中。

 

测试程式码：

import pg

conn = pg.connect(dbname='DbForTest',host='digit.idv.tw', user='tester',passwd='123')

 

for obj in conn.query("select * from public.user").dictresult():

print obj

 

conn.close()

 

posted on 2010-03-22 11:35  reck for zhou  阅读(319)  评论(0)  编辑  收藏  举报

导航