Backing up and restoring with pg_dump v6.5.0

Back up a database with pg_dump and pg_restore, which are included with Postgres, and restore it to a new cluster, for example to create QA or development instances or to move data into or out of EDB Postgres Distributed (PGD). Install Postgres and PGD on every host in the new cluster, restore the backup on a single node, then add the other nodes with pgd node setup, which copies the data from the restored node.

Backing up the database

Back up the database in separate pre-data, data, and post-data sections to reduce the risk of global lock timeouts. Run the commands in a terminal on the server hosting the node, or on any other machine that has pg_dump installed and can connect to it. Take backups ahead of time, on a schedule you choose, because pg_dump reads from a running node and can't back up a node that's already lost. The commands write the backup files to the machine you run them from, so keep them wherever suits your needs. Anything written after the last backup is lost.

pg_dump -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB -v --exclude-schema='"bdr"' --exclude-extension='"bdr"' --section=pre-data -Fc -f pgd-pre-data.dump
pg_dump -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB -v --exclude-schema='"bdr"' --exclude-extension='"bdr"' --section=data -Fc -f pgd-data.dump
pg_dump -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB -v --exclude-schema='"bdr"' --exclude-extension='"bdr"' --section=post-data -Fc -f pgd-post-data.dump

Where:

• $PG_HOST: The host name of the node you're backing up.
• $PG_PORT: The port of the node you're backing up.
• $PG_USER: The user to connect as. The user needs to be able to select data from every object you back up. See the pg_dump documentation.
• $PGD_DB: The name of the PGD database to back up.
• --exclude-schema and --exclude-extension: Exclude the bdr schema and extension from the backup.
• --section: The section to back up, either pre-data, data, or post-data.
• -Fc: Write the backup in the custom format.
• -f: The file to write the backup to.
• -v: Run in verbose mode.

A backup from one node captures all your data if that node subscribes to every replication set and has no unreplicated local data. Nodes can also lag behind each other, so the backup reflects the state of the node you back up. Take the backup when there's little write activity and replication has caught up, so the node has all the changes. For an alternative approach, see Performing physical backups.

Preparing the destination cluster

Prepare the destination cluster before you restore the backup.

1. Install Postgres and PGD on every host in the new cluster, using the same PGD version on each. See Installing and configuring EDB Postgres Distributed for installation options.

2. Create the first node as a single-node cluster by running pgd node setup on its host. See Configuring the first node for an example.

3. Temporarily set the following parameters in postgresql.conf on every node in the cluster, using the same values on each. The parameters reduce the frequency of checkpoints, increase the memory for building indexes, allow large transactions to go through and be applied in parallel, and give the restore more time to acquire global locks.

   • max_wal_size = 100GB increases the default of 1GB to a large value that's still a fraction of your disk space, since the non-WAL data must also fit. The larger value decreases the frequency of checkpoints.
   • maintenance_work_mem = 1GB increases the memory for building indexes from the default of 64MB. For example, use 1GB on a server with 128GB of total RAM.
   • wal_receiver_timeout = 1h and wal_sender_timeout = 1h increase the timeouts from 1 minute to allow large transactions through.
   • bdr.writers_per_subscription = 5 increases the number of writers from the default of 2 to make better use of parallel apply.
   • bdr.raft_global_election_timeout = 20s and bdr.raft_group_election_timeout = 10s increase the Raft-related election timeouts from the defaults of 6s and 3s.
   • bdr.writer_input_queue_size = 32MB increases the size of the shared memory queue the receiver uses to send data to the writer process from the default of 1MB.
   • bdr.global_lock_timeout = 30min increases the maximum wait for a global lock from the default of 1 minute. The post-data section runs many DDL statements in sequence, such as creating indexes and constraints, and each one can take a long time to acquire the global lock.

   Additionally, confirm the following:

   • The default bdr.streaming_mode = 'auto' isn't overridden, so transactions are streamed.
   • Node group-level settings, such as num_writers, don't override any session or postgresql.conf settings listed above.

Restoring the backup

Restore the backup to the first node of the new cluster. Run the following steps in a terminal on the server hosting that node, or on any other machine that can connect to it. Copy the three backup files, pgd-pre-data.dump, pgd-data.dump, and pgd-post-data.dump, to that machine first, and make sure pg_restore is installed there. Set PG_PORT to the node's Postgres port, not the connection manager port.

1. Restore the pre-data section:

   → WrapCopy

   PGOPTIONS="-c bdr.commit_scope=local -c bdr.default_sequence_kind=local" pg_restore -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB --section=pre-data pgd-pre-data.dump

2. Restore the data section:

   → WrapCopy

   PGOPTIONS="-c bdr.commit_scope=local -c bdr.default_sequence_kind=local" pg_restore -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB --section=data pgd-data.dump

