Upgrade from PostgreSQL 7.4 to 8.1

August 3, 2007

I had mistakenly thought that Debian package postgresql was an “alias” for the most recent version of the database server, but evidently it contains support functions specifically for 7.x versions, and hence my earlier install only led to PostgreSQL 7.4. I should therefore be able to upgrade and use PostgresSQL 8.1 under Debian 4.0 after all.

Before installing the 8.1 packages, I backed up by dumping the databases as well as saving copies of the files server.key, server.crt, and root.crt.

# su postgres
postgres# pg_dumpall > outputfile
postgres# exit

This time I referred to the correct package names for the PostgeSQL 8.1 server and client respectively.

# aptitude --show-versions --prompt install postgresql-8.1 postgresql-client-8.1
The following NEW packages will be installed:
  libpq4 [8.1.9-0etch1] postgresql-8.1 [8.1.9-0etch1]
  postgresql-client-8.1 [8.1.9-0etch1]
# aptitude update
# aptitude upgrade

The configuration in /etc/postgresql/8.1/main/postgresql.conf has SSL enabled by default (ssl = true, ssl_socket = true is now obsolete), and I only had to adapt pg_hba.conf by commenting out three lines (they may actually have been commented already) and adding one with the current IPv4 NAT address of my computer at home (actual address masked by placeholder CLIENT).

local   all   postgres              ident sameuser
# local all   all                   ident sameuser
# host  all   all md5
# host  all   all      ::1/128      md5
hostssl guava guava    CLIENT/32    md5

Next, I copied the saved versions of server.key, server.crt, and root.crt into /var/lib/var/lib/postgresql/8.1/main/ (after renaming three existing symbolic links by the same names into *.save), and set their owner and group to postgres. At this point, both versions are installed in parallel, and I can start either server, however the new one listens on port 5433 for the time being.

# /etc/init.d/postgresql-7.4 stop
# /etc/init.d/postgresql-8.1 start
# su postgres
postgres# psql --port 5433
postgres=# \\q

I tried the new client for restoring the data, and encountered a few error messages. From inspection of the SQL file I judged to ignore those.

postgres# /usr/lib/postgresql/8.1/bin/psql -p 5433 -d postgres -f outfile
psql:/home/guava/outfile:11: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:/home/guava/outfile:13: NOTICE:  SYSID can no longer be specified
psql:/home/guava/outfile:20: ERROR:  cannot delete from a view
HINT:  You need an unconditional ON DELETE DO INSTEAD rule.
psql:/home/guava/outfile:122: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create \\ 
implicit index "blog_pkey" for table "blog"
psql:/home/guava/outfile:131: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create \\
implicit index "tag_pkey" for table "tag"

I stopped the 8.1 server and switched it to the default port by setting port = 5432 in postgresql.conf, and then removed the PostgreSQL 7.4 and its redundant dependent packages.

# aptitude -purge postgresql-7.4 postgresql-client-7.4
The following packages will be REMOVED:
  postgresql-7.4{p} postgresql-client-7.4{p}
The following packages have unmet dependencies:
  postgresql: Depends: postgresql-7.4 but it is not installable
  postgresql-client: Depends: postgresql-client-7.4 but it is not installable
The following actions will resolve these dependencies:
Remove the following packages:

The 7.4 installation and its configuration files are now gone (find / -name 7.4), and I can restart the 8.1 server again. The JDBC client on my computer at home does not yet work, presumably because it does not do SSL client authentication against root.crt (was not supported in PostgreSQL 7.4). Local SSL loopback connectivity with psql -h localhost also does not work yet, presumably for related reasons. I’ll try to finish the SSL-related security update next, but at this time can already use the interactive terminal on a local Unix-domain socket connection.

# /etc/init.d/postgresql-8.1restart
# su postgres
postgres# psql
postgres=# \\q

2 Responses to “Upgrade from PostgreSQL 7.4 to 8.1”

  1. Pedro Says:

    Thanks, great tutorial. I was searching for documentation on how to upgrade postgreSQL 7.4 to 8.1.

  2. Anand Chowdary Says:

    Very usefull for me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: