Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1478085
  • 博文数量: 246
  • 博客积分: 3353
  • 博客等级: 中校
  • 技术积分: 3295
  • 用 户 组: 普通用户
  • 注册时间: 2011-11-09 17:56
个人简介

精通测试技术,linux,shell,性能测试

文章分类

全部博文(246)

文章存档

2024年(3)

2023年(7)

2022年(7)

2021年(4)

2020年(1)

2019年(2)

2017年(2)

2016年(3)

2015年(11)

2014年(20)

2013年(10)

2012年(176)

分类: Mysql/postgreSQL

2014-07-26 12:49:44

个人总结了下,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

  33. Dependencies Resolved

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

  48. Transaction Summary
  49. =============================================================================================================================
  50. Install 6 Package(s)
  51. Upgrade 2 Package(s)

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

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

  108. Dependency Installed:
  109.   postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6
  110.   postgresql94.x86_64 0:9.4beta2-1PGDG.rhel6 postgresql94-libs.x86_64 0:9.4beta2-1PGDG.rhel6

  111. Dependency Updated:
  112.   openssl.x86_64 0:1.0.1e-16.el6_5.14 openssl-devel.x86_64 0:1.0.1e-16.el6_5.14


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


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

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

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

  5. [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:


  • postgresql93
  • postgresql93-server
  • postgresql93-contrib
  • pgadmin3_93

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

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

  4. postgres=# create database omd_mmdh owner super;
  5. CREATE DATABASE

  6. [super@davidcchen wesoft]$ psql omd_mmdh
  7. psql (9.4beta2)
  8. Type "help" for help.

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

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

  13. omd_mmdh=> SELECT * FROM user_tbl;
  14. name | signup_date
  15. ------+-------------
  16. 张三 | 2013-12-22
  17. (1 row)

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

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

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

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

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

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

  25. 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

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




阅读(3227) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~