8. Backup and restore

By the end of this chapter: You back up GritDB data consistently, restore it from a backup, and verify the restored result.

8.1 Backup unit

• The backup unit is datafiles/ as a whole. You cannot use a backup of only part of it for a restore.
• pgdata (including WAL and the recovery replication slot), checkpoint, and cold_file must match. All three must be from the same point in time.

⚠ Do not restore to a different real path or a different version

• Reason: With a different real path, GritDB loses the cold files (1.5). Cold files are files the GritDB engine stores by column for analytic queries. With a different version, the storage format does not match.
• Instead: When you back up, write down the real path and the version (8.2 step 4). Restore only to the same real path and the same version.

Keep these too (outside datafiles/):

• config/*.config (GritDB engine configuration, 5.6)
• share/gritdb/manifest.json (installed version)
• Data volume relocation information: the real paths of datafiles and datafiles/pgdata (2.4, 8.2 step 4)

8.2 Offline backup

The backup that 1.1 supports is to stop GritDB normally and copy all of datafiles/. It has the same form as a PostgreSQL file system level backup. For GritDB, the copy holds the whole datafiles/. Downtime is the sum of the normal stop (4.2), the copy, and the start (4.1).

1. Stop applications, reports, BI tools, CDC tools, and ETL tools. Disconnect the remaining sessions (steps 1 to 3 of “Disconnect remaining sessions” in 4.2). For an analytics setup, WAL builds up on the operational database while GritDB is stopped (4.7, 7.5).

2. Write down the values to compare (for the comparison in 8.5).

   B=<backup location>/gritdb_$(date +%Y%m%d_%H%M%S)
   mkdir -p "$B"
   ~/gritdb1/bin/psql -p 5432 -d <db> -Atc "SELECT schema_name, table_name FROM get_gritsurfer_tables() ORDER BY 1, 2" > "$B/tables_<db>.txt"
   ~/gritdb1/bin/psql -p 5432 -d <db> -Atc "SELECT count(*), sum(<numeric column>) FROM <s>.<t>" > "$B/count_<s>.<t>.txt"
   

   • <backup location> is a disk that is not the data volume. Step 5 copies to this location ($B).
   • Run the first two lines only once. Repeat the third line for each database and the fourth line for each sample table. Choose samples by the criteria in 7.10 step 1, and use integer or numeric(p, s) columns. Also write down the summed column name. Step 4 of 8.5 and ① in 3.4 compare the same column.
   • Write them down after step 1. Then no value changes after you write them down.

3. Stop GritDB normally.

   ~/gritdb1/bin/gritdb-stop
   cat ~/gritdb1/run/grit_admin_clean_shutdown
   

   • It is normal if CLEAN_SHUTDOWN appears and there are 0 processes (QuickStart 4.5).
   • If the file does not exist, the stop ended as a quick stop (4.2). In that case, start GritDB with gritdb-start and check it with 4.1. Repeat step 1, and also stop connection pools that reconnected. Run the third and fourth lines of step 2 again with the same $B. Then stop GritDB again with gritdb-stop, and copy.

4. Write down the real paths and the version.

   readlink -f ~/gritdb1/datafiles
   readlink -f ~/gritdb1/datafiles/pgdata
   jq -r .product_version ~/gritdb1/share/gritdb/manifest.json
   

5. Copy to $B in the same shell as step 2. If the shell was disconnected, run B=<location created in step 2>.

   tar --sparse -cf "$B/datafiles.tar" -C "$(readlink -f ~/gritdb1/datafiles)" . ; echo "datafiles.tar = $?"
   tar -cf "$B/install_meta.tar" -C ~/gritdb1 config share/gritdb/manifest.json ; echo "install_meta.tar = $?"
   

   • It is normal if both lines show = 0. If it is not 0, do not use that copy. In that case, fix the space or permissions and repeat step 5.
   • --sparse stores files with a lot of empty space (queue/) at their actual size. If you use rsync, copy with rsync -aHS. -S does the same thing. In that case, also check that the exit status is 0.
   • For an installation with pgdata placed separately with --pgdata (2.4), datafiles.tar contains only the symlink. So also copy the pgdata real path. To do so, run tar --sparse -cf "$B/pgdata.tar" -C "$(readlink -f ~/gritdb1/datafiles/pgdata)" . ; echo "pgdata.tar = $?", and check that it prints = 0.

6. Check the backup.

   tar -tf "$B/datafiles.tar" ./checkpoint/grit_unify.cp ./pgdata/PG_VERSION
   

   • It is normal if both names appear as they are. For an installation with pgdata placed separately, only the first name appears. In that case, to check the pgdata side, run tar -tf "$B/pgdata.tar" ./PG_VERSION. It returns the name.
   • To compare the whole copy with the original, run tar -df "$B/datafiles.tar" -C "$(readlink -f ~/gritdb1/datafiles)" before step 7 (start). It shows no output. Use the same method for pgdata.tar. In that case, downtime grows by about the copy time.

7. Start GritDB with ~/gritdb1/bin/gritdb-start, and check it with 4.1.

• The copy time is the actual size of datafiles/ (du -sh ~/gritdb1/datafiles/) divided by the write speed of the backup location.
• Backup copies contain original SQL text and data values, so allow only the service account to read them (11.7).

⚠ Do not copy datafiles/ of a running GritDB

• Reason: 1.1 does not support online backup. During the copy, pgdata, checkpoint, and cold_file come from different points in time.
• Instead: Copy after you confirm the normal stop in step 3 above.

8.3 PostgreSQL backup tools and GritDB

• 1.1 does not support online backup. It also does not support copies made with file system or storage snapshots (LVM, ZFS, and so on). Use the offline backup (8.2).
• The point you can restore to is the last offline backup. With WAL archiving (archive_mode), recovery to a later point (PITR) does not work in 1.1. So, for a single-database setup, set the backup interval by the period of data you can afford to lose (1.9 item 12). An analytics setup takes the tables again from the operational database (8.6).

⚠ Do not restore a dump of the postgres database to another GritDB

• Reason: The postgres database contains GritDB internal tables (the gritdb schema). A full pg_dumpall dump also includes them.
• Instead: Use pg_dumpall --globals-only for accounts and roles. Use pg_dump for the data of each database, except postgres.

To move to another GritDB (logical backup, on the original server):

~/gritdb1/bin/pg_dumpall -p 5432 --globals-only -f globals.sql
~/gritdb1/bin/pg_dump -p 5432 -d <db> --schema-only -f <db>_schema.sql
~/gritdb1/bin/pg_dump -p 5432 -d <db> --data-only --exclude-table-data=<s>.<t> -f <db>_data.sql
~/gritdb1/bin/psql -p 5432 -d <db> -c "\copy <s>.<t> TO '<t>.csv' (FORMAT csv)"

• <s>.<t> in the third and fourth lines is a large table. A large table has a row count of more than half the single-transaction limit (6.8). The row count is the step 6 comparison value measured on the original server. Use one --exclude-table-data and one \copy line for each large table. If there are no large tables, remove --exclude-table-data and skip the fourth line.
• --exclude-table-data keeps the table definition and leaves out only the data.

Restore on the new GritDB (steps 2 to 5 for each database).

1. Restore accounts and roles with ~/gritdb1/bin/psql -p 5432 -d postgres -f globals.sql.
2. Run CREATE DATABASE <db>;. The extension is created with it (6.1).
3. Restore table definitions and primary keys with ~/gritdb1/bin/psql -p 5432 -d <db> -f <db>_schema.sql.
4. Register the tables (6.3). Do this after the primary keys exist and while the tables are empty.
5. Load the data with ~/gritdb1/bin/psql -p 5432 -d <db> -f <db>_data.sql. Then load large tables with 6.8 step 3 (staging table → load in batches, \copy <s>.<t>_load FROM '<t>.csv' (FORMAT csv)).
6. Use 8.5 step 4 to compare with the values measured on the original server.

• -f in step 5 loads each table in one transaction. So leave large tables out of the dump with the third line. Load them in batches with 6.8 step 3. If you cannot split them, contact support (chapter 0).
• <db>_data.sql and <t>.csv are copies of the data. Allow only the service account to read them, and delete them when the move is complete (11.7).
• Check that the step 6 comparison matches and that the registration list (6.5) has all the moved tables.
• Logical backup and restore on the PostgreSQL side: SQL dump

8.4 Restore

1. Stop GritDB with ~/gritdb1/bin/gritdb-stop, then check that there are 0 processes (QuickStart 4.5).

2. Check that the real paths and the version are the same as at backup time.

   readlink -f ~/gritdb1/datafiles
   readlink -f ~/gritdb1/datafiles/pgdata
   jq -r .product_version ~/gritdb1/share/gritdb/manifest.json
   

   • It is normal if the values are the same as those written down in 8.2 step 4. If they differ, stop here (8.1 warning).

3. Keep the current data (rename it).

   • First check the free space on the data volume. Confirm that Avail of df -h "$(readlink -f ~/gritdb1/datafiles)" is at least the actual size of datafiles/ in the backup (du -sh, 8.2). The kept copy stays on the same volume, so step 4 needs that much more space. For an installation with pgdata placed separately, also check the pgdata volume. If there is not enough space, move the kept copy to another volume. It is the data to go back to if the restore fails, so do not delete it.

   D=$(readlink -f ~/gritdb1/datafiles)
   P=$(readlink -f ~/gritdb1/datafiles/pgdata)
   T=$(date +%Y%m%d_%H%M%S)
   mv "$D" "$D.before_restore_$T"
   mkdir "$D"
   chmod 700 "$D"
   

   • If datafiles/ (or $P below) is a mount point, you cannot move the directory itself. In that case, move the files inside it to another location and leave the empty directory (3.1).
   • For an installation with pgdata placed separately (2.4), keep $P too. To do so, run mv "$P" "$P.before_restore_$T", mkdir "$P", and chmod 700 "$P" in order. Get P before you move datafiles, because after the move you cannot find it through the datafiles/pgdata link.

4. Extract the backup to the same real path.

   tar -xf <backup location>/<backup name>/datafiles.tar -C "$D"
   

   • For an installation with pgdata placed separately, run tar -xf <backup location>/<backup name>/pgdata.tar -C "$P" in the same shell as step 3. Check that readlink -f ~/gritdb1/datafiles/pgdata is the same as $P.
   • Run tar -xf <backup location>/<backup name>/install_meta.tar -C ~/gritdb1 config only when you also return the GritDB engine configuration to the values at backup time.

5. Start GritDB with ~/gritdb1/bin/gritdb-start, and check it with 4.1. It can wait without output until the GritDB engine recovery finishes (4.1).

6. Verify with 8.5.

7. After the verification, delete the data that you kept in step 3.

⚠ Do not restore an analytics setup (6.4) from a backup

• Reason: The operational database slot has already advanced past the backup. So it does not resend the operational database changes made since the backup. Those changes silently go missing from the analytics data.
• Instead: Recover an analytics setup by taking the tables again with 8.6. If you already restored from a backup, take all tables again with 8.6.

8.5 Verify the restore

1. Confirm the start with 4.1. Check for started successfully, and run pg_isready once more after 20 seconds.

2. Check the registered table list for each database.

   ~/gritdb1/bin/psql -p 5432 -d <db> -Atc "SELECT schema_name, table_name FROM get_gritsurfer_tables() ORDER BY 1, 2" | diff - <backup location>/<backup name>/tables_<db>.txt
   

   • No output is normal. It means the list matches the one written down at backup time (8.2 step 2).

3. Confirm that changes are applied (6.6.1).

4. Compare counts and sums.

   Regular session:

   SELECT count(*), sum(<numeric column>) FROM <s>.<t>;
   

   Analytics session (6.6, force the GritDB engine):

   /*+ engine=burst, fallback=false */ SELECT count(*), sum(<numeric column>) FROM <s>.<t>;
   

   • It is normal if the two values match each other and the values written down in 8.2 step 2 (count_<s>.<t>.txt).

