Postgres’s pg_upgrade on Windows: the documentation misses a lot

Postgres is the gift of timesuck. It’s a great database, especially for spatial data. However, without insider knowledge, simple tasks eat time.

Latest example: pg_upgrade. It shrinks upgrade complexity–if it works! For Windows users, the documentation has useless and incomplete steps.

Here’s a better procedure. I used it to upgrade from 9.3 to 9.4. You might substitute for any older and newer version? I assume you’re using the default x64 install location of C:\Program Files\PostgreSQL. The steps:

  1. Install 9.4 with the Windows installer. Don’t let it use TCP port 5432 yet; that’s used by your current Postgres install. The installer saw this and recommended 5433. Add needed extensions with the Application Stack Builder. If you use PostGIS, don’t install the sample database. Do nothing else with the new install.
  2. With the Services control panel, shut down both the old and new databases.
  3. Create a new user on your PC named postgres. This is a Windows user, not a database user. Add it to your PC’s Administrators group. (I didn’t have this account. I don’t know why Postgres or pg_upgrade need it. A better design would permit me to specify database accounts for each install with pg_upgrade command line switches. The --username switch didn’t appear to do that, plus it would use the same username across both databases, which may not always be proper.)
  4. With Windows Explorer, give the Windows postgres account Full Control permission on C:\Program Files\PostgreSQL\ and all children.
  5. Edit the pg_hba.conf files for both servers. They are in C:\Program Files\PostgreSQL\[version]\data. If they are stock, comment out ALL lines, then add the below lines at the bottom. Note that this puts your database in an insecure state; you may want to take the host off the network or aggressively firewall it while this configuration is active.  Be sure to save a copy of the current files so you can revert. The new  lines:
    host all all 127.0.0.1/32 trust
    host all all ::1/128 trust
  6. Open a command prompt window in administrator mode. Run this: RUNAS /USER:postgres “CMD.EXE”
  7. In the new command prompt window, run this: pg_upgrade.exe --old-datadir “C:/Program Files/PostgreSQL/9.3/data” --new-datadir “C:/Program Files/PostgreSQL/9.4/data” --old-bindir “C:/Program Files/PostgreSQL/9.3/bin” --new-bindir “C:/Program Files/PostgreSQL/9.4/bin” This will take a while to run if you have a lot of data. Wait until this is done before continuing.
  8. Edit the postgresql.conf file for the new Postgres install and change the listening port to 5432 (from 5433).
  9. Through the Services control panel, start your new Postgres install back up.
  10. Vacuum and reanalyze all databases.
  11. If you created a postgres Windows account above, remove it.

The new Postgres install’s postgres database account (not the Windows account) will now have the same password as in the old Postgres install.

Once you’ve verified that everything works properly, it may be safe to uninstall the old Postgres copy.

Do projects matter for IT?

(Originally posted on Eric Brown’s Technology, Strategy, People & Projects blog on June 7, 2011, with some edits. Still highly relevant at the end of 2014!)

Short answer: not as much as many believe.

Information technology (IT) focus is shifting from classical projects to agile services. Here’s why.

Reason 1: Much of IT defies project definition

A classical project has a predetermined start, end, work breakdwon, and result. When done, the result goes into “maintenance mode”, and you jump to the next project.

But what if something never has a “maintenance mode”? What if a work breakdown is impossible to know?

For example, the web is never done. A university’s web site must be exciting and work quite well; the key audiences are technologically progressive prospective and current students. What university wants technophobic students? Relevant university sites must keep up with rapidly evolving consumer technologies.

A university’s web site is a good example of an agile service: an adaptive mix of agile applications and expertise. These are where a lot of IT’s attention is going.

Agile services don’t end. They are not classical projects.

Reason 2: Small projects don’t matter much

Isn’t a service just a lot of mini-projects? And isn’t the latest trend to make projects smaller?

Neither matters much. Small projects are really large tasks or iterations in an agile service.

By themselves, small projects don’t tell the value of IT. Agile services do.

Reason 3: Virtualization and clouding

The largest classical IT projects are implementations. Virtualization and especially clouding make it easier to create new things, sometimes minimizing implementation projects into simple tasks.

Without huge implementations, the focus shifts to maximizing value of existing investments. Again, emphasizing agile services at the cost of classical projects.

Reason 4: Agile is where it’s at

Classical projects use waterfall, a prescriptive method from the manufacturing and construction industries. It’s from a time when the pace was steady, change was resisted, and top down was how it happened.

Relevant IT is the opposite: fast-paced, adaptive, and responsive. That’s why agile management is natural for IT: it encourages adaption, continual reassessment, early problem discovery, and faster completion.

I’m not the only one seeing this. Look at Google search trends for agile project (blue) versus waterfall project (red):

agile vs waterfall

But this isn’t just about improving how projects are done. Agile does something that waterfall can’t: manage services.

Paraphrasing Men In Black II, “Waterfall projects: old and busted. Agile services: new hotness.”

Do classical projects belong in IT?

Classical projects still have a place in relevant IT. We will still have cookie cutter projects with well-understood paths and vanilla outcomes.

However, “well-understood” and “vanilla” and are being outsourced, such as email, web systems, ERP systems, and more. If not outsourced, they may be “keep the lights on” , undifferentiated from plant operations. Or their business value is not intrinsic; the value is in what others—users, innovators, developers—can wring from them.

Agile services are the future of IT. It’s how relevant IT works, it’s how relevant IT provides business value, and it’s how relevant IT communicates what it does.