Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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)
share|improve this question
    
Hello my friend, we meet again. The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.5. –  Chloe Dec 27 '14 at 1:56

1 Answer 1

up vote 1 down vote accepted
  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: ftp://www.fruitbat.org/pub/cygwin/circa/index.html
    2. Copy the FTP address
    3. Run /setup-x86.exe -X with the -X option to ignore setup signatures (they aren't archived).
    4. Paste the FTP address into the dialog to choose your download site. You will then see a snapshot of packages available during that time.
  3. Start the old version of Postgres: /usr/sbin/postmaster &
  4. Dump the DB into a temporary file with pg_dumpall > /tmp/pg.sql
  5. Shut down Postgres /usr/sbin/pg_ctl.exe stop
  6. Move old data directory mv /usr/share/postgresql /usr/share/postgresql-9.2
  7. Run setup normally and install latest Postgres. Make sure to select your normal download site.
  8. Initialize the DB /usr/sbin/pg_ctl init
  9. Start the new version of Postgres /usr/sbin/pg_ctl.exe start
  10. Import the old DB psql -d postgres -f /tmp/pg.sql
share|improve this answer
1  
You left out one really important item -- Step Zero: Read the most excellent documentation. Your steps above are otherwise correct, though there shouldn't be "many warnings + errors" at step 13 -- the reload should go smoothly... –  voretaq7 Aug 20 '13 at 19:33
1  
You could also use pg_dumpall instead of dumping individual databases -- might make your life easier. –  voretaq7 Aug 20 '13 at 19:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.