Skip to content

Instantly share code, notes, and snippets.

@grant-humphries

grant-humphries/windows_postgres_upgrade.md

Last active March 28, 2017 17:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save grant-humphries/df7d54143e72560e604d139919db6e4e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save grant-humphries/df7d54143e72560e604d139919db6e4e to your computer and use it in GitHub Desktop.
Download ZIP
Upgrade PostgreSQL & PostGIS on Windows
Raw
windows_postgres_upgrade.md

This is currently incomplete

Upgrade PostgreSQL & PostGIS on Windows

  1. From cmd running as administrator create a Windows user called postgres with the following commands:

    :: if postgres user already exists delete it
net user postgres /delete
rmdir "C:\Users\postgres" /S /Q

:: create the postgres and add to admin group
net user /add postgres <password>
net localgroup administrators postgres /add

:: give the postgres user full permissions on the postgres install dir recursively
:: details on this command here: http://stackoverflow.com/a/8311008/2167004
icacls "<postgres-install-dir>" /grant postgres:(OI)(CI)F /T

  2. In the same shell stop the olde version of postgres with this command:

    pg_ctl stop -D "<postgres-install-dir>\<old-version>\data"

  3. Next launch a cmd session as the postgres Windows user with the command below, this will open a new shell and subsequent commands will be entered there.

    runas /user:postgres cmd

  4. Download the postgres Windows installer and save it in a location that the postgres Windows user can access (like C:\Users\postgres\Downloads).

  5. From the postgres cmd session launch the installer executable that was just downloaded. Step through the installer and use the same password for the postgres database user as the previous install if possible.

  6. Use the stack builder to install PostGIS, don't create a default spatial db, and answer 'No' to all of the prompts regarding PostGIS rasters.

  7. Back in the postgres cmd shell run the following commands to migrate data and settings the new install:

    set PGPASSWORD=<postgres-db-user-password>
pg_upgrade

  8. Within your PATH environment variable remove the directory that holds the older version of the postgres executables and add the folder that contains the new ones. From the home directory of your postgres install these will be in the bin directory.

  9. Make sure the new version of postgres is stopped:

    pg_ctl stop -D "<postgres_install_location>\<new_version_num>\data"

Installer Issues

  • When launching the installer you may recieve the following error:

    There has been an error.
    Unable to write inside TEMP environment variable path.

    To resolve this open regedit and navigate to HKEY_CLASSES_ROOT\.vbs, after clicking on .vbs in left pane move to the right hand pane and under "(Default)" set "Data" to VBSFile. Find more details on this issue here. On my computer this value seems to revert regularly so this task may have to be repeated.

  • Towards the end of the installation the installer may hang when its output reads:

    Initialising the database cluster (this may take a few minutes)...

    If the wizard doesn't progress past this message after more than a minute or two it is likely stuck there. This can be caused by failing to create the postgres Windows user and running the installer as that user.

  • If something goes wrong with a postgres installation and you want to completely uninstall it and try again, you must delete the below key from regedit after running the uninstaller:

    HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\<postgres_install>

Resources

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment