티스토리 뷰

출처: http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos




This post will cover installing and basic configuration of PostgreSQL 9.x on CentOS.

We will install PostgreSQL 9 using the PostgreSQL repository and yum.

The same procedure can be used to install PostgreSQL 9 on Red Hat and Fedora using the appropriate rpm.

Optionally, we'll also see how to install PostGIS. 

As the directory structure of PostgreSQL has changed with the release of PostgreSQL 9, we will also look a look at how we can create symlinks to make life easier when installing software or modules that still expect the old directory structure.

Finally, for Webmin users, we will see how to configuring Webmin to manage PostgreSQL 9.

I am using CentOS 6, but the same procedure works for CentOS 5. Finally, if you are using Webmin, we will also show how to configure Webmin to manage PostgreSQL 9.

With the release of PostgreSQL 9, the directory structure of PostgreSQL has changed. 

We will also creating symlinks (if needed) from the new PostgreSQL 9 file locations to the previous PostgreSQL 8 file locations.

If you are looking trying to install PostgreSQL 9 on cPanel, please see my post here. 

We'll use the simplest method to install, which is the postrgres repo rpms. 


1. Download and Install the PostgreSQL Repository



Download the latest production release for your distro here: http://yum.pgrpms.org/repopackages.php 

The repo rpms are 32 and 64 bit specific.

Since I am installing on CentOS 6 x64, I will need:

http://yum.pgrpms.org/9.1/redhat/rhel-5-x86_64/pgdg-centos91-9.1-4.noarch.rpm

So, using wget: 

wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm

  1. [root@server1 ~]# wget http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm  
  2. --2011-11-01 00:11:50--  http://yum.pgrpms.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm  
  3. Resolving yum.pgrpms.org... 98.129.198.114  
  4. Connecting to yum.pgrpms.org|98.129.198.114|:80... connected.  
  5. HTTP request sent, awaiting response... 200 OK  
  6. Length: 5124 (5.0K) [application/x-redhat-package-manager]  
  7. Saving to: pgdg-centos91-9.1-4.noarch.rpm  
  8.   
  9. 100%[======================================>] 5,124       --.-K/s   in 0s  
  10.   
  11. 2011-11-01 00:11:51 (310 MB/s) - pgdg-centos91-9.1-4.noarch.rpm  
  12.   
  13. [root@server1 ~]#   


Now, install the repo....

  1. [root@server1 ~]# rpm -i pgdg-centos91-9.1-4.noarch.rpm  


We now need to edit the CentOS-Base.repo to exclude postgreql.

To do, so we simply edit CentOS-Base.repo and add 'exclude=postgresql*' to the [base] and [updates] sections:

  1. [root@server1 ~]# cd /etc/yum.repos.d  
  2. [root@server1 yum.repos.d]# vi CentOS-Base.repo  


  1. [root@server1 yum.repos.d]# vi CentOS-Base.repo  
  2. # remarked out baseurl= line instead.  
  3. #  
  4. #  
  5.   
  6. [base]  
  7. name=CentOS-$releasever - Base  
  8. mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os  
  9. #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/  
  10. gpgcheck=1  
  11. gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6  
  12. exclude=postgresql*  
  13.  
  14. #released updates  
  15. [updates]  
  16. name=CentOS-$releasever - Updates  
  17. mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates  
  18. #baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/  
  19. gpgcheck=1  
  20. gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6  
  21. exclude=postgresql*  


Now, let's use 'yum list' to check the packages that are now available.

  1. [root@server1 yum.repos.d]# yum list postgres*  
  2. Loaded plugins: fastestmirror  
  3. base                                                     | 3.7 kB     00:00  
  4. base/primary_db                                          | 4.2 MB     00:09  
  5. extras                                                   | 3.0 kB     00:00  
  6. extras/primary_db                                        | 1.9 kB     00:00  
  7. pgdg91                                                   | 2.8 kB     00:00  
  8. pgdg91/primary_db                                        |  79 kB     00:00  
  9. updates                                                  | 3.5 kB     00:00  
  10. updates/primary_db                                       | 3.3 MB     00:00  
  11. vz-base                                                  |  951 B     00:00  
  12. vz-base/primary                                          | 1.3 kB     00:00  
  13. vz-base                                                                     3/3  
  14. vz-updates                                               |  951 B     00:00  
  15. vz-updates/primary                                       |  157 B     00:00  
  16. Available Packages  
  17. postgresql91.x86_64                         9.1.1-1PGDG.rhel6             pgdg91  
  18. postgresql91-contrib.x86_64                 9.1.1-1PGDG.rhel6             pgdg91  
  19. postgresql91-debuginfo.x86_64               9.1.1-1PGDG.rhel6             pgdg91  
  20. postgresql91-devel.i686                     9.1.1-1PGDG.rhel6             pgdg91  
  21. postgresql91-devel.x86_64                   9.1.1-1PGDG.rhel6             pgdg91  
  22. postgresql91-docs.x86_64                    9.1.1-1PGDG.rhel6             pgdg91  
  23. postgresql91-jdbc.x86_64                    9.1.901-1PGDG.rhel6           pgdg91  
  24. postgresql91-jdbc-debuginfo.x86_64          9.1.901-1PGDG.rhel6           pgdg91  
  25. postgresql91-libs.i686                      9.1.1-1PGDG.rhel6             pgdg91  
  26. postgresql91-libs.x86_64                    9.1.1-1PGDG.rhel6             pgdg91  
  27. postgresql91-odbc.x86_64                    09.00.0200-1PGDG.rhel6        pgdg91  
  28. postgresql91-odbc-debuginfo.x86_64          09.00.0200-1PGDG.rhel6        pgdg91  
  29. postgresql91-plperl.x86_64                  9.1.1-1PGDG.rhel6             pgdg91  
  30. postgresql91-plpython.x86_64                9.1.1-1PGDG.rhel6             pgdg91  
  31. postgresql91-pltcl.x86_64                   9.1.1-1PGDG.rhel6             pgdg91  
  32. postgresql91-python.x86_64                  4.0-2PGDG.rhel6               pgdg91  
  33. postgresql91-python-debuginfo.x86_64        4.0-2PGDG.rhel6               pgdg91  
  34. postgresql91-server.x86_64                  9.1.1-1PGDG.rhel6             pgdg91  
  35. postgresql91-tcl.x86_64                     1.9.0-1.rhel6                 pgdg91  
  36. postgresql91-tcl-debuginfo.x86_64           1.9.0-1.rhel6                 pgdg91  
  37. postgresql91-test.x86_64                    9.1.1-1PGDG.rhel6             pgdg91  
  38. postgresql_autodoc.noarch                   1.40-1.rhel6                  pgdg91  
  39. [root@server1 yum.repos.d]#  



2. Install PostgreSQL 9.1 Using Yum



We can now install PostgreSQL 9 using yum:

yum install postgresql91 postgresql91-devel postgresql91-server postgresql91-libs postgresql91-contrib
  1. [root@server1 yum.repos.d]# yum install postgresql91 postgresql91-devel postgresql91-server postgresql91-libs postgresql91-contrib  
  2. Loaded plugins: fastestmirror  
  3. Determining fastest mirrors  
  4.  * base: mirror.us.leaseweb.net  
  5.  * extras: mirror.lug.udel.edu  
  6.  * updates: centos.mirror.choopa.net  
  7. Setting up Install Process  
  8. Resolving Dependencies  
  9. --> Running transaction check  
  10. ---> Package postgresql91.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated  
  11. ---> Package postgresql91-devel.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated  
  12. ---> Package postgresql91-libs.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated  
  13. ---> Package postgresql91-server.x86_64 0:9.1.1-1PGDG.rhel6 set to be updated  
  14. --> Finished Dependency Resolution  
  15.   
  16. Dependencies Resolved  
  17.   
  18. ================================================================================  
  19.  Package                  Arch        Version                 Repository   Size  
  20. ================================================================================  
  21. Installing:  
  22.  postgresql91             x86_64      9.1.1-1PGDG.rhel6       pgdg91      939 k  
  23.  postgresql91-devel       x86_64      9.1.1-1PGDG.rhel6       pgdg91      1.4 M  
  24.  postgresql91-libs        x86_64      9.1.1-1PGDG.rhel6       pgdg91      186 k  
  25.  postgresql91-server      x86_64      9.1.1-1PGDG.rhel6       pgdg91      3.4 M  
  26.   
  27. Transaction Summary  
  28. ================================================================================  
  29. Install       4 Package(s)  
  30. Upgrade       0 Package(s)  
  31.   
  32. Total download size: 5.9 M  
  33. Installed size: 25 M  
  34. Is this ok [y/N]: y  
  35. Downloading Packages:  
  36. (1/4): postgresql91-9.1.1-1PGDG.rhel6.x86_64.rpm         | 939 kB     00:02  
  37. (2/4): postgresql91-devel-9.1.1-1PGDG.rhel6.x86_64.rpm   | 1.4 MB     00:01  
  38. (3/4): postgresql91-libs-9.1.1-1PGDG.rhel6.x86_64.rpm    | 186 kB     00:00  
  39. (4/4): postgresql91-server-9.1.1-1PGDG.rhel6.x86_64.rpm  | 3.4 MB     00:02  
  40. --------------------------------------------------------------------------------  
  41. Total                                           800 kB/s | 5.9 MB     00:07  
  42. Running rpm_check_debug  
  43. Running Transaction Test  
  44. Transaction Test Succeeded  
  45. Running Transaction  
  46.   Installing     : postgresql91-libs-9.1.1-1PGDG.rhel6.x86_64               1/4  
  47.   Installing     : postgresql91-9.1.1-1PGDG.rhel6.x86_64                    2/4  
  48.   Installing     : postgresql91-server-9.1.1-1PGDG.rhel6.x86_64             3/4  
  49.   Installing     : postgresql91-devel-9.1.1-1PGDG.rhel6.x86_64              4/4  
  50.   
  51. Installed:  
  52.   postgresql91.x86_64 0:9.1.1-1PGDG.rhel6  
  53.   postgresql91-devel.x86_64 0:9.1.1-1PGDG.rhel6  
  54.   postgresql91-libs.x86_64 0:9.1.1-1PGDG.rhel6  
  55.   postgresql91-server.x86_64 0:9.1.1-1PGDG.rhel6  
  56.   
  57. Complete!  
  58. [root@server1 yum.repos.d]#  





3. Initialize and Start PostgreSQL 9.1



We can now initialize and Start PostgreSQL

NOTE: when using Webmin, please see 'Configuring Webmin to Manage PostgreSQL9 below:

  1. [root@server1 yum.repos.d]# service postgresql-9.1 initdb  
  2. Initializing database:                                     [  OK  ]  
  3. [root@server1 yum.repos.d]#  


Start the PostgreSQL server:

  1. [root@server1 yum.repos.d]# service postgresql-9.1 start  
  2. Starting postgresql-9.1 service:                           [  OK  ]  
  3. [root@server1 yum.repos.d]#  


If you encounter startup errors, check under /var/lib/pgsql/9.1/data/pg_log for clues. 


4. Set PostgreSQL 9 Environment



The deault home directory for the user postgres is at /var/lib/pgsql

The bash_profile for the user postgres will look like this:

  1. [ -f /etc/profile ] && source /etc/profile  
  2. PGDATA=/var/lib/pgsql/9.1/data  
  3. export PGDATA  


This contains a path for the data directory, but no path for the executable/binary directory. To ammend this, add the path as below:

  1. [ -f /etc/profile ] && source /etc/profile  
  2. PGDATA=/var/lib/pgsql/9.1/data  
  3. export PGDATA  
  4. PATH=$PATH:$HOME/bin:/usr/pgsql-9.1/bin  
  5. export PATH  


Placing the binary directory in the path for postgres will allow you to invoke pg_ctl and other commands from the shell.


5. Set postgres Password



The superuser postgres has no password set by default.

To set the password, switch to postgres user:

  1. [root@server1 yum.repos.d]# su - postgres  


Connect as postgres to the postgres database and set the password for user postgres using alter user as below:

  1. -bash-4.1$ psql postgres postgres  
  2. psql (9.1.1)  
  3. Type "help" for help.  
  4.   
  5. postgres=# alter user postgres with password 'postgres';  
  6. ALTER ROLE  
  7. postgres=#  



6. Configure PostgreSQL 9 pg_hba.conf File



Locate your pg_hba.conf file under /var/lib/pgsql/9.1/data

On installation, your pg_hba.conf file will look like this:

  1. [root@server1 yum.repos.d]# vi /var/lib/pgsql/9.1/data/pg_hba.conf  
  2. # Put your actual configuration here  
  3. # ----------------------------------  
  4. #  
  5. # If you want to allow non-local connections, you need to add more  
  6. # "host" records.  In that case you will also need to make PostgreSQL  
  7. # listen on a non-local interface via the listen_addresses  
  8. # configuration parameter, or via the -i or -h command line switches.  
  9.  
  10.  
  11.  
  12. # TYPE  DATABASE        USER            ADDRESS                 METHOD  
  13.  
  14. # "local" is for Unix domain socket connections only  
  15. local   all             all                                     peer  
  16. # IPv4 local connections:  
  17. host    all             all             127.0.0.1/32            ident  
  18. # IPv6 local connections:  
  19. host    all             all             ::1/128                 ident  
  20. # Allow replication connections from localhost, by a user with the  
  21. # replication privilege.  
  22. #local   replication     postgres                                peer  
  23. #host    replication     postgres        127.0.0.1/32            ident  
  24. #host    replication     postgres        ::1/128                 ident  


Change the METHOD to md5 as shown below:

  1. # TYPE  DATABASE        USER            ADDRESS                 METHOD  
  2.  
  3. # "local" is for Unix domain socket connections only  
  4. local   all             all                                     md5  
  5. # IPv4 local connections:  
  6. host    all             all             127.0.0.1/32            md5  
  7. # IPv6 local connections:  
  8. host    all             all             ::1/128                 md5  


In order for the change to take effect, reload the pg_hba.conf file.

As with any command, there are several ways you can reload the pg_hba.conf file.

Method 1: From the shell using pg_ctl reload:

  1. [root@server1 yum.repos.d]# su - postgres  
  2. -bash-4.1$ pg_ctl reload  
  3. server signaled  
  4. -bash-4.1$  


Method 2: From psql using pg_reload_conf();
  1. -bash-4.1$ psql postgres postgres  
  2. psql (9.1.1)  
  3. Type "help" for help.  
  4.   
  5. postgres=# select pg_reload_conf();  
  6.  pg_reload_conf  
  7. ----------------  
  8.  t  
  9. (1 row)  
  10.   
  11. postgres=#  
Method 3: From the shell using -c switch to run select pg_reload_conf();
  1. -bash-4.1$ psql postgres postgres -c "select pg_reload_conf();"  
  2. Password for user postgres:  
  3.  pg_reload_conf  
  4. ----------------  
  5.  t  
  6. (1 row)  
  7.   
  8. -bash-4.1$  



7. Configure Remote Access for PostgreSQL 9



Locate the postgresql.conf file under /var/lib/pgsql/9.1/data.

Look for CONNECTIONS AND AUTHENTICATION. It will look as below:

  1. #------------------------------------------------------------------------------  
  2. # CONNECTIONS AND AUTHENTICATION  
  3. #------------------------------------------------------------------------------  
  4.  
  5. # - Connection Settings -  
  6.  
  7. #listen_addresses = 'localhost'     # what IP address(es) to listen on;  
  8.                     # comma-separated list of addresses;  
  9.                     # defaults to 'localhost', '*' = all  
  10.                     # (change requires restart)  
  11. #port = 5432                # (change requires restart)  


By default, access is limited to local machine (localhost).

To enable remote connections, uncomment listen_addresses and change to '*' as shown below.

  1. #------------------------------------------------------------------------------  
  2. # CONNECTIONS AND AUTHENTICATION  
  3. #------------------------------------------------------------------------------  
  4.  
  5. # - Connection Settings -  
  6.   
  7. listen_addresses = '*'      # what IP address(es) to listen on;  
  8.                     # comma-separated list of addresses;  
  9.                     # defaults to 'localhost', '*' = all  
  10.                     # (change requires restart)  
  11. #port = 5432                # (change requires restart)  


You can also set the listen_address limit to a specific IP (or IPs using a comma separated list).

Note: For security, it is also a good idea to change the default port. To do this, uncomment port and set to a new port value. 

If you change the port, you will need to restart the service.

Restart the postgresql service:
  1. service postgresql-9.1 restart  
  2. Stopping postgresql-9.1 service:                           [  OK  ]  
  3. Starting postgresql-9.1 service:                           [  OK  ]  
  4. [root@server1 yum.repos.d]#  


If you encounter startup errors, check under /var/lib/pgsql/9.1/data/pg_log for clues.

Verify the changes to listen_address and port (if changed):

  1. -bash-4.1$ psql  
  2. Password:  
  3. psql (9.1.1)  
  4. Type "help" for help.  
  5.   
  6. postgres=# show listen_addresses;  
  7.  listen_addresses  
  8. ------------------  
  9.  *  
  10. (1 row)  
  11.   
  12.   
  13. postgres=# show port;  
  14.  port  
  15. ------  
  16.  5432  
  17. (1 row)  
  18.   
  19. postgres=#  


8. Create User and Database for PostgreSQL 9



To check Check functionality, connect to postgres db as user postgres.

  1. [root@server1 yum.repos.d]# psql postgres postgres  
  2. Password for user postgres:  
  3. psql (9.1.1)  
  4. Type "help" for help.  
  5.   
  6. postgres=#  


Create a user:

  1. postgres=# create user myuser with password 'secret';  
  2. CREATE ROLE  


Create a database and give ownership to the new user:

  1. postgres=# create database mytestdb owner=myuser;  
  2. CREATE DATABASE  


Connect to the database as user:

  1. postgres=# \c mytestdb myuser  
  2. Password for user myuser:  
  3. You are now connected to database "mytestdb" as user "myuser".  


Create a table and insert row(s):

  1. mytestdb=> create table testtable (col1 varchar);  
  2. CREATE TABLE  
  3. mytestdb=> insert into testtable values('hello');  
  4. INSERT 0 1  


Select on the table you created:

  1. mytestdb=> select * from testtable;  
  2.  col1  
  3. -------  
  4.  hello  
  5. (1 row)  
  6.   
  7. mytestdb=>  


Describe table:

  1. mytestdb=> \dt  
  2.           List of relations  
  3.  Schema |   Name    | Type  | Owner  
  4. --------+-----------+-------+--------  
  5.  public | testtable | table | myuser  
  6. (1 row)  


Note that by default the schema used is Public. You should create a specific schema for your users. 


9. Configure PostgreSQL 9 Service to Start at Boot



By default, the service postgresql-9.1 is added to chkconifg, but all run levels are set to off.

Add for run levels 2,3, and 4 for the postgresql-9.1 service.

  1. [root@server1 yum.repos.d]# chkconfig --level 234 postgresql-9.1 on  





10. Create Symlinks for Backward Compatibility from PostgreSQL 9 to PostgreSQL 8



Many, if not most, third party software and modules are still be set to look for PoistgreSQL's conf file and data directory under their old (pre-version 9) locations.

