Postgresql – How to upgrade Postgres database in Cygwin

cygwinpostgresql

I updated some Cygwin packages and now I can't start Postgres:

$ /usr/sbin/postmaster
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.2, which is not compatible with this version 9.2.4.

I tried pg_upgrade but you need to specify both the old and new binary. Plus, pg_upgrade says it only works with 8.3.

I thought I could use setup-x86.exe to pick the previous version, which is 8.2.11-1, however when I install that, then I can't start Postgres:

$ /usr/sbin/postgres.exe
Bad system call (core dumped)

Best Answer

  1. Uninstall postgres 9
  2. Install postgres 8.2
  3. Install libpq
  4. /etc/rc.d/init.d/postgresql start
  5. Do this for each database:
    • pg_dump -f stocks.sql.gz -Z 9 -C stocks
  6. /etc/rc.d/init.d/postgresql stop
  7. cd $PGDATA/.. # /usr/share/postgresql/..
  8. mv postgresql postgresql-8.2
  9. Install postgres 9, postgresql-contrib, uninstall libpq.
  10. /usr/sbin/pg_ctl.exe init
  11. /usr/sbin/pg_ctl.exe start
  12. createdb.exe # Creates a DB with your username
  13. gzip -dc stocks.sql.gz | psql # many warnings + errors

Upgrading from 9.2 to 9.3

  1. Read about upgrading Postgres: http://www.postgresql.org/docs/current/static/upgrading.html
  2. Install an old version of Postgres if you have to. Someone maintains a historical archive of Cygwin versions.
    1. Browse the time stamp of the setup.ini file you need: http://www.crouchingtigerhiddenfruitbat.org/Cygwin/timemachine.html
    2. Copy the address of the folder (not index.html)
    3. Run /setup-x86.exe -X with the -X option to ignore setup signatures (they aren't archived).
    4. Paste the address into the dialog to choose your download site. You will then see a snapshot of packages available during that time.
  3. Restart cygserver if you shut it down to upgrade: Run As Administrator: cygrunsrv -S cygserver
  4. Start the old version of Postgres: /usr/sbin/postmaster & or /usr/sbin/pg_ctl start
  5. Dump the DB into a temporary file with pg_dumpall > /tmp/pg.sql
  6. Shut down Postgres /usr/sbin/pg_ctl.exe stop
  7. Move old data directory mv /usr/share/postgresql /usr/share/postgresql-9.2
  8. Run setup normally and install latest Postgres. Make sure to select your normal download site.
  9. Initialize the DB /usr/sbin/pg_ctl init
  10. Start the new version of Postgres /usr/sbin/pg_ctl.exe start
  11. Import the old DB psql -d postgres -f /tmp/pg.sql
Related Topic