An Almost Idiot's Guide to PostgreSQL YUM

First we'd like to thank Devrim of Command Prompt for working hard on making this new YUM repository available. In this article we will go over using the new PostgreSQL YUM repository for Redhat Fedora, Enterprise Linux and CentOS distros that is available at http://yum.pgsqlrpms.org/.
UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use this one instead since it seems more up to date than the other. http://yum.pgrpms.org/.

We are gearing the content of this article to the described user profile

NOTE: We have a newer article on Installing PostgreSQL 8.4 with Yum and an even newer one for PostgreSQL 9.0 please refer to Installing PostgreSQL 9.0 via Yum. This old article is for PostgreSQL 8.3.

Steps before getting started

  1. SSH into your Linux box. For windows users, you can use Putty which we covered in PuTTY for SSH Tunneling to PostgreSQL Server
  2. Log in as root
  3. Determine which version of Linux you are running with the following commands:
    uname –a
    If this returns something with el4 (then you are running Enterprise Linux 4), el5 (Enterprise Linux 5), centos (ELsmp). Also pay attention to the bit 32-bit or 64-bit. 64bit will generally have an x64 and 32-bit will have i386 and/or i686 (for intel based).
    vi /etc/redhat-release
    also gives details of the version you are running

Backing up Old Version

If you are running a prior version of PostgreSQL, most likely it was installed in the /usr/local/pgsql/ folder. The first thing you want to do if you care about your data is to back it up with commands similar to the below. 

  1. 
 mkdir dbbackup
 cd dbbackup
 /usr/local/pgsql/bin/pg_dumpall –U postgres postgresqlserverdbs.sql
    You may also want to download the backup if its really important to you and in case you screw up the server really badly.
  2. Now shut down the postgresql service 
    
su postgres
/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data
  3. For extra good measure rename the old folder with and backup the postgresql.conf and pga_hba.conf to a safe location
    mv /usr/local/pgsql /usr/local/pgsqlold

      4. Installing new Version

      1. Login as root
      2. Follow the instructions at http://yum.pgsqlrpms.org/howtoyum.php to prevent your YUM update from getting postgresql from other sources
      3. Select the appropriate repository config file for your OS and choose 8.3 from here and navigating thru: http://yum.pgsqlrpms.org/reporpms/repoview/letter_p.group.html
      4. Note the install file - should look something like http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-redhat-8.3-4.noarch.rpm
      5. Do a wget of the appropriate one: e.g.
        wget http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-redhat-8.3-4.noarch.rpm
      6. Next install the rpm config file with:
        rpm -ivh pgdg-redhat-8.3-2.noarch.rpm
      7. Install the things that you want. For 8.3.5+ you need to add the item in red to also install the server. These are the ones we tend to install 
        yum install postgresql
yum install postgresql-devel
yum install postgresql-server 
yum install postgis
yum install pgadmin3
yum install postgresql-contrib
      8. We tend to like PostgreSQL in the /usr/local/pgsql/data folder since thats where we are used to finding it. So we init there. The default location of new install is different for each system. On EL its /var/lib/pgsql/data so you may want to init there although why its there feels so counter intuitive. Consider the below an example use case. 
        
	mkdir /usr/local/pgsql
	mkdir /usr/local/pgsql/data
	chown postgres /usr/local/pgsql/data
	su postgres
	initdb –D /usr/local/pgsql/data
      9. cd /usr/local/pgsql/data
        and edit the postgresql.conf and pg_hba.conf files to your hearts content. It is fairly safe to copy over the pg_hba.conf file from your backup (using e.g cp /usr/local/pgsqlold/data/pg_hba.conf /usr/local/pgsql/data
        ) but for the postgresql.conf file, we would work with the new and cut in changes from the old since new things have been added between 8.2 and 8.3

      Having PostgreSQL start as a service

      The PostgreSQL install puts in a postgresql service script that you can tweak for your specific need. On RHEL 5, its located in /etc/rc.d/init.d. For other installs, it varies
      1. emacs /etc/rc.d/init.d/postgresql
        Note: if you don't have emacs, you can use vi, but I tend to prefer emacs for simple edits.
      2. Replace all references of /var/lib/pgsql/data with whereever you initD your database cluster
      3. In emacs Ctrlx+Ctrlc to save
      4. service postgresql start
        to test to make sure the service can start
      5. Next to make sure it starts automatically on boot - do the following command 
        		chkconfig --list (to see list of services)
		chkconfig postgresql on
        the chkconfig on thing automatically copies symlinks to those rc.1 , rc.n locations that linux looks for in bootup. If you don't have chkconfig on your box for some reason, you have to manually copy those symlinks to the right locations - a pain.

      Installing PgAdmin pack

      The PgAdmin pack comes with PgAdmin3. This comes in handy if you use PgAdmin3 a lot as it gives you stats activity, and allows you to change pg_hba.conf and postgresql.conf files directly from PgAdmin3 even if accessing from a different computer

      psql -U postgres -d postgres -f /usr/share/pgsql/contrib/adminpack.sql