Sunday, April 17, 2011

Upgrading PostgreSQL from 8.3 to 9.0.4 (Windows 2003 Server)

Updating my old PostgreSQL server on my Windows Server 2003.

1. Backup the current server.
  a. Open pgAdminIII on the sever machine.
  b. Connect to the PostgresSQL server.
  c. Goto Menubar to select Tools menu and Backup Server... option
  d. Specify a backup file name (PgSQLBkup83-2011-04-16.sql).
  e. Press OK to execute backup.

2. Stop and Disable the server.
  a. Open Windows Services control panel.
  b. Stop the "PostgreSQL Database Server 8.3" service.
  c. Disabled the service.

3. Download new version of PostgreSQL.
  a. Go to www.enterprisedb.com
  b. Download an installer: postgresql-9.0.4-1-windows.exe.

4. Install the software.
  a. Accept default install directory: c:\program files\PostgreSQL\9.0\data
  b. Provide user "postgres" account's password.
      I could not remember the password. So I reset the password.
  c. Accept deafult port: 5432
  d. Do not install Stack Builder (extra stuff from EnterpriseDB).
  e. Check the installation (postgresql-9.0, started) in the Services window.
  f. Test login from pgAdminIII (Start > All Programs > PostgreSQL 9.0 > pgAdmin III).

5. Restore the data.
  a. login to Windows with postgres account.
  b. execute: psql -f path\to\mybackupfile postgres.
C:\Program Files\PostgreSQL\9.0\bin\psql -f e:\PGSQLBKUPS\PgSQL83Bkup2011-04-16.sql postgres

6. Restore config.
  a. Rename pg_hba.conf as pg_hba.conf.bak.original in 9.0 directory (c:\program files\PostgreSQL\9.0\data)
  b. Restore pg_hba.conf by copying from 8.3 directory (c:\program files\PostgreSQL\8.3\data).
  c. I did not do anything about postgresql.conf as I wanted to start with default settings.

7. Test connection and databases.
  Everything looks good.

8. To do.
  a. Setup automatic daily backups
  b. Set environmental variable PATH to psql/bin.

5 comments:

  1. I just finished the upgrade from 8.3 to 9.0.4.1 with success. Step 5.a and b didnt work because in Win 7 x64 there was no postgres account I could use to login to windows. Did the restore from pgAdminIII creating the database with PokerTracker3. All looks fine and Im starting to test now. Thanks for the procedure!

    ReplyDelete
  2. I've got a little problem, I got postgres 8.3 on windows 7 X86 but I can't log in with the postgres user and I don't know where are the options to set the account and enable the login

    ReplyDelete
  3. P.S, my email it's matiasorossi@gmail.com

    ReplyDelete
  4. Hi, How I can migrate to 9.4 from 8.3.11? Do you know?
    Thanks for your reply.

    ReplyDelete