What steps are necessary to upgrade my psql 9.5 database to psql 9.6?

[Kyle Manel]

Or is the process as easy as exporting from 9.5 with;

pg_dump -U $DB_USER $DB_NAME > /tmp/db_name.pgsql

& importing into psql 9.6 with;

psql -U $DB_USER $DB_NAME < /tmp/db_name.pgsql

[Andrew Laden · Accepted solution]

This was discussed in this question, you may want to look at that first.

https://community.atlassian.com/t5/Jira-Software-questions/Upgrading-psql-9-5-to-9-6-for-Jira/qaq-p/1042686

But in general you should be able to dump and load, particularly between minor versions.

I was able to to do it as follows

1. Create database and users manually as user postgres
   1. createdb -E utf8 jiradb
   2. psql

      1. create user jira with password '<LOOK IT UP>';

      2. grant all privileges on database jiradb to jira;

2. Shut down Jira on old server
3. Take a dump of the existing database
   1. pg_dump jiradb> jiradb.dump
4. Copy to new server
   1. scp jiradb.dump newserver:/tmp
5. Connect to server as application user and load data
   1. psql -U jira -h <new host> -p 5432 -W jiradb< /tmp/jiradb.dump
6. configure confluence to point to new database
   1. /opt/atlassian/jira-data/dbconfig.xml
      1. <url>jdbc:postgresql://127.0.0.1:5432/jiradb</url> 
7. start up Jira

[Kyle Manel]

Thanks for your assistance.  I've been creating and testing on postgresql 9.5.20, but it will make sense soon, to move to a different primary version.  I'll give this a go in the coming days and get back to you.

[DPKJ]

@Kyle Manel  May I know what OS are you on?

[Kyle Manel]

Linux.

[DPKJ]

@Kyle ManelEach distribution have defined steps for upgrading Postgres without data loss.

I used this gist - https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44 for Debian like distribution, and I had no loss of data.

[Kyle Manel]

Which products (Jira, Confluence, Bamboo, Bitbucket, etc) did you move your data from psql 9.5 to 9.6 with?

[DPKJ]

@Kyle Manel  for Jira and Confluence

[Kyle Manel]

And were there any complexities that arose from the transfer, or was it a (relatively) simple case of exporting the database with pg_dump and importing again with psql?