chbrown · October 7, 2021 13:57 · Nov 20, 2016 · Jan 8, 2016 · Jan 8, 2016
diff --git a/_upgrade-pg9.4-to-pg9.5.md b/_upgrade-pg9.4-to-pg9.5.md
@@ -13,8 +13,19 @@ Third, update Homebrew:
     # you'll have an easier time selecting the right 9.4 bin dir if you run `brew cleanup` now
     # but DO NOT RUN `brew cleanup` if you've `brew update`'d on or after 2016-01-07
     brew update && brew upgrade
+
+_Third and a half_ (skip to **Fourth** below if you are not as reckless with `brew cleanup` as I am):
 
-Fourth, check your installed directories:
+> Caution! Time is weird. This is for the 9.5->9.6 upgrade, but the rest of the notes are still for 9.4->9.5.
+
+If you accidentally deleted the old postgresql version, it's pretty easy to get it back.
+
+    brew unlink postgresql # unlink the current (new) version
+    # Find the last 9.5 commit via `brew log postgresql` (hint: it's db96a14)
+    brew install https://raw.github.com/Homebrew/homebrew-core/db96a14/Formula/postgresql.rb
+    brew switch postgresql 9.6.1
+
+**Fourth**, check your installed directories:
 
     ls -1 /usr/local/Cellar/postgresql/
 

