Greengage DB backup and recovery. Part 3

Two items on this list deserve a closer look.

These two settings are what turn a directory of files into a specific segment. gp_contentid identifies the part of the distributed data that the instance holds. gp_dbid identifies the physical instance itself. A primary segment and its mirror share the same gp_contentid value but have different gp_dbid values, which is exactly what sets them apart.

gp_contentid is stored in postgresql.conf, while gp_dbid is stored in a separate file:

In Greengage, the on-disk path of a user tablespace includes the gp_dbid:

The gp_dbid is part of the path so that several segments on one host can share a tablespace location without conflicts. As a result, editing internal.auto.conf is only one step in reassigning gp_dbid. The tablespace directories must also be moved to a subdirectory named after the new gp_dbid. The symbolic links in pg_tblspc/ must then be updated to point to those directories. The rule is simple: when the gp_dbid changes, the tablespace layout changes with it. Any restore that assigns a different gp_dbid to a segment must also update its tablespace paths. If the cluster has no user tablespaces, this step is unnecessary.

A segment’s pg_hba.conf is specific to its primary and mirror pair: it is generated with the addresses of both instances. Greengage writes these entries itself when mirrors are added or recovered, and they look like this:

The primary and mirror addresses are recorded as they were when the file was written. If you restore this PGDATA on another host or pair the restored segment with a different mirror, the connection rules in the file no longer match the environment. The segment starts (its data is intact), but replication connections are rejected because the new mirror’s address is not on the list.

The mappings in pg_ident.conf can fail for a similar reason. If the target hosts use a different OS user, the mappings no longer apply, and you cannot connect to the database.

The fix is either to save the target cluster’s versions of these files before the restore and reuse them afterward or to regenerate them for the target topology as part of the restore. This is exactly what gprecoverseg and gpaddmirrors do for the segments they manage.

Because the backup includes the configuration, there is also a less obvious side effect. One example is the interconnect network protocol, set with gp_interconnect_type. If this parameter is set to proxy, segments communicate over the interconnect using the addresses in gp_interconnect_proxy_addresses. This setting contains IP addresses or hostnames from the original cluster. As a result, a distributed query on the restored cluster appears to hang. The master cannot reach the segments because it is trying to connect to addresses from the original cluster. One way to fix this is to start only the master and use utility mode to change gp_interconnect_proxy_addresses to match the target cluster’s topology. Then start the rest of the cluster. A simpler option is to switch the interconnect type to udpifc in utility mode and set gp_interconnect_proxy_addresses to an empty string.

A mirror’s data is byte-for-byte identical to its primary segment’s data. We already have all of that data in the repository. So instead of restoring the primary segment and then cloning it, we restore the primary segment and its mirror from the same backup set at the same time. Each segment instance, primary or mirror, then becomes an independent restore task.

The two restored instances should differ only in the identity settings described above:

In other words, restoring a mirror from the same backup set as its primary is perfectly valid.

Restoring the data from the backup is relatively straightforward. The more interesting part is what happens when the primary segment is promoted.

Both copies (primary and mirror) replay WAL up to the same restore point, so at that moment they are identical. The mirror no longer needs a base backup from the primary because it has already replayed WAL to the same position. It only needs to connect to the primary for replication. Two points need a closer look.

Timeline switch. When the primary segment finishes point-in-time recovery and is promoted, it increments its timeline number. New WAL is then written on timeline 2, while the mirror is still on timeline 1. A replica does not switch to the new timeline automatically. The mirror must be configured to follow the latest timeline. It must also be able to fetch the new timeline’s history file, either from the archive through restore_command or by streaming it from the promoted primary segment:

The replication slot is dropped. On every primary segment, Greengage keeps a physical replication slot named internal_wal_replication_slot. This slot keeps the primary segment from recycling WAL that its mirror has not yet received. However, when a segment starts in archive recovery mode, Greengage drops this slot:

After the restore, the primary segment has no slot to prevent it from recycling WAL that the mirror still needs. The slot must be recreated before the mirror connects:

The complete sequence is as follows:

There is a trade-off to consider. gprecoverseg -aF is the standard approach: it is a single command, and it is well tested. The optimization described above involves a long sequence of steps, and each step must be completed correctly.

It is worth the complexity only when the data volume is large enough for the shorter restore time to matter. It also requires thorough checks: match every dbid/content pair against the planned topology, check the local configuration, confirm the replication state, and so on.

First, check these three requirements:

The source topology is saved in the backup metadata, as described in the first article. We map it to the target topology by matching segments with the same content value:

Then, for each segment, we restore the data from the source stanza seg<content> into the target data directory. We update gp_dbid, port, and the tablespace paths to match the target topology. We also regenerate pg_hba.conf and pg_ident.conf for the target hosts and disable the inherited archive_command. Finally, we overwrite gp_segment_configuration on the target master with the target topology and start the cluster.

Now we come to what is probably the most important part. The restore problems covered so far have visible symptoms: if you do not fix them, the cluster either fails to restore or does not work as expected. This problem is different: if everything else is handled correctly, the cluster works normally, but WAL archiving is broken at best. This can affect both the cluster’s future operation and subsequent restores.

In our ggbm example, the restored segment’s postgresql.conf still contains the source cluster’s archive_command:

The stanza name comes from %c, the segment’s content ID, so it is correct on the target cluster. However, the destination repository is determined by the pgbackrest.conf file specified by PGBACKREST_CONFIG on the target host. If that file still refers to the source cluster’s repository, the restored cluster begins sending WAL to the source cluster’s stanza immediately after startup.

It is worth explaining why the usual pgbackrest check does not detect this problem. It validates a stanza against the system identifier and version of the cluster writing to it. A physical copy inherits both values from its source, so the check cannot distinguish the clone from the original cluster.

The safe sequence is:

Once a backup can be restored into a different cluster, it is no longer just a disaster recovery tool. It becomes a tool for provisioning new clusters.

Keep one full backup and a named restore point as a reference image, and you can restore new clusters from it for many purposes: a staging environment with a realistic data volume, a cluster for a developer or a CI run that always starts from the same known state, a copy for analytics that you can discard and recreate as needed, a cluster for testing an upgrade on real data, or a migration to new hardware.

Compared with gpbackup/gprestore for the same task, the trade-off is clear. A logical restore is portable: it supports a different number of segments and can work across cluster versions. A physical restore requires matching segment counts and versions, but it parallelizes well and does not rebuild indexes or recompute statistics.

One more consideration concerns how the restored cluster is used rather than how the backup works: a physical copy of a production cluster includes both production data and production credentials. Roles and their passwords are copied unchanged, along with all catalog entries that reference external systems. Before giving anyone access to a clone, take at least these steps:

Masking the data itself is a separate task that a physical backup does not perform. If the clone is intended for people who should not see production data, you need to mask the data after the restore and before granting them access.