You can address this, and make life easier for yourself, by creating a few symlinks from the new locations to the old.

Symlink 1: Symlink for the binary directory. This is particularly useful as this is the location of the pg_config file

  1. root@server1 [~]# ln -s /usr/pgsql-9.1/bin/pg_config /usr/bin  


Symlink 2: Symlink for the old data directory location of /var/lob/pgsql 

  1. root@server1 [~]# ln -s /var/lib/pgsql/9.1/data /var/lib/pgsql    
  2. root@server1 [~]# ln -s /var/lib/pgsql/9.1/backups /var/lib/pgsql    



11. Install PostGIS on PostgreSQL 9



Using the postgresql repo, we can easily install PostGIS if we wish to.

The installtion will also install Proj4 and Geos and required perl modules.

  1. [root@server1 yum.repos.d]# yum install postgis91 postgis91-utils  
  2. Loaded plugins: fastestmirror  
  3. Loading mirror speeds from cached hostfile  
  4.  * base: mirror.us.leaseweb.net  
  5.  * extras: mirror.lug.udel.edu  
  6.  * updates: centos.mirror.choopa.net  
  7. Setting up Install Process  
  8. Resolving Dependencies  
  9. --> Running transaction check  
  10. ---> Package postgis91.x86_64 0:1.5.3-2.rhel6 set to be updated  
  11. --> Processing Dependency: proj for package: postgis91-1.5.3-2.rhel6.x86_64  
  12. --> Processing Dependency: geos for package: postgis91-1.5.3-2.rhel6.x86_64  
  13. --> Processing Dependency: libgeos_c.so.1()(64bit) for package: postgis91-1.5.3-2.rhel6.x86_64  
  14. --> Processing Dependency: libproj.so.0()(64bit) for package: postgis91-1.5.3-2.rhel6.x86_64  
  15. ---> Package postgis91-utils.x86_64 0:1.5.3-2.rhel6 set to be updated  
  16. --> Processing Dependency: perl-DBD-Pg for package: postgis91-utils-1.5.3-2.rhel6.x86_64  
  17. --> Running transaction check  
  18. ---> Package geos.x86_64 0:3.3.0-1.rhel6 set to be updated  
  19. ---> Package perl-DBD-Pg.x86_64 0:2.15.1-3.el6 set to be updated  
  20. --> Processing Dependency: perl(DBI) for package: perl-DBD-Pg-2.15.1-3.el6.x86_64  
  21. ---> Package proj.x86_64 0:4.7.0-1.rhel6 set to be updated  
  22. --> Running transaction check  
  23. ---> Package perl-DBI.x86_64 0:1.609-4.el6 set to be updated  
  24. --> Finished Dependency Resolution  
  25.   
  26. Dependencies Resolved  
  27.   
  28. ================================================================================  
  29.  Package                Arch          Version               Repository     Size  
  30. ================================================================================  
  31. Installing:  
  32.  postgis91              x86_64        1.5.3-2.rhel6         pgdg91        1.3 M  
  33.  postgis91-utils        x86_64        1.5.3-2.rhel6         pgdg91         21 k  
  34. Installing for dependencies:  
  35.  geos                   x86_64        3.3.0-1.rhel6         pgdg91        502 k  
  36.  perl-DBD-Pg            x86_64        2.15.1-3.el6          base          197 k  
  37.  perl-DBI               x86_64        1.609-4.el6           base          705 k  
  38.  proj                   x86_64        4.7.0-1.rhel6         pgdg91        157 k  
  39.   
  40. Transaction Summary  
  41. ================================================================================  
  42. Install       6 Package(s)  
  43. Upgrade       0 Package(s)  
  44.   
  45. Total download size: 2.9 M  
  46. Installed size: 11 M  
  47. Is this ok [y/N]: y  
  48. Running rpm_check_debug  
  49. Running Transaction Test  
  50. Transaction Test Succeeded  
  51. Running Transaction  
  52.   Installing     : proj-4.7.0-1.rhel6.x86_64                                1/6  
  53.   Installing     : perl-DBI-1.609-4.el6.x86_64                              2/6  
  54.   Installing     : perl-DBD-Pg-2.15.1-3.el6.x86_64                          3/6  
  55.   Installing     : geos-3.3.0-1.rhel6.x86_64                                4/6  
  56.   Installing     : postgis91-1.5.3-2.rhel6.x86_64                           5/6  
  57.   Installing     : postgis91-utils-1.5.3-2.rhel6.x86_64                     6/6  
  58.   
  59. Installed:  
  60.   postgis91.x86_64 0:1.5.3-2.rhel6    postgis91-utils.x86_64 0:1.5.3-2.rhel6  
  61.   
  62. Dependency Installed:  
  63.   geos.x86_64 0:3.3.0-1.rhel6          perl-DBD-Pg.x86_64 0:2.15.1-3.el6  
  64.   perl-DBI.x86_64 0:1.609-4.el6        proj.x86_64 0:4.7.0-1.rhel6  
  65.   
  66. Complete!  
  67. [root@server1 yum.repos.d]#  


