PostgreSQL 9.6.11 官方手册

需积分: 7 112 浏览量更新于2024-07-18 收藏 8.71MB PDF 举报

"PostgreSQL 9.6英文说明文档，官方发布，内容详尽" PostgreSQL是一种高度可扩展的开源关系数据库管理系统（RDBMS），以其强大的功能和稳定性而备受赞誉。文档“postgresql-9.6 manual”是针对PostgreSQL 9.6版本的官方详细指南，由PostgreSQL全球开发团队编撰并维护。这个版本号9.6.11意味着这是9.6主线版本的第11次更新，通常包括错误修复和性能改进。文档覆盖了PostgreSQL的各个方面，包括但不限于以下几个核心知识点： 1. **安装与配置**：详述了在各种操作系统上安装PostgreSQL的步骤，以及如何配置服务器以满足特定需求，如设置数据存储位置、调整内存使用和网络连接等。 2. **SQL语言支持**：全面介绍PostgreSQL对SQL标准的实现，包括数据类型、查询语句（如SELECT、INSERT、UPDATE和DELETE）、事务控制、视图、索引、函数和过程等。 3. **安全性与权限**：阐述了用户管理、角色系统、访问控制列表（ACL）和加密选项，帮助管理员确保数据安全。 4. **备份与恢复**：讨论了不同的备份策略，如pg_dump和pg_restore工具，以及如何进行热备份和流复制以实现高可用性。 5. **并行查询与性能优化**：解释了9.6版中的并行查询特性，以及如何通过统计信息、索引和查询规划器优化查询性能。 6. **复制与集群**：介绍了复制技术，如物理复制和逻辑复制，以及如何设置主从复制和多节点集群，以实现负载均衡和故障转移。 7. **扩展与自定义类型**：讲解了如何创建自定义数据类型、函数和操作符，以及使用PL/pgSQL和其他编程语言编写自定义函数。 8. **监控与诊断**：提供了监控数据库性能和诊断问题的方法，包括使用内置的pg_statistic和pg_stat_activity视图，以及日志系统。 9. **升级与降级**：指导用户如何平滑地从一个PostgreSQL版本升级或降级到另一个版本，以及在升级过程中需要注意的关键点。 10. **系统架构与内部工作原理**：深入探讨PostgreSQL的内部结构，如进程模型、缓存机制和查询执行过程，有助于高级用户和开发者理解其行为。此文档是PostgreSQL用户和管理员的重要参考资料，无论你是初学者还是经验丰富的DBA，都能从中受益。它不仅包含理论知识，还有大量的实际示例和最佳实践，旨在帮助用户更好地理解和利用PostgreSQL的强大功能。虽然本文档是英文版，但对于学习和查阅关键信息来说，语言障碍并不构成太大问题。

30.2. Write-Ahead Logging (WAL) .............................................................................. 710

30.3. Asynchronous Commit......................................................................................... 710

30.4. WAL Conﬁguration .............................................................................................. 712

30.5. WAL Internals ......................................................................................................715

31. Regression Tests................................................................................................................ 716

31.1. Running the Tests ................................................................................................. 716

31.1.1. Running the Tests Against a Temporary Installation ...............................716

31.1.2. Running the Tests Against an Existing Installation ................................. 716

31.1.3. Additional Test Suites.............................................................................. 717

31.1.4. Locale and Encoding................................................................................717

31.1.5. Extra Tests................................................................................................ 718

31.1.6. Testing Hot Standby................................................................................. 718

31.2. Test Evaluation ..................................................................................................... 719

31.2.1. Error Message Differences.......................................................................719

31.2.2. Locale Differences...................................................................................719

31.2.3. Date and Time Differences ...................................................................... 720

31.2.4. Floating-Point Differences.......................................................................720

31.2.5. Row Ordering Differences ....................................................................... 720

31.2.6. Insufﬁcient Stack Depth...........................................................................721

31.2.7. The “random” Test................................................................................... 721

31.2.8. Conﬁguration Parameters.........................................................................721

31.3. Variant Comparison Files ..................................................................................... 721

31.4. TAP Tests.............................................................................................................. 722

31.5. Test Coverage Examination..................................................................................723

IV. Client Interfaces ....................................................................................................................... 724

32. libpq - C Library ...............................................................................................................726

32.1. Database Connection Control Functions.............................................................. 726

32.1.1. Connection Strings...................................................................................732

32.1.1.1. Keyword/Value Connection Strings ............................................732

