个人总结了下,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.
-
[2014-07-26 12:26:41 root@davidcchen wesoft]# yum install postgresql93-server postgresql94-contrib
-
Loaded plugins: aliases, changelog, downloadonly, fastestmirror, kabi, presto, refresh-packagekit, security, tmprepo, verify,
-
: versionlock
-
Loading support for CentOS kernel ABI
-
Loading mirror speeds from cached hostfile
-
* base: mirrors.hust.edu.cn
-
* extras: mirrors.hust.edu.cn
-
* updates: mirror.bit.edu.cn
-
Setting up Install Process
-
Resolving Dependencies
-
--> Running transaction check
-
---> Package postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
-
--> Processing Dependency: postgresql93 = 9.3.5-1PGDG.rhel6 for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
-
--> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
-
--> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql93-server-9.3.5-1PGDG.rhel6.x86_64
-
---> Package postgresql94-contrib.x86_64 0:9.4beta2-1PGDG.rhel6 will be installed
-
--> Processing Dependency: postgresql94 = 9.4beta2 for package: postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64
-
--> Running transaction check
-
---> Package openssl.x86_64 0:1.0.0-27.el6 will be updated
-
--> Processing Dependency: openssl = 1.0.0-27.el6 for package: openssl-devel-1.0.0-27.el6.x86_64
-
---> Package openssl.x86_64 0:1.0.1e-16.el6_5.14 will be an update
-
---> Package postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
-
--> Processing Dependency: postgresql93-libs = 9.3.5-1PGDG.rhel6 for package: postgresql93-9.3.5-1PGDG.rhel6.x86_64
-
---> Package postgresql94.x86_64 0:9.4beta2-1PGDG.rhel6 will be installed
-
--> Processing Dependency: postgresql94-libs = 9.4beta2-1PGDG.rhel6 for package: postgresql94-9.4beta2-1PGDG.rhel6.x86_64
-
--> Running transaction check
-
---> Package openssl-devel.x86_64 0:1.0.0-27.el6 will be updated
-
---> Package openssl-devel.x86_64 0:1.0.1e-16.el6_5.14 will be an update
-
---> Package postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6 will be installed
-
---> Package postgresql94-libs.x86_64 0:9.4beta2-1PGDG.rhel6 will be installed
-
pgdg94/filelists_db | 108 kB 00:00
-
--> Finished Dependency Resolution
-
-
Dependencies Resolved
-
-
=============================================================================================================================
-
Package Arch Version Repository Size
-
=============================================================================================================================
-
Installing:
-
postgresql93-server x86_64 9.3.5-1PGDG.rhel6 pgdg93 4.1 M
-
postgresql94-contrib x86_64 9.4beta2-1PGDG.rhel6 pgdg94 531 k
-
Installing for dependencies:
-
postgresql93 x86_64 9.3.5-1PGDG.rhel6 pgdg93 1.0 M
-
postgresql93-libs x86_64 9.3.5-1PGDG.rhel6 pgdg93 191 k
-
postgresql94 x86_64 9.4beta2-1PGDG.rhel6 pgdg94 1.0 M
-
postgresql94-libs x86_64 9.4beta2-1PGDG.rhel6 pgdg94 192 k
-
Updating for dependencies:
-
openssl x86_64 1.0.1e-16.el6_5.14 updates 1.5 M
-
openssl-devel x86_64 1.0.1e-16.el6_5.14 updates 1.2 M
-
-
Transaction Summary
-
=============================================================================================================================
-
Install 6 Package(s)
-
Upgrade 2 Package(s)
-
-
Total download size: 9.6 M
-
Is this ok [y/N]: y
-
Downloading Packages:
-
Setting up and reading Presto delta metadata
-
Processing delta metadata
-
Download delta size: 1.9 M
-
(1/2): openssl-1.0.0-27.el6_1.0.1e-16.el6_5.14.x86_64.drpm | 973 kB 00:05
-
(2/2): openssl-devel-1.0.0-27.el6_1.0.1e-16.el6_5.14.x86_64.drpm | 961 kB 00:04
-
Finishing rebuild of rpms, from deltarpms
-
<delta rebuild> | 2.7 MB 00:01
-
Presto reduced the update size by 30% (from 2.7 M to 1.9 M).
-
Package(s) data still to download: 7.0 M
-
(1/6): postgresql93-9.3.5-1PGDG.rhel6.x86_64.rpm | 1.0 MB 00:04
-
(2/6): postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64.rpm | 191 kB 00:00
-
(3/6): postgresql93-server-9.3.5-1PGDG.rhel6.x86_64.rpm | 4.1 MB 00:13
-
(4/6): postgresql94-9.4beta2-1PGDG.rhel6.x86_64.rpm | 1.0 MB 00:01
-
(5/6): postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64.rpm | 531 kB 00:00
-
(6/6): postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64.rpm | 192 kB 00:00
-
-----------------------------------------------------------------------------------------------------------------------------
-
Total 331 kB/s | 7.0 MB 00:21
-
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
-
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
-
Importing GPG key 0xC105B9DE:
-
Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>
-
Package: centos-release-6-4.el6.centos.10.x86_64 (@anaconda-CentOS-201303020151.x86_64/6.4)
-
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
-
Is this ok [y/N]: y
-
Running rpm_check_debug
-
Running Transaction Test
-
Transaction Test Succeeded
-
Running Transaction
-
Warning: RPMDB altered outside of yum.
-
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
-
1:emacs-23.1-21.el6_2.3.x86_64 has missing requires of libotf.so.0()(64bit)
-
Updating : openssl-1.0.1e-16.el6_5.14.x86_64 1/10
-
Installing : postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64 2/10
-
Installing : postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64 3/10
-
Installing : postgresql93-9.3.5-1PGDG.rhel6.x86_64 4/10
-
Installing : postgresql94-9.4beta2-1PGDG.rhel6.x86_64 5/10
-
Installing : postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64 6/10
-
Installing : postgresql93-server-9.3.5-1PGDG.rhel6.x86_64 7/10
-
Updating : openssl-devel-1.0.1e-16.el6_5.14.x86_64 8/10
-
Cleanup : openssl-devel-1.0.0-27.el6.x86_64 9/10
-
Cleanup : openssl-1.0.0-27.el6.x86_64 10/10
-
Verifying : postgresql94-libs-9.4beta2-1PGDG.rhel6.x86_64 1/10
-
Verifying : postgresql94-contrib-9.4beta2-1PGDG.rhel6.x86_64 2/10
-
Verifying : postgresql93-9.3.5-1PGDG.rhel6.x86_64 3/10
-
Verifying : postgresql93-libs-9.3.5-1PGDG.rhel6.x86_64 4/10
-
Verifying : postgresql94-9.4beta2-1PGDG.rhel6.x86_64 5/10
-
Verifying : openssl-1.0.1e-16.el6_5.14.x86_64 6/10
-
Verifying : postgresql93-server-9.3.5-1PGDG.rhel6.x86_64 7/10
-
Verifying : openssl-devel-1.0.1e-16.el6_5.14.x86_64 8/10
-
Verifying : openssl-devel-1.0.0-27.el6.x86_64 9/10
-
Verifying : openssl-1.0.0-27.el6.x86_64 10/10
-
-
Installed:
-
postgresql93-server.x86_64 0:9.3.5-1PGDG.rhel6 postgresql94-contrib.x86_64 0:9.4beta2-1PGDG.rhel6
-
-
Dependency Installed:
-
postgresql93.x86_64 0:9.3.5-1PGDG.rhel6 postgresql93-libs.x86_64 0:9.3.5-1PGDG.rhel6
-
postgresql94.x86_64 0:9.4beta2-1PGDG.rhel6 postgresql94-libs.x86_64 0:9.4beta2-1PGDG.rhel6
-
-
Dependency Updated:
-
openssl.x86_64 0:1.0.1e-16.el6_5.14 openssl-devel.x86_64 0:1.0.1e-16.el6_5.14
-
-
-
[2014-07-26 12:32:07 root@davidcchen wesoft]#
初始化和修改postgresql.conf,pg_hba.conf设置访问权限
-
postgresql-9.4 initdb
-
Initializing database: [ OK ]
-
-
[2014-07-26 17:24:50 root@davidcchen wesoft]# service postgresql-9.4 start
-
Starting postgresql-9.4 service: [ OK ]
-
-
[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. 让我们测试下:
-
bash-4.1$ psql
-
-
postgres=# CREATE USER super WITH PASSWORD '123456' ;
-
CREATE ROLE
-
-
postgres=# create database omd_mmdh owner super;
-
CREATE DATABASE
-
-
[super@davidcchen wesoft]$ psql omd_mmdh
-
psql (9.4beta2)
-
Type "help" for help.
-
-
omd_mmdh=> CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
-
CREATE TABLE
-
-
omd_mmdh=> INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
-
INSERT 0 1
-
-
omd_mmdh=> SELECT * FROM user_tbl;
-
name | signup_date
-
------+-------------
-
张三 | 2013-12-22
-
(1 row)
-
-
omd_mmdh=> UPDATE user_tbl set name = '李四' WHERE name = '张三';
-
UPDATE 1
-
-
omd_mmdh=> ALTER TABLE user_tbl ADD email VARCHAR(40);
ALTER TABLE
-
omd_mmdh=> ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
ALTER TABLE
-
omd_mmdh=> ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
ALTER TABLE
-
omd_mmdh=> ALTER TABLE user_tbl DROP COLUMN email;
ALTER TABLE
-
omd_mmdh=> ALTER TABLE user_tbl RENAME TO backup_tbl;
ALTER TABLE
-
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
-
omd_mmdh=> \d
No relations found.
omd_mmdh=>
阅读(3227) | 评论(0) | 转发(0) |