HOW-TO Upgrade PostGres on FreeBSD

Some steps to take while upgrading your PostGres DataBase

Sun Mar 4 23:47:59 PST 2007 This specific example was an 8.0 -> 8.2 upgrade on FreebBSD 5.4 Notes: the dump from 8.0 was in SQL_ASCII while the default import in 8.2 is UTF8, this requires you to alter your ENCODING when reloading your dump. # Get an idea of all the packages you need to upgrade. # Basically, anything using PostGres will need to be rebuilt against # the new client libraries you are about to install with the new server pkg_info | grep postgres # Get your dumps! This step is important. su -m pgsql '/usr/local/bin/pg_dumpall -c > fulldump.sql' # stop postgreql /usr/local/etc/rc.d/postgresql stop (or whatever the name of the rc script) # first update the client lib pkg_delete postgresql-client-\* cd /usr/ports/databases/postgresql82-client/ && make && make install clean # backup your old server install for rollback ability! (run pkg_info to see your old version) cd /tmp && pkg_create -b postgresql-server-8.0.0 # next the server (run pkg_info to see your old version) pkg_delete postgresql-server-\* cd /usr/ports/databases/postgresql82-server/ && make && make install clean # next, upgrade the perl module pkg_delete postgresql-plperl-\* cd /usr/ports/databases/p5-postgresql-plperl/ && make && make install clean # reinit the DB mv /usr/local/pgsql/data /usr/local/pgsql/data-old su pgsql -c "initdb -D /usr/local/pgsql/data" # start the PostGres /usr/local/etc/rc.d/postgresql.sh start (or jsut postgresql depending on your system) # switching to UTF8? run it through recode. pkg_add -r recode cat fulldump.sql | recode iso-8859-1..u8 > fulldump.sql.recoded perl -pi -e 's/SQL_ASCII/UTF8/' fulldump.sql.recoded # OR Maybe, you just want LATIN1... # you can skip the recode and just adjust the encoding perl -pi -e 's/SQL_ASCII/ISO-8859-1/' fulldump.sql # load it up cat fulldump.sql.recoded | psql -U pgsql postgres # OR if not doing UTF8... which you shouldn't unless # you are doing non Latin based languages (eg Chinese) cat fulldump.sql | psql -U pgsql postgre # make sure encodings worked... psql -U pgsql --list # neat, huh? # copy your old access file cat /usr/local/pgsql/data-old/pg_hba.conf > /usr/local/pgsql/data/pg_hba.conf # move over you old config settings.... do this manually... here are your old ones: grep -v # /usr/local/pgsql/data-old/postgresql.conf | grep -v ^$ # Now edit the new config file... using what you want from the old one. vi usr/local/pgsql/data/postgresql.conf # you may just lazily cp your old postgresql.conf file over... it may work... # this is discouraged, because all the commneted out defaults may change from # version to version. ### optional, if you have a dev box... ### # ON the backup server, migrate the data and rename the database to dev. psql -U pgsql postgres ALTER DATABASE bb RENAME TO devbb; # on the web server upgrade all the postgresql client libs # (here is another way, different than above, for you to try) [ -x /usr/local/sbin/portupgrade ] || pkg_add -r portupgrade portupgrade postgresql-client-\* portupgrade postgresql-plperl-\* portupgrade php5-\* portupgrade pear\* portupgrade p5-DBD-Pg\* p5-DBI\*