Search PostgreSQL documentation

Available Character Set ConversionsManual ChaptersPG1823.3.4. Available Character Set Conversions PostgreSQL allows conversion between any two character sets for which a conversion function is listed in the pg_conversion system catalog. Postgre…Available Character Set Conversions B-TreeManual ChaptersPG1811.2.1. B-Tree B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whe…B-Tree B-Tree FunctionsManual ChaptersPG18F.23.3. B-Tree Functions bt_metap(relname text) returns record bt_metap returns information about a B-tree index's metapage. For example: test=# SELECT * FROM bt_metap('pg_cast_oid_index'); …B-Tree Functions B-Tree IndexesManual ChaptersPG1865.1. B-Tree IndexesB-Tree Indexes B-Tree StructureManual ChaptersPG1865.1.4.1. B-Tree Structure PostgreSQL B-Tree indexes are multi-level tree structures, where each level of the tree can be used as a doubly-linked list of pages. A single metapage is stored i…B-Tree Structure B-Tree Support FunctionsManual ChaptersPG1865.1.3. B-Tree Support Functions As shown in Table 36.9, btree defines one required and five optional support functions. The six user-defined methods are: order For each combination of data …B-Tree Support Functions Background Worker ProcessesManual ChaptersPG18Chapter 46. Background Worker Processes PostgreSQL can be extended to run user-supplied code in separate processes. Such processes are started, stopped and monitored by postgres, which permi…Background Worker Processes Background WriterManual ChaptersPG1819.4.4. Background Writer There is a separate server process called the background writer, whose function is to issue writes of “dirty” (new or modified) shared buffers. When the number of c…Background Writer Backing Up the Data DirectoryManual ChaptersPG1825.3.4.1. Backing Up the Data Directory Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. When taking a base backup o…Backing Up the Data Directory Backup and RestoreManual ChaptersPG18Chapter 25. Backup and Restore As with everything that contains valuable data, PostgreSQL databases should be backed up regularly. While the procedure is essentially simple, it is important …Backup and Restore Backup Control FunctionsManual ChaptersPG189.28.3. Backup Control Functions The functions shown in Table 9.97 assist in making on-line backups. These functions cannot be executed during recovery (except pg_backup_start, pg_backup_sto…Backup Control Functions Backup Manifest File ObjectManual ChaptersPG1870.2. Backup Manifest File Object The object which describes a single file contains either a Path key or an Encoded-Path key. Normally, the Path key will be present. The associated string va…Backup Manifest File Object Backup Manifest FormatManual ChaptersPG18Chapter 70. Backup Manifest Format The backup manifest generated by pg_basebackup is primarily intended to permit the backup to be verified using pg_verifybackup. However, it is also possibl…Backup Manifest Format Backup Manifest Top-level ObjectManual ChaptersPG1870.1. Backup Manifest Top-level Object The backup manifest JSON document contains the following keys. PostgreSQL-Backup-Manifest-Version The associated value is an integer. Beginning in Post…Backup Manifest Top-level Object Backup Manifest WAL Range ObjectManual ChaptersPG1870.3. Backup Manifest WAL Range Object The object which describes a WAL range always has three keys: Timeline The timeline for this range of WAL records, as an integer. Start-LSN The LSN at …Backup Manifest WAL Range Object Base Backup Progress ReportingManual ChaptersPG1827.4.6. Base Backup Progress Reporting Whenever an application like pg_basebackup is taking a base backup, the pg_stat_progress_basebackup view will contain a row for each WAL sender process…Base Backup Progress Reporting Base TypesManual ChaptersPG1836.2.1. Base Types Base types are those, like integer, that are implemented below the level of the SQL language (typically in a low-level language such as C). They generally correspond to wh…Base Types Base TypesManual ChaptersPG18Base Types The fourth form of CREATE TYPE creates a new base type (scalar type). To create a new base type, you must be a superuser. (This restriction is made because an erroneous type defin…Base Types Base Types in C-Language FunctionsManual ChaptersPG1836.10.2. Base Types in C-Language Functions To know how to write C-language functions, you need to know how PostgreSQL internally represents base data types and how they can be passed to and…Base Types in C-Language Functions basebackup_to_shell — example "shell" pg_basebackup moduleManual ChaptersPG18F.4. basebackup_to_shell — example "shell" pg_basebackup module basebackup_to_shell adds a custom basebackup target called shell. This makes it possible to run pg_basebackup --target=shell o…basebackup_to_shell — example "shell" pg_basebackup module Basic API Structure for IndexesManual ChaptersPG1863.1. Basic API Structure for Indexes Each index access method is described by a row in the pg_am system catalog. The pg_am entry specifies a name and a handler function for the index access…Basic API Structure for Indexes Basic ConfigurationManual ChaptersPG1826.2.8.1. Basic Configuration Once streaming replication has been configured, configuring synchronous replication requires only one additional configuration step: synchronous_standby_names m…Basic Configuration Basic Regular ExpressionsManual ChaptersPG189.7.3.7. Basic Regular Expressions BREs differ from EREs in several respects. In BREs, |, +, and ? are ordinary characters and there is no equivalent for their functionality. The delimiters …Basic Regular Expressions Basic SetupManual ChaptersPG1818.10.1. Basic Setup The PostgreSQL server will listen for both normal and GSSAPI-encrypted connections on the same TCP port, and will negotiate with any connecting client whether to use GSS…Basic Setup Basic SetupManual ChaptersPG1818.9.1. Basic Setup With SSL support compiled in, the PostgreSQL server can be started with support for encrypted connections using TLS protocols enabled by setting the parameter ssl to on i…Basic Setup Basic StatementsManual ChaptersPG1841.5. Basic Statements In this section and the following ones, we describe all the statement types that are explicitly understood by PL/pgSQL. Anything not recognized as one of these stateme…Basic Statements Basic Text MatchingManual ChaptersPG1812.1.2. Basic Text Matching Full text searching in PostgreSQL is based on the match operator @@, which returns true if a tsvector (document) matches a tsquery (query). It doesn't matter whic…Basic Text Matching basic_archive — an example WAL archive moduleManual ChaptersPG18F.5. basic_archive — an example WAL archive module basic_archive is an example of an archive module. This module copies completed WAL segment files to the specified directory. This may not b…basic_archive — an example WAL archive module BEGINManual ChaptersPG18BEGIN BEGIN — start a transaction block Synopsis BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] where transaction_mode is one of: ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ …BEGIN BehaviorManual ChaptersPG1823.1.2. Behavior The locale settings influence the following SQL features: Sort order in queries using ORDER BY or the standard comparison operators on textual data The upper, lower, and ini…Behavior Behavior in Threaded ProgramsManual ChaptersPG1832.21. Behavior in Threaded Programs As of version 17, libpq is always reentrant and thread-safe. However, one restriction is that no two threads attempt to manipulate the same PGconn object…Behavior in Threaded Programs Behavior of B-Tree Operator ClassesManual ChaptersPG1865.1.2. Behavior of B-Tree Operator Classes As shown in Table 36.3, a btree operator class must provide five comparison operators, <, <=, =, >= and >. One might expect that <> should also be…Behavior of B-Tree Operator Classes BenchmarkManual ChaptersPG18F.19.4. Benchmark The source directory contrib/intarray/bench contains a benchmark test suite, which can be run against an installed PostgreSQL server. (It also requires DBD::Pg to be instal…Benchmark Benchmarking OptionsManual ChaptersPG18Benchmarking Options pgbench accepts the following command-line benchmarking arguments: -b scriptname[@weight]--builtin=scriptname[@weight] Add the specified built-in script to the list of s…Benchmarking Options Best Practices for Declarative PartitioningManual ChaptersPG185.12.6. Best Practices for Declarative Partitioning The choice of how to partition a table should be made carefully, as the performance of query planning and execution can be negatively affe…Best Practices for Declarative Partitioning BibliographyManual ChaptersPG18Bibliography Selected references and readings for SQL and PostgreSQL. Some white papers and technical reports from the original POSTGRES development team are available at the University of C…Bibliography BibliographyManual ChaptersPG18F.20.6. Bibliography The information to implement this module was collected from several sites, including: https://www.isbn-international.org/ https://www.issn.org/ https://www.ismn-internat…Bibliography Binary Data TypesManual ChaptersPG188.4. Binary Data Types The bytea data type allows storage of binary strings; see Table 8.6. Table 8.6. Binary Data Types Name Storage Size Description bytea 1 or 4 bytes plus the actual bina…Binary Data Types Binary FormatManual ChaptersPG18Binary Format The binary format option causes all data to be stored/read as binary format rather than as text. It is somewhat faster than the text and CSV formats, but a binary-format file i…Binary Format Binary String Functions and OperatorsManual ChaptersPG189.5. Binary String Functions and Operators This section describes functions and operators for examining and manipulating binary strings, that is values of type bytea. Many of these are equiv…Binary String Functions and Operators