Postgresql service can not be stopped on OSX
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
1 | pg_upgrade -b /usr/local/Cellar/postgresql94/9.4.9_1/bin -B /usr/local/Cellar/postgresql/9.6.1/bin -d /usr/local/var/postgres.9.4.9.bak -D /usr/local/var/postgres |
I got the error (from pg_dump_log):
1 | 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 |
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.