YUM安装postgreSQL与基本配置
# YUM安装
# 下载源
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装postgres
yum -y install postgresql12 postgresql12-server postgresql12-contrib
# 修改数据库数据目录
默认安装目录在： /var/lib/pgsql/12/data/，修改数据目录为：/data/pgsql/data/
sed -i 's#^Environment=PGDATA.*#Environment=PGDATA=/data/pgsql/data/#g' /usr/lib/systemd/system/postgresql-12.service
systemctl daemon-reload
2
# 初始化数据库
/usr/pgsql-12/bin/postgresql-12-setup initdb
# 启动数据库
chown -R postgres:postgres /data/pgsql
systemctl enable postgresql-12.service --now
2
# 创建数据库用户
非交互式执行
su - postgres -c "psql -d postgres -c \"alter user postgres with password 'yfktest';\""
alter user postgres with password 'yfktest';
或者切换postgres 普通用户执行
su - postgres
psql
create user yfk with password '123456';
create database testdb owner yfk;
grant all privileges on database testdb to yfk;
2
3
4
5
6
# 配置文件修改权限：pg_hba.conf可远程登录
网段：192.168.5.186/24
cp /data/pgsql/data/pg_hba.conf{,_bak}
cat >/data/pgsql/data/pg_hba.conf<<OPO
local all all md5
host all all 0.0.0.0/0 md5
host replication replica 192.168.5.186/24 md5
OPO
chown -R postgres:postgres /data/pgsql
sed -i "s#\#listen_addresses.*#listen_addresses='0.0.0.0'#g" /data/pgsql/data/postgresql.conf
sed -i 's#max_connections = 100#max_connections = 1000#g' /data/pgsql/data/postgresql.conf
systemctl restart postgresql-12.service
2
3
4
5
6
7
8
9
10
11
12
13
# 数据库的操作
- 创建数据库
create database testdb;
CREATE DATABASE "testdb" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8';
- 删除数据库
drop database testdb;
- 删除用户连接test库pid
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='test' AND pid<>pg_backend_pid();
- 重命名数据库(该数据库必须没有活动的连接)
alter database testdb rename to newname;
- 以其他数据库为模板创建数据库（表结构、数据都会复制，模板库没有活动的连接）
create database newdb template testdb;
- 建立新的数据库用户
create user yfk with password '123456';
- 为新用户建立数据库
create database testdb owner yfk;
- 把新建的数据库权限赋予新用户
grant all privileges on database testdb to yfk;
- 修改数据库用户
alter user replica with password 'replica@pgsql';
- 创建只读用户
--创建用户
create user [username] password '[password]';
--更新只读权限
alter user [username] set default_transaction_read_only = on;
--设置可操作的数据库
grant all on database [database_name] to [username];
--设置可操作的模式和权限
grant select on all tables in schema [schema_name] to [username];
2
3
4
5
6
7
8
9
10
11
# 常用交互式命令
| 参数 | 说明 |
|---|---|
| \h | 查看所有sql命令，\h select 等可以查看具体命令 |
| ? | 查看所有psql命令 |
| \d | 查看当前数据库所有表 |
| \d | [tablename] 查看具体的表结构 |
| \du | 查看所有用户 |
| \l | 查看所有数据库 |
| \e | 打开文本编辑器 |
| \c | 非交互模式 |
# 命令行的各个参数解释说明：
| 参数 | 说明 |
|---|---|
| -U : username | 用户名，默认值postgres |
| -d : dbname | 要连接的数据库名，默认值postgres。如果单指定-U，没指定-d参数，则默认访问与用户名名称相同的数据库。 |
| -h : hostname | 主机名，默认值localhost |
| -p : port | 端口号，默认值5432 |
# 常见的四种身份验证方式
trust： 凡是能连接到服务器的，都是可信任的。只需要提供数据库用户名，可以没有对应的操作系统同名用户；
password 和 md5： 对于外部访问，需要提供 psql 用户名和密码。对于本地连接，提供 psql 用户名密码之外，还需要有操作系统访问权（用操作系统同名用户验证）。password 和 md5 的区别就是外部访问时传输的密码是否用 md5 加密；
ident： 对于外部访问，从 ident 服务器获得客户端操作系统用户名，然后把操作系统作为数据库用户名进行登录；对于本地连接，实际上使用了peer；
peer： 通过客户端操作系统内核来获取当前系统登录的用户名，并作为psql用户名进行登录。
# 登录
PostgreSQL登陆默认是peer，不需要验证用户密码即可进入psql相关数据库， 但前提是必须切换用户登陆。类似于最开始执行的su postgres;psql一样。
记录，扩展
- 配置本地用户无密码登录：
先创建用户，修改配置
useradd yfk
tail -2 /var/lib/pgsql/12/data/pg_ident.conf
map_durant root yfk
tail -5 /var/lib/pgsql/12/data/pg_hba.conf
local all all peer map=map_yfk
host all all 0.0.0.0/0 md5
host replication replica 192.168.5.186/24 md5
2
3
4
5
6
7
8
9
# 个人存储下载地址。。。
#yum包
postgis-12.14.zip
2
3