EDB Postgres™ Advanced Server Guide

EDB PostgresTM Advanced Server Guide

EDB PostgresTM Advanced Server 11 April 3, 2019

EDB PostgresTM Advanced Server Guide by EnterpriseDB? Corporation

Copyright ? 2014 - 2019 EnterpriseDB Corporation

EnterpriseDB Corporation, 34 Crosby Drive, Suite 201, Bedford, MA 01730, USA T +1 781 357 3390 F +1 978 467 1307 E info@

Copy right ? 2014 - 2019 EnterpriseDB Corporation. All rights reserv ed.

2

EDB Postgres Advanced Server Guide

Table of Contents

1 Introduction ......................................................................................................................8 1.1 Whats New............................................................................................................10 1.2 Typographical Conventions Used in this Guide..................................................11

1.3 Other Conventions Used in this Guide.................................................................12 1.4 About the Examples Used in this Guide...............................................................13

1.4.1 Sample Database Description...........................................................................13

2 Enhanced Compatibility Features .................................................................................22 2.1 Enabling Compatibility Features ..........................................................................23

2.2 Stored Procedural Language .................................................................................23 2.3 Optimizer Hints......................................................................................................23 2.4 Data Dictionary Views ..........................................................................................24

2.5 dblink_ora...............................................................................................................24 2.6 Profile Management...............................................................................................25 2.7 Built-In Packages...................................................................................................25

2.8 Open Client Library...............................................................................................27 2.9 Utilities ...................................................................................................................28 2.10 ECPGPlus...............................................................................................................29

2.11 Table Partitioning ..................................................................................................30 3 Database Administration ...............................................................................................31

3.1 Configuration Parameters......................................................................................31 3.1.1 Setting Configuration Parameters.....................................................................32 3.1.2 Summary of Configuration Parameters............................................................35

3.1.3 Configuration Parameters by Functionality.....................................................54 3.1.3.1 Top Performance Related Parameters......................................................55 3.1.3.2 Resource Usage / Memory .......................................................................66

3.1.3.3 Resource Usage / EDB Resource Manager.............................................68 3.1.3.4 Query Tuning.............................................................................................70

3.1.3.5 Query Tuning / Planner Method Configuration ......................................70 3.1.3.6 Reporting and Logging / What to Log.....................................................73 3.1.3.7 Auditing Settings.......................................................................................75

3.1.3.8 Client Connection Defaults / Locale and Formatting .............................80 3.1.3.9 Client Connection Defaults / Statement Behavior..................................80 3.1.3.10 Client Connection Defaults / Other Defaults.......................................83

3.1.3.11 Compatibility Options...........................................................................85 3.1.3.12 Customized Options..............................................................................94 3.1.3.13 Ungrouped ...........................................................................................104 3.2 Index Advisor.......................................................................................................107 3.2.1 Index Advisor Components ............................................................................108 3.2.2 Index Advisor Configuration..........................................................................109 3.2.3 Using Index Advisor.......................................................................................112 3.2.3.1 Using the pg_advise_index Utility.........................................................112 3.2.3.2 Using Index Advisor at the psql Command Line..................................114

Copy right ? 2014 - 2019 EnterpriseDB Corporation. All rights reserv ed.

3

EDB Postgres Advanced Server Guide

3.2.4 Reviewing the Index Advisor Recommendations .........................................116 3.2.4.1 Using the show_index_recommendations() Function ..........................116 3.2.4.2 Querying the index_advisor_log Table..................................................117 3.2.4.3 Querying the index_recommendations View........................................119

3.2.5 Limitations.......................................................................................................120 3.3 SQL Profiler .........................................................................................................122 3.4 pgsnmpd ...............................................................................................................124

3.4.1 Configuring pgsnmpd......................................................................................124 3.4.2 Setting the Listener Address...........................................................................125 3.4.3 Invoking pgsnmpd...........................................................................................125 3.4.4 Viewing pgsnmpd Help...................................................................................126 3.4.5 Requesting Information from pgsnmpd .........................................................126 3.5 EDB Audit Logging.............................................................................................127 3.5.1 Audit Logging Configuration Parameters......................................................128 3.5.2 Selecting SQL Statements to Audit................................................................131

