# - 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
$ 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;