{"id":158,"date":"2017-10-19T12:20:08","date_gmt":"2017-10-19T10:20:08","guid":{"rendered":"http:\/\/blog.chloesoe.ch\/?p=158"},"modified":"2017-10-19T16:55:37","modified_gmt":"2017-10-19T14:55:37","slug":"upgrade-postgresql-database-from-9-4-to-9-6-on-opensuse","status":"publish","type":"post","link":"https:\/\/blog.chloesoe.ch\/?p=158","title":{"rendered":"Upgrade PostgreSQL Database from 9.4 to 9.6 on openSUSE"},"content":{"rendered":"

This tutorial shows how to upgrade PostgreSQL from 9.4 (openSUSE 42.2) to 9.6 (openSUSE 42.3)<\/p>\n

    \n
  1. Stop postgresql and disable service\n
    systemctl stop postgresql.service\r\nsystemctl disable postgresql.service<\/code><\/pre>\n<\/li>\n
  2. Install PostgreSQL 9.6 (9.4 should still be installed after the update)\n
    zypper -n install postgresql96 postgresql96-server postgresql96-contrib<\/code><\/pre>\n<\/li>\n
  3. Move current cluster and create new data dir with user postgres\n
    mv \/var\/lib\/pgsql\/data\/ \/var\/lib\/pgsql\/data94\/\r\nsu postgres -c \"mkdir \/var\/lib\/pgsql\/data\"\r\n<\/code><\/pre>\n<\/li>\n
  4. Enable service, start it to create new database cluster at data folder, and stop service again to prepare the following upgrade\n
    systemctl enable postgresql.service\r\nsystemctl start postgresql.service\r\nsystemctl stop postgresql.service<\/code><\/pre>\n<\/li>\n
  5. Run pg_upgrade where -b is old bin dir, -B is new bin dir, -d is old data dir, and -D new data dir. This must run as user postgres ( su - postgres )\n
    su - postgres -c \"pg_upgrade -b \/usr\/lib\/postgresql94\/bin\/ -B \/usr\/lib\/postgresql96\/bin\/ -d \/var\/lib\/pgsql\/data94\/ -D \/var\/lib\/pgsql\/data\"<\/code><\/pre>\n<\/li>\n
  6. Copy the pg_hba.conf from old data dir to the new one (if you changed something at postgresql.conf you could copy that to the new data folder too). After that, start the postgresql service.\n
    cp \/var\/lib\/pgsql\/data94\/pg_hba.conf \/var\/lib\/pgsql\/data\r\nsystemctl start postgresql.service<\/code><\/pre>\n<\/li>\n
  7.  Try to login to postgresql, Version 9.6.x should be printed, leave with \\q\n
     # psql -U postgres\r\npsql (9.6.4)\r\nType \"help\" for help.\r\n\r\npostgres=# \\q<\/code><\/pre>\n<\/li>\n
  8. If you don't need the old cluster data, run this:\n
    \/var\/lib\/pgsql\/delete_old_cluster.sh<\/code><\/pre>\n<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    This tutorial shows how to upgrade PostgreSQL from 9.4 (openSUSE 42.2) to 9.6 (openSUSE 42.3) Stop postgresql and disable service systemctl stop postgresql.service systemctl disable postgresql.service Install PostgreSQL 9.6 (9.4 should still be installed after the update) zypper -n install postgresql96 postgresql96-server postgresql96-contrib Move current cluster and create new data dir with user postgres mv… Continue reading Upgrade PostgreSQL Database from 9.4 to 9.6 on openSUSE<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,12],"tags":[],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-linux","category-postgresql"],"_links":{"self":[{"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=158"}],"version-history":[{"count":6,"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions"}],"predecessor-version":[{"id":164,"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=\/wp\/v2\/posts\/158\/revisions\/164"}],"wp:attachment":[{"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.chloesoe.ch\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}