postgreSQL 9.1 的安装、基本配置、简单使用

postgresql 9.1 安装rpm包清单

postgresql91-9.1.1-1PGDG.rhel5.x86_64.rpm
postgresql91-libs-9.1.1-1PGDG.rhel5.x86_64.rpm
postgresql91-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 M
Installing 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 postgres
postgres: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 -a
total 32
drwx------. 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 .bashrc
drwx------. 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 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password: //设置超级用户密码
Enter it again:
setting password ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgsql server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

Success. You can now start the database server using:

postgres -D data
or
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 md5
host 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.... done
server stopped
server starting

//登录数据库

[postgres@node0 ~]$ psql
Password:
psql (9.1.1)
Type "help" for help.

//创建数据库

postgres=# CREATE DATABASE testdb;
CREATE DATABASE
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
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=# 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 ~]#

postgreSQL 9.1 的安装、基本配置、简单使用

猜你在找的Postgre SQL相关文章