Guide on upgrading local postgres database

Postgresql Mac OSX upgrade

Figure out what verion of postgres your homebrew data directory currently has:

$ cat /usr/local/var/postgres/PG_VERSION
9.6

And what is your currently active postgres minor/patch version:

$ psql --version
psql (PostgreSQL) 9.6.1

If you find that your homebrew directory version is out of step, like on 10.x then you can step back down as below

$ brew uninstall postgresql
$ brew install [email protected]
$ brew services start [email protected]
$ brew link [email protected] --force

Backup (optional)

Locate current DB data directory

$ psql
psql (9.6.1)
Type "help" for help.

jsugarman=# \c cbo_development
You are now connected to database "cbo_development" as user "jsugarman".
cbo_development=# SHOW data_directory;
     data_directory
-------------------------
 /usr/local/var/postgres
(1 row)

cbo_development=# \q

Db data directory backup, if desired:

$ mv /usr/local/var/postgres/ /usr/local/var/postgres.9.6.1.backup/

Start a new clean database:

$ initdb /usr/local/var/postgres/
The files belonging to this database system will be owned by user "jsugarman".
This user must also own the server process.

The database cluster will be initialized with locale "en_GB.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
running bootstrap script ... ok
performing post-bootstrap initialization ... 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

Upgrade new database

Find available formula

$ brew search postgresql
==> Formulae
postgresql ✔  [email protected]  [email protected]  [email protected]

Upgrade or install latest 9.6 version, if you are on 9.6.x this should just work

$ brew upgrade [email protected]

If it fails you may have the binary installed directly. You can instead install with homebrew:

$ brew install [email protected]
==> Downloading https://homebrew.bintray.com/bottles/[email protected]_sierra.bottle.1.tar.gz
==> Downloading from https://akamai.bintray.com/da/da0d71fb4913b6ad4fc959a770295416b69c7d04ceb3995b5d46006949fc1c46?__gda__=exp=1539939438~hmac=2ca65d88e72a6d1428ccaee79d8043c3
######################################################################## 100.0%
==> Pouring [email protected]_sierra.bottle.1.tar.gz
==> /usr/local/Cellar/[email protected]/9.6.10/bin/initdb /usr/local/var/[email protected]
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/legacy-homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.0) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.6/static/upgrading.html

To migrate existing data from a previous minor version (9.0-9.5) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.6/static/pgupgrade.html

  You will need your previous PostgreSQL installation from brew to perform `pg_upgrade`.
    Do not run `brew cleanup [email protected]` until you have performed the migration.

[email protected] is keg-only, which means it was not symlinked into /usr/local,
because this is an alternate version of another formula.

If you need to have [email protected] first in your PATH run:
  echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.bash_profile

For compilers to find [email protected] you may need to set:
  export LDFLAGS="-L/usr/local/opt/[email protected]/lib"
  export CPPFLAGS="-I/usr/local/opt/[email protected]/include"

For pkg-config to find [email protected] you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/[email protected]/lib/pkgconfig"


To have launchd start [email protected] now and restart at login:
  brew services start [email protected]
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/[email protected] start
==> Summary
🍺  /usr/local/Cellar/[email protected]/9.6.10: 3,292 files, 37.3MB

Put 9.6 first in your path

$ echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile
$ psql --version
psql (PostgreSQL) 9.6.10

create your role (so you can login in just with psql)

$ createdb

and test

$ psql
psql (9.6.10)
Type "help" for help.

jsugarman=#

Upgrade backed up database and switch back to it (optional)

Shutting all db instances down

Check status of Db and stop

$ pg_ctl -D /usr/local/var/postgres status
$ pg_ctl -D /usr/local/var/postgres stop -m fast
$ pg_ctl -D /usr/local/var/postgres.9.6.1.backup status
$ pg_ctl -D /usr/local/var/postgres.9.6.1.backup stop -m fast

or, if using homebrew services

$ brew services stop postgresql
$ brew services list

check for still running postgres processes and remove if necessary

$ cat /usr/local/var/postgres/postmaster.pid
$ rm /usr/local/var/postgres/postmaster.pid
# or
$ cat /usr/local/var/[email protected]/postmaster.pid
$ rm /usr/local/var/[email protected]/postmaster.pid

If you want to upgrade the old database data you can try this. You will need to change the old version binary and old version directory to match your previous old postgres version, but this is what the pg_upgrade command should look like:

Note: Lowercase flags (-b and -d) are for old binary and data directories respectively. Their uppercase counterparts are for their new equivalents.

$ pg_upgrade \
-b /usr/local/Cellar/postgresql/9.6.1/bin/ \
-B /usr/local/Cellar/[email protected]/9.6.10/bin/ \
-d /usr/local/var/postgres.9.6.1.backup/ \
-D /usr/local/var/postgres

$ brew services start [email protected]
==> Successfully started `[email protected]` (label: [email protected])

vacuum/analyze/optimise the new db

$ ./analyze_new_cluster.sh

move upgraded backed up database back

Note: I was not able to get this working

$ brew services stop [email protected]
Stopping `[email protected]`... (might take a while)
==> Successfully stopped `[email protected]` (label: [email protected])
$ mv /usr/local/var/postgres.9.6.1.backup/ /usr/local/var/[email protected]/
$ ./delete_old_cluster.sh

tidy up after upgrading backed up database

$ rm ./analyze_new_cluster.sh
$ rm ./delete_old_cluster.sh
$ rm logfile

Re-setup CCCD

Note: Only needed for CCCD app and don't need this if you have upgraded and switched over to the old data directory in any event. Also, at time of writing, the creation of demo data is currently broken but this task will, nonetheless, create the various users anyway)

$ cd <cccd dir>
$ rake db:setup
$ rake db:reload

jsugarman/postgres_local_upgrade.md