This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

PostgreSQL

1 - Backup

Pg_basebackup is used to create backup. Also see the section ‘Schedule’.

CCX backups the Secondary server.

Backups are streamed directly to S3 staroge.

Schedule

The backup schedule can be tuned and backups can be paused

2 - Configuration

These settings cannot be changed as it affects system stability

Important default values

Parameter Default value
wal_keep_size 1024 (v.1.50+) / 512
max_wal_senders min 16, max 4 x Db Node count
wal_level replica
hot_standby ON
max_connections see below
shared_buffers instance_memory x 0.25
effective_cache_size instance_memory x 0.75
work_mem instance_memory / max_connections
maintenance_work_mem instance_memory/16

Max connections

The maximum number of connections depends on the instance size. The number of connections can be scaled by adding a new database secondary allowing of a larger instance size. The new replica can then be promoted to the new primary. See Promoting a replica for more information.

Instance size (GiB RAM) Max connections
< 4 100
8 200
16 400
32 800
64+ 1000

Archive mode

All nodes are configured with archive_mode=always.

Auto-vacuum

Auto-vacuum settings are set to default. Please read more about automatic vaccuming here

3 - Extensions

Supported extentions

Extension Postgres version
vector (pgvector) 15 and later
postgis 15 and later

Creating an extension

Connect to PostgreSQL using an admin account (e.g ccxadmin).

CREATE EXTENSION vector;
CREATE EXTENSION

See Postgres documentation for more information.

4 - Importing Data

This procedure describes how to import data to a PostgreSQL datastore located in CCX.

  • The PostgreSQL Datastore on CCX is denoted as the ‘replica’
  • The source of the data is denoted as the ‘source’

Create a database dump file

Dump the database schema from the <DATABASE> you wish to replicate:

pg_dump --no-owner -d<DATABASE> > /tmp/DATABASE.sql

Apply the dumpfile on the replica

postgres=# CREATE DATABASE <DATABASE>;

Copy the DSN from Nodes, Connection Information in the CCX UI. Change ‘ccxdb’ to <DATABASE>:

psql postgres://ccxadmin:.../<DATABASE> <  /tmp/DATABASE.sql

5 - Limitations

Every product has limitations. Here is a list PostgreSQL limitations:

Permissions

PostgreSQL users are created with the following permissions:

  • NOSUPERUSER, CREATEROLE, LOGIN, CREATEDB

6 - Restore

Postgres configures archive_command and archive_mode=always. Morever, during the restore the restore_command is set.

There are two options to restore a backup:

  • Restore a backup on the existing datastore
  • Restore a backup on a new datastore

Please note that restoring a backup may be a long running process.

This option allows to restore a backup with point in time recovery. The WAL logs are replayed until the desired PITR. Warning! Running several restores may change the timelines.

This option allows to restore a backup on a new datastore. This option does not currently support PITR.