Skip to content

Instantly share code, notes, and snippets.

@ShvaykaD

ShvaykaD/upgrade-postgresql-service-ubuntu.md

Forked from delameko/upgrade-postgres-9.5-to-9.6.md
Last active July 3, 2023 22:17
Download ZIP

Revisions

  1. ShvaykaD revised this gist May 4, 2020. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion upgrade-postgresql-service-ubuntu.md
    View file
    Original file line number Diff line number Diff line change
    @@ -34,7 +34,6 @@ echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo
    # install and launch the postgresql service:
    sudo apt update
    sudo apt -y install postgresql-11
    sudo service postgresql start
    ```

    Use `dpkg -l | grep postgresql` to check which versions of postgres are installed:
  2. ShvaykaD revised this gist May 4, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion upgrade-postgresql-service-ubuntu.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,7 +4,7 @@ Create a backup:


    ```bash
    pg_dumpall > mybackup.sql
    sudo -Hiu postgres pg_dumpall > mybackup.sql
    ```

    Install Postgres 11, and then:
  3. ShvaykaD revised this gist May 4, 2020. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions upgrade-postgresql-service-ubuntu.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,12 @@
    **TL;DR**

    Create a backup:


    ```bash
    pg_dumpall > mybackup.sql
    ```

    Install Postgres 11, and then:

    ```bash
  4. ShvaykaD revised this gist May 4, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion upgrade-postgresql-service-ubuntu.md
    View file
    Original file line number Diff line number Diff line change
    @@ -94,4 +94,4 @@ Lastly, please make sure that in the postgresql.conf for new cluster **cluster_n
    sudo nano /etc/postgresql/11/main/postgresql.conf
    ```

    and change `cluster_name = '9.6/main'` to `cluster_name = '11/main'`.
    change `cluster_name = '9.6/main'` to `cluster_name = '11/main'`.
  5. ShvaykaD revised this gist May 4, 2020. No changes.
  6. ShvaykaD renamed this gist May 4, 2020. 1 changed file with 0 additions and 0 deletions.
    0 upgrade-postgres-9.5-to-9.6.md → upgrade-postgresql-service-ubuntu.md
    View file
    File renamed without changes.
  7. ShvaykaD revised this gist May 4, 2020. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion upgrade-postgres-9.5-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -88,7 +88,7 @@ Check that the upgraded cluster works, then remove the 9.6 cluster.
    sudo pg_dropcluster 9.6 main
    ```

    Lastly, please make sure that in the postgresql.conf for new cluster **cluster_name** is equals to '11/main', otherwise replace the existing value with '11/main':
    Lastly, please make sure that in the postgresql.conf for new cluster **cluster_name** parameter is equals to '11/main', otherwise replace the existing value with '11/main':

    ```
    sudo nano /etc/postgresql/11/main/postgresql.conf
  8. ShvaykaD revised this gist May 4, 2020. 1 changed file with 51 additions and 39 deletions.
    90 changes: 51 additions & 39 deletions upgrade-postgres-9.5-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,85 +1,97 @@
    **TL;DR**

    Install Postgres 9.6, and then:
    Install Postgres 11, and then:

    ```bash
    sudo pg_dropcluster 9.6 main --stop
    sudo pg_upgradecluster 9.5 main
    sudo pg_dropcluster 9.5 main
    sudo pg_dropcluster 11 main --stop
    sudo pg_upgradecluster 9.x main
    sudo pg_dropcluster 9.x main

    (x is a sub-version number)
    ```

    ---
    Install PostgreSQL:
    Install PostgreSQL 11:

    ```
    sudo echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list
    ```bash
    # install **wget** if not already installed:
    sudo apt install -y wget

    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
    sudo apt-key add -
    sudo aptitude update
    # import the repository signing key:
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

    sudo aptitude install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6
    # add repository contents to your system:
    RELEASE=$(lsb_release -cs)
    echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee /etc/apt/sources.list.d/pgdg.list

    # install and launch the postgresql service:
    sudo apt update
    sudo apt -y install postgresql-11
    sudo service postgresql start
    ```

    Use `dpkg -l | grep postgresql` to check which versions of postgres are installed:

    ```
    i postgresql - object-relational SQL database (supported version)
    i A postgresql-9.5 - object-relational SQL database, version 9.5 server
    i A postgresql-9.6 - object-relational SQL database, version 9.6 server
    i A postgresql-client-9.5 - front-end programs for PostgreSQL 9.5
    i A postgresql-client-9.6 - front-end programs for PostgreSQL 9.6
    i A postgresql-contrib-9.5 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.6 - additional facilities for PostgreSQL
    ii postgresql-11 object-relational SQL database, version 11 server
    ii postgresql-9.6 object-relational SQL database, version 9.6 server
    ii postgresql-client-11 front-end programs for PostgreSQL 11
    ii postgresql-client-9.6 front-end programs for PostgreSQL 9.6
    ii postgresql-client-common manager for multiple PostgreSQL client versions
    ii postgresql-common PostgreSQL database-cluster manager
    ii postgresql-contrib-9.6 additional facilities for PostgreSQL
    ```

    Run `pg_lsclusters`, your 9.5 and 9.6 main clusters should be "online".
    In my case, I will be performing the upgrade from 9.6 to 11 version, so at the next steps, I will replace the 9.x version with the actual 9.6 version.

    **Note:** guide can be also used to upgrade PostgreSQL 10 version. Upgrade steps fully the same.

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

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

    There already is a cluster "main" for 9.6 (since this is created by default on package installation).
    There already is a cluster "main" for 11 (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.5/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`.
    but of course it clashes when you try to upgrade 9.6/main when 11/main also exists.
    The recommended procedure is to remove the 11 cluster with `pg_dropcluster` and then upgrade the 9.6/main with `pg_upgradecluster`.

    Stop the 9.6 cluster and drop it.
    Stop the 11 cluster and drop it.

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

    Upgrade the 9.5 cluster to the latest version.
    Upgrade the 9.6 cluster to the latest version.

    ```bash
    sudo pg_upgradecluster 9.5 main
    sudo pg_upgradecluster 9.6 main
    ```

    Your 9.5 cluster should now be "down".
    Your 9.6 cluster should now be "down".

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

    Check that the upgraded cluster works, then remove the 9.5 cluster.
    Check that the upgraded cluster works, then remove the 9.6 cluster.

    ```bash
    sudo pg_dropcluster 9.5 main
    sudo pg_dropcluster 9.6 main
    ```


    Lastly:
    Lastly, please make sure that in the postgresql.conf for new cluster **cluster_name** is equals to '11/main', otherwise replace the existing value with '11/main':

    ```
    sudo nano /etc/postgresql/9.6/main/postgresql.conf
    sudo nano /etc/postgresql/11/main/postgresql.conf
    ```

    Change `cluster_name = '9.5/main'` to `cluster_name = '9.6/main'`.
    and change `cluster_name = '9.6/main'` to `cluster_name = '11/main'`.
  9. @delameko delameko revised this gist Sep 20, 2018. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion upgrade-postgres-9.5-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    **TL;DR**

    Install Postgres 9.5, and then:
    Install Postgres 9.6, and then:

    ```bash
    sudo pg_dropcluster 9.6 main --stop
  10. @delameko delameko revised this gist Oct 1, 2016. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion upgrade-postgres-9.5-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -9,8 +9,20 @@ sudo pg_dropcluster 9.5 main
    ```

    ---
    Install PostgreSQL:

    `dpkg -l | grep postgresql` to check which versions of postgres are installed:
    ```
    sudo echo 'deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main' > /etc/apt/sources.list.d/pgdg.list
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
    sudo apt-key add -
    sudo aptitude update
    sudo aptitude install postgresql-9.6 postgresql-client-9.6 postgresql-contrib-9.6
    ```

    Use `dpkg -l | grep postgresql` to check which versions of postgres are installed:

    ```
    i postgresql - object-relational SQL database (supported version)
  11. @delameko delameko revised this gist Oct 1, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion upgrade-postgres-9.5-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -70,4 +70,4 @@ Lastly:
    sudo nano /etc/postgresql/9.6/main/postgresql.conf
    ```

    Change `cluster_name = '9.5/main'` to `cluster_name = '9.5/main'`.
    Change `cluster_name = '9.5/main'` to `cluster_name = '9.6/main'`.
  12. @delameko delameko revised this gist Oct 1, 2016. 2 changed files with 73 additions and 92 deletions.
    92 changes: 0 additions & 92 deletions upgrade-postgres-9.3-to-9.5.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,92 +0,0 @@
    **TL;DR**

    Install Postgres 9.5, and then:

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

    ---

    Specifically for using the WAL-E enabled Docker image, make sure WAL-E is turned off:

    ```
    sudo pkill backupcron
    ```

    Install postgresql 9.5:

    ```
    wget https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh
    chmod a+x apt.postgresql.org.sh
    sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g' apt.postgresql.org.sh
    sudo ./apt.postgresql.org.sh
    sudo apt-get install postgresql-9.5
    sudo apt-get install postgresql-client-9.5
    sudo apt-get install postgresql-contrib-9.5
    ```

    Once the installation 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.5 - object-relational SQL database, version 9.5 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.5 - front-end programs for PostgreSQL 9.5
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.5 - additional facilities for PostgreSQL
    ```

    Looks like we successfully installed PostgreSQL 9.5, but I still need to upgrade from 9.3 to 9.5.

    Run `pg_lsclusters`, your 9.3 and 9.5 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.5 main 5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
    ```

    There already is a cluster "main" for 9.5 (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.5/main also exists.
    The recommended procedure is to remove the 9.5 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.

    Stop the 9.5 cluster and drop it.

    ```bash
    sudo pg_dropcluster 9.5 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.5 main 5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
    ```

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

    ```bash
    sudo pg_dropcluster 9.3 main
    ```


    Now push an initial WAL-E base backup:

    ```
    sudo -u postgres envdir /etc/wal-e.d/env wal-e backup-push
    ```
    73 changes: 73 additions & 0 deletions upgrade-postgres-9.5-to-9.6.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,73 @@
    **TL;DR**

    Install Postgres 9.5, and then:

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

    ---

    `dpkg -l | grep postgresql` to check which versions of postgres are installed:

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

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

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.5 main 5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.5-main.log
    9.6 main 5433 online postgres /var/lib/postgresql/9.5/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.5/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.5 cluster to the latest version.

    ```bash
    sudo pg_upgradecluster 9.5 main
    ```

    Your 9.5 cluster should now be "down".

    ```
    pg_lsclusters
    Ver Cluster Port Status Owner Data directory Log file
    9.5 main 5433 down postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-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.5 cluster.

    ```bash
    sudo pg_dropcluster 9.5 main
    ```


    Lastly:

    ```
    sudo nano /etc/postgresql/9.6/main/postgresql.conf
    ```

    Change `cluster_name = '9.5/main'` to `cluster_name = '9.5/main'`.
  13. @johanndt johanndt revised this gist Nov 19, 2015. 2 changed files with 92 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
    ```
    92 changes: 92 additions & 0 deletions upgrade-postgres-9.3-to-9.5.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,92 @@
    **TL;DR**

    Install Postgres 9.5, and then:

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

    ---

    Specifically for using the WAL-E enabled Docker image, make sure WAL-E is turned off:

    ```
    sudo pkill backupcron
    ```

    Install postgresql 9.5:

    ```
    wget https://alioth.debian.org/scm/loggerhead/pkg-postgresql/postgresql-common/trunk/download/head:/apt.postgresql.org.s-20130224224205-px3qyst90b3xp8zj-1/apt.postgresql.org.sh
    chmod a+x apt.postgresql.org.sh
    sed -i -e 's/$CODENAME-pgdg main/$CODENAME-pgdg main 9.5/g' apt.postgresql.org.sh
    sudo ./apt.postgresql.org.sh
    sudo apt-get install postgresql-9.5
    sudo apt-get install postgresql-client-9.5
    sudo apt-get install postgresql-contrib-9.5
    ```

    Once the installation 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.5 - object-relational SQL database, version 9.5 server
    i A postgresql-client-9.3 - front-end programs for PostgreSQL 9.3
    i A postgresql-client-9.5 - front-end programs for PostgreSQL 9.5
    i A postgresql-contrib-9.3 - additional facilities for PostgreSQL
    i A postgresql-contrib-9.5 - additional facilities for PostgreSQL
    ```

    Looks like we successfully installed PostgreSQL 9.5, but I still need to upgrade from 9.3 to 9.5.

    Run `pg_lsclusters`, your 9.3 and 9.5 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.5 main 5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
    ```

    There already is a cluster "main" for 9.5 (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.5/main also exists.
    The recommended procedure is to remove the 9.5 cluster with `pg_dropcluster` and then upgrade with `pg_upgradecluster`.

    Stop the 9.5 cluster and drop it.

    ```bash
    sudo pg_dropcluster 9.5 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.5 main 5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
    ```

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

    ```bash
    sudo pg_dropcluster 9.3 main
    ```


    Now push an initial WAL-E base backup:

    ```
    sudo -u postgres envdir /etc/wal-e.d/env wal-e backup-push
    ```
  14. @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.
  15. @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".

  16. @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
  17. @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
    ```