Create and Load PostgreSQL Dumps

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!

1. Create a copy of the codebeamer system to be obfuscated, if necessary.
2. Stop the codebeamer test instance.
3. 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

4. Dump the obfuscated database as described above.

Check the Obfuscated Database

1. 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');

2. Delete the directories under /repository/docs.
3. Start codebeamer.
4. Login to codebeamer with user-<user-id>/007. For example: user-1/007