*****************************************************
1.编译安装
*****************************************************


/*******

make --version

gcc --version

perl --version

python --version




yum -y install wget gcc gcc-c++ readline-devel zlib-devel make   systemtap systemtap-sdt-devel \
perl perl-devel python python-devel tcl  tcl-devel    perl-ExtUtils-Embed \
sgml-common docbook stylesheets openjade  sgml-tools  xsltproc libxslt libxslt-devel \
libxml2 libxml2-devel zlib zlib-devel openssl openssl-devel   pam pam-devel bison flex libreadline6-devel 


******/


# wget https://ftp.postgresql.org/pub/source/v9.4.0/postgresql-9.4.0.tar.bz2

#mkdir -p /soft && cd /soft

#rz

# tar xf postgresql-9.4.4.tar.gz && cd postgresql-9.4.4



#./configure


./configure --prefix=/data/pg \
--with-pgport=5432  \
--with-perl --with-python --with-tcl  \
--with-openssl  --without-ldap  \
 --with-libxml  --with-libxslt    \
--enable-thread-safety    \
--with-wal-blocksize=16   \
--with-blocksize=16 \
-enable-dtrace  \
--enable-debug


#make && make install


#useradd postgres && passwd postgres

pg01!@#


#mkdir /data/pg/data -p  && chown -R postgres /data/pg/data


# su - postgres

$ vi  .bash_profile

# postgres
#Postgresql端口
PGPORT=5432

#Postgresql数据目录
PGDATA=/data/pg/data

export PGPORT PGDATA 

#所使用的语言
export LANG=en_US.utf8

#Postgresql 安装目录
export PGHOME=/data/pg

#Postgresql 连接库文件
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`

#将Postgresql的命令行添加到 PATH 环境变量
export PATH=$PGHOME/bin:$PATH

#Postgresql的 man 手册
export MANPATH=$PGHOME/share/man:$MANPATH

#Postgresql的默认用户
export PGUSER=postgres

#Postgresql默认主机地址
export PGHOST=127.0.0.1

#默认的数据库名
export PGDATABASE=postgres



#source .bash_profile 


#echo $PATH


#执行数据库初始化脚本
$/data/pg/bin/initdb --encoding=utf8 -D /data/pg/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.utf8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/pg/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /data/pg/data/base/1 ... ok
initializing pg_authid ... 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
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A,or
--auth-local and --auth-host,the next time you run initdb.

Success. You can now start the database server using:

    /data/pg/bin/postgres -D /data/pg/data
or
    /data/pg/bin/pg_ctl -D /data/pg/data -l logfile start



$退出变更登录
exit


#复制Postgresql执行脚本
cp /soft/postgresql-9.4.4/contrib/start-scripts/linux  /etc/init.d/postgresql

#增加执行权限
chmod +x /etc/init.d/postgresql

#编辑Postgresql执行脚本,确定以下参数或修改

vi /etc/init.d/postgresql

# Installation prefix
prefix=/data/pg

# Data directory
PGDATA="/data/pg/data"

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

# Where to keep a log file
PGLOG="$PGDATA/serverlog"


#编辑配置文件，配置可访问数据库的网络地址
(注意别忘了去掉#listen_addresses=前面的#)

#vi /data/pg/data/postgresql.conf
59 listen_addresses = '*' 


#启动或停止PG

$pg_ctl start 

$pg_ctl stop


# 设置开机自动启动服务
chkconfig postgresql on



*****************************************************
2.数据库配置
*****************************************************

--2.1 设置远程登录数据库

#vi /data/pg/data/postgresql.conf

59 listen_addresses = '*' 
67 superuser_reserved_connections = 3 
91 password_encryption = on
101 tcp_keepalives_idle = 60 
115 shared_buffers = 6000MB 
126 work_mem = 4MB
151 vacuum_cost_delay = 10ms   
159 bgwriter_delay = 10ms 
180 wal_sync_method = fsync
190 wal_buffers = 128000kB 
192 wal_writer_delay = 20ms 
200 checkpoint_timeout = 30min
201 checkpoint_completion_target = 0.9 
321 logging_collector = on   
327 log_directory = 'pg_log'                
329 log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' 
333 log_truncate_on_rotation = off          
341 log_rotation_age = 0            
343 log_rotation_size = 50MB  
395 log_min_duration_statement = 2000ms  #跟踪哪些sql执行时间长
434 log_statement = 'ddl'   #记录DDL语句,一般用于跟踪数据库中的危险操作
407 log_checkpoints = on   #记录每一次checkpoint到日志中.
433 log_lock_waits = on  #记录锁等待超过1秒的操作,一般用于排查业务逻辑上的问题 
558 deadlock_timeout = 1s
451 track_activity_query_size = 2048  #显示更长的sql.
468 autovacuum = on  #log_autovacuum_min_duration = 0记录所有的autovacuum操作.
470 log_autovacuum_min_duration = 0







#vi /data/pg/data/pg_hba.conf

#to allow your client visiting postgresql server
host all all 0.0.0.0 0.0.0.0 md5


重启数据库.

pg_ctl -m "immediate" restart


--2.2 以postgres用户登录数据库，修改postgres用户的数据库密码
psql -U postgres
postgres=# ALTER USER postgres PASSWORD 'password';
postgres=# \q


--2.3 远程连接
psql -h 192.168.50.110  -d postgres -U postgres 
Password for user postgres: 



*****************************************************
3.管理配置数据库
*****************************************************



1.登录postgre sql数据库
# psql -U postgres -h 127.0.0.1

2.创建新用户wind，但不给建数据库的权限
postgres=#select * from pg_user;

postgres=# create user "wind" with password 'wind' nocreatedb;
//注意用户名要用双引号，以区分大小写，密码不用


3.建立数据库，并指定所有者
postgres=# create database "wind" with owner="wind";


4.在外部命令行的管理命令
# -u postgres createuser -D -P test1

//-D该用户没有创建数据库的权利，-P提示输入密码,选择管理类型y/n

# -u postgres createdb -O test1 db1
//-O设定所有者为test1





*****************************************************
4.环境变量
*****************************************************
---postgres用户

vi ~/.bash_profile 


alias psqla="psql -U postgres -h 127.0.0.1"

source ~/.bash_profile 

---root用户

vi ~/.bash_profile 

alias sudop="su - postgres"

source ~/.bash_profile