PostgreSQL 9.4官方文档：安装配置与高级特性详解

需积分: 9 60 浏览量更新于2024-07-16 收藏 7.96MB PDF 举报

PostgreSQL 9.4官方文档英文版是一份详尽的指南，由全球PostgreSQL开发团队编撰，涵盖了这款强大的开源关系型数据库系统的关键特性和技术细节。这份文档对于想要深入学习和使用PostgreSQL（通常简称为PG）的用户来说具有很高的价值，无论你是初学者还是经验丰富的开发者。该文档的核心内容包括： 1. **安装与配置**：文档详细介绍了PostgreSQL 9.4的安装流程，从下载、依赖项设置到配置服务器环境，以及如何管理数据库实例，确保系统的稳定运行。 2. **数据类型和基本操作**：提供了关于PostgreSQL的数据模型，如表格、列、索引等基础知识，以及SQL查询语言的基础操作，如SELECT、INSERT、UPDATE和DELETE语句。 3. **函数与存储过程**：深入讲解了内置函数的使用，以及如何编写自定义存储过程，以实现更复杂的逻辑处理和业务流程自动化。 4. **扩展功能**：文档涵盖了许多扩展模块，如PL/pgSQL（用于编写高级SQL函数）、全文搜索、并发控制、备份恢复策略等，展示了PostgreSQL的强大功能和灵活性。 5. **安全性与权限管理**：强调了数据库安全的重要性，包括用户认证、角色管理、访问控制以及数据加密等方面的知识。 6. **错误处理与调试**：提供了应对常见问题和错误的解决方法，以及性能调优和故障排查的技巧。 7. **法律责任声明**：文档明确指出，PostgreSQL的使用和分发在某些条件下受到版权保护，并包含了免责声明，特别是关于损害赔偿和责任豁免的条款。这份官方文档是PostgreSQL 9.4用户不可或缺的参考资料，它不仅为新用户提供了入门路径，也对已有经验的开发人员提供了深入理解和实践的平台。通过阅读和应用文档中的内容，用户可以熟练掌握PostgreSQL，并在实际项目中充分发挥其高效、稳定和可扩展的优势。

27.1. Standard Unix Tools ....................................................................................................639

27.2. The Statistics Collector................................................................................................640

27.2.1. Statistics Collection Conﬁguration .................................................................640

27.2.2. Viewing Collected Statistics ...........................................................................640

27.2.3. Statistics Functions .........................................................................................654

27.3. Viewing Locks.............................................................................................................657

27.4. Dynamic Tracing .........................................................................................................657

27.4.1. Compiling for Dynamic Tracing.....................................................................657

27.4.2. Built-in Probes ................................................................................................657

27.4.3. Using Probes ...................................................................................................666

27.4.4. Deﬁning New Probes ......................................................................................667

28. Monitoring Disk Usage............................................................................................................669

28.1. Determining Disk Usage .............................................................................................669

28.2. Disk Full Failure..........................................................................................................670

29. Reliability and the Write-Ahead Log.......................................................................................671

29.1. Reliability ....................................................................................................................671

29.2. Write-Ahead Logging (WAL) .....................................................................................673

29.3. Asynchronous Commit................................................................................................673

29.4. WAL Conﬁguration .....................................................................................................675

29.5. WAL Internals .............................................................................................................678

30. Regression Tests.......................................................................................................................679

30.1. Running the Tests ........................................................................................................679

30.1.1. Running the Tests Against a Temporary Installation ......................................679

30.1.2. Running the Tests Against an Existing Installation ........................................680

30.1.3. Additional Test Suites.....................................................................................680

30.1.4. Locale and Encoding.......................................................................................681

30.1.5. Extra Tests.......................................................................................................681

30.1.6. Testing Hot Standby........................................................................................681

30.2. Test Evaluation ............................................................................................................682

30.2.1. Error Message Differences..............................................................................682

30.2.2. Locale Differences..........................................................................................683

30.2.3. Date and Time Differences .............................................................................683

30.2.4. Floating-Point Differences..............................................................................683