The required PostGIS sql files will be installed under /usr/pgsql-9.1/share/contrib/postgis-1.5

Create a database.

  1. -bash-4.1$ createdb pgisdb  
  2. Password:  
  3. -bash-4.1$  


Run the postgis.sql and spatial_ref_sys.sql files using below.

  1. -bash-4.1$ psql -d pgisdb -f /usr/pgsql-9.1/share/contrib/postgis-1.5/postgis.sql  


  1. -bash-4.1$ psql -d pgisdb -f /usr/pgsql-9.1/share/contrib/postgis-1.5/spatial_ref_sys.sql  



12. Configuring Webmin to Manage PostegreSQL 9



Due to the directory structure of PostgreSQL 9, you will need to make a few changes to the Webmin management interface it let Webmin know where the Postgre files are located.

Under Servers>PostgreSQL Database Server Click on Module Configuration.

Make the following substitutions in the System Configuration Section:

1. Path to psql command: Original:Change to:
  1. /usr/pgsql-9.1/bin/psql  
2. Command to start PostgreSQL Original:
  1. if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql start; fi  
Change to:
  1. if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.1 start; fi  
3. Command to stop PostgreSQL  Original:
  1. if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql stop; fi  
Change to:
  1. if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb stop; else /etc/rc.d/init.d/postgresql-9.1 stop; fi  
4. Command to initialize PostgreSQL Original:
  1. if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql initdb ; /etc/rc.d/init.d/postgresql start; fi  
Change to:
  1. if [ -r /etc/rc.d/init.d/rhdb ]; then /etc/rc.d/init.d/rhdb start; else /etc/rc.d/init.d/postgresql-9.1 initdb ; /etc/rc.d/init.d/postgresql-9.1 start; fi  
5. Path to postmaster PID file Original: 
  1. /var/run/postmaster.pid  

Change to:
  1. /var/run/postmaster-9.1.pid  

6. Paths to host access config file Original: 
  1. /var/lib/pgsql/data/pg_hba.conf  

Change to: 
  1. /var/lib/pgsql/9.1/data/pg_hba.conf  

7. Default backup repository directory Original: 

  1. /home/db_repository  

Change to: 

  1. /var/lib/pgsql/9.1/backups  

Save the configuration.

If you have not already initialized the database, do so now by clicking the initialize database button.




출처: http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos

'데이터베이스 > PostgreSQL' 카테고리의 다른 글

Install PostgreSQL 9 on CentOS  (0) 2012.10.29
delete 에서 join 사용하기  (0) 2012.10.24
pg_get_tabledef  (0) 2012.06.13
Install PostgreSQL 9.0 on CentOS 5.5  (0) 2011.03.27
pgadmin 서버 목록  (0) 2010.11.10
postgresql.conf, pg_hba.conf 등의 설정 reload...  (0) 2010.10.11
댓글
댓글쓰기 폼