Update all extensions in all databases

It is possible that development cluster might contain a number of databases that are being carried over from one PostgreSQL version to another with pg_upgrade. There might be a chance that some less frequently used databases will have older extension versions installed requiring updating those to make pg_upgrade possible. Here are a couple of scripts for Windows and Linux that can help to update all extensions in all databases in a cluster to current versions.

MS Windows

Copy and paste the following into a PowerShell script adjusting accordingly.

# $env:PGPORT=5430
# Maintenance database to connect to initially
$env:PGDATABASE='postgres'
$env:PGUSER='postgres'

ForEach ($db in & psql -tAc 'select datname from pg_database where datallowconn') {
  Write-Output "**** Working on $db ****"
  $sql = ((& psql -tAc 'select name from pg_available_extensions where default_version != installed_version' $db) -join " update;`nalter extension ")
  if ($sql) {
    $sql = "alter extension $sql update;"
    & psql -tAc "$sql" $db
  }
}