一、简介
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 fi }
# 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;