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 -Bbackup_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 -Bbackup_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 -Bbackup_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 -Bbackup_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 -Bbackup_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:
Create a bucket with a unique name in the S3 storage for you future backups.
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.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-fileoption 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_HOSTAddress 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_PORTis 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_PORTThe port of the S3 server.
PG_PROBACKUP_S3_REGIONThe region of the S3 server. The default value is
us-east-1.PG_PROBACKUP_S3_BUCKET_NAMEThe name of the bucket on the S3 server.
PG_PROBACKUP_S3_ACCESS_KEYPG_PROBACKUP_S3_SECRET_ACCESS_KEYSecure tokens on the S3 server.
PG_PROBACKUP_S3_HTTPSThe protocol to be used. Possible values:
ONorHTTPS— use HTTPSOther than
ONorHTTPS— use HTTP
PG_PROBACKUP_S3_BUFFER_SIZEThe size of the read/write buffer for communicating with S3, in MiB. The default is
16.PG_PROBACKUP_S3_RETRIESThe maximum number of attempts to execute an S3 request in case of failures. The default is
3.PG_PROBACKUP_S3_TIMEOUTThe maximum amount of time to execute an HTTP request to the S3 server, in seconds. The default is
300.PG_PROBACKUP_S3_IGNORE_CERT_VERDo not verify the certificate host and peer. The default is
OFF.PG_PROBACKUP_S3_CA_CERTIFICATESpecify the path to a file with the trust Certificate Authority (CA) bundle.
PG_PROBACKUP_S3_CA_PATHSpecify the directory with the trust CA certificates.
PG_PROBACKUP_S3_CLIENT_CERTSet up the SSL client certificate.
PG_PROBACKUP_S3_CLIENT_KEYSet 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:ListBucketVersionsfor all commands that requires3:ListBuckets3:DeleteObjectVersionfor all commands that requires3:DeleteObject