安装PostgreSQL 在RHEL

3170阅读　0评论2014-07-26　centrify
分类：Mysql/postgreSQL

个人总结了下，PostgreSQL的安装和简单地psql命令练习，中英文结合希望大家看得懂，有问题请留言！！！

1. download packageTo use the yum repository, you must first install the repository RPM. To do this, download the correct RPM from the , and install it with commands like: 
yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm

Or install from package as below command:
[2014-07-26 12:25:34 root@davidcchen wesoft]# rpm -hiv pgdg-redhat94-9.4-1.noarch.rpm
Preparing... ########################################### [100%]
1:pgdg-redhat94 ########################################### [100%]


2. Once this is done, you can proceed to install and update packages the same way as the ones included in the distribution.
  1. [2014-07-26 12:26:41 root@davidcchen wesoft]# yum install postgresql93-server postgresql94-contrib
  2. Loaded plugins: aliases, changelog, downloadonly, fastestmirror, kabi, presto, refresh-packagekit, security, tmprepo, verify,
  3.               : versionlock
  4. Loading support for CentOS kernel ABI
  5. Loading mirror speeds from cached hostfile
  6.  * base: mirrors.hust.edu.cn
  7.  * extras: mirrors.hust.edu.cn
  8.  * updates: mirror.bit.edu.cn
  9. Setting up Install Process
  10. Resolving Dependencies
  11. --> Running transaction check
  12. ---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
  13. --> Processing Dependency: postgresql93 = 9.3.5-1PGDG.rhel6 for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
  14. --> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
  15. --> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
  16. ---> Package postgresql94-contrib.x86_64 0:9.4beta2-1PGDG.rhel6 will be installed
  17. --> Processing Dependency: postgresql94 = 9.4beta2 for package: postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64
  18. --> Running transaction check
  19. ---> Package openssl.x86_64 0:1.0.0-27.el6 will be updated
  20. --> Processing Dependency: openssl = 1.0.0-27.el6 for package: openssl-devel-1.0.0-27.el6.x86_64
  21. ---> Package openssl.x86_64 0:1.0.1e-16.el6_5.14 will be an update
  22. ---> Package postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
  23. --> Processing Dependency: postgresql93-libs = 9.3.5-1PGDG.rhel6 for package: postgresql93-9.3.5-1PGDG.rhel6.x86_64
  24. ---> Package postgresql94.x86_64 0:9.4beta2-1PGDG.rhel6 will be installed
  25. --> Processing Dependency: postgresql94-libs = 9.4beta2-1PGDG.rhel6 for package: postgresql94-9.4beta2-1PGDG.rhel6.x86_64
  26. --> Running transaction check
  27. ---> Package openssl-devel.x86_64 0:1.0.0-27.el6 will be updated
  28. ---> Package openssl-devel.x86_64 0:1.0.1e-16.el6_5.14 will be an update
  29. ---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
  30. ---> Package postgresql94-libs.x86_64 0:9.4beta2-1PGDG.rhel6 will be installed
  31. pgdg94/filelists_db | 108 kB 00:00
  32. --> Finished Dependency Resolution

  34. Dependencies Resolved

  36. =============================================================================================================================
  37.  Package Arch Version Repository Size
  38. =============================================================================================================================
  39. Installing:
  40.  postgresql93-server x86_64 9.3.5-1PGDG.rhel6 pgdg93 4.1 M
  41.  postgresql94-contrib x86_64 9.4beta2-1PGDG.rhel6 pgdg94 531 k
  42. Installing for dependencies:
  43.  postgresql93 x86_64 9.3.5-1PGDG.rhel6 pgdg93 1.0 M
  44.  postgresql93-libs x86_64 9.3.5-1PGDG.rhel6 pgdg93 191 k
  45.  postgresql94 x86_64 9.4beta2-1PGDG.rhel6 pgdg94 1.0 M
  46.  postgresql94-libs x86_64 9.4beta2-1PGDG.rhel6 pgdg94 192 k
  47. Updating for dependencies:
  48.  openssl x86_64 1.0.1e-16.el6_5.14 updates 1.5 M
  49.  openssl-devel x86_64 1.0.1e-16.el6_5.14 updates 1.2 M

  51. Transaction Summary
  52. =============================================================================================================================
  53. Install 6 Package(s)
  54. Upgrade 2 Package(s)

  56. Total download size: 9.6 M
  57. Is this ok [y/N]: y
  58. Downloading Packages:
  59. Setting up and reading Presto delta metadata
  60. Processing delta metadata
  61. Download delta size: 1.9 M
  62. (1/2): openssl-1.0.0-27.el6_1.0.1e-16.el6_5.14.x86_64.drpm | 973 kB 00:05
  63. (2/2): openssl-devel-1.0.0-27.el6_1.0.1e-16.el6_5.14.x86_64.drpm | 961 kB 00:04
  64. Finishing rebuild of rpms, from deltarpms
  65. <delta rebuild> | 2.7 MB 00:01
  66. Presto reduced the update size by 30% (from 2.7 M to 1.9 M).
  67. Package(s) data still to download: 7.0 M
  68. (1/6): postgresql93-9.3.5-1PGDG.rhel6.x86_64.rpm | 1.0 MB 00:04
  69. (2/6): postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64.rpm | 191 kB 00:00
  70. (3/6): postgresql93-server-9.3.5-1PGDG.rhel6.x86_64.rpm | 4.1 MB 00:13
  71. (4/6): postgresql94-9.4beta2-1PGDG.rhel6.x86_64.rpm | 1.0 MB 00:01
  72. (5/6): postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64.rpm | 531 kB 00:00
  73. (6/6): postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64.rpm | 192 kB 00:00
  74. -----------------------------------------------------------------------------------------------------------------------------
  75. Total 331 kB/s | 7.0 MB 00:21
  76. warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
  77. Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
  78. Importing GPG key 0xC105B9DE:
  79.  Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>
  80.  Package: centos-release-6-4.el6.centos.10.x86_64 (@anaconda-CentOS-201303020151.x86_64/6.4)
  81.  From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
  82. Is this ok [y/N]: y
  83. Running rpm_check_debug
  84. Running Transaction Test
  85. Transaction Test Succeeded
  86. Running Transaction
  87. Warning: RPMDB altered outside of yum.
  88. ** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
  89. 1:emacs-23.1-21.el6_2.3.x86_64 has missing requires of libotf.so.0()(64bit)
  90.   Updating : openssl-1.0.1e-16.el6_5.14.x86_64 1/10
  91.   Installing : postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64 2/10
  92.   Installing : postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64 3/10
  93.   Installing : postgresql93-9.3.5-1PGDG.rhel6.x86_64 4/10
  94.   Installing : postgresql94-9.4beta2-1PGDG.rhel6.x86_64 5/10
  95.   Installing : postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64 6/10
  96.   Installing : postgresql93-server-9.3.5-1PGDG.rhel6.x86_64 7/10
  97.   Updating : openssl-devel-1.0.1e-16.el6_5.14.x86_64 8/10
  98.   Cleanup : openssl-devel-1.0.0-27.el6.x86_64 9/10
  99.   Cleanup : openssl-1.0.0-27.el6.x86_64 10/10
  100.   Verifying : postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64 1/10
  101.   Verifying : postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64 2/10
  102.   Verifying : postgresql93-9.3.5-1PGDG.rhel6.x86_64 3/10
  103.   Verifying : postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64 4/10
  104.   Verifying : postgresql94-9.4beta2-1PGDG.rhel6.x86_64 5/10
  105.   Verifying : openssl-1.0.1e-16.el6_5.14.x86_64 6/10
  106.   Verifying : postgresql93-server-9.3.5-1PGDG.rhel6.x86_64 7/10
  107.   Verifying : openssl-devel-1.0.1e-16.el6_5.14.x86_64 8/10
  108.   Verifying : openssl-devel-1.0.0-27.el6.x86_64 9/10
  109.   Verifying : openssl-1.0.0-27.el6.x86_64 10/10

  111. Installed:
  112.   postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 postgresql94-contrib.x86_64 0:9.4beta2-1PGDG.rhel6

  114. Dependency Installed:
  115.   postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6
  116.   postgresql94.x86_64 0:9.4beta2-1PGDG.rhel6 postgresql94-libs.x86_64 0:9.4beta2-1PGDG.rhel6

  118. Dependency Updated:
  119.   openssl.x86_64 0:1.0.1e-16.el6_5.14 openssl-devel.x86_64 0:1.0.1e-16.el6_5.14


  122. [2014-07-26 12:32:07 root@davidcchen wesoft]#


初始化和修改postgresql.conf,pg_hba.conf设置访问权限

  1. postgresql-9.4 initdb
  2. Initializing database: [ OK ]

  4. [2014-07-26 17:24:50 root@davidcchen wesoft]# service postgresql-9.4 start
  5. Starting postgresql-9.4 service: [ OK ]

  7. [2014-07-26 17:24:16 root@davidcchen wesoft]# chkconfig --level 345 postgresql-9.4 onNote:

Package names in the PostgreSQL yum repository follows the same standard as the ones included in the main repositories, but include the version number, such as:



3.  让我们测试下：
  1. bash-4.1$ psql

  3. postgres=# CREATE USER super WITH PASSWORD '123456' ;
  4. CREATE ROLE

  6. postgres=# create database omd_mmdh owner super;
  7. CREATE DATABASE

  9. [super@davidcchen wesoft]$ psql omd_mmdh
  10. psql (9.4beta2)
  11. Type "help" for help.

  13. omd_mmdh=> CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
  14. CREATE TABLE

  16. omd_mmdh=> INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
  17. INSERT 0 1

  19. omd_mmdh=> SELECT * FROM user_tbl;
  20. name | signup_date
  21. ------+-------------
  22. 张三 | 2013-12-22
  23. (1 row)

  25. omd_mmdh=> UPDATE user_tbl set name = '李四' WHERE name = '张三';
  26. UPDATE 1

  28. omd_mmdh=> ALTER TABLE user_tbl ADD email VARCHAR(40);
    ALTER TABLE

  29. omd_mmdh=> ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
    ALTER TABLE

  30. omd_mmdh=> ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
    ALTER TABLE

  31. omd_mmdh=> ALTER TABLE user_tbl DROP COLUMN email;
    ALTER TABLE

  32. omd_mmdh=> ALTER TABLE user_tbl RENAME TO backup_tbl;
    ALTER TABLE

  33. omd_mmdh=> \d
              List of relations
     Schema |    Name    | Type  | Owner
    --------+------------+-------+-------
     public | backup_tbl | table | super
    (1 row)


    omd_mmdh=> DROP TABLE IF EXISTS backup_tbl;
    DROP TABLE

  34. omd_mmdh=> \d
    No relations found.
    omd_mmdh=>

上一篇：Chapter 1. 从头开始
下一篇：sqlite3简单语句学习