3.5.2.1 Data Definition Language and Data Control Language Statements ....132 3.5.2.2 Data Manipulation Language Statements ..............................................137 3.5.3 Enabling Audit Logging..................................................................................140 3.5.4 Audit Log File..................................................................................................145 3.5.5 Using Error Codes to Filter Audit Logs.........................................................150 3.5.6 Using Command Tags to Filter Audit Logs...................................................151 3.5.7 Redacting Passwords from Audit Logs..........................................................153 3.6 Unicode Collation Algorithm..............................................................................153 3.6.1 Basic Unicode Collation Algorithm Concepts ..............................................155 3.6.2 International Components for Unicode..........................................................156 3.6.2.1 Locale Collations.....................................................................................156 3.6.2.2 Collation Attributes.................................................................................157 3.6.3 Creating an ICU Collation ..............................................................................160 3.6.3.1 CREATE COLLATION.........................................................................160 3.6.3.2 CREATE DATABASE...........................................................................162 3.6.3.3 initdb ........................................................................................................163 3.6.4 Using a Collation.............................................................................................165 3.7 Customizable WAL Segment File Size..............................................................169 4 Security.........................................................................................................................171 4.1 Protecting Against SQL Injection Attacks .........................................................171 4.1.1 SQL/Protect Overview....................................................................................172 4.1.1.1 Types of SQL Injection Attacks.............................................................172 4.1.1.2 Monitoring SQL Injection Attacks.........................................................173 4.1.2 Configuring SQL/Protect................................................................................176 4.1.2.1 Selecting Roles to Protect.......................................................................178 4.1.2.2 Monitoring Protected Roles....................................................................180 4.1.3 Common Maintenance Operations.................................................................187 4.1.3.1 Adding a Role to the Protected Roles List ............................................187 4.1.3.2 Removing a Role From the Protected Roles List..................................187 4.1.3.3 Setting the Types of Protection for a Role ............................................188 4.1.3.4 Removing a Relation From the Protected Relations List .....................189

Copy right ? 2014 - 2019 EnterpriseDB Corporation. All rights reserv ed.

4

EDB Postgres Advanced Server Guide

4.1.3.5 Deleting Statistics....................................................................................189 4.1.3.6 Deleting Offending Queries....................................................................190 4.1.3.7 Disabling and Enabling Monitoring.......................................................191 4.1.4 Backing Up and Restoring a SQL/Protect Database.....................................193 4.1.4.1 Object Identification Numbers in SQL/Protect Tables.........................193 4.1.4.2 Backing Up the Database........................................................................194 4.1.4.3 Restoring From the Backup Files...........................................................194 4.2 Virtual Private Database......................................................................................199 4.3 sslutils...................................................................................................................200 4.3.1 openssl_rsa_generate_key...............................................................................200 4.3.2 openssl_rsa_key_to_csr...................................................................................200 4.3.3 openssl_csr_to_crt...........................................................................................201 4.3.4 openssl_rsa_generate_crl................................................................................202 4.4 Data Redaction.....................................................................................................203 4.4.1 CREATE REDACTION POLICY.................................................................204 4.4.2 ALTER REDACTION POLICY....................................................................209 4.4.3 DROP REDACTION POLICY......................................................................212 4.4.4 System Catalogs ..............................................................................................214 4.4.4.1 edb_redaction_column............................................................................214 4.4.4.2 edb_redaction_policy..............................................................................214 5 EDB Resource Manager..............................................................................................215 5.1 Creating and Managing Resource Groups..........................................................216 5.1.1 CREATE RESOURCE GROUP ....................................................................216 5.1.2 ALTER RESOURCE GROUP .......................................................................217 5.1.3 DROP RESOURCE GROUP .........................................................................219 5.1.4 Assigning a Process to a Resource Group .....................................................219 5.1.5 Removing a Process from a Resource Group................................................220 5.1.6 Monitoring Processes in Resource Groups ....................................................221 5.2 CPU Usage Throttling .........................................................................................223 5.2.1 Setting the CPU Rate Limit for a Resource Group .......................................223 5.2.2 Example ? Single Process in a Single Group ................................................224 5.2.3 Example ? Multiple Processes in a Single Group .........................................225 5.2.4 Example ? Multiple Processes in Multiple Groups.......................................227 5.3 Dirty Buffer Throttling........................................................................................230 5.3.1 Setting the Dirty Rate Limit for a Resource Group.......................................230 5.3.2 Example ? Single Process in a Single Group ................................................232 5.3.3 Example ? Multiple Processes in a Single Group .........................................233 5.3.4 Example ? Multiple Processes in Multiple Groups.......................................235 5.4 System Catalogs...................................................................................................239 5.4.1 edb_all_resource_groups ................................................................................239 5.4.2 edb_resource_group ........................................................................................239 6 libpq C Library.............................................................................................................240 6.1 Using libpq with EnterpriseDB SPL...................................................................240 6.2 REFCURSOR Support........................................................................................240 6.3 Array Binding ......................................................................................................247 6.3.1 PQBulkStart.....................................................................................................247

Copy right ? 2014 - 2019 EnterpriseDB Corporation. All rights reserv ed.

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download