How-To: Upgrading PostgreSQL to 8.1
From CPWiki
How to Update Postgres to 8.1
Contents |
Warning
First, do it by your risk and IF you know what your doing AND IF you do really need and AND IF your customers do not use postgres on this server yet.
DO NOT UPGRADE IT ON PRODUCTION SERVERS UNLESS YOU'RE SURE ABOUT DOING IT. THERE ARE NO WAY TO GO BACK!!!!
ALSO, USING POSTGRES WITH CPANEL ISN'T RECOMMENDED WITHOUT ANOTHER BACKUP SYSTEM: CPANEL CURRENTLY IS BUGGED AND DOES NOT MAKE BACKUPS OF POSTGRES 8.1 DATABASES!!!
Currently I'm on testing phase on a RHEL3.
Using this howto
# logged as root $ logged as postgres user
Backuping-up your databases
Let get a fresh postgres data backup:
# su - postgres $ pg_dumpall > /tmp/dbdo28.out $ exit
If an error like 'pg_dumpall: could not connect to database template1: FATAL: Password authentication failed for user "postgres"' appear, you'll need to do section 1.1 above, otherwise skip next section..
Downloading PostgreSQL 8.1
go to http://www.postgresql.org/download/ and download following rpm on some temporary place like /root/src:
- postgresql-8.1.x.rpm
- postgresql-libs-8.1.x.rpm
- postgresql-devel-8.1.x.rpm
- postgresql-python-8.1.x.rpm
- postgresql-server-8.1.x.rpm
Moving postgresql database and removing old install
- mv -f /var/lib/pgsql /root/pgsql.old
- rpm -e rh-postgresql rh-postgresql-devel rh-postgresql-python rh-postgresql-server
Do not remove rh-postgresql-libs. It is needed to courier-authlib compatibily
Installing new version
rpm -ivh postgresql-*
Restarting Postgres
Restart postgres with
# service postgresql restart
Restoring your backup
# su - postgres $ psql -f /tmp/dbdo28.out template1 $ exit
Changing Postgres config at cPanel
change pgsql password on cpanel "postgres config" option to something random and click on intall config
Moving old Postgres databases to a safer place
make sure you have an old copy
# mv /tmp/dbdo28.out /root
Restarting Postgres again
Restart postgres with
# service postgresql restart
Making daily postgres backups
Installing PostgreSQL 8.1 breake cPanel backup for now. So, we do need to use an alternative.
Getting postgres login:
su -l postgres
Put following content on /var/lib/pgsql/backup.sh
#!/bin/sh
DATA=`/bin/date +%F`
BKPFILE=/var/lib/pgsql/dbback-${DATA}.sql
pg_dumpall > ${BKPFILE}
umask 077
gzip ${BKPFILE}
Add exec privileges to backup script:
chmod 0700 /var/lib/pgsql/backup.sh
Add it to crontab with: crontab -e -u postgres
10 2 * * * /var/lib/pgsql/backup.sh
It will run every day at 2:10 AM.
Note that this backup solution is just a copy of all postgres databases.
We hope cPanel fix postgres in /script/pckgact backup
