Pgsql常见问题

Pgsql常见问题系统管理问题3.1)我怎样能把 PostgreSQL 装在 /usr/local/pgsql 以外的地方？在运行 configure 时加上 -prefix 选项。 3.2)我如何控制来自其他主机的连接？缺省时，PostgreSQL 只允许通过 unix 域套接字或TCP/IP方式且来自本机的连接。 你只有在修改了配置文件postgresql.conf中的listen_addresses，且也在配置文件pg_hba.conf中打开了 主机为基础（ host-based ）的身份认证，并重新启动PostgreSQL，否则其他机器是不能与你的PostgreSQL服务器连接的。 3.3)我怎样调整数据库引擎以获得更好的性能？有三个主要方面可以提升PostgreSQL的潜能。 查询方式的变化 这主要涉及修改查询方式以获取更好的性能: 创建索引，包括表达式和部分索引； 使用COPY语句代替多个Insert语句； 将多个SQL语句组成一个事务以减少提交事务的开销； 从一个索引中提取多条记录时使用CLUSTER； 从一个查询结果中取出部分记录时使用LIMIT； 使用预编译式查询（Prepared Query)； 使用ANALYZE以保持精确的优化统计； 定期使用 VACUUM 或 pg_autovacuum 进行大量数据更改时先删除索引（然后重建索引） 服务器的配置 配置文件postgres.conf中的很多设置都会影响性能，所有参数的列表可见： Administration Guide/Server Run-time Environment/Run-time Configuration， 有关参数的解释可见： /varlena/GeneralBits/Tidbits/annotated_conf_e.html 和 /varlena/GeneralBits/Tidbits/perf.html。 硬件的选择 计算机硬件对性能的影响可浏览 http:/candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html 和 /PerfList/。 3.4)PostgreSQL 里可以获得什么样的调试特性？PostgreSQL 有很多类似 log_* 的服务器配置变量可用于查询的打印和进程统计，而这些工作对调试和性能测试很有帮助。 3.5)为什么在试图连接时收到“Sorry, too many clients”消息？这表示你已达到缺省100个并发后台进程数的限制，你需要通过修改postgresql.conf文件中的max_connections值来 增加postmaster的后台并发处理数，修改后需重新启动postmaster。 3.6)为什么要在升级 PostgreSQL 主要发布版本时做 dump 和 restore ？PostgreSQL 开发组对每次小的升级仅做了较少的修改，因此从 7.4.0 升级到 7.4.1 不需要 dump 和 restore。 但是主要的升级（例如从 7.3 到 7.4）通常会修改系统表和数据表的内部格式。 这些变化一般比较复杂，因此我们不维数据文件的向后兼容。 dump 将数据按照通用的格式输出，随后可以被重新加载并使用新的内部格式。 3.7)(使用PostgreSQL)我需要使用什么计算机硬件 ？由于计算机硬件大多数是兼容的，人们总是倾向于相信所有计算机硬件质量也是相同的。事实上不是， ECC RAM（带奇偶校验的内存），SCSI （硬盘）和优质的主板比一些便宜货要更加可靠且具有更好的性能。PostgreSQL几乎可以运行在任何硬件上， 但如果可靠性和性能对你的系统很重要，你就需要全面的研究一下你的硬件配置了。在我们的邮件列表上也有关于 硬件配置和性价比的讨论。 操作问题4.1)如何只选择一个查询结果的头几行？或是随机的一行？如果你只是要提取几行数据，并且你在执行查询中知道确切的行数，你可以使用LIMIT功能。 如果有一个索引与 ORDER BY中的条件匹配，PostgreSQL 可能就只处理要求的头几条记录， （否则将对整个查询进行处理直到生成需要的行）。如果在执行查询功能时不知道确切的记录数， 可使用游标(cursor)和FETCH功能。 可使用以下方法提取一行随机记录的： SELECT colsFROM tabORDER BY random()LIMIT 1 ;4.2)如何查看表、索引、数据库以及用户的定义？如何查看psql里用到的查询指令并显示它们？在psql中使用 dt 命令来显示数据表的定义，要了解psql中的完整命令列表可使用? ，另外，你也可以阅读 psql 的源代码 文件pgsql/src/bin/psql/describe.c，它包括为生成psql反斜杠命令的输出的所有 SQL 命令。你还可以带 -E 选项启动 psql， 这样它将打印出执行你在psql中所给出的命令的内部实际使用的SQL查询。PostgreSQL也提供了一个兼容SQL的INFORMATION SCHEMA接口， 你可以从这里获取关于数据库的信息。 在系统中有一些以pg_ 打头的系统表也描述了表的定义。 使用 psql -l 指令可以列出所有的数据库。 也可以浏览一下 pgsql/src/tutorial/syscat.source文件，它列举了很多可从数据库系统表中获取信息的SELECT语法。 4.3)如何更改一个字段的数据类型？在8.0版本里更改一个字段的数据类型很容易，可使用 ALTER TABLE ALTER COLUMN TYPE 。 在以前的版本中，可以这样做： BEGIN; ALTER TABLE tab ADD COLUMN new_col new_data_type; UPDATE tab SET new_col = CAST(old_col AS new_data_type); ALTER TABLE tab DROP COLUMN old_col; COMMIT;你然后可以使用VACUUM FULL tab 指令来使系统收回无效数据所占用的空间。 4.4)一行记录，一个表，一个库的最大尺寸是多少？下面是一些限制： 一个数据库最大尺寸？无限制（已存在有 32TB 的数据库）一个表的最大尺寸？32 TB一行记录的最大尺寸？1.6 TB一个字段的最大尺寸?1 GB一个表里最大行数？无限制一个表里最大列数？250-1600 （与列类型有关）一个表里的最大索引数量？无限制当然，实际上没有真正的无限制，还是要受可用磁盘空间、可用内存/交换区的制约。 事实上，当这些数值变得异常地大时，系统性能也会受很大影响。 表的最大尺寸 32 TB 不需要操作系统对大文件的支持。大表用多个 1 GB 的文件存储，因此文件系统尺寸的限制是不重要的。 如果缺省的块大小增长到 32K ，最大的表尺寸和最大列数还可以增加到四倍。 4.5)存储一个典型的文本文件里的数据需要多少磁盘空间？一个 Postgres 数据库（存储一个文本文件）所占用的空间最多可能需要相当于这个文本文件自身大小5倍的磁盘空间。 例如，假设有一个 100,000 行的文件，每行有一个整数和一个文本描述。 假设文本串的平均长度为20字节。文本文件占用 2.8 MB。存放这些数据的 PostgreSQL 数据库文件大约是 6.4 MB: 32 字节: 每行的头（估计值） 24 字节: 一个整数型字段和一个文本型字段 + 4 字节: 页面内指向元组的指针 - 60 字节每行 PostgreSQL 数据页的大小是 8192 字节 (8 KB)，则： 8192 字节每页 - = 136 行/数据页（向下取整） 60 字节每行 100000 数据行 - = 735 数据页（向上取整） 128 行每页 735 数据页 * 8192 字节/页 = 6,021,120 字节（6 MB）索引不需要这么多的额外消耗，但也确实包括被索引的数据，因此它们也可能很大。 空值NULL存放在位图中，因此占用很少的空间。 4.6)为什么我的查询很慢？为什么这些查询没有利用索引？并非每个查询都会自动使用索引。只有在表的大小超过一个最小值，并且查询只会选中表中较小比例的记录时才会采用索引。 这是因为索引扫描引起的随即磁盘存取可能比直接地读取表（顺序扫描）更慢。 为了判断是否使用索引，PostgreSQL必须获得有关表的统计值。这些统计值可以使用 VACUUM ANALYZE，或 ANALYZE 获得。 使用统计值，优化器知道表中有多少行，就能够更好地判断是否利用索引。 统计值对确定优化的连接顺序和连接方法也很有用。在表的内容发生变化时，应定期进行统计值的更新收集。 索引通常不用于 ORDER BY 或执行连接。对一个大表的一次顺序扫描，再做一个显式的排序通常比索引扫描要快。 但是，在 LIMIT 和 ORDER BY 结合使用时经常会使用索引，因为这只会返回表的一小部分。 实际上，虽然 MAX() 和 MIN() 并不使用索引，通过对 ORDER BY 和 LLIMIT 使用索引取得最大值和最小值也是可以的： SELECT colFROM tabORDER BY col DESC LIMIT 1;如果你确信PostgreSQL的优化器使用顺序扫描是不正确的，你可以使用SET enable_seqscan TO off指令， 然后再次运行查询，你就可以看出使用一个索引扫描是否确实要快一些。 当使用通配符操作，例如 LIKE 或 时，索引只能在特定的情况下使用： 字符串的开始部分必须是普通字符串，也就是说： o LIKE 模式不能以 % 打头。 o （正则表达式）模式必须以 打头。 字符串不能以匹配多个字符的模式类打头，例如 a-e。 大小写无关的查找，如 ILIKE 和 * 等不使用索引，但可以用 4.8 节描述的函数索引。 在做 initdb 时必须采用缺省的本地设置 C locale，因为系统不可能知道在非C locale情况时下一个最大字符是什么。 在这种情况下，你可以创建一个特殊的text_pattern_ops索引来用于LIKE的索引。 在8.0之前的版本中，除非要查询的数据类型和索引的数据类型相匹配，否则索引经常是未被用到，特别是对int2,int8和数值型的索引。 4.7)我如何才能看到查询优化器是怎样评估处理我的查询？参考 EXPLAIN 手册页。 4.8)我怎样做正则表达式搜索和大小写无关的正则表达式查找？怎样利用索引进行大小写无关查找？操作符 处理正则表达式匹配，而 * 处理大小写无关的正则表达式匹配。大写些无关的 LIKE 变种成为 ILIKE。 大小写无关的等式比较通常写做： SELECT * FROM tab WHERE lower(col) = abc;这样将不会使用标准的索引。但是可以创建一个可被利用的函数索引: CREATE INDEX tabindex ON tab (lower(col);4.9)在一个查询里，我怎样检测一个字段是否为 NULL ？我如何才能准确排序而不论某字段是否含 NULL 值？用 IS NULL 和 IS NOT NULL 测试这个字段，具体方法如下： SELECT * FROM tab WHERE col IS NULL;为了能对含 NULL字段排序，可在 ORDER BY 条件中使用 IS NULL和 IS NOT NULL 修饰符，条件为真 true 将比条件为假false 排在前面，下面的例子就会将含 NULL 的记录排在结果的上面部分： SELECT * FROM tab ORDER BY (col IS NOT NULL)4.10)各种字符类型之间有什么不同？类型内部名称说明VARCHAR(n)varchar指定了最大长度，变长字符串，不足定义长度的部分不补齐CHAR(n)bpchar定长字符串，实际数据不足定义长度时，以空格补齐TEXTtext没有特别的上限限制（仅受行的最大长度限制）BYTEAbytea变长字节序列（使用NULL也是允许的）charchar一个字符在系统表和在一些错误信息里你将看到内部名称。 上面所列的前四种类型是varlena（变长）类型（也就是说，开头的四个字节是长度，后面才是数据）。 于是实际占用的空间比声明的大小要多一些。 然而这些类型都可以被压缩存储，也可以用 TOAST 脱机存储，因此磁盘空间也可能比预想的要少。 VARCHAR(n) 在存储限制了最大长度的变长字符串是最好的。 TEXT 适用于存储最大可达 1G左右但未定义限制长度的字符串。 CHAR(n) 最适合于存储长度相同的字符串。 CHAR(n)会根据所给定的字段长度以空格补足（不足的字段内容）， 而 VARCHAR(n) 只存储所给定的数据内容。 BYTEA 用于存储二进制数据，尤其是包含 NULL 字节的值。这些类型具有相似的性能特性。 4.11.1)我怎样创建一个序列号/自动递增的字段？PostgreSQL 支持 SERIAL 数据类型。它在字段上自动创建一个序列和索引。例如： CREATE TABLE person ( id SERIAL, name TEXT );会自动转换为： CREATE SEQUENCE person_id_seq;CREATE TABLE person ( id INT4 NOT NULL DEFAULT nextval(person_id_seq),name TEXT);参考 create_sequence 手册页获取关于序列的更多信息。 4.11.2)我如何获得一个插入的序列号的值？一种方法是在插入之前先用函数 nextval() 从序列对象里检索出下一个 SERIAL 值，然后再显式插入。使用 4.11.1 里的例表，可用伪码这样描述： new_id = execute(SELECT nextval(person_id_seq);execute(INSERT INTO person (id, name) VALUES (new_id, Blaise Pascal);这样还能在其他查询中使用存放在 new_id 里的新值（例如，作为 person 表的外键）。 注意自动创建的 SEQUENCE 对象的名称将会是 _seq， 这里 table 和 serialcolumn 分别是你的表的名称和你的 SERIAL 字段的名称。 类似的，在 SERIAL 对象缺省插入后你可以用函数 currval() 检索刚赋值的 SERIAL 值，例如： execute(INSERT INTO person (name) VALUES (Blaise Pascal);new_id = execute(SELECT currval(person_id_seq);4.11.3)使用 currval() 会导致和其他用户的冲突情况（race condition）吗？不会。currval() 返回的是你本次会话进程所赋的值而不是所有用户的当前值。4.11.4)为什么不在事务异常中止后重用序列号呢？为什么在序列号字段的取值中存在间断呢？为了提高并发性，序列号在需要的时候赋予正在运行的事务，并且在事务结束之前不进行锁定， 这就会导致异常中止的事务后，序列号会出现间隔。 4.12)什么是 OID ？什么是 CTID ？PostgreSQL 里创建的每一行记录都会获得一个唯一的OID，除非在创建表时使用WITHOUT OIDS选项。 OID创建时会自动生成一个4字节的整数，所有 OID 在整个 PostgreSQL 中均是唯一的。 然而，它在超过40亿时将溢出， OID此后会出现重复。PostgreSQL 在它的内部系统表里使用 OID 在表之间建立联系。 在用户的数据表中，最好是使用SERIAl来代替OID 因为SERIAL只是保证在单个表中数据是唯一的，这样它溢出的可能性就非常小了， SERIAL8可用来保存8字节的序列号字段。 CTID 用于标识带着数据块（地址）和（块内）偏移的特定的物理行。 CTID 在记录被更改或重载后发生改变。索引入口使用它们指向物理行。 4.13)为什么我收到错误信息“ERROR: Memory exhausted in AllocSetAlloc()”？这很可能是系统的虚拟内存用光了，或者内核对某些资源有较低的限制值。在启动 postmaster 之前试试下面的命令： ulimit -d 262144limit datasize 256m取决于你用的 shell，上面命令只有一条能成功，但是它将把你的进程数据段限制设得比较高， 因而也许能让查询完成。这条命令应用于当前进程，以及所有在这条命令运行后创建的子进程。 如果你是在运行SQL客户端时因为后台返回了太多的数据而出现问题，请在运行客户端之前执行上述命令。 4.14)我如何才能知道所运行的 PostgreSQL 的版本？从 psql 里，输入 SELECT version();指令。 4.15)我如何创建一个缺省值是当前时间的字段？使用 CURRENT_TIMESTAMP： CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );4.16)我怎样进行 outer join （外连接）？PostgreSQL 采用标准的 SQL 语法支持外连接。这里是两个例子： SELECT *FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);或是SELECT *FROM t1 LEFT OUTER JOIN t2 USING (col);这两个等价的查询在 t1.col 和 t2.col 上做连接，并且返回 t1 中所有未连接的行（那些在 t2 中没有匹配的行）。 右外连接(RIGHT OUTER JOIN)将返回 t2 中未连接的行。 完全外连接（FULL OUTER JOIN）将返回 t1 和 t2 中未连接的行。 关键字 OUTER 在左外连接、右外连接和完全外连接中是可选的，普通连接被称为内连接（INNER JOIN）。