32.1.1.2. Connection URIs......................................................................... 733

32.1.2. Parameter Key Words .............................................................................. 733

32.2. Connection Status Functions ................................................................................ 737

32.3. Command Execution Functions ...........................................................................742

32.3.1. Main Functions ........................................................................................ 742

32.3.2. Retrieving Query Result Information ...................................................... 750

32.3.3. Retrieving Other Result Information .......................................................753

32.3.4. Escaping Strings for Inclusion in SQL Commands .................................754

32.4. Asynchronous Command Processing................................................................... 756

32.5. Retrieving Query Results Row-By-Row .............................................................. 760

32.6. Canceling Queries in Progress.............................................................................. 761

32.7. The Fast-Path Interface......................................................................................... 762

32.8. Asynchronous Notiﬁcation................................................................................... 763

32.9. Functions Associated with the COPY Command ..................................................764

32.9.1. Functions for Sending COPY Data............................................................ 765

32.9.2. Functions for Receiving COPY Data.........................................................766

32.9.3. Obsolete Functions for COPY ...................................................................766

32.10. Control Functions ............................................................................................... 768

32.11. Miscellaneous Functions ....................................................................................770

32.12. Notice Processing ............................................................................................... 772

32.13. Event System...................................................................................................... 773

32.13.1. Event Types............................................................................................ 773

xvi

32.13.2. Event Callback Procedure......................................................................775

32.13.3. Event Support Functions........................................................................776

32.13.4. Event Example.......................................................................................777

32.14. Environment Variables .......................................................................................779

32.15. The Password File ..............................................................................................781

32.16. The Connection Service File ..............................................................................781

32.17. LDAP Lookup of Connection Parameters.......................................................... 782

32.18. SSL Support........................................................................................................783

32.18.1. Client Veriﬁcation of Server Certiﬁcates ............................................... 783

32.18.2. Client Certiﬁcates...................................................................................784

32.18.3. Protection Provided in Different Modes ................................................784

32.18.4. SSL Client File Usage............................................................................786

32.18.5. SSL Library Initialization ......................................................................786

32.19. Behavior in Threaded Programs......................................................................... 787

32.20. Building libpq Programs.....................................................................................788

32.21. Example Programs.............................................................................................. 789

33. Large Objects ....................................................................................................................799

33.1. Introduction ..........................................................................................................799

33.2. Implementation Features ......................................................................................799

33.3. Client Interfaces.................................................................................................... 799

33.3.1. Creating a Large Object...........................................................................800

33.3.2. Importing a Large Object.........................................................................800

33.3.3. Exporting a Large Object.........................................................................801

33.3.4. Opening an Existing Large Object...........................................................801

33.3.5. Writing Data to a Large Object................................................................801

33.3.6. Reading Data from a Large Object ..........................................................802

33.3.7. Seeking in a Large Object........................................................................802

33.3.8. Obtaining the Seek Position of a Large Object........................................802

33.3.9. Truncating a Large Object ....................................................................... 803

33.3.10. Closing a Large Object Descriptor ........................................................803

33.3.11. Removing a Large Object ......................................................................803

33.4. Server-side Functions ...........................................................................................804

33.5. Example Program ................................................................................................. 805

34. ECPG - Embedded SQL in C............................................................................................ 811

34.1. The Concept.......................................................................................................... 811

34.2. Managing Database Connections .........................................................................811

34.2.1. Connecting to the Database Server..........................................................811

34.2.2. Choosing a Connection............................................................................813

34.2.3. Closing a Connection...............................................................................814

34.3. Running SQL Commands.....................................................................................814

34.3.1. Executing SQL Statements ......................................................................814

34.3.2. Using Cursors...........................................................................................815

34.3.3. Managing Transactions............................................................................816

34.3.4. Prepared Statements.................................................................................816

34.4. Using Host Variables ............................................................................................817

34.4.1. Overview..................................................................................................817

34.4.2. Declare Sections.......................................................................................817

34.4.3. Retrieving Query Results.........................................................................818

34.4.4. Type Mapping ..........................................................................................819

34.4.4.1. Handling Character Strings ......................................................... 820

34.4.4.2. Accessing Special Data Types..................................................... 820

34.4.4.2.1. timestamp, date............................................................... 821

xvii

34.4.4.2.2. interval ............................................................................ 821

34.4.4.2.3. numeric, decimal.............................................................822

