The results and ramblings of research

phewww!

Installing Postgres9.0 and PostGIS on CentOS

with 10 comments

So, I work with spatial (GIS) data quite often. Since quite often I have to rebuild databases I am going to write down the steps I take to install PostGIS + Postgresql. The first step on CentOS is to configure the repositories:

  1. Use http://www.pgrpms.org/ for postgresql. As root, go to /etc/yum.repo.d/ and create a file called pgrpms9.repo and add the following lines:
[pgdg90]
name=PostgreSQL 9.0 $releasever - $basearch
baseurl=http://yum.pgrpms.org/9.0/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
[pgdg90-source]
name=PostgreSQL 9.0 $releasever - $basearch - Source
failovermethod=priority
baseurl=http://yum.pgrpms.org/srpms/9.0/redhat/rhel-$releasever-$basearch
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
  1. Add EPEL repository by doing the following from shell
su -c 'rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm'
  1. Update CentOS base repository to remove postgresql being downloaded by adding “exclude=postgresql*” to the end of the repo Centos-Base.repo
  1. Install PostGRES server, PostGIS, GEOS and PROJ on x86_64 using yum from shell prompt as given the command below:
    $ yum install postgresql90-contrib.x86_64 postgresql90-docs.x86_64  postgresql90-devel.x86_64 postgis90 postgis90-utils postgis90-docs postgresql90-server.x86_64 proj-devel.x86_64 geos-devel.x86_64
  2. Install PostGRES server, PostGIS, GEOS and PROJ on i386 linux using yum from shell prompt as given the command below:
$ yum install postgresql90-contrib postgresql90-docs  postgresql90-devel postgis90 postgis90-utils postgis90-docs postgresql90-server proj-devel geos-devel
  1. Initialize and Start Postgres Database server
$ /sbin/service postgresql-9.0 initdb
$ /sbin/service postgresql-9.0 start
  1. Create PostGIS template database
$ su - postgres
$ createdb -E UTF8 -T template0 postgis_template
$ createlang -d postgis_template plpgsql
$ psql -d postgis_template -f  /usr/pgsql-9.0/share/contrib/postgis-1.5/postgis.sql
$ psql -d postgis_template -f  /usr/pgsql-9.0/share/contrib/postgis-1.5/spatial_ref_sys.sql
$ psql -d postgis_template -c "GRANT ALL ON geometry_columns TO PUBLIC;"
$ psql -d postgis_template -c "GRANT ALL ON geography_columns TO PUBLIC;"
$ psql -d postgis_template -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
$ psql -d postgis_template -c "VACUUM FULL;"
$ psql -d postgis_template -c "VACUUM FREEZE;"
$ psql -d postgres -c "UPDATE pg_database SET datistemplate='true' WHERE datname='postgis_template';"
$ psql -d postgres -c "UPDATE pg_database SET datallowconn='false' WHERE datname='postgis_template';"
  1. Create PostGRES database using postgis_template template database
$ su - postgres
$ psql -Upostgres
$ create role owner with createdb createrole login;
$ create database db with owner=owner template=postgis_template;

AND you are set to go.

Advertisements

Written by anujjaiswal

June 14, 2011 at 8:16 pm

Posted in Database, Linux, PostGIS, PostGRES

Tagged with , ,

10 Responses

