Postgres Pro Documentation

https://postgrespro.com/docs
Admin
Postgres Pro Documentation provides mirrored PostgreSQL documentation alongside official...
Tokens:496,815
Snippets:8,038
Trust Score:9.6
Update:3 months ago
Show doc for...
Tokens:
Raw
### Example postgresql.conf File

Source: https://postgrespro.com/docs/enterprise/current/config-setting

Illustrates the basic syntax and common parameters found in the `postgresql.conf` file. Each parameter is on a new line, with optional equals signs and comments starting with '#'.

```postgresql
# This is a comment
log_connections = all
log_destination = 'syslog'
search_path = '"$user", public'
shared_buffers = 128MB

```

--------------------------------

### Complex Setup: Create Unprivileged User and Schema

Source: https://postgrespro.com/docs/enterprise/current/pgpro-pwr

Create an unprivileged user (`pwr_user`) who will own the extension schema and collect samples. Then, create a schema for the pgpro_pwr installation, authorizing it to this user.

```sql
CREATE USER pwr_user;
CREATE SCHEMA profile AUTHORIZATION pwr_user;

```

--------------------------------

### Simple Setup: Create Schema and Extension

Source: https://postgrespro.com/docs/enterprise/current/pgpro-pwr

Use this procedure when pgpro_pwr is installed on the target cluster as a superuser. This involves creating a dedicated schema and then the extension within that schema.

```sql
CREATE SCHEMA profile;
CREATE EXTENSION pgpro_pwr SCHEMA profile;

```

--------------------------------

### Start PostgreSQL Server

Source: https://postgrespro.com/docs/enterprise/current/app-pg-ctl

Use this command to start the PostgreSQL server and wait until it is accepting connections.

```bash
$ **pg_ctl start**
```

--------------------------------

### Example: Corresponding CREATE SERVER and CREATE USER MAPPING

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-creating-a-simple-secret.html

The SQL commands generated by the example function call for creating a foreign server and user mapping.

```sql
CREATE SERVER simple_s3_secret
  TYPE 'S3'
  FOREIGN DATA WRAPPER duckdb
  OPTIONS (region 'us-east-1', endpoint 'minio.example.com:9000', url_style 'path', use_ssl 'false');

CREATE USER MAPPING FOR PUBLIC
  SERVER simple_s3_secret
  OPTIONS (
    key_id 'AKIAIOSFODNN7EXAMPLE',
    secret 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
);
```

--------------------------------

### System Metrics Query Output Example

Source: https://postgrespro.com/docs/enterprise/current/proxima

This example shows the typical output format when querying system metrics like CPU load and available RAM.

```sql
           name           | class  | node_id |       value
--------------------------+--------+---------+--------------------
 system.cpu_load          | system |       0 |  6.779661016949152
 system.avail_ram_b       | system |       0 |        22180810752
 system.total_ram_b       | system |       0 |        32619315200
 system.io_avg_latency_ms | system |       0 | 3.6666666666666665

```

--------------------------------

### Primary Connection Info Example

Source: https://postgrespro.com/docs/pg_probackup3/3.4/app-pgprobackup

Provides an example of how to set the primary_conninfo parameter, which is used when restoring as a replica. This option is ignored unless the -R flag is specified.

```bash
--primary-conninfo="host=192.168.1.50 port=5432 user=foo password=foopass"
```

--------------------------------

### S3 Configuration File Example

Source: https://postgrespro.com/docs/pg_probackup3/3.4/app-pgprobackup

This is an example of an S3 configuration file. Settings in this file override environment variables. Passwords are omitted and displayed as asterisks.

```ini
access-key=admin
s3=on
s3-bucket=test
s3-host=127.0.0.1
s3-port=9000
s3-region=us-west-2
secret-key=***

```

--------------------------------

### Proxy Table Name Examples

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-pga-proxy-table.html

Provides concrete examples of proxy table names, demonstrating the application of the naming convention for different pgpro_metastore object types.

```sql
s_localstorage_00001
```

```sql
t_hits_00002
```

```sql
f_shared_00001
```

--------------------------------

### Example: Creating a Local Storage

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-creating-a-storage.html

Execute this command to create a local storage named 'local_storage_example' with specified root and temporary directory URIs.

```sql
SELECT metastore.add_storage('local_storage_example', 'file:///tmp/local_storage_example/', 'file:///tmp/local_storage_example/tmp_dir/');
```

--------------------------------

### Initialize a new backup instance with pg_probackup3

Source: https://postgrespro.com/docs/pg_probackup3/3.4/app-pgprobackup

Use this command to set up a new backup instance. It initializes the catalog and creates the configuration file. The `--skip-if-exists` option can prevent errors if the catalog is already initialized. WAL archive location can be customized with `--wal-archive-dir`.

```bash
pg_probackup3 add-instance -B _backup_dir_ -D _data_dir_ --instance=_instance_name_
[--skip-if-exists] [--wal-archive-dir=_directory_path_] [--wal-tree] [--help]
[_s3_options_] [_ssh_options_] [_logging_options_]
[_connection_options_] [_compression_options_] [_retention_options_]
[_buffer_options_]
```

--------------------------------

### Read Committed Isolation Example

Source: https://postgrespro.com/docs/enterprise/current/transaction-iso

Demonstrates a scenario where a DELETE command might not have the expected effect in Read Committed mode due to concurrent updates. This occurs because each command starts with a new snapshot.

```sql
BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session:  DELETE FROM website WHERE hits = 10;
COMMIT;

```

--------------------------------

### Example: Creating an S3 Storage

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-creating-a-storage.html

Execute this command to create an S3 storage named 's3_storage_example' with specified S3 bucket URIs for root and temporary directories.

```sql
SELECT metastore.add_storage('s3_storage_example', 's3://demo/s3_storage_example/','s3://demo/s3_storage_example/tmp/');
```

--------------------------------

### Catchup Example: FULL Mode for New Standby

Source: https://postgrespro.com/docs/enterprise/current/app-pgprobackup

Add a new remote standby server by running the catchup command in FULL mode on four parallel threads. This is suitable for initial setup or when a full resync is needed.

```bash
pg_probackup catchup --source-pgdata=/master-pgdata --destination-pgdata=/replica-pgdata -p 5432 -d postgres -U remote-postgres-user --stream --backup-mode=FULL --remote-host=remote-hostname --remote-user=remote-unix-username -j 4
```

--------------------------------

### Include multiple configuration files

Source: https://postgrespro.com/docs/enterprise/current/config-setting

This example demonstrates how to include separate configuration files for shared, memory-specific, and server-specific settings, allowing for modular management of different server environments.

```postgresql
include 'shared.conf'
include 'memory.conf'
include 'server.conf'
```

--------------------------------

### Verify pg_probackup3 Installation

Source: https://postgrespro.com/docs/pg_probackup3/3.4/pbk-installation

Check the installed pg_probackup3 version to confirm successful installation.

```bash
pg_probackup3 --version
```

--------------------------------

### START TRANSACTION

Source: https://postgrespro.com/docs/postgrespro/18/reference.html

Starts a transaction block.

```APIDOC
## START TRANSACTION

### Description
Starts a transaction block.

### Method
SQL Statement

### Endpoint
N/A

### Parameters
N/A

### Request Example
N/A

### Response
N/A
```

--------------------------------

### Build and Install All Contrib Modules

Source: https://postgrespro.com/docs/postgresql/18/contrib.html

To build and install all optional components from the contrib directory, navigate to the 'contrib' directory in a configured source tree and run 'make' followed by 'make install'.

```bash
make
make install
```

--------------------------------

### Remove Node Example

Source: https://postgrespro.com/docs/enterprise/current/biha-reference

Example of removing a node from the cluster using the remove_node function.

```sql
biha_db=# SELECT biha.remove_node(3);
 remove_node
-------------
 t
(1 row)
```

--------------------------------

### Initialize Metastore

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-secret-management-scenario.html

Initialize the metastore catalog. This is a prerequisite for adding storage and tables.

```sql
SELECT metastore.init(true);
```

--------------------------------

### Initialize pg_probackup Catalog

Source: https://postgrespro.com/docs/enterprise/current/app-pgprobackup

Initializes a new backup catalog on the backup host. This is the first step before adding any instances.

```bash
backup_user@backup_host:~$ pg_probackup init -B /mnt/backups
INFO: Backup catalog '/mnt/backups' successfully initialized

```

--------------------------------

### Verify pg_probackup3 Installation Version

Source: https://postgrespro.com/docs/pg_probackup3/3.4/upgrading-pg-probackup3.html

Check the installed version of pg_probackup3 to confirm the upgrade was successful.

```bash
pg_probackup3 --version

```

--------------------------------

### Initialize ora2pgpro Project

Source: https://postgrespro.com/docs/ora2pgpro/26/app-ora2pgpro.html

