postgresql安装及配置超详细教程

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install postgresql96 postgresql96-server
rpm -qa|grep postgre

/usr/pgsql-9.6/bin/postgresql96-setup initdb

systemctl enable postgresql-9.6

systemctl start postgresql-9.6
systemctl status postgresql-9.6

# - Connection Settings -

#listen_addresses = 'localhost' # what IP address(es) to listen on;
     # comma-separated list of addresses;
     # defaults to 'localhost'; use '*' for all
     # (change requires restart)
#port = 5432 # (change requires restart)

# This is used when logging to stderr: 
logging_collector = on # Enable capturing of stderr and csvlog 
     # into log files. Required to be on for 
     # csvlogs. 
     # (change requires restart) 

# These are only used if logging_collector is on: 
log_directory = 'pg_log' # directory where log files are written, 
     # can be absolute or relative to PGDATA 

log_filename = 'postgresql-%a.log' # log file name pattern, 
     # can include strftime() escapes 
log_truncate_on_rotation = on # If on, an existing log file of the 
     # same name as the new log file will be 
     # truncated rather than appended to. 
     # But such truncation only occurs on 
     # time-driven rotation, not on restarts 
     # or size-driven rotation. Default is 
     # off, meaning append to existing files 
     # in all cases. 
log_rotation_age = 1d # Automatic rotation of logfiles will 
     # happen after that time. 0 disables. 
log_rotation_size = 0 # Automatic rotation of logfiles will 

# - Memory - 
shared_buffers = 32MB # min 128kB 
     # (change requires restart) 

# actively intend to use prepared transactions. 
#work_mem = 1MB # min 64kB 

$ su postgres
bash-4.2$ psql
psql (9.6)
Type "help" for help.

postgres=#

# 创建数据库
create database testdb;
# 删除数据库
drop database testdb;
# 重命名数据库(该数据库必须没有活动的连接)
alter database testdb rename to newname;
# 以其他数据库为模板创建数据库（表结构、数据都会复制）
create database newdb template testdb;

# 将查询结果写入文件
/o /tmp/test.txt
select * from test;
# 列状显示
/w
# 再一次/o关闭写入，否则是连续写入的
/o
# 退出控制台
/q

# 建立新的数据库用户
create user zhangsan with password '123456';
# 为新用户建立数据库
create database testdb owner zhangsan;
# 把新建的数据库权限赋予新用户
grant all privileges on database testdb to zhangsan;

listen_addresses = '*' # what IP address(es) to listen on;

host all all 0.0.0.0/0 ident

# server：重启生效
systemctl restart postgresql-9.6
# client：命令行远程登录
psql -U zhangsan -d testdb -h 10.122.45.97 -p 5432

[root@sltkp3cbpch data]# psql -U zhangsan -d testdb -p 5432
psql: FATAL: Peer authentication failed for user "zhangsan"

# MAPNAME SYSTEM-USERNAME PG-USERNAME
map_zhangsan root zhangsan