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
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
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
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):
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
- Stop new postgresql service
- Upgrade postgresql server (with
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.