As I experieced many times before, migrating/upgrating a database always hurts. That’s the reason I won’t upgrade my Postgresql to the latest version until I have to. And the day finally comes.
I have to upgrade my Postgresql server from 9.4.9 to 9.6.1 with pg_upgrade. Otherwise, I can not properly install postgis, which is another long story. I am surely not the first one who is doing this upgrading. Olivier Lacan has a great post about Postgresql database migrating. But the thing is, I still can not run the pg_upgrade command properly after unloading the homebrew.mxcl.postgresql.plist. And the most weird thing is I can not even kill the postgres process even with kill -9. After each time I enter the kill command, the postgres services appear again with a new PID just like a ghost virus program. And the pg_ctl stop command, of course, doesn’t work with whatever mode option. Finally, I figure out that there is another pList - homebrew.mxcl.postgresql.plist.bak still active in the ~/Library/LaunchAgents/ directory. And it IS the control script that starting the current Postgresql service. So I immediatly launchctl unload it, then all the postgres services are gone.
All done? Don’t be naive. After running the command
command: "/usr/local/Cellar/postgresql/9.6.1/bin/pg_dump" --host '/usr/local/var/log' --port 50432 --username 'user' --schema-only --quote-all-identifiers --binary-upgrade --format=custom --file="pg_upgrade_dump_16417.custom" 'dbname=mmrp_munich' >> "pg_upgrade_dump_16417.log" 2>&1 pg_dump: [archiver (db)] query failed: ERROR: could not access file "$libdir/postgis-2.1": No such file or directory pg_dump: [archiver (db)] query was: SELECT a.attnum, a.attname, a.atttypmod, a.attstattarget, a.attstorage, t.typstorage, a.attnotnull, a.atthasdef, a.attisdropped, a.attlen, a.attalign, a.attislocal, pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, array_to_string(a.attoptions, ', ') AS attoptions, CASE WHEN a.attcollation <> t.typcollation THEN a.attcollation ELSE 0 END AS attcollation, pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(option_name) || ' ' || pg_catalog.quote_literal(option_value) FROM pg_catalog.pg_options_to_table(attfdwoptions) ORDER BY option_name), E', ') AS attfdwoptions FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t ON a.atttypid = t.oid WHERE a.attrelid = '28564'::pg_catalog.oid AND a.attnum > 0::pg_catalog.int2 ORDER BY a.attrelid, a.attnum
So it’s postgis’s show time. could not access file "$libdir/postgis-2.1": No such file or directory. Of course you could not access the file because it has already upgraded to 2.3. I tried relaunching the old 9.4.9 server, but failed because all the client toolset are already 9.6.1 which reported incompatibility FATAL error. I feel so helpless. And I think a better workflow should be:
Upgrade the postgis extention of your spatial database instances
Stop old postgresql service
Move old postgresql’s data cluster directory to a backup location
Install new postgresql server
Init new postgresql data cluster (with initdb)
Stop new postgresql service
Upgrade postgresql server (with pg_upgrade)
I don’t think there are many choices for me. So I decide using Postgres.app instead, and just give up all the postgresql stuff from Homebrew. I hope that will bring less pain in the future. How about the data? Don’t worry, I still have a running database on AWS.
Let me make a convention about the blog writing language. From now on, the posts will be in English if they are categorized in “Programming”, or in Chinese if they are in “Life”.
I have just run a brew cleanup command to release some of my hard drive space. But after that, I suddenly found my python doesn’t work:
1
pyenv /usr/local/var/pyenv/shims/python: line 21: /usr/local/Cellar/pyenv/20160310/libexec/pyenv: No such file or directory
As it says, the directory of /usr/local/Cellar/pyenv/20160310/ really does not exist. And the solution can be found from brew info pyenv:
1 2 3 4 5
To use Homebrew's directories rather than ~/.pyenv add to your profile: export PYENV_ROOT=/usr/local/var/pyenv To enable shims and autocompletion add to your profile: if which pyenv > /dev/null; then eval "$(pyenv init -)"; fi
They are obviously already in my .bash_profile file. The only necessary action is to do source ~/.bash_profile.