Initializes a new ora2pgpro project with a standard directory structure, configuration file, and automation scripts. Use `--project_base` to specify the parent directory for the project.

```bash
ora2pgpro --project_base /app/migration/ --init_project test_project
	Creating project test_project.
	/app/migration/test_project/
		schema/
			dblinks/
			directories/
			functions/
			grants/
			mviews/
			packages/
			partitions/
			procedures/
			sequences/
			synonyms/
			tables/
			tablespaces/
			triggers/
			types/
			views/
		sources/
			functions/
			mviews/
			packages/
			partitions/
			procedures/
			triggers/
			types/
			views/
		data/
		config/
		reports/

	Generating generic configuration file
	Creating script export_schema.sh to automate all exports.
	Creating script import_all.sh to automate all imports.

```

--------------------------------

### pg_backup_start

Source: https://postgrespro.com/docs/enterprise/current/functions-admin

Prepares the server to begin an on-line backup. Accepts an optional boolean to execute as quickly as possible.

```APIDOC
## pg_backup_start ( label text [, fast boolean ] ) -> pg_lsn

### Description
Prepares the server to begin an on-line backup. The only required parameter is an arbitrary user-defined label for the backup. (Typically this would be the name under which the backup dump file will be stored.) If the optional second parameter is given as `true`, it specifies executing `pg_backup_start` as quickly as possible. This forces an immediate checkpoint which will cause a spike in I/O operations, slowing any concurrently executing queries.

### Parameters
#### Path Parameters
- **label** (text) - Required - An arbitrary user-defined label for the backup.
- **fast** (boolean) - Optional - If `true`, executes `pg_backup_start` as quickly as possible, forcing an immediate checkpoint.

### Response
#### Success Response (200)
- **return value** (pg_lsn) - The write-ahead log location after the backup preparation.
```

--------------------------------

### Example: Calling duckdb.create_simple_secret()

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-creating-a-simple-secret.html

An example demonstrating the call to the duckdb.create_simple_secret() function with specific S3 parameters.

```sql
SELECT duckdb.create_simple_secret(
    type      := 'S3',
    key_id    := 'AKIAIOSFODNN7EXAMPLE',
    secret    := 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
    region    := 'us-east-1',
    url_style := 'path',
    endpoint  := 'minio.example.com:9000',
    use_ssl   := 'false'
);
```

--------------------------------

### Create and Connect to Backup Database

Source: https://postgrespro.com/docs/enterprise/current/app-pgprobackup

Create a dedicated database for configuration and connect to it. This is recommended for security reasons.

```sql
postgres=# CREATE DATABASE backupdb;
postgres=# \c backupdb

```

--------------------------------

### Install amcheck Extension

Source: https://postgrespro.com/docs/enterprise/current/app-pgprobackup

Install the amcheck extension in every database of the cluster to enable logical verification.

```sql
CREATE EXTENSION amcheck;

```

--------------------------------

### Install pg_probackup3 on Debian Systems

Source: https://postgrespro.com/docs/pg_probackup3/3.4/pbk-installation

Install the pg-probackup3 package on Debian family systems using apt.

```bash
sudo apt update
sudo apt install pg-probackup3
```

--------------------------------

### FTS Query Example

Source: https://postgrespro.com/docs/ora2pgpro/26/ora2pgpro-oracle-schema-to-export

Example of how to rewrite a CONTAINS() clause using to_tsvector() for querying FTS indexes.

```sql
SELECT id,title FROM t_document
  WHERE to_tsvector(title) @@ to_tsquery('search_word');

```

--------------------------------

### S3 Storage Path Example

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-storages.html

Example of a URI structure for storing OLAP data in an S3 storage.

```text
s3://bucket/db_name/schema_name/table_name
```

--------------------------------

### Initialize pg_probackup

Source: https://postgrespro.com/docs/enterprise/current/app-pgprobackup

Initializes the pg_probackup environment in the specified backup directory. Use --skip-if-exists to avoid re-initialization if the directory already exists.

```bash
pg_probackup init -B _backup_dir --skip-if-exists
```

--------------------------------

### Install an Extension

Source: https://postgrespro.com/docs/postgrespro/18/contrib.html

Use the CREATE EXTENSION command to register new SQL objects provided by an extension in the current database. Run this command in every database where the extension's facilities are needed, or in the template1 database for default availability in new databases.

```sql
CREATE EXTENSION _extension_name_;
```

