8. DB Performance

8.1. Dobre praktyki

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

8.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 root@adresIP

8.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

8.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

8.5. Restore data with psql from plaintext SQL

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

8.6. Change JIRA DB config

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

$ service jira start

8.7. Assignments

8.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