3. Wait for all replication slots to pass the current write position using bdr.wait_slot_confirm_lsn:

   → WrapCopy

   psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB -c 'SELECT bdr.wait_slot_confirm_lsn(NULL, NULL)'

4. Restore the post-data section:

   → WrapCopy

   PGOPTIONS="-c bdr.commit_scope=local -c bdr.default_sequence_kind=local" pg_restore -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB --section=post-data pgd-post-data.dump

5. Wait again for all replication slots to pass the current write position:

   → WrapCopy

   psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PGD_DB -c 'SELECT bdr.wait_slot_confirm_lsn(NULL, NULL)'

Where:

• $PG_HOST, $PG_PORT, $PG_USER, and $PGD_DB: The host, port, user, and database of the node you're restoring to.
• PGOPTIONS="-c bdr.commit_scope=local -c bdr.default_sequence_kind=local": Sets the bdr.commit_scope and bdr.default_sequence_kind parameters to local for the restore session. Restoring every sequence as local keeps its data type, whatever the default sequence kind of the cluster. For example, a snowflakeid sequence creates 64-bit values that don't fit an integer column. See Resetting sequence kinds.
• --section: The section to restore, either pre-data, data, or post-data.
• pgd-pre-data.dump, pgd-data.dump, and pgd-post-data.dump: The backup file for each section.

Monitoring a restore

Monitor the restore by running the following queries in a separate psql session on the node you're restoring to, using the same connection details as the restore ($PG_HOST, $PG_PORT, $PG_USER, and $PGD_DB) while pg_restore is running.

Check the database size on the restoring node. The size grows as the restore makes progress and approaches the size of the original node. However, due to bloat, a restored database is always a little smaller than the original.

SELECT pg_size_pretty(pg_database_size('bdrdb'));

Check the rate of replication. However, the progress info can be misleading for big transactions. Lag and progress appear to stair-step.

SELECT * FROM bdr.node_replication_rates;

Check what pg_restore is currently doing, including whether it's blocked and what it's waiting on. The status changes continuously while the restore runs.

SELECT
  application_name,
  state,
  wait_event_type,
  wait_event,
  now() - state_change AS state_change_ago
FROM
  pg_stat_activity
WHERE
  application_name LIKE '%pg_restore%';

Check the following views to see issues with replication slots, accumulated lag, and broken replication, and statistics for each subscription.

• pg_catalog.pg_stat_replication_slots
• pg_catalog.pg_replication_slots
• bdr.node_slots
• bdr.stat_subscription, to check on parallel apply or transaction stream

Adding the remaining nodes

Add the remaining nodes to the cluster by running pgd node setup on the host of each node, with --cluster-dsn pointing to the node you restored to. The command does a physical join, which copies the data from that node. See Configuring a second node for an example and pgd node setup for all options.

Resetting sequence kinds

Reset the sequence kinds after the restore, because pg_dump dumps both local and global sequences as local sequences. This behavior is intentional, so you can dump a PGD schema and port it to other Postgres databases. The dump doesn't keep the sequence kind, and creating sequences with the default kind could give them a data type that doesn't match their columns. The restore commands set bdr.default_sequence_kind to local, so every sequence comes back as a standard local sequence.

The restored cluster holds no record of the original sequence kinds, so record them on the source cluster when you take the backup. Run the following query on the source cluster and keep the output with the backup files:

SELECT nspname, relname, seqkind FROM bdr.sequences ORDER BY nspname, relname;

On the restored cluster, set each sequence back to its original kind with bdr.alter_sequence_set_kind. Run the statements in a session with bdr.commit_scope set to local, so they don't wait for a synchronous commit. For snowflakeid sequences, pass only the kind:

SET bdr.commit_scope = 'local';
SELECT bdr.alter_sequence_set_kind('<schema>.<sequence>'::regclass, 'snowflakeid');

For galloc sequences, first move the start of the sequence past every value already in use with ALTER SEQUENCE ... RESTART, which sets the current value of the sequence on the node. Then set the kind with the same start value, which sets where the first galloc range starts. Both steps are needed, so new values don't collide with existing rows:

SET bdr.commit_scope = 'local';
ALTER SEQUENCE <schema>.<sequence> START WITH <start_value> RESTART;
SELECT bdr.alter_sequence_set_kind('<schema>.<sequence>'::regclass, 'galloc', <start_value>);

For a sequence that fills a table column, use the largest value in that column on any node plus one as the start value. Get the largest value on every node with bdr.run_on_all_nodes:

SELECT bdr.run_on_all_nodes('SELECT max(<column>) FROM <schema>.<table>');

For a sequence that doesn't fill a table column, run SELECT last_value FROM <schema>.<sequence> the same way and use the largest result plus one as the start value.

Reset the sequence kinds before applications write to the restored cluster, so new values can't collide with existing ones. See Converting a local sequence to a galloc sequence for the other prerequisites.