--------------------------------

### Install HAProxy, Keepalived, and psmisc

Source: https://postgrespro.com/docs/ppem/2/ppem-installation-in-the-high-availability-mode.html

Installs necessary packages for high availability on HAProxy servers using apt-get.

```bash
sudo apt-get install haproxy keepalived psmisc

```

--------------------------------

### Run ora2pgpro with Default Configuration

Source: https://postgrespro.com/docs/ora2pgpro/26/app-ora2pgpro.html

Execute ora2pgpro using the default configuration file located at `/etc/ora2pgpro/ora2pgpro.conf`. This is the simplest way to start the migration process if the default configuration is set up.

```bash
/usr/local/bin/ora2pgpro

```

--------------------------------

### KVik INVALIDATE Command Example with redis-cli

Source: https://postgrespro.com/docs/enterprise/current/proxima

Example of using the INVALIDATE command with redis-cli to clear the entire cache.

```bash
redis-cli -p 4550 INVALIDATE 'CRUD'
```

--------------------------------

### init

Source: https://postgrespro.com/docs/enterprise/current/app-pgprobackup

Initializes the backup catalog in the specified directory, which will store backup copies, WAL archive, and meta information. The directory must be empty or the `--skip-if-exists` option can be used.

```APIDOC
## init

### Description
Initializes the backup catalog in _`backup_dir`_that will store backup copies, WAL archive, and meta information for the backed up database clusters. If the specified _`backup_dir`_already exists, it must be empty. Otherwise, pg_probackup displays a corresponding error message. You can ignore this error by specifying the `--skip-if-exists` option. Although the backup will not be initialized, the application will return 0 code.

### Usage
```
pg_probackup init -B _backup_dir_ [--skip-if-exists] [_s3_options_] [--help]
[_logging_options_]
```
```

--------------------------------

### Oracle DSN Example (Service Name)

Source: https://postgrespro.com/docs/ora2pgpro/26/ora2pgpro-oracle-database-connection

Example of an Oracle DSN for version 18c using a service name.

```plaintext
dbi:Oracle:host=192.168.1.29;service_name=pdb1;port=1521
```

--------------------------------

### Initialize Ora2PgPro Project

Source: https://postgrespro.com/docs/ora2pgpro/26/ora2pgpro-usage.html

Create a new migration project with a specified base directory and project name. This command generates a project template including configuration files and export/import scripts.

```bash
ora2pgpro --project_base /app/migration/ --init_project test_project

```

--------------------------------

### Install DuckDB Extension

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-administration-functions.html

Installs a DuckDB extension and configures it for automatic loading in pgpro_axe sessions. Can only be executed by a superuser by default.

```sql
SELECT duckdb.install_extension('iceberg');
SELECT duckdb.install_extension('avro', 'community');

```

--------------------------------

### Start Postgres Pro Enterprise Server

Source: https://postgrespro.com/docs/enterprise/current/proxima

Use pg_ctl to start the Postgres Pro Enterprise server after configuration changes.

```bash
pg_ctl start -D _PGDATA_directory_
```

--------------------------------

### Initialize Metadata Catalog

Source: https://postgrespro.com/docs/axe/1/postgres-pro-axe-initializing-the-metadata-catalog.html

Execute this command to initialize the metadata catalog. The `force_init` parameter is optional and defaults to `false`.

```sql
SELECT metastore.init(force_init bool DEFAULT false)
```

--------------------------------

### Start Referee Node with pg_ctl

Source: https://postgrespro.com/docs/enterprise/current/biha-setting-up-a-biha-cluster

Starts the referee node after it has been added to the BiHA cluster. Ensure the shared_buffers configuration is appropriate.

```bash
pg_ctl start -D _referee_PGDATA_directory_
```

--------------------------------

### Create Backup Database and Connect

Source: https://postgrespro.com/docs/pg_probackup3/3.4/pbk-configuring-the-database-cluster

Creates a new database named 'backupdb' and connects to it. This is a prerequisite for setting up backup permissions.

```sql
CREATE DATABASE backupdb;
\c backupdb
```

--------------------------------

### Modify Struct Directive Example

Source: https://postgrespro.com/docs/ora2pgpro/26/ora2pgpro-oracle-schema-to-export

Example of using the MODIFY_STRUCT directive to specify which columns to extract for specific tables during export.

```ora2pg
MODIFY_STRUCT   T_TEST1(id,dossier) T_TEST2(id,fichier)

```
Tokens:
Raw