Skip to content

Instantly share code, notes, and snippets.

@cjolly

cjolly/pg.sh

Last active June 19, 2025 14:34
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save cjolly/2870054 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save cjolly/2870054 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. cjolly revised this gist Dec 9, 2016. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,11 @@
    newpg=9.6.1 # set to new PG version number
    oldpg=`pg_config --version | cut -d' ' -f2`

    # PG 96. upgrades the readline to v7, which breaks anything linked against readline v6, like ruby via ruby-build.
    # I *think* this should prevent it from installing v7. But if weird shit happens with various rubies,
    # you'll have to reinstall them.
    brew pin readline

    # Stop current Postgres server
    brew services stop postgresql

  2. cjolly revised this gist Dec 7, 2016. 1 changed file with 1 addition and 4 deletions.
    5 changes: 1 addition & 4 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,4 @@
    newpg=9.6.1 # set to new PG version number
    oldpg=`pg_config --version | cut -d' ' -f2`

    # Stop current Postgres server
    @@ -10,10 +11,6 @@ mv /usr/local/var/postgres/ /usr/local/var/postgres-$oldpg
    brew update
    brew upgrade postgresql

    # set below to new version number "9.6.1", or use cmd to below
    # newpg=9.6.1
    newpg=`brew list --versions postgresql | sort | tail -1 | cut -d' ' -f2`

    # Create new DB
    initdb /usr/local/var/postgres -E utf8

  3. cjolly revised this gist Dec 7, 2016. 1 changed file with 0 additions and 1 deletion.
    1 change: 0 additions & 1 deletion pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -14,7 +14,6 @@ brew upgrade postgresql
    # newpg=9.6.1
    newpg=`brew list --versions postgresql | sort | tail -1 | cut -d' ' -f2`


    # Create new DB
    initdb /usr/local/var/postgres -E utf8

  4. cjolly revised this gist Dec 7, 2016. 1 changed file with 0 additions and 2 deletions.
    2 changes: 0 additions & 2 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -39,7 +39,5 @@ brew services start postgresql
    ./delete_old_cluster.sh
    brew cleanup postgresql



    # Go home
    cd ~
  5. cjolly revised this gist Dec 7, 2016. 1 changed file with 10 additions and 2 deletions.
    12 changes: 10 additions & 2 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,4 @@
    newpg=9.6.1
    oldpg=9.3.2 # set this to your current PG version
    oldpg=`pg_config --version | cut -d' ' -f2`

    # Stop current Postgres server
    brew services stop postgresql
    @@ -11,6 +10,11 @@ mv /usr/local/var/postgres/ /usr/local/var/postgres-$oldpg
    brew update
    brew upgrade postgresql

    # set below to new version number "9.6.1", or use cmd to below
    # newpg=9.6.1
    newpg=`brew list --versions postgresql | sort | tail -1 | cut -d' ' -f2`


    # Create new DB
    initdb /usr/local/var/postgres -E utf8

    @@ -30,8 +34,12 @@ brew services start postgresql
    # Clean new DB
    ./analyze_new_cluster.sh

    # Make sure all your shit works!
    # After determining upgrade successful you can remove old DB.
    ./delete_old_cluster.sh
    brew cleanup postgresql



    # Go home
    cd ~
  6. cjolly revised this gist Dec 7, 2016. 1 changed file with 12 additions and 18 deletions.
    30 changes: 12 additions & 18 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,37 +1,31 @@
    newpg=9.3.2
    oldpg=9.2.4 # set this to your current PG version
    newpg=9.6.1
    oldpg=9.3.2 # set this to your current PG version

    # Stop current Postgres server
    launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    brew services stop postgresql

    # Backup current db
    mv /usr/local/var/postgres/ /usr/local/var/postgres-$oldpg

    # Homebrew
    # Check here if you have issues with either of these https://github.com/Homebrew/homebrew/wiki#troubleshooting
    brew update
    brew upgrade postgresql

    # OS X launch agents for PG, so it starts on boot automatically
    cp /usr/local/Cellar/postgresql/$newpg/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

    # These settings may or may not be necessary, investigate on your own
    # http://www.postgresql.org/docs/9.3/static/kernel-resources.html
    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216

    # Create new DB
    initdb /usr/local/var/postgres
    initdb /usr/local/var/postgres -E utf8

    # Upgrade old DB to new DB
    mkdir -p /tmp/pgupgrade && cd $_
    pg_upgrade -d /usr/local/var/postgres-$oldpg/ \
    -D /usr/local/var/postgres \
    -b /usr/local/Cellar/postgresql/$oldpg/bin \
    -B /usr/local/Cellar/postgresql/$newpg/bin
    pg_upgrade \
    --old-datadir=/usr/local/var/postgres-$oldpg/ \
    --new-datadir=/usr/local/var/postgres \
    --old-bindir=/usr/local/Cellar/postgresql/$oldpg/bin \
    --new-bindir=/usr/local/Cellar/postgresql/$newpg/bin \
    --jobs=4

    # Start new Postgres server
    launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    # launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    brew services start postgresql

    # Clean new DB
    ./analyze_new_cluster.sh
  7. cjolly revised this gist Jan 8, 2014. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -7,9 +7,12 @@ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    # Backup current db
    mv /usr/local/var/postgres/ /usr/local/var/postgres-$oldpg

    # Homebrew
    # Check here if you have issues with either of these https://github.com/Homebrew/homebrew/wiki#troubleshooting
    brew update
    brew upgrade postgresql

    # OS X launch agents for PG, so it starts on boot automatically
    cp /usr/local/Cellar/postgresql/$newpg/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

    # These settings may or may not be necessary, investigate on your own
  8. cjolly revised this gist Jan 8, 2014. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -22,7 +22,10 @@ initdb /usr/local/var/postgres

    # Upgrade old DB to new DB
    mkdir -p /tmp/pgupgrade && cd $_
    pg_upgrade -d /usr/local/var/postgres-$oldpg/ -D /usr/local/var/postgres -b /usr/local/Cellar/postgresql/$oldpg/bin -B /usr/local/Cellar/postgresql/$newpg/bin
    pg_upgrade -d /usr/local/var/postgres-$oldpg/ \
    -D /usr/local/var/postgres \
    -b /usr/local/Cellar/postgresql/$oldpg/bin \
    -B /usr/local/Cellar/postgresql/$newpg/bin

    # Start new Postgres server
    launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
  9. cjolly revised this gist Jan 8, 2014. 1 changed file with 6 additions and 4 deletions.
    10 changes: 6 additions & 4 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,3 @@

    newpg=9.3.2
    oldpg=9.2.4 # set this to your current PG version

    @@ -22,14 +21,17 @@ sudo sysctl -w kern.sysv.shmmax=16777216
    initdb /usr/local/var/postgres

    # Upgrade old DB to new DB
    mkdir -p /tmp/pgupgrade && cd $_
    pg_upgrade -d /usr/local/var/postgres-$oldpg/ -D /usr/local/var/postgres -b /usr/local/Cellar/postgresql/$oldpg/bin -B /usr/local/Cellar/postgresql/$newpg/bin

    # Start new Postgres server
    launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

    # Clean new DB (probably not necessary)
    vacuumdb --all
    # Clean new DB
    ./analyze_new_cluster.sh

    # After determining upgrade successful you can remove old DB.
    rm -rf /usr/local/var/postgres-$oldpg
    ./delete_old_cluster.sh

    # Go home
    cd ~
  10. cjolly revised this gist Jan 8, 2014. 1 changed file with 22 additions and 7 deletions.
    29 changes: 22 additions & 7 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,20 +1,35 @@
    pg_ctl -D /usr/local/var/postgres stop -s -m fast
    launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
    rm ~/Library/LaunchAgents/org.postgresql.postgres.plist

    mv /usr/local/var/postgres/ /usr/local/var/postgres-9.0.4/
    newpg=9.3.2
    oldpg=9.2.4 # set this to your current PG version

    # Stop current Postgres server
    launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

    # Backup current db
    mv /usr/local/var/postgres/ /usr/local/var/postgres-$oldpg

    brew update
    brew upgrade postgresql

    cp /usr/local/Cellar/postgresql/9.1.3/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
    cp /usr/local/Cellar/postgresql/$newpg/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

    # These settings may or may not be necessary, investigate on your own
    # http://www.postgresql.org/docs/9.3/static/kernel-resources.html
    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216

    # Create new DB
    initdb /usr/local/var/postgres
    pg_upgrade -d /usr/local/var/postgres-9.0.4/ -D /usr/local/var/postgres -b /usr/local/Cellar/postgresql/9.0.4/bin -B /usr/local/Cellar/postgresql/9.1.3/bin

    # Upgrade old DB to new DB
    pg_upgrade -d /usr/local/var/postgres-$oldpg/ -D /usr/local/var/postgres -b /usr/local/Cellar/postgresql/$oldpg/bin -B /usr/local/Cellar/postgresql/$newpg/bin

    # Start new Postgres server
    launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    vacuumdb --all --analyze-only

    # Clean new DB (probably not necessary)
    vacuumdb --all

    # After determining upgrade successful you can remove old DB.
    rm -rf /usr/local/var/postgres-$oldpg

  11. cjolly created this gist Jun 4, 2012.
    20 changes: 20 additions & 0 deletions pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    pg_ctl -D /usr/local/var/postgres stop -s -m fast
    launchctl unload -w ~/Library/LaunchAgents/org.postgresql.postgres.plist
    rm ~/Library/LaunchAgents/org.postgresql.postgres.plist

    mv /usr/local/var/postgres/ /usr/local/var/postgres-9.0.4/

    brew update
    brew upgrade postgresql

    cp /usr/local/Cellar/postgresql/9.1.3/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/

    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216

    initdb /usr/local/var/postgres
    pg_upgrade -d /usr/local/var/postgres-9.0.4/ -D /usr/local/var/postgres -b /usr/local/Cellar/postgresql/9.0.4/bin -B /usr/local/Cellar/postgresql/9.1.3/bin

    launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
    vacuumdb --all --analyze-only