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