34.4.4.3. Host Variables with Nonprimitive Types .................................... 823

34.4.4.3.1. Arrays .............................................................................823

34.4.4.3.2. Structures ........................................................................ 824

34.4.4.3.3. Typedefs..........................................................................825

34.4.4.3.4. Pointers ...........................................................................826

34.4.5. Handling Nonprimitive SQL Data Types................................................. 826

34.4.5.1. Arrays.......................................................................................... 826

34.4.5.2. Composite Types .........................................................................828

34.4.5.3. User-deﬁned Base Types .............................................................830

34.4.6. Indicators..................................................................................................831

34.5. Dynamic SQL....................................................................................................... 831

34.5.1. Executing Statements without a Result Set ............................................. 831

34.5.2. Executing a Statement with Input Parameters ......................................... 832

34.5.3. Executing a Statement with a Result Set ................................................. 832

34.6. pgtypes Library..................................................................................................... 833

34.6.1. Character Strings......................................................................................834

34.6.2. The numeric Type ....................................................................................834

34.6.3. The date Type........................................................................................... 836

34.6.4. The timestamp Type................................................................................. 840

34.6.5. The interval Type .....................................................................................843

34.6.6. The decimal Type..................................................................................... 844

34.6.7. errno Values of pgtypeslib .......................................................................844

34.6.8. Special Constants of pgtypeslib...............................................................845

34.7. Using Descriptor Areas ........................................................................................846

34.7.1. Named SQL Descriptor Areas .................................................................846

34.7.2. SQLDA Descriptor Areas ........................................................................848

34.7.2.1. SQLDA Data Structure................................................................849

34.7.2.1.1. sqlda_t Structure ............................................................. 849

34.7.2.1.2. sqlvar_t Structure............................................................850

34.7.2.1.3. struct sqlname Structure .................................................850

34.7.2.2. Retrieving a Result Set Using an SQLDA ..................................851

34.7.2.3. Passing Query Parameters Using an SQLDA..............................852

34.7.2.4. A Sample Application Using SQLDA ........................................853

34.8. Error Handling...................................................................................................... 859

34.8.1. Setting Callbacks ..................................................................................... 859

34.8.2. sqlca ......................................................................................................... 861

34.8.3. SQLSTATE vs. SQLCODE........................................................................... 862

34.9. Preprocessor Directives ........................................................................................ 866

34.9.1. Including Files ......................................................................................... 866

34.9.2. The deﬁne and undef Directives .............................................................. 866

34.9.3. ifdef, ifndef, else, elif, and endif Directives.............................................867

34.10. Processing Embedded SQL Programs................................................................ 868

34.11. Library Functions ...............................................................................................869

34.12. Large Objects...................................................................................................... 869

34.13. C++ Applications ...............................................................................................871

34.13.1. Scope for Host Variables........................................................................ 871

34.13.2. C++ Application Development with External C Module ......................873

34.14. Embedded SQL Commands ...............................................................................875

ALLOCATE DESCRIPTOR ...............................................................................875

CONNECT...........................................................................................................877

xviii

DEALLOCATE DESCRIPTOR .......................................................................... 880

DECLARE ...........................................................................................................881

DESCRIBE ..........................................................................................................883

DISCONNECT ....................................................................................................884

EXECUTE IMMEDIATE.................................................................................... 886

GET DESCRIPTOR ............................................................................................ 887

OPEN ...................................................................................................................890

PREPARE ............................................................................................................892

SET AUTOCOMMIT .......................................................................................... 893

SET CONNECTION ........................................................................................... 894

SET DESCRIPTOR.............................................................................................895

TYPE....................................................................................................................897

VAR...................................................................................................................... 899

WHENEVER .......................................................................................................900

34.15. Informix Compatibility Mode ............................................................................902

34.15.1. Additional Types.................................................................................... 902

34.15.2. Additional/Missing Embedded SQL Statements ...................................902

34.15.3. Informix-compatible SQLDA Descriptor Areas....................................903

34.15.4. Additional Functions..............................................................................906

34.15.5. Additional Constants..............................................................................914

34.16. Internals .............................................................................................................. 915

35. The Information Schema...................................................................................................918

35.1. The Schema ..........................................................................................................918

35.2. Data Types ............................................................................................................918

35.3. information_schema_catalog_name ...........................................................919

35.4. administrable_role_authorizations.......................................................919

35.5. applicable_roles............................................................................................919

35.6. attributes.........................................................................................................920