Subscribe to comments with RSS.

  1. thank you very much, clear and very useful! 🙂

    stefan0n

    June 23, 2011 at 3:55 pm

  2. [root@srvccrj ~]# exclude=postgresql*
    [root@srvccrj ~]# yum install postgresql90-contrib.x86_64 postgresql90-docs.x86_64 postgresql90-devel.x86_64 postgis90 postgis90-utils postgis90-docs postgresql90-server.x86_64 proj-devel.x86_64
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
    * base: hpc.arc.georgetown.edu
    * epel: mirror.symnds.com
    * extras: mirror.symnds.com
    * updates: centos.mirror.nac.net
    epel | 3.7 kB 00:00
    epel/primary_db | 3.1 MB 01:29
    pgdg90 | 2.8 kB 00:00
    pgdg90/primary_db | 54 kB 00:00
    pgdg90-source | 2.6 kB 00:00
    pgdg90-source/primary_db | 17 kB 00:00
    http://yum.pgrpms.org/srpms/9.0/redhat/rhel-5-i386/repodata/primary.sqlite.bz2: [Errno -3] Error performing checksum
    Trying other mirror.
    pgdg90-source/primary_db | 17 kB 00:00
    http://yum.pgrpms.org/srpms/9.0/redhat/rhel-5-i386/repodata/primary.sqlite.bz2: [Errno -3] Error performing checksum
    Trying other mirror.
    Error: failure: repodata/primary.sqlite.bz2 from pgdg90-source: [Errno 256] No more mirrors to try.
    [root@srvccrj ~]#

    Error, Help!! Please..

    David

    July 16, 2011 at 12:33 am

    • Hi David

      Please follow the steps below:
      $ su
      $ cd /etc/yum.repo.d/
      $ vi CentOS-Base.repo
      under the repository [base] which looks like
      [base]
      name=CentOS-$releasever – Base
      mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
      #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
      gpgcheck=1
      gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

      add the line exclude=postgresql* so that it will look like

      [base]
      name=CentOS-$releasever – Base
      mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
      #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
      gpgcheck=1
      exclude=postgresql*
      gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

      Then save the file. Then open the pgrpms9.repo (or the repo file you created for postgresql90), and go the repository settings for [ source]which looks like

      [pgdg90-source]
      name=PostgreSQL 9.0 $releasever – $basearch – Source
      failovermethod=priority
      baseurl=http://yum.pgrpms.org/srpms/9.0/redhat/rhel-$releasever-$basearch
      gpgcheck=0
      gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

      add the line “enabled=0” after the failovermethod line so that it will look like
      [pgdg90-source]
      name=PostgreSQL 9.0 $releasever – $basearch – Source
      failovermethod=priority
      enabled=0
      baseurl=http://yum.pgrpms.org/srpms/9.0/redhat/rhel-$releasever-$basearch
      gpgcheck=0
      gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

      Then save that file. Then on shell do

      $ yum makecache
      $ yum install yum install postgresql90-contrib.x86_64 postgresql90-docs.x86_64 postgresql90-devel.x86_64 postgis90 postgis90-utils postgis90-docs postgresql90-server.x86_64 proj-devel.x86_64 geos-devel.x86_64

      Let me know if you still have errors.

      AJ

      anujjaiswal

      July 16, 2011 at 12:58 am

      • [root@srvccrj ~]# yum makecache
        Loaded plugins: fastestmirror
        Repository pgdg90 is listed more than once in the configuration
        Repository pgdg90-source is listed more than once in the configuration
        Loading mirror speeds from cached hostfile
        * base: centos.mirror.lstn.net
        * epel: mirror.symnds.com
        * extras: mirror.fdcservers.net
        * updates: mirror.rackspace.com
        base | 1.1 kB 00:00
        base/other | 9.6 MB 04:05
        epel | 3.7 kB 00:00
        epel/filelists_db | 4.6 MB 01:40
        epel/updateinfo | 373 kB 00:08
        epel/other_db | 1.6 MB 00:36
        extras | 2.1 kB 00:00
        extras/other_db | 437 kB 00:13
        pgdg90 | 2.8 kB 00:00
        pgdg90/filelists_db | 82 kB 00:02
        pgdg90/other_db | 22 kB 00:00
        http://yum.pgrpms.org/9.0/redhat/rhel-%24releasever-%24basearch/repodata/repomd.xml: [Errno 14] HTTP Error 404: Not Found
        Trying other mirror.
        Error: Cannot retrieve repository metadata (repomd.xml) for repository: pgrpms9.repo. Please verify its path and try again
        [root@srvccrj ~]#
        ——————————–
        Another mistake helps please
        otro error ayuda por favor

        David

        July 16, 2011 at 1:59 am

      • Hi David,

        Did you make two entries of [pgdg90] and [pgdg90-source]. You should have only one entire of each. Your pgrpms9.repo file should have the following (each entry for repo — ie stuff below [] only once]
        [pgdg90]
        name=PostgreSQL 9.0 $releasever – $basearch
        baseurl=http://yum.pgrpms.org/9.0/redhat/rhel-$releasever-$basearch
        enabled=1
        gpgcheck=0
        gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

        [pgdg90-source]
        name=PostgreSQL 9.0 $releasever – $basearch – Source
        failovermethod=priority
        enabled=0
        baseurl=http://yum.pgrpms.org/srpms/9.0/redhat/rhel-$releasever-$basearch
        gpgcheck=0
        gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

        AJ

        anujjaiswal

        July 16, 2011 at 2:42 am

  3. Hello anujjaiswal.

    [root@srvccrj ~]# /sbin/service postgresql-9.0 initdb
    postgresql-9.0: service desconocido

    [root@srvccrj ~]# yum install postgresql90-contrib.x86_64 postgresql90-docs.x86_64 postgresql90-devel.x86_64 postgis90 postgis90-utils postgis90-docs postgresql90-server.x86_64 proj-devel.x86_64
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
    * addons: mirrors.netdna.com
    * base: hpc.arc.georgetown.edu
    * epel: nas1.itc.virginia.edu
    * extras: mirror.ash.fastserv.com
    * updates: mirror.cs.vt.edu
    Excluding Packages from CentOS-5 – Base
    Finished
    Setting up Install Process
    No package postgresql90-contrib.x86_64 available.
    No package postgresql90-docs.x86_64 available.
    No package postgresql90-devel.x86_64 available.
    Package postgis90-1.5.2-1.rhel5.i386 already installed and latest version
    Package postgis90-utils-1.5.2-1.rhel5.i386 already installed and latest version
    Package postgis90-docs-1.5.2-1.rhel5.i386 already installed and latest version
    No package postgresql90-server.x86_64 available.
    No package proj-devel.x86_64 available.
    Nothing to do
    [root@srvccrj ~]#
    ————————————————————————————————————
    This one is my file”CentOS-Base.repo”

    [base]
    name=CentOS-$releasever – Base
    mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
    #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
    gpgcheck=1
    exclude=postgresql*
    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5

    This one is my file “pgrpms9.repo”

    [pgdg90]
    name=PostgreSQL 9.0 $releasever – $basearch
    baseurl=http://yum.pgrpms.org/9.0/redhat/rhel-$releasever-$basearch
    enabled=1
    gpgcheck=0
    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

    [pgdg90-source]
    name=PostgreSQL 9.0 $releasever – $basearch – Source
    failovermethod=priority
    enabled=0
    baseurl=http://yum.pgrpms.org/srpms/9.0/redhat/rhel-$releasever-$basearch
    gpgcheck=0
    gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

    ———————————————-
    Help me please, already not c that to do
    ayudeme por favor, ya no c que hacer

    David

    July 16, 2011 at 4:41 am

    • Okay we are almost there. I missed you are running 32 bit linux. Run

      $ yum install postgresql90-contrib postgresql90-docs postgresql90-devel postgis90 postgis90-utils postgis90-docs postgresql90-server proj-devel geos-devel

      That should work.
      AJ

      anujjaiswal

      July 16, 2011 at 5:19 am

  4. This is great stuff. Worked perfectly.
    Do you have instructions for postgresql92 with Centos 6.3 x86_64?
    I’ve tried both 9.2 and 9.2.2, the psql gives me error: undefined symbol: PQconnectdbParams or symbol lookup error:libldap_r-2.4.so.2.5.6

    JoggerJoel

    February 1, 2013 at 7:32 pm

    • Hey
      I think your executable isnt on your path. Try looking up ldd /path/to/binary and setting LD_LIBRARY_PATH to directory of postgres 92.
      AJ

      anujjaiswal

      February 19, 2013 at 12:58 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: