Skip to content

Instantly share code, notes, and snippets.

@preagan
Created March 14, 2020 19:36
Show Gist options
  • Save preagan/4eb04fc2243f7271de7ad4863ab2e1af to your computer and use it in GitHub Desktop.
Save preagan/4eb04fc2243f7271de7ad4863ab2e1af to your computer and use it in GitHub Desktop.
Postgresql OS-X upgrade from v10 to v12 using pg_dumpall
This is a copy from my notes, written to help me quickly accomplish the goal. Included also are short explanations and tests that help me transition back into postgres admin after coding something else.
- Note two aliases used below are in my ~/.bash_profile file...
1) alias ll='LC_COLLATE=C ls -alhlTF'
# showing .files (sorted first),
# permissions/sizes included, with special characters (after directory
# symlink, etc) names, long-format dates, and with human-readable file sizes.
2) alias path='echo -e ${PATH//:/\\n}'
# path: Echo all executable Paths
The tl:dr...
- make copy of v10 PGDATA dir as safety
- perform a pg_dumpall
- remove all v10 postgres files
- install v12
- rebuild cluster using psql and the dump file
- test operation and data
Details...
- make copy of v10 PGDATA directory as a safety (note that OS-X Finder copies faster than bash cp)
- perform a pg_dumpall to collect cluster information into one file
$ pg_dumpall -f <your dump file path/name>
- this creates a file of commands that describe how to recreate you schema and data
- the option -f is to specify file path/name
- made sure that brew is up to date
$ brew update // 1.5 minutes, will vary depending upon how big an update you need
- ensure there are no conflicts or errors
$ brew doctor // 0.5 mintes, again will vary depending upon your situation
- fix any errors that might impact postgres
- confirm the name of your postgres instance
$ brew services list // note the postgres whose status is "started"
- If you don’t already have brew services installed. It may be installed with this command:
$ brew tap homebrew/services
- stop postgres
$ brew services stop postgresql@10 // substitue the name of your instance if different
- check that brew has removed launchctl's plist file
$ ll ~/library/launchagents // should NOT include "homebrew.mxcl.postgresql.plist"
- confirm postgres was not running
$ psql the following result means the server process is not running...
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
- further confirm in OS-X Activity Monitor there's no running postgres processes
- even further confirm
$ ps auxwww | grep postgres // should only show the your grep postgres process
- even further confirm using brew services
$ brew services list // postgres should be "stopped"
- remove postgres v10
$ brew uninstall --force postgresql@10 // substitue the name of your instance
- the force option is supposed to "Delete all installed versions of formula."
- confirm that no links remain for postgres
$ ll /usr/local/opt // should have nothing for postgres v10
- if any found, remove them using rm
$ rm /usr/local/opt/<whatever was found>
- view old PGDATA postgres user files (if present)
$ ll /usr/local/var
- if any found, remove them using rm
$ rm -r /usr/local/var/<whatever was found> // -r = recusively remove folder and contents
- view postgres log files (if present)
$ ll /usr/local/var // same command as when checking for user files, look for "log" dir
- if any found, remove it using rm
$ rm -r /usr/local/var/log // -r = recusively remove entire log folder and contents
- check for any remaining postgres files
$ sudo find /usr -name postgres // in or below dir usr, find any files that begin with "postgres"
- Use of sudo here is to allow find to search all directories
- I got "/usr/local/Homebrew" and "/usr/local/python3.6", which are OK to leave in
- determine what versions of postgres are available through brew
$ brew search postgresql
- confirm info about "postgres", should be v12
$ brew info postgres // it should return...
postgresql: stable 12.2 (bottled), HEAD
...
Not installed
...
- install postgres
$ brew install postgres // 0.75 minutes, ytmv
- confirm install occurred
$ brew info postgres // should show Cellar install path
...
/usr/local/Cellar/postgresql/12.2 (3,218 files, 36.9MB) * // or something like this
...
- check symlinks
$ ll /usr/local/opt // should all point to .../Cellar/postgresql/12.2 // or something like this
postgres@ -> ../Cellar/postgresql/12.2
postgresql@ -> ../Cellar/postgresql/12.2
postgreeql!12@ -> ../Cellar/postgresql/12.2
- start postgres using brew
- you COULD use $ pg_ctl -D /usr/local/var/<postres dir name> start, but brew will also create a
launchctl .plist file to auto-start postgres when your Mac starts up
$ brew services list // should show postgresql "stopped"
$ brew services start postgresql // should show "==> Successfully started..."
$ ll ~/library/launchagents // should include "homebrew.mxcl.postgresql.plist"
$ brew services list // should now show postgresql as "started"
- set PATH to include your postgres path
$ path // to show "before" PATH for postgres
- change your .bash_profile file for v12 postgres path for later-started processes
$ sudo nano ~/.bash_profile
- my old path looked like this...
export PATH="/usr/local/opt/postgresql@10/bin:$PATH"
- adjust it to look like this (my v12 needed a different dir structure)...
export PATH="/usr/local/opt/postgresql/12/bin:$PATH"
- open new bash tab to force a clean build of path, then check it...
$ path // (same as: $ echo $PATH) to show "after" PATH, should include the v12 path
- staying in the new tab from now on, confirm postgres is running
$ pg_config --version // mine showed: PostgreSQL 12.2
- manually create the user database
$ createdb <your user name>
- display the version number, client and server should be same, the return # applies to both
$ psql // mine: psql (12.2)
- check what db's are in present
# \l // should show only your newly created <user name> db and the default db's (postgres, template0, template1)
- close out of psql
# Control-D
- rebuild your schema, and populate it with data
$ psql -f <your dump file path/name> postgres
this tells psql to run all the commands contained in your previously created pg_dumpall file
-f allows you to specify the pg_dumpall file name
"postgres" is the user name to run the commands
- start psql to confirm proper postgres version
$ psql
- confirm v10 db's, and previously viewed default db's are now visible
# \l
- do your own checks of the v10 db data
- close out of psql
# Control-D
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment