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.
Upgrading from PostgreSQL 8.* to PostgreSQL 9.0
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.
Installing PostgreSQL 9.0 from Yum repository
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.
- Figure out which OS you are on. Note the PGDG repository only works with CentOS and Redhat 4 and above, or Fedora.
First run
uname -a
Mine happens to return: i686 i386 GNU/Linux so I know its a 32-bit.
vi /etc/redhat-release
Mine returns:
CentOS release 5.5 (Final)
- Download the right files - from http://www.pgrpms.org/reporpms/repoview/letter_p.group.html . In our case we are running CentOS release 5.5. 32-bit so we use the CentOS repo
mkdir /pgbak
cd /pgbak
wget http://www.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
- Install the rpms
rpm -ivh pgdg-centos-9.0-2.noarch.rpm
yum list | grep postgresql
You should see a bunch of entries and the ones we want are marked with pgdg90.
If 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-devel
- You should get a prompt something like:
Dependencies 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.
Creating data cluster and starting the service
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.
Just get me there fast
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 need to configure things the way you want to configure things
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 exit
only 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.conf
or
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.
Turning off firewall or enabling PostgreSQL access
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
Also running 9.1 beta
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.
PostGIS in Action
About the Authors
Consulting
Tracked: Apr 18, 11:27
Tracked: Apr 18, 11:40
Tracked: Apr 18, 11:43
Tracked: Apr 21, 01:37
Tracked: May 27, 08:41
Tracked: Jun 21, 04:16
Tracked: Sep 13, 04:49
Tracked: Apr 03, 14:52