postgresql安装及配置超详细教程_PostgreSQL

1. 安装
初始化数据库
2. PostgrepSQL的简单配置
3. 数据库的基础操作
4. 认证登录

1. 安装

根据业务需求选择版本，官网下载

									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

初始化数据库

执行完初始化任务之后，postgresql 会自动创建和生成两个用户和一个数据库：

linux 系统用户 postgres：管理数据库的系统用户；
密码由于是默认生成的，需要在系统中修改一下，$passwd postgres
数据库用户 postgres：数据库超级管理员此
用户默认数据库为postgres

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

设置成 centos7 开机启动服务

1	`systemctl enable postgresql-9.6`

启动 postgresql 服务

1 2	`systemctl start postgresql-9.6` `systemctl status postgresql-9.6`

2. PostgrepSQL的简单配置

pgsql9.6配置文件位置默认在：/var/lib/pgsql/9.6/data/postgresql.conf

2.1 修改监听的ip和端口

监听IP使用localhost时，只能通过127.0.0.1访问数据库；
如果需要通过其他远程地址访问PostgreSQL，可以使用“,”作为分隔符，把IP地址添加到listen_addresses后，或者使用“*”，让所有IP都可以访问数据库。

注意：这里只是开启数据库的远程访问权限，具体是否能够进行远程登录，还需要依据pg_hba.conf的认证配置，详细内容见下节。

									# - 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)

2.2 修改数据库log相关的参数

日志收集，一般是打开的

									# 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

2.3 内存参数

共享内存的大小，用于共享数据块。如果你的机器上有足够的内存，可以把这个参数改的大一些，这样数据库就可以缓存更多的数据块，当读取数据时，就可以从共享内存中读，而不需要再从文件上去读取。

									# - Memory - 

									shared_buffers = 32MB # min 128kB 

									     # (change requires restart)

单个SQL执行时，排序、hash json所用的内存，SQL运行完后，内存就释放了。

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

PostgreSQL安装完成后，可以主要修改以下两个主要内存参数：
shared_buffer：共享内存的大小，主要用于共享数据块，默认是128MB；
如果服务器内存有富余，可以把这个参数适当改大一些，这样数据库就可以缓存更多的数据块，当读取数据时，就可以从共享内存中读取，而不需要去文件读取。

work_mem：单个SQL执行时，排序、hash join所使用的内存，SQL运行完成后，内存就释放了，默认是4MB；
增加这个参数，可以提高排序操作的速度。

3. 数据库的基础操作

3.1 连接数据库控制台

如果想连接到数据库，需要切换到postgres用户下（默认的认证配置前提下）

在postgres用户下连接数据库，是不需要密码的。

切换 postgres 用户后，提示符变成 -bash-4.2$
使用psql连接到数据库控制台，此时系统提示符变为'postgres=#'

									$ su postgres

									bash-4.2$ psql

									psql (9.6)

									Type "help" for help.

									postgres=#

3.2 一些常用控制台命令

命令	作用
\h	查看所有sql命令，\h select 等可以查看具体命令
?	查看所有psql命令
\d	查看当前数据库所有表
\d	[tablename] 查看具体的表结构
\du	查看所有用户
\l	查看所有数据库
\e	打开文本编辑器

3.3 SQL控制台操作语句

数据库创建与修改

									# 创建数据库

									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;

4. 认证登录

认证权限配置文件： /var/lib/pgsql/9.6/data/pg_hba.conf

命令行的各个参数解释说明：

-U username 用户名，默认值postgres
-d dbname 要连接的数据库名，默认值postgres。如果单指定-U，没指定-d参数，则默认访问与用户名名称相同的数据库。
-h hostname 主机名，默认值localhost
-p port 端口号，默认值5432

4.1 认证方式

常见的四种身份验证方式

trust：凡是能连接到服务器的，都是可信任的。只需要提供数据库用户名，可以没有对应的操作系统同名用户；
password 和 md5：对于外部访问，需要提供 psql 用户名和密码。对于本地连接，提供 psql 用户名密码之外，还需要有操作系统访问权（用操作系统同名用户验证）。password 和 md5 的区别就是外部访问时传输的密码是否用 md5 加密；
ident：对于外部访问，从 ident 服务器获得客户端操作系统用户名，然后把操作系统作为数据库用户名进行登录；对于本地连接，实际上使用了peer；
peer：通过客户端操作系统内核来获取当前系统登录的用户名，并作为psql用户名进行登录。

4.2 远程登录

postgresql.conf

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

pg_hba.conf
所有的用户通过任意ip都可以通过md5（密码）的方式登陆PostgreSQL，配置如下：

1	`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