postgreSQL 9.1 安装rpm包清单
postgresql91-9.1.1-1PGDG.rhel5.x86_64.rpmpostgresql91-libs-9.1.1-1PGDG.rhel5.x86_64.rpmpostgresql91-server-9.1.1-1PGDG.rhel5.x86_64.rpm
//安装
配置好yum源后，在软件包所在目录下，此处为/root
[root@node0 ~]# yum install *.rpm -y
=============================================================================================== Package Arch Version Repository Size===============================================================================================Installing: postgresql91 x86_64 9.1.1-1PGDG.rhel5 /postgresql91-9.1.1-1PGDG.rhel5.x86_64 4.6 M postgresql91-libs x86_64 9.1.1-1PGDG.rhel5 /postgresql91-libs-9.1.1-1PGDG.rhel5.x86_64 597 k postgresql91-server x86_64 9.1.1-1PGDG.rhel5 /postgresql91-server-9.1.1-1PGDG.rhel5.x86_64 14 MInstalling for dependencies: compat-libtermcap x86_64 2.0.8-49.el6 Server 16 k compat-openldap x86_64 1:2.3.43-2.el6 Server 160 k compat-readline5 x86_64 5.2-17.1.el6 Server 129 k openssl098e x86_64 0.9.8e-17.el6_2.2 Server 761 k
//vipw命令设置数据库用户postgres家目录为/var/lib/pgsql/9.1
[root@node0 ~]# cat /etc/passwd |grep postgrespostgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
[root@node0 ~]# vipw
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql/9.1:/bin/bash
You have modified /etc/passwd.You may need to modify /etc/shadow for consistency.Please use the command 'vipw -s' to do so.
//设置postgreSQL环境变量
[root@node0 ~]# cp /etc/skel/.* /var/lib/pgsql/
[root@node0 ~]# chown -R postgres:postgres /var/lib/pgsql/
系统切换到postgres数据库用户
[root@node0 ~]# su - postgres
[postgres@node0 ~]$ pwd/var/lib/pgsql/9.1
[postgres@node0 ~]$ ll -atotal 32drwx------. 4 postgres postgres 4096 Jul 10 07:35 .drwx------. 3 postgres postgres 4096 Jul 10 07:20 ..drwx------. 2 postgres postgres 4096 Sep 23 2011 backups-rw-------. 1 postgres postgres 12 Jul 10 07:28 .bash_history-rw-r--r--. 1 postgres postgres 18 Jul 10 07:30 .bash_logout-rw-r--r--. 1 postgres postgres 177 Jul 10 07:35 .bash_profile-rw-r--r--. 1 postgres postgres 124 Jul 10 07:30 .bashrcdrwx------. 2 postgres postgres 4096 Sep 23 2011 data
[postgres@node0 ~]$ vi .bash_profile //设置PATH如下
PATH=$PATH:/usr/pgsql-9.1/bin
[postgres@node0 ~]$ source .bash_profile //使设置立即生效
[postgres@node0 ~]$ echo $PATH //检测设置是否正确/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/var/lib/pgsql/9.1/bin:/usr/pgsql-9.1/bin
//初始化数据库
[postgres@node0 ~]$ initdb -A md5 -D data/ -E UTF8 --locale=C -W
--locale=C 代表数据库语言同系统自身语言一致
The files belonging to this database system will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized with locale C.The default text search configuration will be set to "english".fixing permissions on existing directory data ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 32MBcreating configuration files ... okcreating template1 database in data/base/1 ... okinitializing pg_authid ... okEnter new superuser password: //设置超级用户密码Enter it again: setting password ... okinitializing dependencies ... okcreating system views ... okloading system objects' descriptions ... okcreating collations ... okcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... okSuccess. You can now start the database server using: postgres -D dataor pg_ctl -D data -l logfile start
//启动数据库服务
[postgres@node0 ~]$ pg_ctl start -m fast -D data/server starting
-m参数代表快速启动
数据库配置文件目录为家目录下的data
[postgres@node0 data]$ pwd/var/lib/pgsql/9.1/data
//设置数据库允许远程访问 [postgres@node0 data]$ vi pg_hba.conf
# IPv4 local connections:#host all all 127.0.0.1/32 md5host all all 0.0.0.0/0 md5
//设置监听地址（端口号默认为5432）
# - Connection Settings -#listen_addresses = 'localhost' # what IP address(es) to listen on;listen_addresses = '0' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart)#port = 5432 # (change requires restart)max_connections = 100 # (change requires restart)
更改了配置文件，须重启服务
[postgres@node0 data]$ cd [postgres@node0 ~]$ pg_ctl restart -m fast -D data/waiting for server to shut down.... doneserver stoppedserver starting
//登录数据库
[postgres@node0 ~]$ psql Password: psql (9.1.1)Type "help" for help.
//显示数据库列表 postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres(3 rows)
//创建数据库
postgres=# CREATE DATABASE testdb;CREATE DATABASEpostgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | C | C | (4 rows)
//查看数据库大小（假设数据库名称为：dbname）
postgres=# SELECT pg_size_pretty(pg_database_size('dbname')); pg_size_pretty ---------------- 28 MB(1 行记录)
//连接数据库
postgres=# \c testdb You are now connected to database "testdb" as user "postgres".testdb=#
//创建表
testdb=# CREATE TABLE users(id int,name varchar(20));CREATE TABLE
//显示表
testdb=# \d List of relations Schema | Name | Type | Owner --------+-------+-------+---------- public | users | table | postgres(1 row)
//显示表字段
testdb=# \d users Table "public.users" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(20) | 入记录
//插入记录
testdb=# INSERT INTO users VALUES (1,'tianxin');INSERT 0 1
//显示记录
testdb=# SELECT * from users ; id | name ----+--------- 1 | tianxin(1 row)
//断开数据库连接
testdb=# \q
//退出数据库 [postgres@node0 ~]$ exitlogout[root@node0 ~]#