Skip to content

Instantly share code, notes, and snippets.

@dmitrykustov

dmitrykustov/upgrade-postgres-9.4-to-9.6.md

Forked from dideler/upgrade-postgres-9.3-to-9.4.md
Last active September 7, 2025 14:21
Show Gist options
  • Save dmitrykustov/27c673ec4f7abd716912e4c830910019 to your computer and use it in GitHub Desktop.
Save dmitrykustov/27c673ec4f7abd716912e4c830910019 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Dmitry Kustov revised this gist Nov 30, 2016. 1 changed file with 22 additions and 1 deletion.
    23 changes: 22 additions & 1 deletion upgrade-postgres-9.4-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -9,6 +9,7 @@ Install a new version of PostgreSQL server.
    Once the Debian upgrade finished, I used `dpkg-query -l postgresql*` to check which versions of postgres I have installed.

    ```
    dpkg-query -l postgresql*
    Desired=Unknown/Install/Remove/Purge/Hold
    | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
    |/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
    @@ -36,7 +37,7 @@ Looks like the Debian upgrade included PostgreSQL 9.6, but I still need to upgra
    Run `pg_lsclusters`, your 9.4 and 9.6 main clusters should be "online".

    ```
    $ pg_lsclusters
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    9.6 main 5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
    @@ -77,4 +78,24 @@ sudo pg_dropcluster 9.4 main
    After all you may totally remove version 9.4 from the server:
    ```bash
    sudo apt-get --purge remove postgresql-client-9.4 postgresql-9.4
    ```
    ```bash
    dpkg-query -l postgresql*
    Desired=Unknown/Install/Remove/Purge/Hold
    | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
    |/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
    ||/ Name Version Architecture Description
    +++-===========================-==================-==================-============================================================
    ii postgresql 9.6+177.pgdg80+1 all object-relational SQL database (supported version)
    un postgresql-7.4 <none> <none> (no description available)
    un postgresql-8.0 <none> <none> (no description available)
    un postgresql-9.1 <none> <none> (no description available)
    ii postgresql-9.6 9.6.1-1.pgdg80+1 amd64 object-relational SQL database, version 9.6 server
    un postgresql-client <none> <none> (no description available)
    ii postgresql-client-9.6 9.6.1-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.6
    ii postgresql-client-common 177.pgdg80+1 all manager for multiple PostgreSQL client versions
    ii postgresql-common 177.pgdg80+1 all PostgreSQL database-cluster manager
    ii postgresql-contrib-9.6 9.6.1-1.pgdg80+1 amd64 additional facilities for PostgreSQL
    un postgresql-doc <none> <none> (no description available)
    un postgresql-doc-9.6 <none> <none> (no description available)
    ```
  2. Dmitry Kustov revised this gist Nov 30, 2016. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions upgrade-postgres-9.4-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -72,4 +72,9 @@ Check that the upgraded cluster works, then remove the 9.4 cluster.

    ```bash
    sudo pg_dropcluster 9.4 main
    ```

    After all you may totally remove version 9.4 from the server:
    ```bash
    sudo apt-get --purge remove postgresql-client-9.4 postgresql-9.4
    ```
  3. Dmitry Kustov revised this gist Nov 30, 2016. 2 changed files with 75 additions and 68 deletions.
    68 changes: 0 additions & 68 deletions upgrade-postgres-9.3-to-9.4.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,68 +0,0 @@
    **TL;DR**

    ```bash
    sudo pg_dropcluster 9.4 main --stop
    sudo pg_upgradecluster 9.3 main
    sudo pg_dropcluster 9.3 main
    ```

    ---

    While upgrading Ubuntu 14.04 to 14.10, I got the following message:

    > The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster
    Once the Ubuntu upgrade finished, I used `aptitude search` to check which versions of postgres I have installed.

    ```
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    ```

    Looks like the Ubuntu upgrade included PostgreSQL 9.4, but I still need to upgrade from 9.3 to 9.4.

    Run `pg_lsclusters`, your 9.3 and 9.4 main clusters should be "online".

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.3 main 5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
    9.4 main 5433 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    ```

    There already is a cluster "main" for 9.4 (since this is created by default on package installation).
    This is done so that a fresh installation works out of the box without the need to create a cluster first,
    but of course it clashes when you try to upgrade 9.3/main when 9.4/main also exists.
    The recommended procedure is to remove the 9.4 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.

    Stop the 9.4 cluster and drop it.

    ```bash
    sudo pg_dropcluster 9.4 main --stop
    ```

    Upgrade the 9.3 cluster to the latest version.

    ```bash
    sudo pg_upgradecluster 9.3 main
    ```

    Your 9.3 cluster should now be "down".

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.3 main 5433 down postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
    9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    ```

    Check that the upgraded cluster works, then remove the 9.3 cluster.

    ```bash
    sudo pg_dropcluster 9.3 main
    ```
    75 changes: 75 additions & 0 deletions upgrade-postgres-9.4-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,75 @@
    To use the most modern version of Postgres software we need to add postgresql repository. Edit `/etc/apt/sources.list` or create `/etc/apt/sources.list.d/pgdg.list` and add there a line: `deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main`
    Then import the repository signing key, and update the package lists:
    ```bash
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    sudo apt-get update
    ```
    Install a new version of PostgreSQL server.

    Once the Debian upgrade finished, I used `dpkg-query -l postgresql*` to check which versions of postgres I have installed.

    ```
    Desired=Unknown/Install/Remove/Purge/Hold
    | Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
    |/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
    ||/ Name Version Architecture Description
    +++-===========================-==================-==================-============================================================
    ii postgresql 9.6+177.pgdg80+1 all object-relational SQL database (supported version)
    un postgresql-7.4 <none> <none> (no description available)
    un postgresql-8.0 <none> <none> (no description available)
    un postgresql-9.1 <none> <none> (no description available)
    ii postgresql-9.4 9.4.9-0+deb8u1 amd64 object-relational SQL database, version 9.4 server
    ii postgresql-9.6 9.6.1-1.pgdg80+1 amd64 object-relational SQL database, version 9.6 server
    un postgresql-client <none> <none> (no description available)
    ii postgresql-client-9.4 9.4.10-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.4
    ii postgresql-client-9.6 9.6.1-1.pgdg80+1 amd64 front-end programs for PostgreSQL 9.6
    ii postgresql-client-common 177.pgdg80+1 all manager for multiple PostgreSQL client versions
    ii postgresql-common 177.pgdg80+1 all PostgreSQL database-cluster manager
    ii postgresql-contrib-9.6 9.6.1-1.pgdg80+1 amd64 additional facilities for PostgreSQL
    un postgresql-doc <none> <none> (no description available)
    un postgresql-doc-9.4 <none> <none> (no description available)
    un postgresql-doc-9.6 <none> <none> (no description available)
    ```

    Looks like the Debian upgrade included PostgreSQL 9.6, but I still need to upgrade from 9.4 to 9.6.

    Run `pg_lsclusters`, your 9.4 and 9.6 main clusters should be "online".

    ```
    $ pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    9.6 main 5433 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
    ```

    There already is a cluster "main" for 9.6 (since this is created by default on package installation).
    This is done so that a fresh installation works out of the box without the need to create a cluster first,
    but of course it clashes when you try to upgrade 9.4/main when 9.6/main also exists.
    The recommended procedure is to remove the 9.6 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.

    Stop the 9.6 cluster and drop it.

    ```bash
    sudo pg_dropcluster 9.6 main --stop
    ```

    Upgrade the 9.4 cluster to the latest version.

    ```bash
    sudo pg_upgradecluster 9.4 main
    ```

    Your 9.4 cluster should now be "down".

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.4 main 5433 down postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
    ```

    Check that the upgraded cluster works, then remove the 9.4 cluster.

    ```bash
    sudo pg_dropcluster 9.4 main
    ```
  4. @dideler dideler revised this gist Nov 2, 2014. 1 changed file with 17 additions and 7 deletions.
    24 changes: 17 additions & 7 deletions upgrade-postgres-9.3-to-9.4.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,17 +1,27 @@
    **TL;DR**

    ```bash
    sudo pg_dropcluster 9.4 main --stop
    sudo pg_upgradecluster 9.3 main
    sudo pg_dropcluster 9.3 main
    ```

    ---

    While upgrading Ubuntu 14.04 to 14.10, I got the following message:

    > The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster
    Once the Ubuntu upgrade finished, I used `aptitude search` to check which versions of postgres I have installed.

    ```
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    ```

    Looks like the Ubuntu upgrade included PostgreSQL 9.4, but I still need to upgrade from 9.3 to 9.4.
  5. @dideler dideler revised this gist Nov 2, 2014. 1 changed file with 9 additions and 9 deletions.
    18 changes: 9 additions & 9 deletions upgrade-postgres-9.3-to-9.4.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,19 +2,19 @@ While upgrading Ubuntu 14.04 to 14.10, I got the following message:

    > The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster
    When the Ubuntu upgrade was done, I used `aptitude search` to check which versions of postgres I had installed.
    Once the Ubuntu upgrade finished, I used `aptitude search` to check which versions of postgres I have installed.

    ```
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    ```

    Looks like the Ubuntu upgrade installed PostgreSQL 9.4 for me, but I still need to upgrade from 9.3 to 9.4.
    Looks like the Ubuntu upgrade included PostgreSQL 9.4, but I still need to upgrade from 9.3 to 9.4.

    Run `pg_lsclusters`, your 9.3 and 9.4 main clusters should be "online".

  6. @dideler dideler renamed this gist Nov 2, 2014. 1 changed file with 16 additions and 17 deletions.
    33 changes: 16 additions & 17 deletions readme.md → upgrade-postgres-9.3-to-9.4.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,22 +1,22 @@
    During the Ubuntu upgrade to 14.10, I got the following message.
    While upgrading Ubuntu 14.04 to 14.10, I got the following message:

    > The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster
    When the Ubuntu upgrade was done, I used `aptitude` to check which versions of postgres I had installed.
    When the Ubuntu upgrade was done, I used `aptitude search` to check which versions of postgres I had installed.

    ```
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    ```

    Looks like the Ubuntu upgrade installed PostgreSQL 9.4 for me. So now I still had to upgrade from 9.3 to 9.4.
    Looks like the Ubuntu upgrade installed PostgreSQL 9.4 for me, but I still need to upgrade from 9.3 to 9.4.

    Run `pg_lsclusters` your 9.3 and 9.4 main clusters should be "online".
    Run `pg_lsclusters`, your 9.3 and 9.4 main clusters should be "online".

    ```
    pg_lsclusters
    @@ -25,19 +25,18 @@ Ver Cluster Port Status Owner Data directory Log file
    9.4 main 5433 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    ```

    As it says, there already is a cluster 'main' for 9.4 (since this is
    created by default on package installation). This is done so that a
    fresh installation works out of the box without the need to create a
    cluster first, but of course it clashes when you try to upgrade
    9.3/main when 9.4/main also exists. So the recommended procedure is to remove the 9.4 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.
    There already is a cluster "main" for 9.4 (since this is created by default on package installation).
    This is done so that a fresh installation works out of the box without the need to create a cluster first,
    but of course it clashes when you try to upgrade 9.3/main when 9.4/main also exists.
    The recommended procedure is to remove the 9.4 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.

    Stop the 9.4 cluster and drop it.

    ```bash
    sudo pg_dropcluster 9.4 main --stop
    ```

    Upgrade the 9.3 cluster to the latest version
    Upgrade the 9.3 cluster to the latest version.

    ```bash
    sudo pg_upgradecluster 9.3 main
  7. @dideler dideler created this gist Nov 2, 2014.
    59 changes: 59 additions & 0 deletions readme.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    During the Ubuntu upgrade to 14.10, I got the following message.

    > The PostgreSQL version 9.3 is obsolete, but the server or client packages are still installed. Please install the latest packages (postgresql-9.4 and postgresql-client-9.4) and upgrade the existing clusters with pg_upgradecluster
    When the Ubuntu upgrade was done, I used `aptitude` to check which versions of postgres I had installed.

    ```
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.3 - object-relational SQL database, version 9.3 server
    i A postgresql-9.4 - object-relational SQL database, version 9.4 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.4 - front-end programs for PostgreSQL 9.4
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.4 - additional facilities for PostgreSQL
    ```

    Looks like the Ubuntu upgrade installed PostgreSQL 9.4 for me. So now I still had to upgrade from 9.3 to 9.4.

    Run `pg_lsclusters` your 9.3 and 9.4 main clusters should be "online".

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.3 main 5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
    9.4 main 5433 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    ```

    As it says, there already is a cluster 'main' for 9.4 (since this is
    created by default on package installation). This is done so that a
    fresh installation works out of the box without the need to create a
    cluster first, but of course it clashes when you try to upgrade
    9.3/main when 9.4/main also exists. So the recommended procedure is to remove the 9.4 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.

    Stop the 9.4 cluster and drop it.

    ```bash
    sudo pg_dropcluster 9.4 main --stop
    ```

    Upgrade the 9.3 cluster to the latest version

    ```bash
    sudo pg_upgradecluster 9.3 main
    ```

    Your 9.3 cluster should now be "down".

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.3 main 5433 down postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
    9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
    ```

    Check that the upgraded cluster works, then remove the 9.3 cluster.

    ```bash
    sudo pg_dropcluster 9.3 main
    ```