Installing PostGres, PostGIS, pgRouting in Linux
April 3rd, 2007
A. Installing PostGres
A.1. Download and install the source: postgresql-8.2.3.tar.gz
[root@rupert-linux routing]tar -xvzf postgresql-8.1.2.tar.gz [root@rupert-linux routing]LDFLAGS=-lstdc++ [root@rupert-linux routing]./configure --prefix=/usr/local/pgsql --with-perl --with-python --with-krb5 --with-openssl [root@rupert-linux routing]make [root@rupert-linux routing]make install A.2. Post Compile Operations: [root@rupert-linux routing]/usr/sbin/adduser postgres [root@rupert-linux routing]mkdir /usr/local/pgsql/data [root@rupert-linux routing]chown postgres /usr/local/pgsql/data/ [root@rupert-linux routing]su - postgres [root@rupert-linux routing]/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/
Note: If youre default locale is not EN, say chinese, then you would get:
The files belonging to this database system will be owned by user “postgres”.
This user must also own the server process.The database cluster will be initialized with locale zh_CN.GB18030.
initdb: could not find suitable encoding for locale “zh_CN.GB18030″
A.3. Edit environment variables. Add the ff to your .bash_profile
[root@rupert-linux routing] vi /root/.bash_profile PATH=$PATH:$HOME/bin:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:$JAVA_HOME/bin:/usr/local/pgsql/bin export PGLIB=/usr/local/pgsql/lib export PGDATA=/usr/local/pgsql/data export PGPORT=5432 export PGOPTS="-i"
A.4. Start PostGres Automatically on Startup. Postgres Startup Script
[root@rupert-linux routing]touch /etc/init.d/postgresql [root@rupert-linux routing] vi /etc/init.d/postgresql
PostGres Startup Script
#!/bin/sh # postgresql This is the init script for starting up the PostgreSQL # server # chkconfig: - 85 15 # description: Starts and stops the PostgreSQL backend daemon that handles all database requests. # processname: postmaster # pidfile: /usr/local/pgsql/data/postmaster.pid # # Source function library. . /etc/rc.d/init.d/functions # Get config. . /etc/sysconfig/network # Check that networking is up. # Pretty much need it for postmaster. [ ${NETWORKING} = "no" ] && exit 0 [ -f /usr/local/pgsql/bin/postmaster ] || exit 0 # See how we were called. case "$1" in start) pid=`pidof postmaster` if [ $pid ] then echo "Postmaster already running." else echo -n "Starting postgresql service: " su -l postgres -c '/usr/local/pgsql/bin/pg_ctl -o -i -D /usr/local/pgsql/data/ -l /usr/local/pgsql/data/logfile start' sleep 1 echo exit fi ;; stop) echo -n "Stopping postgresql service: " killproc postmaster sleep 2 rm -f /usr/local/pgsql/data/postmaster.pid echo ;; restart) $0 stop $0 start ;; *) echo "Usage: postgresql {start|stop|restart}" exit 1 esac exit 0
A.5. PostGres Testing
[root@rupert-linux routing]# su - postgres [postgres@rupert-linux ~]$ /usr/local/pgsql/bin/createdb test [postgres@rupert-linux ~]$ /usr/local/pgsql/bin/psql test
B. Installing PostGIS.
B.1. Download Geos
[root@rupert-linux /]# bzip2 -d geos-3.0.0rc4.tar.bz2 [root@rupert-linux /]# tar -xvf geos-3.0.0rc4.tar [root@rupert-linux /]# ./configure [root@rupert-linux /]# make [root@rupert-linux /]# make install
B.2. Download PostGIS.
[root@rupert-linux /]# cp postgis-1.2.1.tar.gz /home/installers/postgresql-8.2.3/contrib/ [root@rupert-linux /]# tar -zxvf postgis-1.2.1.tar.gz [root@rupert-linux /]# cd /home/installers/postgresql-8.2.3/contrib/postgis-1.2.1/ [root@rupert-linux /]# ./configure --with-pgsql=/usr/local/pgsql/bin/pg_config [root@rupert-linux /]# make [root@rupert-linux /]# make install
B.3 Reload ldconfig
[root@rupert-linux /]# vi /etc/ld.so.conf /usr/local/include /usr/local/lib [root@rupert-linux /]# ldconfig
B.4. Testing PostGIS
$ su - postgres $ createlang plpgsql test $ /usr/local/pgsql/bin/psql -d test -f /usr/local/pgsql/share/lwpostgis.sql BEGIN psql:/usr/local/pgsql/share/postgresql/contrib/lwpostgis.sql:39 NOTICE: type "histogram2d" is not yet defined DETAIL: Creating a shell type definition. . . . COMMIT $ /usr/local/pgsql/bin/psql -d test -f /usr/local/pgsql/share/spatial_ref_sys.sql BEGIN INSERT 0 1 INSERT 0 1 . . . VACUUM
C. Installing pgRouting.
C.1 Install Boost Library if you don’t have it. Download boost
[root@rupert-linux /]# tar -jxvf boost_1_33_1.tar.bz2 [root@rupert-linux /]# ./configure [root@rupert-linux /]# make [root@rupert-linux /]# make install [root@rupert-linux /]# cd /usr/local/include/boost1.3.3 [root@rupert-linux /]# cp -Rf boost /usr/local/include/
C.2 Download and install CGAL.
[root@rupert-linux installers]# tar zxvf CGAL-3.2.1.tar.gz [root@rupert-linux installers]# cd CGAL-3.2.1 [root@rupert-linux CGAL-3.2.1]# ./install_cgal --prefix=/usr/local/cgal --with-boost=n --without-autofind -ni /usr/bin/g++ Copy libCGAL.so to the pgsql/lib directory... [root@rupert-linux CGAL-3.2.1]# cp -Rf /usr/local/cgal/lib/i686_Linux-2.6_g++-3.4.3/libCGAL.so /usr/local/pgsql/lib/
C.3 Download and install GAUL.
[root@rupert-linux installers]# tar zxvf gaul-devel-0.1849.tar.gz [root@rupert-linux gaul-devel-0.1849-0]# ./configure -prefix=/usr/local/gaul [root@rupert-linux gaul-devel-0.1849-0]# make [root@rupert-linux gaul-devel-0.1849-0]# make install [root@rupert-linux gaul-devel-0.1849-0]# cd /usr/local/gaul (Note: Copy gaul includes and libraries to standard directories...) [root@rupert-linux gaul]# cp -Rf include/* /usr/include/ [root@rupert-linux gaul]# cp -Rf lib/* /usr/local/lib/
C.4 Download and install pgRouting.
[root@rupert-linux installers]# tar -jxvf pgRouting-0.9.9.tgz [root@rupert-linux routing]# vi configure ########################################## Edit line 3036... CGAL_MKF='find /usr/local/cgal/include/CGAL.....' ########################################## [root@rupert-linux routing]# ./configure --with-cgal=/usr/local/cgal --with-gaul=/usr/local/gaul --with boost=/usr/local --pg-sql=/usr/local/pgsql [root@rupert-linux routing]# make [root@rupert-linux routing]# make install
If you receive the ff error in windows:
rupert@rupert-winxp /c/Program Files/PostgreSQL/8.2/data
$ psql -U postgres
Password for user postgres:
psql: FATAL: password authentication failed for user “postgres”
1. Make sure you have a local user “postgres”. The postgres service should run as the same “postgres” user.
2. You need to change the authentication type from md5 to trust. Then restart postgres from services.msc
# IPv4 local connections:
#host all all 127.0.0.1/32 md5
host all all 127.0.0.1/32 trust
# IPv6 local connections:
#host all all ::1/128 md5
#host all all 192.168.1.0 255.255.255.0 md5
host all all 192.168.1.0 255.255.255.0 trust
3. No need to specify the password on specifing -U postgres
rupert@rupert-winxp /c/Program Files/PostgreSQL/8.2/data
$ psql -U postgres
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
Warning: Console code page (437) differs from Windows code page (936)
8-bit characters may not work correctly. See psql reference
page “Notes for Windows users” for details.
When you want to connect to postgres remotely, make sure you allow it (5432) from your firewall (/etc/sysconfig/iptables). It appears that I can telnet 222.xxx.xxx.xxx 5432 where my postgres run, but pgadmin fails to connect. So I have to add a line in my /usr/local/pgsql/data/pg_hba.conf:
host all all 219.xxx.xxx.0 255.255.255.0 md5
From PG Reference
http://www.postgresql.org/docs/8.2/static/auth-pg-hba-conf.html
You can comment out this line in /etc/postgresql/8.1/main/postgresql.conf to enable postgres to listen to 192.168.1.211
listen_addresses = ‘*’ # what IP address(es) to listen on;
DEBIAN: Allowing postgres to listen to IP
rupert-debian:/etc/postgresql/8.2/main# pwd
/etc/postgresql/8.2/main
vi postgresql.conf
#listen_addresses = ‘localhost’ # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to ‘localhost’, ‘*’ = all
listen_addresses = ‘*’
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)