5. Run one frequently used report query in both sessions and compare the results.

• Check that steps 1 to 5 all pass. If any one differs, do not delete the data that you kept in 8.4 step 3. In that case, collect diagnostic data with QuickStart 7.4 and contact support (chapter 0).

8.6 Take the analytics setup again

To recover an analytics setup, the default is to take the tables again from the operational database, not to restore from a backup (8.4 warning).

• Taking a table again runs the initial copy again. So if a table’s row count is more than half the single-transaction limit (6.8), contact support (chapter 0) before you do it. Also see the warning in 6.4.1 step 8.
• Do not empty the same table and register it again right away. Drop it and register a newly created table (6.4.2 step 5).

Take all tables again:

1. Clean up the subscription and the operational database slot with 6.4.4 steps 1 to 3. Leave the publication as it is.
2. Confirm that changes are applied (6.6.1).
3. Unregister and drop the GritDB tables (6.3).
4. Create them again with the same definitions and register them (6.4.1 steps 6 to 7).
5. Create the subscription again and confirm the initial copy (6.4.1 steps 8 to 9).

Take all tables again on a new server (when the GritDB server is lost):

1. On the operational database, drop the old subscription slots with 6.4.4 steps 2 and 3. Pick them out by the oid that you wrote down in 6.4.1 step 8.
2. Install on the new server (QuickStart, chapters 2 and 3).
3. If the new server has a different address, update the connection permissions on the operational database (6.4.1 step 4).
4. Follow 6.4.1 steps 5 to 9.

• Check that the three values in 6.4.1 step 9 are the same.

8.7 Moving to another server and standby servers

Moving to another server:

1. On the new server, install the same version. Use the same installation directory, the same options (--datafiles and --pgdata, chapter 2), and a service account with the same name.

2. On the original server, make an offline backup (8.2).

3. On the new server, follow 8.4 steps 1 to 6. Extract to the same real path. With the second line of step 4, also extract config, the GritDB engine configuration of the old server.

   • To check, run diff -r <config from backup> ~/gritdb1/config. It shows no output. This matters because config/ on the new server has the installation defaults at first (3.4 ③).

• For an analytics setup, do not move the backup. Take all tables again on the new server (8.6 “Take all tables again on a new server”).

In 1.1, standby servers and disaster recovery servers are manual. There is no automatic failover (10.6).

• To switch over, the operator changes the connection address of applications and BI tools to the standby server.
• Before you set up a standby server, contact support (chapter 0).

If a problem occurs

• See the RECOVERY entries in TroubleShooting.

Learn more

• For reinitialization and rollback, see chapter 3. For the analytics setup, see 6.4.
• PostgreSQL 17 documentation: Backup and restore, File system level backup, and SQL dump