diff --git a/upgrade-pg9.4-to-pg9.5.md → _upgrade-pg9.4-to-pg9.5.md b/upgrade-pg9.4-to-pg9.5.md → _upgrade-pg9.4-to-pg9.5.md
diff --git a/analyze_new_cluster.sh b/analyze_new_cluster.sh
@@ -0,0 +1,22 @@
+#!/bin/sh
+
+echo 'This script will generate minimal optimizer statistics rapidly'
+echo 'so your system is usable, and then gather statistics twice more'
+echo 'with increasing accuracy.  When it is done, your system will'
+echo 'have the default level of optimizer statistics.'
+echo
+
+echo 'If you have used ALTER TABLE to modify the statistics target for'
+echo 'any tables, you might want to remove them and restore them after'
+echo 'running this script because they will delay fast statistics generation.'
+echo
+
+echo 'If you would like default statistics as quickly as possible, cancel'
+echo 'this script and run:'
+echo '    "/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-only'
+echo
+
+"/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-in-stages
+echo
+
+echo 'Done'
diff --git a/analyze_new_cluster.sh.log b/analyze_new_cluster.sh.log
@@ -0,0 +1,24 @@
+This script will generate minimal optimizer statistics rapidly
+so your system is usable, and then gather statistics twice more
+with increasing accuracy.  When it is done, your system will
+have the default level of optimizer statistics.
+
+If you have used ALTER TABLE to modify the statistics target for
+any tables, you might want to remove them and restore them after
+running this script because they will delay fast statistics generation.
+
+If you would like default statistics as quickly as possible, cancel
+this script and run:
+    "/usr/local/Cellar/postgresql/9.5.0/bin/vacuumdb" --all --analyze-only
+
+vacuumdb: processing database "formious": Generating minimal optimizer statistics (1 target)
+vacuumdb: processing database "metry": Generating minimal optimizer statistics (1 target)
+vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
+vacuumdb: processing database "formious": Generating medium optimizer statistics (10 targets)
+vacuumdb: processing database "metry": Generating medium optimizer statistics (10 targets)
+vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
+vacuumdb: processing database "formious": Generating default (full) optimizer statistics
+vacuumdb: processing database "metry": Generating default (full) optimizer statistics
+vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
+
+Done
diff --git a/delete_old_cluster.sh b/delete_old_cluster.sh
@@ -0,0 +1,3 @@
+#!/bin/sh
+
+rm -rf '/usr/local/var/postgres94'
diff --git a/homebrew.mxcl.postgresql.plist.xml b/homebrew.mxcl.postgresql.plist.xml
@@ -0,0 +1,24 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
+<plist version="1.0">
+<dict>
+  <key>KeepAlive</key>
+  <true/>
+  <key>Label</key>
+  <string>homebrew.mxcl.postgresql</string>
+  <key>ProgramArguments</key>
+  <array>
+    <string>/usr/local/opt/postgresql/bin/postgres</string>
+    <string>-D</string>
+    <string>/usr/local/var/postgres</string>
+    <string>-r</string>
+    <string>/usr/local/var/postgres/server.log</string>
+  </array>
+  <key>RunAtLoad</key>
+  <true/>
+  <key>WorkingDirectory</key>
+  <string>/usr/local</string>
+  <key>StandardErrorPath</key>
+  <string>/usr/local/var/postgres/server.log</string>
+</dict>
+</plist>
diff --git a/initdb.log b/initdb.log
@@ -0,0 +1,38 @@
+The files belonging to this database system will be owned by user "chbrown".
+This user must also own the server process.
+
+The database cluster will be initialized with locale "en_US.UTF-8".
+The default database encoding has accordingly been set to "UTF8".
+The default text search configuration will be set to "english".
+
+Data page checksums are disabled.
+
+creating directory /usr/local/var/postgres ... ok
+creating subdirectories ... ok
+selecting default max_connections ... 100
+selecting default shared_buffers ... 128MB
+selecting dynamic shared memory implementation ... posix
+creating configuration files ... ok
+creating template1 database in /usr/local/var/postgres/base/1 ... ok
+initializing pg_authid ... ok
+initializing dependencies ... ok
+creating system views ... ok
+loading system objects' descriptions ... ok
+creating collations ... ok
+creating conversions ... ok
+creating dictionaries ... ok
+setting privileges on built-in objects ... ok
+creating information schema ... ok
+loading PL/pgSQL server-side language ... ok
+vacuuming database template1 ... ok
+copying template1 to template0 ... ok
+copying template1 to postgres ... ok
+syncing data to disk ... ok
+
+WARNING: enabling "trust" authentication for local connections
+You can change this by editing pg_hba.conf or using the option -A, or
+--auth-local and --auth-host, the next time you run initdb.
+
+Success. You can now start the database server using:
+
+    pg_ctl -D /usr/local/var/postgres -l logfile start
diff --git a/pg_upgrade.log b/pg_upgrade.log
@@ -0,0 +1,20 @@
+copying "/usr/local/var/postgres94/base/1/12360" to "/usr/local/var/postgres/base/1/2613"
+copying "/usr/local/var/postgres94/base/1/12362" to "/usr/local/var/postgres/base/1/2683"
+copying "/usr/local/var/postgres94/base/1/12222" to "/usr/local/var/postgres/base/1/2995"
+copying "/usr/local/var/postgres94/base/1/12224" to "/usr/local/var/postgres/base/1/2996"
+                                                            ok
+Setting next OID for new cluster                            "/usr/local/Cellar/postgresql/9.5.0/bin/pg_resetxlog" -o 420396 "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
+ok
+Sync data directory to disk                                 "/usr/local/Cellar/postgresql/9.5.0/bin/initdb" --sync-only "/usr/local/var/postgres" >> "pg_upgrade_utility.log" 2>&1
+ok
+Creating script to analyze new cluster                      ok
+Creating script to delete old cluster                       ok
+
+Upgrade Complete
+----------------
+Optimizer statistics are not transferred by pg_upgrade so,
+once you start the new server, consider running:
+    ./analyze_new_cluster.sh
+
+Running this script will delete the old cluster's data files:
+    ./delete_old_cluster.sh
diff --git a/upgrade-pg9.4-to-pg9.5.md b/upgrade-pg9.4-to-pg9.5.md
@@ -0,0 +1,69 @@
+First, check your current config (example output in `homebrew.mxcl.postgresql.plist.xml` lower down in this gist):
+
+    cat ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
+
+Most importantly, note the `-D /usr/local/var/postgres` argument.
+
+Second, shut down your current PostgreSQL.
+
+    launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
+
+Third, update Homebrew:
+
+    # you'll have an easier time selecting the right 9.4 bin dir if you run `brew cleanup` now
+    # but DO NOT RUN `brew cleanup` if you've `brew update`'d on or after 2016-01-07
+    brew update && brew upgrade
+
+Fourth, check your installed directories:
+
+    ls -1 /usr/local/Cellar/postgresql/
+
+output on my machine:
+
+    9.4.5_2
+    9.5.0
+
+Fifth, prepare your new database (adjusting for your value of `-D` from launchctl config file, if needed):
+
+    mv /usr/local/var/postgres /usr/local/var/postgres94
+    initdb --pgdata=/usr/local/var/postgres
+    # example output later in this gist
+
+Sixth, run `pg_upgrade` (this is the good stuff, and the paths may need adjusting):
+
+    pg_upgrade \
+      --old-datadir=/usr/local/var/postgres94 \
+      --new-datadir=/usr/local/var/postgres \
+      --old-bindir=/usr/local/Cellar/postgresql/9.4.5_2/bin \
+      --new-bindir=/usr/local/Cellar/postgresql/9.5.0/bin \
+      --verbose
+
+That'll produce a ton of output and takes a minute or two, but should end with an "Upgrade Complete" message (see `pg_upgrade.log` later in gist).
+
+It also creates `analyze_new_cluster.sh` and `delete_old_cluster.sh` files in your current directory.
+
+Seventh, start up your shiny new PostgreSQL database:
+
+    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
+
+And ensure it's running properly:
+
+    psql postgres -c 'select version()'
+    >                                                   version
+    > --------------------------------------------------------------------------------------------------------------
+    >  PostgreSQL 9.5.0 on x86_64-apple-darwin15.2.0, compiled by Apple LLVM version 7.0.2 (clang-700.1.81), 64-bit
+    > (1 row)
+
+Eighth, optimize the new databases (I've elided some databases in the output shown in `analyze_new_cluster.sh.log` later in the gist):
+
+    ./analyze_new_cluster.sh
+
+Ninth, delete the old database files:
+
+    ./delete_old_cluster.sh
+
+Tenth, delete the helper scripts,
+
+    rm analyze_new_cluster.sh delete_old_cluster.sh
+
+And you're DONE!
No results found