30.2.5. Row Ordering Differences ..............................................................................684

30.2.6. Insufﬁcient Stack Depth..................................................................................684

30.2.7. The “random” Test..........................................................................................684

30.2.8. Conﬁguration Parameters................................................................................685

30.3. Variant Comparison Files ............................................................................................685

30.4. TAP Tests.....................................................................................................................686

30.5. Test Coverage Examination.........................................................................................686

IV. Client Interfaces ..............................................................................................................................687

31. libpq - C Library ......................................................................................................................689

31.1. Database Connection Control Functions.....................................................................689

31.1.1. Connection Strings..........................................................................................695

31.1.1.1. Keyword/Value Connection Strings ...................................................696

31.1.1.2. Connection URIs................................................................................696

31.1.2. Parameter Key Words .....................................................................................697

xvi

31.2. Connection Status Functions .......................................................................................701

31.3. Command Execution Functions ..................................................................................705

31.3.1. Main Functions ...............................................................................................705

31.3.2. Retrieving Query Result Information .............................................................712

31.3.3. Retrieving Other Result Information ..............................................................716

31.3.4. Escaping Strings for Inclusion in SQL Commands ........................................717

31.4. Asynchronous Command Processing..........................................................................720

31.5. Retrieving Query Results Row-By-Row .....................................................................724

31.6. Canceling Queries in Progress.....................................................................................725

31.7. The Fast-Path Interface................................................................................................726

31.8. Asynchronous Notiﬁcation..........................................................................................727

31.9. Functions Associated with the COPY Command .........................................................728

31.9.1. Functions for Sending COPY Data...................................................................729

31.9.2. Functions for Receiving COPY Data................................................................729

31.9.3. Obsolete Functions for COPY ..........................................................................730

31.10. Control Functions ......................................................................................................732

31.11. Miscellaneous Functions ...........................................................................................733

31.12. Notice Processing ......................................................................................................735

31.13. Event System.............................................................................................................737

31.13.1. Event Types...................................................................................................737

31.13.2. Event Callback Procedure.............................................................................739

31.13.3. Event Support Functions...............................................................................740

31.13.4. Event Example..............................................................................................741

31.14. Environment Variables ..............................................................................................743

31.15. The Password File .....................................................................................................745

31.16. The Connection Service File .....................................................................................745

31.17. LDAP Lookup of Connection Parameters.................................................................746

31.18. SSL Support...............................................................................................................747

31.18.1. Client Veriﬁcation of Server Certiﬁcates ......................................................747

31.18.2. Client Certiﬁcates..........................................................................................748

31.18.3. Protection Provided in Different Modes .......................................................748

31.18.4. SSL Client File Usage...................................................................................750

31.18.5. SSL Library Initialization .............................................................................751

31.19. Behavior in Threaded Programs................................................................................751

31.20. Building libpq Programs............................................................................................752

31.21. Example Programs.....................................................................................................753

32. Large Objects ...........................................................................................................................763

32.1. Introduction .................................................................................................................763

32.2. Implementation Features .............................................................................................763

32.3. Client Interfaces...........................................................................................................763

32.3.1. Creating a Large Object..................................................................................764

32.3.2. Importing a Large Object................................................................................764

32.3.3. Exporting a Large Object................................................................................765

32.3.4. Opening an Existing Large Object..................................................................765

32.3.5. Writing Data to a Large Object.......................................................................766

32.3.6. Reading Data from a Large Object .................................................................766

32.3.7. Seeking in a Large Object...............................................................................766

32.3.8. Obtaining the Seek Position of a Large Object...............................................767

xvii

32.3.9. Truncating a Large Object ..............................................................................767

32.3.10. Closing a Large Object Descriptor ...............................................................768

32.3.11. Removing a Large Object .............................................................................768

32.4. Server-side Functions ..................................................................................................768

32.5. Example Program ........................................................................................................769

33. ECPG - Embedded SQL in C...................................................................................................775

33.1. The Concept.................................................................................................................775

