View source

How to Update Postgres to 8.1 


== 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.'''

<div style="color: red; font-size: 20;font-weight:bold">
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!!!

</div>

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


=== Resources ===
[http://forums.cpanel.net/showthread.php?p=229798 cPanel Forum discussion]

[[Category:How-To]]
[[Category:Author:DarkElder]]