2.3. Setting up Remote Mode #

pg_probackup3 supports operations in remote mode. The backup data can be obtained from a remote Postgres Pro server using the built-in replication protocol. Backups can be saved to an S3 storage or to an SSH server via the SFTP protocol.

Note

If you do not intend to use remote mode, skip this section and proceed to initializing a backup catalog.

Depending on the type of operation, you can choose from the following scenarios:

  • To connect to the Postgres Pro server to create a backup, use the standard connection options: --pghost, --pgport, --pgdatabase, and --pguser.

  • To save, restore, and validate backups in an S3 storage, use the S3 options. Refer to Section 2.3.2 for more details.

  • To manage backups on an SSH server via the SFTP protocol, use the SSH options. Refer to Section 2.3.1 for more details.

This way, both the backup catalog and the Postgres Pro instance to back up can be located on remote servers.

The typical workflow is as follows:

  • On your backup host, configure pg_probackup3 as explained in the section Installation and Setup. For the init, add-instance, backup, and set-config commands, make sure to specify the connection options to the server with the Postgres Pro instance, as well as the options to access the storage where the backups will be saved.

  • If you would like to rely on ARCHIVE WAL delivery mode, configure continuous WAL archiving from the database host as explained in the section Setting up continuous WAL archiving. For the archive-push command, specify the SSH options that point to the backup host with the backup catalog.

Note

To use continuous WAL archiving mode, pg_probackup3 executable files must be located on the Postgres Pro server. The Postgres Pro server calls pg_probackup3 with the archive-push and archive-get commands in accordance with the archive_command and restore_command parameters in its configuration.

For example, to create a local archive full backup of a Postgres Pro cluster located on a remote host with the 192.168.0.2 address on behalf of the postgres user through the 2302 port, run:

pg_probackup3 backup -B backup_dir --instance=instance_name -b FULL --pguser=postgres --pghost=192.168.0.2 --pgport=2302

To create a backup on an SSH server with the 10.0.3.77 address for the ubuntu user, run:

pg_probackup3 backup -B backup_dir --instance=instance_name -b FULL --pguser=postgres --pghost=192.168.0.2 --pgport=2302 --remote-host=10.0.3.77 --remote-user=ubuntu

The script for creating a backup in an S3 storage is as follows:

export PG_PROBACKUP_S3_PORT=9000
export PG_PROBACKUP_S3_ACCESS_KEY=admin
export PG_PROBACKUP_S3_SECRET_ACCESS_KEY=password
export PG_PROBACKUP_S3_REGION=us-west-2
export PG_PROBACKUP_S3_HOST=10.0.3.77
export PG_PROBACKUP_S3_BUCKET_NAME=test
pg_probackup3 backup -B backup_dir --instance=instance_name -b FULL --pguser=postgres --pghost=192.168.0.2 --pgport=2302 --s3

To set up continuous WAL archiving to an SSH server with the 10.0.3.77 address, set the archive_command server parameter, as follows:

archive_command = 'pg_probackup3 archive-push -B backup_dir --instance=instance_name --wal-file-name=%f --compress-algorithm=zstd --remote-host=10.0.3.77 --remote-user=ubuntu'

To set up continuous WAL archiving to an S3 storage, set the archive_command server parameter, as follows:

archive_command = 'pg_probackup3 archive-push -B backup_dir --instance=instance_name --compress-algorithm=zstd --wal-file-name=%f --s3 --config-file=s3.config'

The connection options must be saved in the s3.config file of the Postgres Pro data directory.

2.3.1. Configuring SSH Connection #

pg_probackup3 supports the remote mode that allows you to perform backup and WAL archiving operations remotely. To operate in the remote mode, pg_probackup3 uses an SSH connection between the Postgres Pro server (where pg_probackup3 is run) and the backup storage server. This allows managing backups on a remote host as if they were stored locally.

pg_probackup3 can store and read backup files and metadata on an SSH server using the SFTP protocol. This operation scheme is similar to that of S3.

If you are going to use pg_probackup3 in the remote mode via SSH, set up a passwordless SSH connection to the server via a public and private keys: set the public key on the server side and the private one — on the client.

Note

If the remote host is not specified, pg_probackup3 will look for the backup locally in --backup-path.

See the SSH Options section for details on the remote connection parameters.

pg_probackup3 in the remote mode via SSH works as follows:

  • All commands can be launched in the remote mode.

  • Operating in the remote mode does not require the pg_probackup3 binary to be installed on the remote system.

You can provide SSH server settings in the configuration file using the --config-file option. See the section Common Options for details.

2.3.2. Configuring S3 Connection #

pg_probackup3 supports S3 interface for storing backups. Backup data is transferred to and from S3 without saving it in intermediate locations thus eliminating the need of having a large temporary storage.

Note

S3 is available only when pg_probackup3 is used with Postgres Pro Enterprise.