33.2. Managing Database Connections ................................................................................775

33.2.1. Connecting to the Database Server.................................................................775

33.2.2. Choosing a Connection...................................................................................777

33.2.3. Closing a Connection......................................................................................778

33.3. Running SQL Commands............................................................................................778

33.3.1. Executing SQL Statements .............................................................................778

33.3.2. Using Cursors..................................................................................................779

33.3.3. Managing Transactions...................................................................................780

33.3.4. Prepared Statements........................................................................................780

33.4. Using Host Variables ...................................................................................................781

33.4.1. Overview.........................................................................................................782

33.4.2. Declare Sections..............................................................................................782

33.4.3. Retrieving Query Results................................................................................783

33.4.4. Type Mapping .................................................................................................784

33.4.4.1. Handling Character Strings ................................................................784

33.4.4.2. Accessing Special Data Types............................................................785

33.4.4.2.1. timestamp, date......................................................................785

33.4.4.2.2. interval ...................................................................................786

33.4.4.2.3. numeric, decimal....................................................................787

33.4.4.3. Host Variables with Nonprimitive Types ...........................................788

33.4.4.3.1. Arrays ....................................................................................788

33.4.4.3.2. Structures ...............................................................................789

33.4.4.3.3. Typedefs.................................................................................790

33.4.4.3.4. Pointers ..................................................................................791

33.4.5. Handling Nonprimitive SQL Data Types........................................................791

33.4.5.1. Arrays.................................................................................................791

33.4.5.2. Composite Types ................................................................................793

33.4.5.3. User-deﬁned Base Types ....................................................................795

33.4.6. Indicators.........................................................................................................796

33.5. Dynamic SQL..............................................................................................................797

33.5.1. Executing Statements without a Result Set ....................................................797

33.5.2. Executing a Statement with Input Parameters ................................................797

33.5.3. Executing a Statement with a Result Set ........................................................798

33.6. pgtypes Library............................................................................................................799

33.6.1. The numeric Type ...........................................................................................799

33.6.2. The date Type..................................................................................................802

33.6.3. The timestamp Type........................................................................................805

33.6.4. The interval Type ............................................................................................809

33.6.5. The decimal Type............................................................................................810

33.6.6. errno Values of pgtypeslib ..............................................................................810

33.6.7. Special Constants of pgtypeslib......................................................................811

xviii

33.7. Using Descriptor Areas ...............................................................................................812

33.7.1. Named SQL Descriptor Areas ........................................................................812

33.7.2. SQLDA Descriptor Areas ...............................................................................814

33.7.2.1. SQLDA Data Structure.......................................................................815

33.7.2.1.1. sqlda_t Structure ....................................................................815

33.7.2.1.2. sqlvar_t Structure...................................................................816

33.7.2.1.3. struct sqlname Structure ........................................................817

33.7.2.2. Retrieving a Result Set Using an SQLDA .........................................817

33.7.2.3. Passing Query Parameters Using an SQLDA.....................................819

33.7.2.4. A Sample Application Using SQLDA ...............................................820

33.8. Error Handling.............................................................................................................826

33.8.1. Setting Callbacks ............................................................................................826

33.8.2. sqlca ................................................................................................................828

33.8.3. SQLSTATE vs. SQLCODE..................................................................................829

33.9. Preprocessor Directives ...............................................................................................833

33.9.1. Including Files ................................................................................................833

33.9.2. The deﬁne and undef Directives .....................................................................834

33.9.3. ifdef, ifndef, else, elif, and endif Directives....................................................835

33.10. Processing Embedded SQL Programs.......................................................................835

33.11. Library Functions ......................................................................................................836

33.12. Large Objects.............................................................................................................837

33.13. C++ Applications ......................................................................................................839

33.13.1. Scope for Host Variables...............................................................................839

33.13.2. C++ Application Development with External C Module .............................840

33.14. Embedded SQL Commands ......................................................................................843

ALLOCATE DESCRIPTOR ......................................................................................843

CONNECT..................................................................................................................845

DEALLOCATE DESCRIPTOR .................................................................................848

