Centos5下搭建PostgreSQL数据库和温和化管理
本节中介绍在 centos上如何安装PostgreSQL数据库和如何通过phppgadmin进行温和化，phppgadmin的安装需要apache和php 支持！PostgreSQL数据库和MySQL一样，都是很受欢迎的开源 数据库产品，但是PostgreSQL和MySQL相比下，前者更大型点，更适合做软件开发的数据库支持！
[root@centos5 ~]#yum -y install postgresql-server 在线安装PostgreSQL数据库
[root@centos5 ~]# su - postgres 切换到PostgreSQL数据库专用账号Postgres
-bash-3.2$ ls
backups data
-bash-3.2$ pwd
/var/lib/pgsql
-bash-3.2$ initdb -D /var/lib/pgsql/data/ 初始化数据库
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 en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating directory /var/lib/pgsql/data/global ... ok
creating directory /var/lib/pgsql/data/pg_xlog ... ok
creating directory /var/lib/pgsql/data/pg_xlog/archive_status ... ok
creating directory /var/lib/pgsql/data/pg_clog ... ok
creating directory /var/lib/pgsql/data/pg_subtrans ... ok
creating directory /var/lib/pgsql/data/pg_twophase ... ok
creating directory /var/lib/pgsql/data/pg_multixact/members ... ok
creating directory /var/lib/pgsql/data/pg_multixact/offsets ... ok
creating directory /var/lib/pgsql/data/base ... ok
creating directory /var/lib/pgsql/data/base/1 ... ok
creating directory /var/lib/pgsql/data/pg_tblspc ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 1000
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
enabling unlimited row size for system tables ... ok
initializing dependencies ... ok
creating system views ... ok
loading pg_description ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.
Success. You can now start the database server using:
postmaster -D /var/lib/pgsql/data
or
pg_ctl -D /var/lib/pgsql/data -l logfile start
-bash-3.2$ pg_ctl start 试启动服务，发现只侦听本地回环地址的5432端口
postmaster starting
-bash-3.2$ netstat -ntpl
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:751 0.0.0.0:* LISTEN -
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN -
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 3640/postmaster
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN -
tcp 0 0 :::22 :::* LISTEN -
-bash-3.2$ ls
base pg_clog pg_ident.conf pg_multixact pg_tblspc PG_VERSION postgresql.conf postmaster.pid
global pg_hba.conf pg_log pg_subtrans pg_twophase pg_xlog postmaster.opts
修改相关配置文件如下：
-bash-3.2$ grep -r host pg_hba.conf
# This file controls: which hosts are allowed to connect, how clients
# host DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTION]
# "host" is either a plain or SSL-encrypted TCP/IP socket, "hostssl" is an
# SSL-encrypted TCP/IP socket, and "hostnossl" is a plain TCP/IP socket.
# CIDR-ADDRESS specifies the set of hosts the record matches.
# an IP address and netmask in separate columns to specify the set of hosts.
# "host" records. In that case you will also need to make PostgreSQL listen
host all all 127.0.0.1/32 trust
host all all 192.168.0.0/24 md5
#host all all ::1/128 trust
-bash-3.2$ grep -r port postgresql.conf
port = 5432
# might also need to raise shared_buffers to support more connections.
# supported by the operating system:
# %r = remote host and port
-bash-3.2$ grep -r listen_address postgresql.conf
# "pg_ctl reload". Some settings, such as listen_addresses, require
listen_addresses = '*' # what IP address(es) to listen on;
-bash-3.2$ pg_ctl restart 重启服务，侦听本机的所有IP地址的5432端口
waiting for postmaster to shut down....LOG: logger shutting down
done
postmaster stopped
postmaster starting
-bash-3.2$ netstat -ntpl |grep 5432
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3684/postmaster
tcp 0 0 :::5432 :::* LISTEN 3684/postmaster
-bash-3.2$ createuser --help 查看相关的命令帮助
createuser creates a new PostgreSQL role.
Usage:
createuser [OPTION]... [ROLENAME]
Options:
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles
-l, --login role can login (default)
-L, --no-login role cannot login
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-c, --connection-limit=N connection limit for role (default: no limit)
-P, --pwprompt assign a password to new role
-E, --encrypted encrypt stored password
-N, --unencrypted do not encrypt stored password
-e, --echo show the commands being sent to the server
-q, --quiet don't write any messages
--help show this help, then exit
--version output version information, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as (not the one to create)
-W, --password prompt for password to connect
If one of -s, -S, -d, -D, -r, -R and ROLENAME is not specified, you will
be prompted interactively.
Report bugs to <>.
-bash-3.2$ createuser -P yang 创建一个数据库用户，并赋予管理员的权限
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE
下载phppgadmin包，并解压到/var/www/目录下，修改 apache配置文件如下：
[root@centos5 /]# grep -r Alias /etc/httpd/conf/httpd.conf |grep phppgadmin
Alias "/phppgadmin" "/var/www/phppgadmin"
[root@centos5 /]# service httpd restart
Stopping httpd: [ OK ]
Starting httpd: [ OK ]
我出现了php错误，原因是缺少php-pgsql包支持，在线安装即可！
[root@centos5 /]# yum -y install php-pgsql
Setting up Install Process
Parsing package install arguments
Resolving Dependencies
--> Running transaction check
---> Package php-pgsql.i386 0:5.1.6-23.2.el5_3 set to be updated
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
php-pgsql i386 5.1.6-23.2.el5_3 updates 67 k
Transaction Summary
=============================================================================
Install 1 Package(s)
Update 0 Package(s)
Remove 0 Package(s)
Total download size: 67 k
Downloading Packages:
(1/1): php-pgsql-5.1.6-23 100% |=========================| 67 kB 00:05
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: php-pgsql ######################### [1/1]
- 最新评论