EDB Postgres Advanced Server → 12 → User Guides → User Guide Other versions of this page: 12 · 11 · 10 · 9.6 · 9.5 · 9.4
Subscribe to get advanced Postgres how-tos.
Table of Contents 1 Introduction 1.1 What’s New 1.2 Typographical Conventions Used in this Guide 1.3 Other Conventions Used in this Guide 1.4 About the Examples Used in this Guide 1.4.1 Sample Database Description 2 Enhanced Compatibility Features 2.1 Enabling Compatibility Features 2.2 Stored Procedural Language 2.3 Optimizer Hints 2.4 Data Dictionary Views 2.5 dblink_ora 2.6 Profile Management 2.7 Built-In Packages 2.8 Open Client Library 2.9 Utilities 2.10 ECPGPlus 2.11 Table Partitioning 3 Database Administration 3.1 Configuration Parameters 3.1.1 Setting Configuration Parameters 3.1.2 Summary of Configuration Parameters 3.1.3 Configuration Parameters by Functionality 3.1.3.1 Top Performance Related Parameters 3.1.3.1.1 shared_buffers 3.1.3.1.2 work_mem 3.1.3.1.3 maintenance_work_mem 3.1.3.1.4 wal_buffers 3.1.3.1.5 checkpoint_segments 3.1.3.1.6 checkpoint_completion_target 3.1.3.1.7 checkpoint_timeout 3.1.3.1.8 max_wal_size 3.1.3.1.9 min_wal_size 3.1.3.1.10 bgwriter_delay 3.1.3.1.11 seq_page_cost 3.1.3.1.12 random_page_cost 3.1.3.1.13 effective_cache_size 3.1.3.1.14 synchronous_commit 3.1.3.1.15 edb_max_spins_per_delay 3.1.3.1.16 pg_prewarm.autoprewarm 3.1.3.1.17 pg_prewarm.autoprewarm_interval 3.1.3.2 Resource Usage / Memory 3.1.3.2.1 edb_dynatune 3.1.3.2.2 edb_dynatune_profile 3.1.3.3 Resource Usage / EDB Resource Manager 3.1.3.3.1 edb_max_resource_groups 3.1.3.3.2 edb_resource_group 3.1.3.4 Query Tuning 3.1.3.4.1 enable_hints 3.1.3.5 Query Tuning / Planner Method Configuration 3.1.3.5.1 edb_enable_pruning 3.1.3.6 Reporting and Logging / What to Log 3.1.3.6.1 trace_hints 3.1.3.6.2 edb_log_every_bulk_value 3.1.3.7 Auditing Settings 3.1.3.7.1 edb_audit 3.1.3.7.2 edb_audit_directory 3.1.3.7.3 edb_audit_filename 3.1.3.7.4 edb_audit_rotation_day 3.1.3.7.5 edb_audit_rotation_size 3.1.3.7.6 edb_audit_rotation_seconds 3.1.3.7.7 edb_audit_connect 3.1.3.7.8 edb_audit_disconnect 3.1.3.7.9 edb_audit_statement 3.1.3.7.10 edb_audit_tag 3.1.3.7.11 edb_audit_destination 3.1.3.7.12 edb_log_every_bulk_value 3.1.3.8 Client Connection Defaults / Locale and Formatting 3.1.3.8.1 icu_short_form 3.1.3.9 Client Connection Defaults / Statement Behavior 3.1.3.9.1 default_heap_fillfactor 3.1.3.9.2 edb_data_redaction 3.1.3.10 Client Connection Defaults / Other Defaults 3.1.3.10.1 oracle_home 3.1.3.10.2 odbc_lib_path 3.1.3.11 Compatibility Options 3.1.3.11.1 edb_redwood_date 3.1.3.11.2 edb_redwood_greatest_least 3.1.3.11.3 edb_redwood_raw_names 3.1.3.11.4 edb_redwood_strings 3.1.3.11.5 edb_stmt_level_tx 3.1.3.11.6 db_dialect 3.1.3.11.7 default_with_rowids 3.1.3.11.8 optimizer_mode 3.1.3.12 Customized Options 3.1.3.12.1 custom_variable_classes 3.1.3.12.2 dbms_alert.max_alerts 3.1.3.12.3 dbms_pipe.total_message_buffer 3.1.3.12.4 index_advisor.enabled 3.1.3.12.5 edb_sql_protect.enabled 3.1.3.12.6 edb_sql_protect.level 3.1.3.12.7 edb_sql_protect.max_protected_relations 3.1.3.12.8 edb_sql_protect.max_protected_roles 3.1.3.12.9 edb_sql_protect.max_queries_to_save 3.1.3.12.10 edb_wait_states.directory 3.1.3.12.11 edb_wait_states.retention_period 3.1.3.12.12 edb_wait_states.sampling_interval 3.1.3.12.13 edbldr.empty_csv_field 3.1.3.12.14 utl_encode.uudecode_redwood 3.1.3.12.15 utl_file.umask 3.1.3.13 Ungrouped 3.1.3.13.1 nls_length_semantics 3.1.3.13.2 query_rewrite_enabled 3.1.3.13.3 query_rewrite_integrity 3.1.3.13.4 timed_statistics 3.2 Index Advisor 3.2.1 Index Advisor Components 3.2.2 Index Advisor Configuration 3.2.3 Using Index Advisor 3.2.3.1 Using the pg_advise_index Utility 3.2.3.2 Using Index Advisor at the psql Command Line 3.2.4 Reviewing the Index Advisor Recommendations 3.2.4.1 Using the show_index_recommendations() Function 3.2.4.2 Querying the index_advisor_log Table 3.2.4.3 Querying the index_recommendations View 3.2.5 Limitations 3.3 SQL Profiler 3.4 pgsnmpd 3.4.1 Configuring pgsnmpd 3.4.2 Setting the Listener Address 3.4.3 Invoking pgsnmpd 3.4.4 Viewing pgsnmpd Help 3.4.5 Requesting Information from pgsnmpd 3.5 EDB Audit Logging 3.5.1 Audit Logging Configuration Parameters 3.5.2 Selecting SQL Statements to Audit 3.5.2.1 Data Definition Language and Data Control Language Statements 3.5.2.2 Data Manipulation Language Statements 3.5.3 Enabling Audit Logging 3.5.4 Audit Log File 3.5.5 Using Error Codes to Filter Audit Logs 3.5.6 Using Command Tags to Filter Audit Logs 3.5.7 Redacting Passwords from Audit Logs 3.6 Unicode Collation Algorithm 3.6.1 Basic Unicode Collation Algorithm Concepts 3.6.2 International Components for Unicode 3.6.2.1 Locale Collations 3.6.2.2 Collation Attributes 3.6.3 Using a Collation 4 Security 4.1 Protecting Against SQL Injection Attacks 4.1.1 SQL/Protect Overview 4.1.1.1 Types of SQL Injection Attacks 4.1.1.2 Monitoring SQL Injection Attacks 4.1.1.2.1 Protected Roles 4.1.1.2.2 Attack Attempt Statistics 4.1.1.2.3 Attack Attempt Queries 4.1.2 Configuring SQL/Protect 4.1.2.1 Selecting Roles to Protect 4.1.2.1.1 Setting the Protected Roles List 4.1.2.1.2 Setting the Protection Level 4.1.2.2 Monitoring Protected Roles 4.1.2.2.1 Learn Mode 4.1.2.2.2 Passive Mode 4.1.2.2.3 Active Mode 4.1.3 Common Maintenance Operations 4.1.3.1 Adding a Role to the Protected Roles List 4.1.3.2 Removing a Role From the Protected Roles List 4.1.3.3 Setting the Types of Protection for a Role 4.1.3.4 Removing a Relation From the Protected Relations List 4.1.3.5 Deleting Statistics 4.1.3.6 Deleting Offending Queries 4.1.3.7 Disabling and Enabling Monitoring 4.1.4 Backing Up and Restoring a SQL/Protect Database 4.1.4.1 Object Identification Numbers in SQL/Protect Tables 4.1.4.2 Backing Up the Database 4.1.4.3 Restoring From the Backup Files 4.2 Virtual Private Database 4.3 sslutils 4.3.1 openssl_rsa_generate_key 4.3.2 openssl_rsa_key_to_csr 4.3.3 openssl_csr_to_crt 4.3.4 openssl_rsa_generate_crl 4.4 Data Redaction 4.4.1 CREATE REDACTION POLICY 4.4.2 ALTER REDACTION POLICY 4.4.3 DROP REDACTION POLICY 4.4.4 System Catalogs 4.4.4.1 edb_redaction_column 4.4.4.2 edb_redaction_policy 5 EDB Resource Manager 5.1 Creating and Managing Resource Groups 5.1.1 CREATE RESOURCE GROUP 5.1.2 ALTER RESOURCE GROUP 5.1.3 DROP RESOURCE GROUP 5.1.4 Assigning a Process to a Resource Group 5.1.5 Removing a Process from a Resource Group 5.1.6 Monitoring Processes in Resource Groups 5.2 CPU Usage Throttling 5.2.1 Setting the CPU Rate Limit for a Resource Group 5.2.2 Example – Single Process in a Single Group 5.2.3 Example – Multiple Processes in a Single Group 5.2.4 Example – Multiple Processes in Multiple Groups 5.3 Dirty Buffer Throttling 5.3.1 Setting the Dirty Rate Limit for a Resource Group 5.3.2 Example – Single Process in a Single Group 5.3.3 Example – Multiple Processes in a Single Group 5.3.4 Example – Multiple Processes in Multiple Groups 5.4 System Catalogs 5.4.1 edb_all_resource_groups 5.4.2 edb_resource_group 6 libpq C Library 6.1 Using libpq with EnterpriseDB SPL 6.2 REFCURSOR Support 6.3 Array Binding 6.3.1 PQBulkStart 6.3.2 PQexecBulk 6.3.3 PQBulkFinish 6.3.4 PQexecBulkPrepared 6.3.5 Example Code (Using PQBulkStart, PQexecBulk, PQBulkFinish) 6.3.6 Example Code (Using PQexecBulkPrepared) 7 Debugger 7.1 Configuring the Debugger 7.2 Starting the Debugger 7.3 The Debugger Window 7.4 Main Debugger Window 7.4.1 The Program Body Panel 7.4.2 The Tabs Panel 7.4.3 The Stack Tab 7.5 Debugging a Program 7.5.1 Stepping Through the Code 7.5.2 Using Breakpoints 7.5.3 Setting a Global Breakpoint for In-Context Debugging 7.5.4 Exiting the Debugger 8 Performance Analysis and Tuning 8.1 Dynatune 8.1.1 edb_dynatune 8.1.2 edb_dynatune_profile 8.2 EDB Wait States 8.2.1 edb_wait_states_data 8.2.2 edb_wait_states_queries 8.2.3 edb_wait_states_sessions 8.2.4 edb_wait_states_samples 8.2.5 edb_wait_states_purge 9 EDB Clone Schema 9.1 Setup Process 9.1.1 Installing Extensions and PL/Perl 9.1.2 Setting Configuration Parameters 9.1.2.1 Performance Configuration Parameters 9.1.2.2 Status Logging 9.1.3 Installing EDB Clone Schema 9.1.4 Creating the Foreign Servers and User Mappings 9.1.4.1 Foreign Server and User Mapping for Local Cloning Functions 9.1.4.2 Foreign Server and User Mapping for Remote Cloning Functions 9.2 EDB Clone Schema Functions 9.2.1 localcopyschema 9.2.2 localcopyschema_nb 9.2.3 remotecopyschema 9.2.4 remotecopyschema_nb 9.2.5 process_status_from_log 9.2.6 remove_log_file_and_job 10 Enhanced SQL and Other Miscellaneous Features 10.1 COMMENT 10.2 Output of Function version() 10.3 Logical Decoding on Standby 11 System Catalog Tables 11.1 edb_dir 11.2 edb_all_resource_groups 11.3 edb_policy 11.4 edb_profile 11.5 edb_redaction_column 11.6 edb_redaction_policy 11.7 edb_resource_group 11.8 edb_variable 11.9 pg_synonym 11.10 product_component_version 12 Advanced Server Keywords