DECLARE ..................................................................................................................849

DESCRIBE .................................................................................................................851

DISCONNECT ...........................................................................................................853

EXECUTE IMMEDIATE...........................................................................................855

GET DESCRIPTOR ...................................................................................................856

OPEN ..........................................................................................................................859

PREPARE ...................................................................................................................861

SET AUTOCOMMIT .................................................................................................863

SET CONNECTION ..................................................................................................864

SET DESCRIPTOR....................................................................................................865

TYPE...........................................................................................................................867

VAR.............................................................................................................................870

WHENEVER ..............................................................................................................871

33.15. Informix Compatibility Mode ...................................................................................873

33.15.1. Additional Types...........................................................................................873

33.15.2. Additional/Missing Embedded SQL Statements ..........................................873

33.15.3. Informix-compatible SQLDA Descriptor Areas...........................................874

33.15.4. Additional Functions.....................................................................................877

33.15.5. Additional Constants.....................................................................................886

33.16. Internals .....................................................................................................................887

xix

34. The Information Schema..........................................................................................................890

34.1. The Schema .................................................................................................................890

34.2. Data Types ...................................................................................................................890

34.3. information_schema_catalog_name ..................................................................891

34.4. administrable_role_authorizations..............................................................891

34.5. applicable_roles...................................................................................................892

34.6. attributes................................................................................................................892

34.7. character_sets.......................................................................................................896

34.8. check_constraint_routine_usage ....................................................................897

34.9. check_constraints ................................................................................................898

34.10. collations..............................................................................................................898

34.11. collation_character_set_applicability ...................................................899

34.12. column_domain_usage ..........................................................................................899

34.13. column_options.....................................................................................................900

34.14. column_privileges ..............................................................................................900

34.15. column_udt_usage.................................................................................................901

34.16. columns ....................................................................................................................902

34.17. constraint_column_usage .................................................................................907

34.18. constraint_table_usage....................................................................................908

34.19. data_type_privileges........................................................................................908

34.20. domain_constraints ............................................................................................909

34.21. domain_udt_usage.................................................................................................910

34.22. domains ....................................................................................................................910

34.23. element_types .......................................................................................................914

34.24. enabled_roles .......................................................................................................917

34.25. foreign_data_wrapper_options.......................................................................917

34.26. foreign_data_wrappers......................................................................................918

34.27. foreign_server_options....................................................................................918

34.28. foreign_servers...................................................................................................919

34.29. foreign_table_options......................................................................................919

34.30. foreign_tables.....................................................................................................920

34.31. key_column_usage.................................................................................................920

34.32. parameters..............................................................................................................921

34.33. referential_constraints .................................................................................924

34.34. role_column_grants ............................................................................................925

34.35. role_routine_grants ..........................................................................................926

34.36. role_table_grants ..............................................................................................926

34.37. role_udt_grants...................................................................................................927

34.38. role_usage_grants ..............................................................................................928

34.39. routine_privileges ............................................................................................929

34.40. routines..................................................................................................................929

34.41. schemata..................................................................................................................935

34.42. sequences................................................................................................................936

34.43. sql_features .........................................................................................................937

34.44. sql_implementation_info .................................................................................938

34.45. sql_languages .......................................................................................................939

34.46. sql_packages .........................................................................................................939

34.47. sql_parts................................................................................................................940

剩余3194页未读，继续阅读

alizee123_1

粉丝: 0
资源: 9

PostgreSQL 9.4官方文档：安装配置与高级特性详解

postgresql中英文对照文档

PostgreSQL-9.5-US官方文档英文版

mysql-oracle-postgree的比较文档-英文版[原创文档]

postgresql-9.4-A4.pdf

postgresql-9.4.1209.jar中文-英文对照文档.zip

postgresql-9.4.26-1-windows-32.exe

postgresql-9.4.1207.jre6.jar

postgresql-9.4.1212.jre6.jar

postgresql-9.4.1209.jre6.jar

postgresql-9.4.1211.jre7.jar

最新资源