35.7. character_sets................................................................................................ 924

35.8. check_constraint_routine_usage .............................................................925

35.9. check_constraints ......................................................................................... 925

35.10. collations.......................................................................................................926

35.11. collation_character_set_applicability ............................................926

35.12. column_domain_usage ...................................................................................927

35.13. column_options..............................................................................................927

35.14. column_privileges ....................................................................................... 928

35.15. column_udt_usage..........................................................................................928

35.16. columns ............................................................................................................. 929

35.17. constraint_column_usage .......................................................................... 934

35.18. constraint_table_usage............................................................................. 935

35.19. data_type_privileges................................................................................. 935

35.20. domain_constraints .....................................................................................936

35.21. domain_udt_usage..........................................................................................937

35.22. domains ............................................................................................................. 937

35.23. element_types ................................................................................................940

35.24. enabled_roles ................................................................................................943

35.25. foreign_data_wrapper_options................................................................944

35.26. foreign_data_wrappers...............................................................................944

35.27. foreign_server_options............................................................................. 944

35.28. foreign_servers............................................................................................ 945

35.29. foreign_table_options...............................................................................945

35.30. foreign_tables..............................................................................................946

xix

35.31. key_column_usage..........................................................................................946

35.32. parameters.......................................................................................................947

35.33. referential_constraints .......................................................................... 950

35.34. role_column_grants .....................................................................................951

35.35. role_routine_grants ...................................................................................951

35.36. role_table_grants ....................................................................................... 952

35.37. role_udt_grants............................................................................................ 953

35.38. role_usage_grants ....................................................................................... 953

35.39. routine_privileges .....................................................................................954

35.40. routines...........................................................................................................955

35.41. schemata...........................................................................................................961

35.42. sequences......................................................................................................... 961

35.43. sql_features ..................................................................................................962

35.44. sql_implementation_info .......................................................................... 963

35.45. sql_languages ................................................................................................964

35.46. sql_packages ..................................................................................................964

35.47. sql_parts......................................................................................................... 965

35.48. sql_sizing.......................................................................................................965

35.49. sql_sizing_profiles ...................................................................................966

35.50. table_constraints ....................................................................................... 966

35.51. table_privileges..........................................................................................967

35.52. tables ...............................................................................................................968

35.53. transforms.......................................................................................................969

35.54. triggered_update_columns ........................................................................ 970

35.55. triggers...........................................................................................................970

35.56. udt_privileges..............................................................................................972

35.57. usage_privileges..........................................................................................972

35.58. user_defined_types .....................................................................................973

35.59. user_mapping_options................................................................................. 975

35.60. user_mappings ................................................................................................975

35.61. view_column_usage ....................................................................................... 976

35.62. view_routine_usage .....................................................................................977

35.63. view_table_usage..........................................................................................977

35.64. views .................................................................................................................978

V. Server Programming ................................................................................................................. 980

36. Extending SQL.................................................................................................................. 982

36.1. How Extensibility Works......................................................................................982

36.2. The PostgreSQL Type System..............................................................................982

36.2.1. Base Types ...............................................................................................982

36.2.2. Composite Types...................................................................................... 982

36.2.3. Domains ...................................................................................................983

36.2.4. Pseudo-Types ...........................................................................................983

36.2.5. Polymorphic Types ..................................................................................983

36.3. User-deﬁned Functions......................................................................................... 984

36.4. Query Language (SQL) Functions .......................................................................984

36.4.1. Arguments for SQL Functions.................................................................985

36.4.2. SQL Functions on Base Types................................................................. 986

36.4.3. SQL Functions on Composite Types .......................................................988

36.4.4. SQL Functions with Output Parameters ..................................................990

36.4.5. SQL Functions with Variable Numbers of Arguments............................ 991

36.4.6. SQL Functions with Default Values for Arguments................................ 992

剩余3452页未读，继续阅读

Mario

粉丝: 0
资源: 6

PostgreSQL 9.6.11 官方手册

postgresql-9.6.17.tar.gz

postgresql-9.6.12-1-windows-x64.exe

postgresql-9.6.24.tar.gz

postgresql-9.6.22.tar

postgresql-phppgadmin容器和postgresql-postgres区别

windows如何将postgresql-x64-14服务修改为my_postgresql-x64-14

sudo: postgresql-setup: command not found

安装postgresql-devel

postgresql-libs-9.2.24-7.el7_9.x86_64 linux安装

最新资源