If you are going to use pg_probackup3 with S3 interface, complete the following steps:

  1. Create a bucket with a unique name in the S3 storage for you future backups.

  2. Create ACCESS_KEY and SECRET_ACCESS_KEY tokens to be used for secure connectivity instead of your username and password.

    Note

    If a bucket and credentials are not provided, pg_probackup3 will look for the backup locally in --backup-path.

  3. For communication between pg_probackup3 and S3 server, set values of environment variables corresponding to your S3 server. For example:

    export PG_PROBACKUP_S3_HOST=127.0.0.1
    export PG_PROBACKUP_S3_PORT=9000
    export PG_PROBACKUP_S3_REGION=ru-msk
    export PG_PROBACKUP_S3_BUCKET_NAME=test1
    export PG_PROBACKUP_S3_ACCESS_KEY=admin
    export PG_PROBACKUP_S3_SECRET_ACCESS_KEY=password
    export PG_PROBACKUP_S3_HTTPS=ON
    

    Alternatively, you can provide S3 server settings in the configuration file or by using the command-line options. For more details, refer to the --config-file option in the section Common Options and to the section S3 Options.

    It makes sense to specify S3 server settings if --s3=minio, as described in the section S3 Options.

    The following environment variables can be specified:

    PG_PROBACKUP_S3_HOST

    Address of the S3 server. Can include the port number, separated by a colon. If the port number is not specified in a host string, the value of PG_PROBACKUP_S3_PORT is assumed. Do not add a colon if the port number is not specified.

    For example:

    export PG_PROBACKUP_S3_PORT=80
    export PG_PROBACKUP_S3_HOST="127.0.0.1:9000"
    

    In this example, for the “127.0.0.1” address, the port 9000 is explicitly specified and will be used instead of value 80, specified through PG_PROBACKUP_S3_PORT.

    PG_PROBACKUP_S3_PORT

    The port of the S3 server.

    PG_PROBACKUP_S3_REGION

    The region of the S3 server. The default value is us-east-1.

    PG_PROBACKUP_S3_BUCKET_NAME

    The name of the bucket on the S3 server.

    PG_PROBACKUP_S3_ACCESS_KEY
    PG_PROBACKUP_S3_SECRET_ACCESS_KEY

    Secure tokens on the S3 server.

    PG_PROBACKUP_S3_HTTPS

    The protocol to be used. Possible values:

    • ON or HTTPS — use HTTPS

    • Other than ON or HTTPS — use HTTP

    PG_PROBACKUP_S3_BUFFER_SIZE

    The size of the read/write buffer for communicating with S3, in MiB. The default is 16.

    PG_PROBACKUP_S3_RETRIES

    The maximum number of attempts to execute an S3 request in case of failures. The default is 3.

    PG_PROBACKUP_S3_TIMEOUT

    The maximum amount of time to execute an HTTP request to the S3 server, in seconds. The default is 300.

    PG_PROBACKUP_S3_IGNORE_CERT_VER

    Do not verify the certificate host and peer. The default is OFF.

    PG_PROBACKUP_S3_CA_CERTIFICATE

    Specify the path to a file with the trust Certificate Authority (CA) bundle.

    PG_PROBACKUP_S3_CA_PATH

    Specify the directory with the trust CA certificates.

    PG_PROBACKUP_S3_CLIENT_CERT

    Set up the SSL client certificate.

    PG_PROBACKUP_S3_CLIENT_KEY

    Set up a private key file for the TLS and SSL client certificate.

2.3.2.1. S3 Required Privileges #

The following minimal privileges to the target S3 bucket should be granted for the access key used by pg_probackup3 with versioning disabled:

  • for the init command:

    s3:GetBucketVersioning
    s3:ListBucket
    
  • for the add-instance, set-config, and set-backup commands:

    s3:GetBucketVersioning
    s3:ListBucket
    s3:PutObject
    
  • for the del-instance command:

    s3:GetBucketVersioning
    s3:ListBucket
    s3:DeleteObject
    
  • for the backup, archive-push, and file-map commands:

    s3:ListBucket
    s3:PutObject
    s3:GetBucketVersioning
    s3:AbortMultipartUpload
    s3:GetObject
    
  • for the restore, fuse, show, show-config, and send-backup commands:

    s3:GetBucketVersioning
    s3:GetObject
    s3:ListBucket
    
  • for the validate command:

    s3:GetBucketVersioning
    s3:GetObject
    s3:ListBucket
    s3:PutObject
    
  • for the merge and retention commands:

    s3:ListBucket
    s3:PutObject
    s3:GetBucketVersioning
    s3:AbortMultipartUpload
    s3:GetObject
    s3:DeleteObject
    
  • for the delete command:

    s3:GetBucketVersioning
    s3:GetObject
    s3:ListBucket
    s3:DeleteObject
    

When versioning is enabled, the following additional privileges are required:

  • s3:ListBucketVersions for all commands that require s3:ListBucket

  • s3:DeleteObjectVersion for all commands that require s3:DeleteObject

FAQ