Create and Load PostgreSQL Dumps
Supported since codebeamer release 21.09-LTS (EMMA).
Remember to replace <placeholders> in the examples with appropriate values.
Create Dump
pg_dump
To export the database in PostgreSQL, execute the following command:
pg_dump -U <codebeamer_database_user> <codebeamer_database_name> > codebeamer_dump.sql
If the postgresql server runs on a different machine, the pg_dump can be configured with host and port values of the database server. For example:
pg_dump -U <codebeamer_database_user> -h <postgresql_server_host> -p <postgresql_server_port> <codebeamer_database_name> > codebeamer_dump.sql
Exporting Dump Without Binary Data
If the binary data is stored in the database, then exporting database without binary data is recommended:
pg_dump -U <codebeamer_database_user> -B --exclude-table-data=*_blobs <codebeamer_database_name> > codebeamer_dump.sql
If the postgresql server runs on a different machine
pg_dump -U <codebeamer_database_user> -h <postgresql_server_host> -p <postgresql_server_port> -B --exclude-table-data=*_blobs <codebeamer_database_name> > codebeamer_dump.sql
The pg_dump command produces a codebeamer_dump.sql file which can be compressed with zip, tar or tar.gz.
For more information about this tool, see https://www.postgresql.org/docs/9.1/backup-dump.html
Import Dump
To import the exported postgresql dump, use the following psql command:
psql -U <codebeamer_database_user> <codebeamer_database_name> < codebeamer_dump.sql
If the postgresql server runs on a different machine, the command can be configured with database host and port values of the database server:
psql -U <codebeamer_database_user> -h <postgresql_server_host> -p <postgresql_server_port> <codebeamer_database_name> < codebeamer_dump.sql
Obfuscate Sensitive Data
THE FOLLOWING PROCESS MODIFIES THE DATA IN THE DATABASE, AND IT IS NOT POSSIBLE TO REVERT MODIFICATIONS. DO NOT USE IT ON PRODUCTION ENVIRONMENT!
- Create a copy of the codebeamer system to be obfuscated, if necessary.
- Stop the codebeamer test instance.
- Run the following script against the test database obfuscate_data_postgres.sql. The following command can be used:
psql -U <user> -h <host> -p <port> -d <your_db> -f ./obfuscate_data_postgres.sql
- Dump the obfuscated database as described above.
Check the Obfuscated Database
- After executing the script, it is not possible to login to codebeamer because all user credentials (usernames and passwords) are removed.
- Log in to the postgresql and execute the following commands to set all user password to 007:
- create extension pgcrypto;
- execute the following SQL command:
update users set registrydate=null, passwd=encode(digest(concat('007', id, '700101010000'), 'sha512'), 'hex');
- Delete the directories under /repository/docs.
- Start codebeamer.
- Login to codebeamer with user-<user-id>/007. For example: user-1/007