There are 4 methods below.
Use pg_basebackup
Use pg_dumpall to back up the entire database cluster as a script file
Use pg_dump and pg_restore to back up and restore only some databases
Use pg_dump's pg_restore to back up and restore only some tables and schemas
Of these, if you adopt the method of using pg_basebackup, you do not need to be particularly careful. The following special precautions should be taken when adopting other methods.
Use pg_dumpall to back up the entire database cluster as a script file
Note the following.
Do not use the -O option of the pg_dumpall command. This feature does not work properly if the confidentiality object changes ownership.
Do not use the -x option of the pg_dumpall command. Of course, this would change tightly controlled privileges.
Execute the backup script file as a superuser. A backup script file contains the CREATE EXTENSION statement for this extension. This is because the CREATE EXTENSION statement must be executed as a superuser.
Backup and restore only some databases using pg_dump and pg_restore
Note the following.
Use the -r option of the pg_dumpall command to back up and restore role information as well. Of course, because roles are essential to confidentiality management.
Do not use the -O option of the pg_dump command. This feature will not work properly if the confidentiality object changes ownership.
Do not use the -x option of the pg_dump command. Of course, this would change tightly controlled privileges.
Restore by a superuser for CREATE EXTENSION.
Use pg_dump's pg_restore to backup and restore only some tables and schema
Note the following.
Use the -e option of the pg_dump command to back up and restore this extension as well. This is because the tables contained in this extension store the information required for confidentiality management.
Use the -r option of the pg_dumpall command to back up and restore role information as well. Of course, because roles are essential to confidentiality management.
Do not use the -O option of the pg_dump command. This feature will not work properly if the confidentiality object changes ownership.
Do not use the -x option of the pg_dump command. Of course, this would change tightly controlled privileges.
Restore by a superuser for CREATE EXTENSION.
