Postgres - Upgrading and Using with Eternal Drives

PostgreSQL has some amazing features that make it a brilliant choice as a database engine. As well as being a fully functional RDBMS, it is ACID compliant, supports JSON fields, can support GIS, and, of course, it’s open source, completely free, and has a great community behind it. However, a couple of things weren’t as easy as I’d hoped, namely working with postgres data on an external drive, and also dealing with different versions of postgres and fixing by upgrading. After going through both of these recently, I thought I’d write down the procedures as a a reference.

Use PostgreSQL with a non-default database cluster (data directory)

N.B. If the location of this database cluster is a remote disk, make sure that it is mounted/unmounted properly, after stopping the PostgreSQL server!

1. Create new database cluster (data directory) with initdb:

    $ initdb -D /path/to/new/data/directory -E UTF8
   

2. Stop PostgreSQL server - as I’m using brew, stopping, starting and viewing the status of the PostgreSQL servers is done with the brew services command:

    $ brew services list
     $ brew services stop postgresql
     $ brew services start postgresql
   

3. Start the PostgreSQL server with the new data directory:

    $ pg_ctl -D /path/to/new/data/directory start
   

4. Open psql to open front end command line to query database:

    $ psql
    psql (9.5.1)
    Type "help" for help.
   
    user=# 
   

Upgrading isn’t as easy Things aren’t always as easy as they could be, no one-click upgrade, even with Homebrew! I’ve recently had to go through this experience . I found this great guide on https://keita.blog/2016/01/09/homebrew-and-postgresql-9-5/ and most of the steps are taken from there, with some expansion in various places.

The overall procedure is:

1. Backup PostgreSQL data
   • use pg_dump
2. Stop the PostgreSQL server postgres
3. Update PostgreSQL
   • using Homebrew for Mac
4. Create a new postgres database
5. Migrate the data from the old database to the new database
   • the pg_upgrade command allows you to specify locations for:
     • the old database data
     • the new database data
     • the old database server
     • the new database server
6. Move the old data directory to where PostgresSQL expects it to be

$ brew cleanup

A number of old formula versions were taking up a surprising amount of sapce, visible during the cleanup process, ranging from 22 versions of Node.js each taking ~35-40MB, and 11 versions of MySQL, each between 315-445MB. In total I managed to clear 21.9 GB of disk space, which was very welcome indeed! If I need older versions, I can reinstall and brew switch to them as needed.

A dry-run of the cleanup, where nothing is deleted but the items to be deleted are listed can be done by using the -n switch:

$ brew cleanup -n

Fixing virtualenv problems post-cleanup

One adverse thing from this cleanup was that my virtualenvironments for Python projects now seem to be all broken. This is due to the original symlinks made when the virtualenv was created were links to the Python installed by Homebrew and in use at the time of creation. When brew cleanup is run, Homebrew deletes old versions of Python, and those symlinks then point to paths that don’t exist. The fix to this is relatively simple though, recreating the symlinks in the virtualenv. For example, in a virtualenv called ‘foo-env’, this would be:

# Delete old symlinks for virtualenv foo-env
find ~/.virtualenvs/foo-env/ -type l -delete

# Create new symlinks for virtualenv foo-env
virtualenv ~/.virtualenvs/foo-env

Further, using gfind can be used to fix only those links that are broken:

gfind ~/.virtualenvs/my-virtual-env/ -type l -xtype l -delete

The fix to this problem was found in this Stackoverflow post.