安装Postgresql数据库过程记录

官网下载Postgresql源程序 解压缩，编译

$ cd /usr/local/src/tarbag
$ tar xvfz postgresql-8.4.1.tar.gz -C /usr/local/src
$ cd /usr/local/src/postgresql-8.4.1
$ ./configure
$gmake
$gmake install
$adduser portal（创建一个portal用户）
$mkdir /usr/local/pgsql/data
$chown portal /usr/local/pgsql/data
$su portal
#/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data (初始化数据库，在data目录下生成所需文件)
#/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
加入环境变量到.bash_profile

$ cd /usr/local/pgsql
$ vi .bash_profile
在文件最后追加
export PATH="$PATH":/usr/local/ pgsql /bin
export POSTGRES_HOME=/usr/local/ pgsql
export PGLIB=$POSTGRES_HOME/lib
export PGDATA=$POSTGRES_HOME/data
export MANPATH="$MANPATH":$POSTGRES_HOME/man
export LD_LIBRARY_PATH="$LD_LIBRARY_PATH":"$PGLIB"

重新读入，让设置成功
$ source ~/.bash_profile

配置监听器postgresql.conf

$ mkdir /usr/local/postgres/data

将Data目录的权限设为0700。

$ chmod 0700 /usr/local/postgres/data
$ chown portal /usr/local/postgres/data

# su - portal
# initdb -D /usr/local/postgres/data

# cd /usr/local/postgres/data
# touch /usr/local/postgres/data/postgresql.conf

修改内容

listen_addresses = 'localhost,127.0.0.1,192.168.50.50'
port = 5432
password_encryption = on

配置 pg_hba.conf，加入访问数据库的ip

#vi pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

host    all             all             all             trust

加入开机启动脚本和服务
$ su root
# tar xvfz postgresql-8.3.7.tar.gz
# cd postgresql-8.3.7
# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql
# chmod a+x /etc/rc.d/init.d/postgresql

#/sbin/chkconfig --add postgresql

#chkconfig postgresql on

#service postgresql [start|stop] //可以使用这条命令了

创建数据库和用户

/usr/local/pgsql/bin/createdb ent-portal-db
/usr/local/pgsql/bin/psql ent-portal-db
sql > # CREATE USER scc WITH password 'scc';
     CREATE ROLE
安装客户端软件

推荐官网下载Pg-admin III，需要注册，可以免费试用60天。

常见问题

问题1：could not bind IPv4 socket

WARNING:  could not create listen socket for "XXX.XXX.XXX.XXX"

LOG:  could not bind IPv4 socket: Cannot assign requested address

HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.

 解答

# ls ?a /tmp

删除所有postgre的.s.*临时文件，重启服务

2 database Access denied

解答：配置 pg_hba.conf，加入访问数据库的ip

#vi pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

host    all             all             all             trust

3. 如何修改 PostgreSQL root密码?

$ /usr/local/pgsql/bin/psql postgres postgres

Password: (oldpassword)

# ALTER USER postgres WITH PASSWORD 'tmppassword';

$ /usr/local/pgsql/bin/psql postgres postgres

Password: (tmppassword)

4. 如何创建 PostgreSQL 用户?

There are two methods in which you can create user.

Method 1: Creating the user in the PSQL prompt, with CREATE USER command.

# CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE

Method 2: Creating the user in the shell prompt, with createuser command.

$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE