In this exercise, we'll go thru installing PostgreSQL 9.0 on a Cent OS 5.5 32-bit box. This I'm testing on a GoGrid Cloud server so I can do parallel benchmarks between my windows GoGrid and Linux GoGrid server.
If you are upgrading from a PostgreSQL 8.4 to PostgreSQL 9.0, please refer to Devrim's article: Upgrading from 8.4 to 9.0 on Fedora / Red Hat / CentOS using RPMs.
For the rest of this article, we'll go over configuring your yum to use the PostgreSQL PGDG Yum repository managed by Devrim Gunduz, which has the latest and greatest of 9.0 as well as the 9.1 latest development release. We'll also demonstrate how to have two instances of PostgreSQL running so you can experiment with the new features of PostgreSQL 9.1 while reminiscing about the soon to be old-hat features of PostgreSQL 9.0.
The list of latest PostgreSQL versions and repos for Fedora 7-14, Red Hat 4-6, and CentOS 4-6 are located at http://www.pgrpms.org/reporpms/repoview/letter_p.group.html.
First run
uname -a i686 i386 GNU/Linux so I know its a 32-bit. vi /etc/redhat-release CentOS release 5.5 (Final)
mkdir /pgbak
cd /pgbak
wget http://www.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpmrpm -ivh pgdg-centos-9.0-2.noarch.rpmyum list | grep postgresqlIf you see postgresql from other repositories besides PGDG, then you may want to exclude them to minimize the risk of installing the wrong thing. This is optional since these
days the PostgreSQL package names now include the version number as part of the name so less likely for you to screw up:
I use emacs mostly for editing because I can never remember how to navigate with vim.
* As root, cd /etc/yum.repos.d
* Edit distro's .repo file:
o On Fedora, edit fedora.repo and fedora-updates.repo, [fedora] sections
o On CentOS, edit CentOS-Base.repo, [base] and [updates] sections.
o On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section.
+ Add to the bottom of the section:
exclude=postgresql*
emacs CentOS-Base.repo
Then once you've made the edits Ctrl-x Ctrl-s, Ctrl-x Ctrl-c.
yum list | grep postgresql should give you entries that look something like:
postgresql90.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-contrib.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-debuginfo.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-devel.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-docs.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-jdbc.i386 9.0.801-1PGDG.rhel5 pgdg90 postgresql90-jdbc-debuginfo.i386 9.0.801-1PGDG.rhel5 pgdg90 postgresql90-libs.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-odbc.i386 09.00.0200-1PGDG.rhel5 pgdg90 postgresql90-odbc-debuginfo.i386 09.00.0200-1PGDG.rhel5 pgdg90 postgresql90-plperl.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-plpython.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-pltcl.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-python.i386 4.0-2PGDG.rhel5 pgdg90 postgresql90-python-debuginfo.i386 4.0-2PGDG.rhel5 pgdg90 postgresql90-server.i386 9.0.3-1PGDG.rhel5 pgdg90 postgresql90-tcl.i386 1.8.0-2.rhel5 pgdg90 postgresql90-tcl-debuginfo.i386 1.8.0-2.rhel5 pgdg90
I usually install the below. The devel is optional but needed if you want to compile PostGIS or some other add on later.
yum install postgresql90 postgresql90-server postgresql90-libs postgresql90-contrib postgresql90-develDependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: postgresql90 i386 9.0.3-1PGDG.rhel5 pgdg90 1.3 M postgresql90-contrib i386 9.0.3-1PGDG.rhel5 pgdg90 443 k postgresql90-devel i386 9.0.3-1PGDG.rhel5 pgdg90 1.5 M postgresql90-libs i386 9.0.3-1PGDG.rhel5 pgdg90 220 k postgresql90-server i386 9.0.3-1PGDG.rhel5 pgdg90 4.8 M Installing for dependencies: uuid i386 1.5.1-4.rhel5 pgdg90 53 k Transaction Summary ================================================================================ Install 6 Package(s) Upgrade 0 Package(s) Total download size: 8.3 M Is this ok [y/N]:Type: y
It should then download the packaged and install them.
With the new Yum setup each version of postgresql goes in its own separate folder, so you can easily have parallel installs of different versions of PostgreSQL, with as much ease as you could always do on Windows :). Devrim has the details of these changes in What-is-new-in-PostgreSQL-9.0-RPMs.html.
I'll reiterate the key elements and consequences of these changes you need to watch out for.
If you want to take all the default locations of ports and data cluster and want the service to start up on reboot, then simply do this:
su root service postgresql-9.0 initdb service postgresql-9.0 start chkconfig --list #(to see list of services) chkconfig postgresql-9.0 on
The data cluster is created by default in /var/lib/pgsql/9.0/data and the contribs folder is in /usr/pgsql-9.0/share/contrib/
To connect to your postgres server simply do this:
su postgres psql -p 5432
To install the adminpack.sql useful for viewing logs and editing postgresql.conf from the comfort of PgAdmin, don't forget to install the admin pack
su postgres psql -p 5432 -f /usr/pgsql-9.0/share/contrib/adminpack.sql
If you plan to run multiple versions of PostgreSQL e.g. stable 9.0 and devl 9.1, on the same server (so they use different ports), or you just need to be in control of all your options, you'll want to tweak your services scripts a bit. The best way to do that is to edit/create a file in /etc/sysconfig/pgsql/postgresql-9.0 with your preferred changes:
Note that # are remarks to you so don't type them in
su root emacs /etc/sysconfig/pgsql/postgresql-9.0 #if the file doesn't exist -- it will be blank which is okay PGPORT=5433 PGDATA=/pgdata90 #ctrl-x ctrl-s to save #ctrl-x ctrol-c to exitonly do these if you set the data cluseter in the sysconfig
service postgresql-9.0 stop #not necessary if you didn't init and start service postgresql-9.0 initdb service postgresql-9.0 start chkconfig postgresql-9.0 on cd /pgdata90 ls
You should see data cluster files. If you had accidentally followed the initial default steps and didn't mean to, you can simply do a
rm -rf /var/lib/pgsql/9.0/data
If you get a:
FATAL: could not open relation mapping file "global/pg_filenode.map": Permission denied
When you attempt to connect to psql. Most likely the problem is caused by a locked file from left over. You could go thru the effort of shutting down things looking, for what went wrong, but you are an Almost idiot with little patience for these things.
just do a:
reboot
and all should be fine.
On startup do a:
service postgresql-9.0 status You should see a pid runnig ... message if all is good. If it says stopped, then check your /var/lib/pgsql/9.0/data/pg_log last log file which usually lists what went wrong. Often times it's usually caused by a typo you introduced in pg_hba.conf or postgresql.conf .
su postgres psql -p 5433 #or whatever port you chose CREATE role regina LOGIN PASSWORD 'whateveryouwant' SUPERUSER;
At the psql prompt: type the query below to verify the location of your cluster:
SELECT setting FROM pg_settings WHERE name = 'config_file';which should return an answer of (if you kept the defaults):
setting ----------------------------------------- /var/lib/pgsql/9.0/data/postgresql.confor The location of where you setup your cluster.
then do a \q to exit the psql console
After that:
cd /var/lib/pgsql/9.0/data (or the location of your data cluster) emacs postgresql.conf
#make changes to memory, enable etc.
change #listen_addresses='localhost' to listen_addresses='*' if you want postgresql to run on all ips. You can set to a specific if you have multiple ips too.
if you need to change ports and data cluster -- don't do it in postgresql.conf -- remember in /etc/sysconfig/pgsql/postgresql-9.0
emacs pg_hba.conf #make changes to access .e.g add a line below if you want to be able to access postgresql from external. host all all 0.0.0.0/0 md5 ctrl-x ctrl-s to save ctrl-x ctrl-c to exit su root service postgresql-9.0 restart
Whenever you change the pg_hba.conf file, you need to do a restart or a:
service postgresql-9.0 reload
for the changes to take effect.
If after all those changes, you still can't access your PostgreSQL server from pgAdmin external, verify if you have firewall running. The GoGrid Image I grabbed had iptables on.
su root service iptables status
Just for perspective, my rule table looked like this before. As you can see no rule to enable postgres access.
Chain INPUT (policy ACCEPT) num target prot opt source destination 1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0 Chain FORWARD (policy ACCEPT) num target prot opt source destination 1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0 Chain OUTPUT (policy ACCEPT) num target prot opt source destination Chain RH-Firewall-1-INPUT (2 references) num target prot opt source destination 1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255 3 ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0 4 ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0 5 ACCEPT udp -- 0.0.0.0/0 224.0.0.251 udp dpt:5353 6 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED 7 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 8 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:80 9 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:443 10 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
service iptables stop
Verify you can connect after turning off. Okay now turn back on. The below example will enable tcp ports 5432-5434 (which we will use for postgres services) and insert the rule in the chain RH-Firewall-1-INPUT at row 9. Note my 10th rule is to reject all, so want all my access rules to be before that.
service iptables start iptables -I RH-Firewall-1-INPUT 9 -p tcp --dport 5432:5434 -j ACCEPT service iptables save service iptables restart
After I was done, it looked like this:
Chain INPUT (policy ACCEPT) num target prot opt source destination 1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0 Chain FORWARD (policy ACCEPT) num target prot opt source destination 1 RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0 Chain OUTPUT (policy ACCEPT) num target prot opt source destination Chain RH-Firewall-1-INPUT (2 references) num target prot opt source destination 1 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 2 ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255 3 ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0 4 ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0 5 ACCEPT udp -- 0.0.0.0/0 224.0.0.251 udp dpt:5353 6 ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED 7 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22 8 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:80 9 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpts:5432:5434 10 ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:443 11 REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
If you want to play with 9.1 also, repeat the same steps as above except with 9.1 and the http://www.pgrpms.org/reporpms/9.1/pgdg-[your os here]-9.1-1.noarch.rpm. Note that if you plan to run both, you will need to use a sysconfig .. to prevent your two services from trying to run on the same port.