7. DB Performance

7.1. Dobre praktyki

  • Terminal z połączeniem SSH do produkcji Background color RED

7.2. About

  • AO = Add-On (plugins)
  • cwd_user i cwd_directories
  • jiraissue
  • mailserver
  • filtersubscription
  • worklog
  • customfieldvalue i customfield
  • project i project_key
  • fileattachment
ssh -L 5432:localhost:5432 [email protected]

7.3. Backup data with pg_dump

$ service jira stop
$ pg_dump -i -h localhost -p 5432 -U jira -F c -b -v -f "/tmp/$(date +%F)_jira.pgdump" jira
$ pg_dump -?
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode
-F, –format=c|t|p output file format (custom, tar, plain text)
-c, –clean clean (drop) schema prior to create
-b, –blobs include large objects in dump
-v, –verbose verbose mode
-f, –file=FILENAME output file name

7.4. Restore data with pg_restore

DROP DATABSE jira;
CREATE DATABASE jira_new;
GRANT ALL PRIVILEGES ON DATABASE jira_new TO jira;
$ pg_restore -i -h localhost -p 5432 -U jira -v "/tmp/$(date +%F)_jira.pgdump" -d jira_new
$ pg_restore -?
-p, –port=PORT database server port number
-i, –ignore-version proceed even when server version mismatches
-h, –host=HOSTNAME database server host or socket directory
-U, –username=NAME connect as specified database user
-W, –password force password prompt (should happen automatically)
-d, –dbname=NAME connect to database name
-v, –verbose verbose mode

7.5. Restore data with psql from plaintext SQL

$ psql -h localhost -p 5432 -U jira -d jira < "/tmp/$(date +%F)_jira.pgdump"

7.6. Change JIRA DB config

  • Change /var/atlassian/application-data/jira/dbconfig.xml
$ service jira start

7.7. Assignments

7.7.1. Administracja - bazą danych

  1. Zrób backup bazy danych (musi być data w nazwie pliku)
  2. Zrób drop bazy
  3. Zmień DB Pool connection
  4. Przywróć backup do bazy jira_new
  5. Dodaj polecenie backupu bazy danych do crontab z @midnight