Forked from chbrown/_upgrade-pg9.4-to-pg9.5.md
Last active
November 13, 2017 16:22
-
-
Save austinmcconnell/a273ba50487e603086a2d59aefb10955 to your computer and use it in GitHub Desktop.
Upgrade PostgreSQL 9.4 to 9.5 on Mac OS X with Homebrew
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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. | |
brew services stop postgresql | |
Third, update Homebrew: | |
brew update && brew upgrade | |
_Third and a half_ (skip to **Fourth** below if you are not as reckless with `brew cleanup` as I am): | |
> 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/ | |
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: | |
brew services start postgresql | |
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! |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/bin/sh | |
rm -rf '/usr/local/var/postgres94' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment