Back up the keystore at the following times in case it is corrupted or lost. Note that you must store the database and the keystore on separate data storage media. Storing both on the same data storage medium risks the danger of the encrypted data being deciphered if the medium is stolen. A passphrase is not required to open an automatically opening keystore, so store this type of keystore in a safe location.
When the master encryption key is first configured
When the master encryption key is changed
When the database is backed up
When the keystore passphrase is changed
Point
Do not overwrite an old keystore when backing up a keystore. This is because during database recovery, you must restore the keystore to its state at the time of database backup. When the backup data of the database is no longer required, delete the corresponding keystore.
Example
Back up the database and the keystore on March 1, 2022.
> pgx_dmpall -D /database/inst1 > cp -p /key/store/location/keystore.ks /keybackup/keystore_20220301.ks
Specify the following in the pgx_dmpall command:
Specify the data storage destination in the -D option. If the -D option is omitted, the value of the PGDATA environment variable is used by default.
Change the master encryption key, and back up the keystore on March 5, 2022.
> psql -c "SELECT pgx_set_master_key('passphrase')" postgres
> cp -p /key/store/location/keystore.ks /keybackup/keystore_20220305.ksSpecify the following in the psql command:
Specify the SQL function that sets the master encryption key in the -c option.
Specify the name of the database to be connected to as the argument.
If the keystore is corrupted or lost, restore the keystore containing the latest master encryption key. If there is no keystore containing the latest master encryption key, restore the keystore to its state at the time of database backup, and recover the database from the database backup. This action recovers the keystore to its latest state.
Example
Restore the keystore containing the latest master encryption key as of March 5, 2022.
> cp -p /keybackup/keystore_20220305.ks /key/store/location/keystore.ks
If there is no backup of the keystore containing the latest master encryption key, recover the keystore by restoring the keystore that was backed up along with the database on 1 March 2022.
> cp -p /keybackup/keystore_20220301.ks /key/store/location/keystore.ks > pgx_rcvall -B /backup/inst1 -D /database/inst1 --keystore-passphrase
Specify the following in the pgx_rcvall command:
Specify the data storage directory in the -D option. If the -D option is omitted, the value of the PGDATA environment variable is used by default.
Specify the backup data storage directory in the -B option.
The --keystore-passphrase option prompts you to enter the passphrase to open the keystore.
If you have restored the keystore, repeat the process of enabling automatic opening of the keystore. This ensures that the contents of the automatically opening keystore (keystore.aks) are identical to the contents of the restored keystore.
It is recommended that you do not back up the automatically opening keystore file, keystore.aks. If the database backup medium and the backup medium storing the automatically opening keystore are both stolen, the attacker will be able to read the data even without knowing the passphrase.
If the automatically opening keystore is corrupted or lost, you must again enable automatic opening. The keystore.aks file will be recreated from keystore.ks at this time.
See
Refer to "pgx_rcvall" and "pgx_dmpall" in the Reference for information on the pgx_rcvall and pgx_dmpall commands.
Refer to "psql" under "Reference" in the PostgreSQL Documentation for information on the psql command.
Refer to "B.2 Transparent Data Encryption Control Functions" for information on the pgx_set_master_key function.
Refer to "5.6.3 Enabling Automatic Opening of the Keystore" for information on how to enable automatic opening of the keystore.