没有合适的资源?快使用搜索试试~ 我知道了~
首页PostgreSQL 9.6.11 官方手册
PostgreSQL 9.6.11 官方手册
需积分: 7 1 下载量 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的强大功能。虽然本文档是英文版,但对于学习和查阅关键信息来说,语言障碍并不构成太大问题。
![](https://csdnimg.cn/release/download_crawler_static/10791454/bg10.jpg)
30.2. Write-Ahead Logging (WAL) .............................................................................. 710
30.3. Asynchronous Commit......................................................................................... 710
30.4. WAL Configuration .............................................................................................. 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. Insufficient Stack Depth...........................................................................721
31.2.7. The “random” Test................................................................................... 721
31.2.8. Configuration 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 Notification................................................................................... 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
![](https://csdnimg.cn/release/download_crawler_static/10791454/bg11.jpg)
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 Verification of Server Certificates ............................................... 783
32.18.2. Client Certificates...................................................................................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
![](https://csdnimg.cn/release/download_crawler_static/10791454/bg12.jpg)
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-defined 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 define 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
![](https://csdnimg.cn/release/download_crawler_static/10791454/bg13.jpg)
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
![](https://csdnimg.cn/release/download_crawler_static/10791454/bg14.jpg)
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-defined 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
xx
剩余3452页未读,继续阅读
2020-04-17 上传
2019-08-13 上传
2024-03-24 上传
2024-01-29 上传
2024-05-27 上传
2023-05-16 上传
2023-12-01 上传
2024-05-31 上传
2023-06-09 上传
![](https://profile-avatar.csdnimg.cn/2e6d76be78184bc69b8ca70b43c4d82d_amoy_sun.jpg!1)
Mario
- 粉丝: 0
- 资源: 6
上传资源 快速赚钱
我的内容管理 展开
我的资源 快来上传第一个资源
我的收益
登录查看自己的收益我的积分 登录查看自己的积分
我的C币 登录后查看C币余额
我的收藏
我的下载
下载帮助
![](https://csdnimg.cn/release/wenkucmsfe/public/img/voice.245cc511.png)
最新资源
- 多模态联合稀疏表示在视频目标跟踪中的应用
- Kubernetes资源管控与Gardener开源软件实践解析
- MPI集群监控与负载平衡策略
- 自动化PHP安全漏洞检测:静态代码分析与数据流方法
- 青苔数据CEO程永:技术生态与阿里云开放创新
- 制造业转型: HyperX引领企业上云策略
- 赵维五分享:航空工业电子采购上云实战与运维策略
- 单片机控制的LED点阵显示屏设计及其实现
- 驻云科技李俊涛:AI驱动的云上服务新趋势与挑战
- 6LoWPAN物联网边界路由器:设计与实现
- 猩便利工程师仲小玉:Terraform云资源管理最佳实践与团队协作
- 类差分度改进的互信息特征选择提升文本分类性能
- VERITAS与阿里云合作的混合云转型与数据保护方案
- 云制造中的生产线仿真模型设计与虚拟化研究
- 汪洋在PostgresChina2018分享:高可用 PostgreSQL 工具与架构设计
- 2018 PostgresChina大会:阿里云时空引擎Ganos在PostgreSQL中的创新应用与多模型存储
安全验证
文档复制为VIP权益,开通VIP直接复制
![](https://csdnimg.cn/release/wenkucmsfe/public/img/green-success.6a4acb44.png)