knowledgebase/linux/miscapplications/postgresql notes 1 Installing Postgresql The following code will install postgresql on an rhel4 server (it would work with a tweak or two for WBEL3/RHEL3). {code} function installpostgresql() { apt-get update # needed for postgresql-contrib apt-get -y install libxslt apt-get -y remove postgresql VERSION=8.2.3 PV=1PGDG # change rhel-as-4 to rhel-es-3 for rhel3/whitebox3 URL=ftp://ftp5.us.postgresql.org/pub/PostgreSQL/binary/v$VERSION/linux/rpms/redhat/rhel-as-4 PACKAGES="postgresql-plpython postgresql-plperl postgresql-libs postgresql-contrib postgresql postgresql-server" for PACKAGE in $PACKAGES; do FILE=$PACKAGE-$VERSION-$PV.i686.rpm if [ -e "$FILE" ] ; then echo "Skipping $FILE, already have it" continue fi echo "Fetching $URL/$FILE" wget --no-verbose "$URL/$FILE" if [ $? -ne 0 ] ; then echo "Failed getting $URL/$FILE" return 1 fi done # need -i - i.e. keep the old one - on this one to avoid dependency issues with dovecot and php-pgsql rpm -i postgresql-libs-$VERSION*i686.rpm # the rest can just be -U - i.e. upgrade any other packages rpm -Uvfh postgresql-plpython-$VERSION-$PV.i686.rpm postgresql-plperl-$VERSION-$PV.i686.rpm postgresql-contrib-$VERSION-$PV.i686.rpm postgresql-$VERSION-$PV.i686.rpm postgresql-server-$VERSION-$PV.i686.rpm if [ $? -ne 0 ] ; then echo "Failed installing rpms" return 1 fi chkconfig --level 35 postgresql on /etc/init.d/postgresql start psql --version } installpostgresql {code} 1 Backups The following file will backup a given database to /var/log/. It will create a different file for each day of the week - the %u option in the date command. After that it will overwrite files. {code:none} cat /etc/cron.d/backupfsn.sh #!/bin/bash backup_dir="/var/log/" timeslot=`date +%u` i=dbnametobackup /usr/bin/pg_dump -U postgres $i | gzip > "$backup_dir/postgresql-$i-$timeslot-database.gz" {code} 1 Getting a List of Databases {code:none} psql -U postgres -q -c "\l" dbname | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} {code} Not sure how to bootstrap this. You need to provide a dbname, but what to use by default? I think dbname could be postgres by default? Not sure. 1 RHEL4, PostgreSQL 8.1 and Dovecot The default Dovecot is built against the older PostgreSQL 7.4.x RPMs, and needs to be removed to install 8.1. If you still need Dovecot, use this RPM which is compiled against the PostgreSQL 8.1.x -devel packages. {code:none}rpm -Uvh "http://downloads.rimuhosting.com/dovecot-0.99.11-4-pg81.EL4.i386.rpm"{code} 2007-03-20 20:50:12.0 2005-12-25 21:50:13.0