X86-PostgreSQL 源码安装与配置-已测

一、简介

PostgreSQL 是一种非常复杂的对象-关系型数据库管理系统（ORDBMS），也是目前功能最强大，特性最丰富和

最复杂的自由软件数据库系统。有些特性甚至连商业数据库都不具备。这个起源于伯克利（BSD）的数据库研究

计划目前已经衍生成一项国际开发项目，并且有非常广泛的用户。

二、系统环境

系统平台：CentOS release 6.5 (Final)

PostgreSQL 版本：postgresql-9.6.1

防火墙已关闭/iptables: Firewall is not running.

SELINUX=disabled

三、源码安装

1. 下载PostgreSQL 源码包

# mkdir -p /tools

# cd /tools

# wget https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.gz

2. 解压源码包

# tar xf postgresql-9.6.1.tar.gz

3. 进入解压后的目录

# cd postgresql-9.6.1

4.安装依赖包

# yum -y install gcc*

# yum -y install readline-devel

5.增加用户设置密码

# adduser postgres

# passwd postgres

6.开始编译安装PostgreSQL 数据库。

# ./configure --prefix=/home/postgres/pgsql

# echo $?

# gmake

# echo $?

# gmake install

# echo $?

7.设置环境变量

# cd /home/postgres/ && ls

pgsql

# vi .bash_profile

把 PATH=$PATH:$HOME/bin

改成 PATH=$PATH:$HOME/bin:/home/postgres/pgsql/bin

保存退出。

让环境变量生效：

# source .bash_profile

8.初始化数据库

8.1新建数据目录

# mkdir /home/postgres/pgsql/data

8.2更改权限

# chown postgres:postgres /home/postgres/pgsql/data

8.3切换到postgres用户

# su - postgres

8.4 init db

$ /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data

到这里数据的初始化就完成

9.系统服务

9.1回到root用户

$ exit

9.2复制安装目录下的linux文件到/etc/init.d/

# cd /tools/postgresql-9.6.1

# cp contrib/start-scripts/linux /etc/init.d/postgresql

9.3修改/etc/init.d/postgresql

# vim /etc/init.d/postgresql

#! /bin/sh

# chkconfig: 2345 98 02

# description: PostgreSQL RDBMS

# This is an example of a start/stop script for SysV-style init, such

# as is used on Linux systems. You should edit some of the variables

# and maybe the 'echo' commands.

# Place this file at /etc/init.d/postgresql (or

# /etc/rc.d/init.d/postgresql) and make symlinks to

# /etc/rc.d/rc0.d/K02postgresql

# /etc/rc.d/rc1.d/K02postgresql

# /etc/rc.d/rc2.d/K02postgresql

# /etc/rc.d/rc3.d/S98postgresql

# /etc/rc.d/rc4.d/S98postgresql

# /etc/rc.d/rc5.d/S98postgresql

# Or, if you have chkconfig, simply:

# chkconfig --add postgresql

# Proper init scripts on Linux systems normally require setting lock

# and pid files under /var/run as well as reacting to network

# settings, so you should treat this with care.

# Original author: Ryan Kirkpatrick <pgsql@rkirkpat.net>

# contrib/start-scripts/linux

## EDIT FROM HERE

# Installation prefix

prefix=/home/postgres/pgsql

# Data directory

PGDATA="/home/postgres/pgsql/data"

# Who to run the postmaster as, usually "postgres". (NOT "root")

PGUSER=postgres

# Where to keep a log file

PGLOG="$PGDATA/serverlog"

# It's often a good idea to protect the postmaster from being killed by the

# OOM killer (which will tend to preferentially kill the postmaster because

# of the way it accounts for shared memory). Setting the OOM_SCORE_ADJ value

# to -1000 will disable OOM kill altogether. If you enable this, you probably

# want to compile PostgreSQL with "-DLINUX_OOM_SCORE_ADJ=0", so that

# individual backends can still be killed by the OOM killer.

#OOM_SCORE_ADJ=-1000

# Older Linux kernels may not have /proc/self/oom_score_adj, but instead

# /proc/self/oom_adj, which works similarly except the disable value is -17.

# For such a system, enable this and compile with "-DLINUX_OOM_ADJ=0".

#OOM_ADJ=-17

## STOP EDITING HERE

# The path that is to be used for the script

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster. (If you want the script to wait

# until the server has started, you could use "pg_ctl start -w" here.

# But without -w, pg_ctl adds no value.)

DAEMON="$prefix/bin/postmaster"

# What to use to shut down the postmaster

PGCTL="$prefix/bin/pg_ctl"

set -e

# Only start if we can find the postmaster.

test -x $DAEMON ||

{

echo "$DAEMON not found"

if [ "$1" = "stop" ]

then exit 0

else exit 5

}

# Parse command line parameters.

case $1 in

start)

echo -n "Starting PostgreSQL: "

test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj

test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj

su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1

echo "ok"

;;

stop)

echo -n "Stopping PostgreSQL: "

su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast"

echo "ok"

;;

restart)

echo -n "Restarting PostgreSQL: "

su - $PGUSER -c "$PGCTL stop -D '$PGDATA' -s -m fast -w"

test x"$OOM_SCORE_ADJ" != x && echo "$OOM_SCORE_ADJ" > /proc/self/oom_score_adj

test x"$OOM_ADJ" != x && echo "$OOM_ADJ" > /proc/self/oom_adj

su - $PGUSER -c "$DAEMON -D '$PGDATA' &" >>$PGLOG 2>&1

echo "ok"

;;

reload)

echo -n "Reload PostgreSQL: "

su - $PGUSER -c "$PGCTL reload -D '$PGDATA' -s"

echo "ok"

;;

status)

su - $PGUSER -c "$PGCTL status -D '$PGDATA'"

;;

# Print help

echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2

exit 1

;;

esac

exit 0

9.4添加执行权限

# chmod +x /etc/init.d/postgresql

9.5启动数据库

# /etc/init.d/postgresql start

Starting PostgreSQL: ok

9.6让数据库开机启动

# chkconfig --add postgresql

# chkconfig postgresql on

9.7创建数据库操作历史记录文件

# touch /home/postgres/pgsql/.pgsql_history

# chown postgres:postgres /home/postgres/pgsql/.pgsql_history

10.测试使用

# su - postgres

$ createdb test

$ psql test

psql (9.4.3)

Type "help" for help.

test=#

控制台命令

除了前面已经用到的password命令（设置密码）和q命令（退出）以外，控制台还提供一系列其他命令。

h：查看SQL命令的解释，比如h select。
?：查看psql命令列表。
l：列出所有数据库。
c [database_name]：连接其他数据库。
d：列出当前数据库的所有表格。
d [table_name]：列出某一张表格的结构。
du：列出所有用户。
e：打开文本编辑器。
conninfo：列出当前数据库和连接的信息。

数据库操作

基本的数据库操作，就是使用一般的SQL语言。

# 创建新表

CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

# 插入数据

INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

# 选择记录

SELECT * FROM user_tbl;

# 更新数据

UPDATE user_tbl set name = '李四' WHERE name = '张三';

# 删除记录

DELETE FROM user_tbl WHERE name = '李四' ;

# 添加栏位

ALTER TABLE user_tbl ADD email VARCHAR(40);

# 更新结构

ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

# 更名栏位

ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

# 删除栏位

ALTER TABLE user_tbl DROP COLUMN email;

# 表格更名

ALTER TABLE user_tbl RENAME TO backup_tbl;

# 删除表格

DROP TABLE IF EXISTS backup_tbl;