Misc Notes

关于PostgreSQL

CentOS安装配置PostgreSQL

1. 下载离线包并安装

1
2
3
4
5
6
7
https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-8.2-x86_64/

postgresql13-13.1-1PGDG.rhel8.x86_64.rpm
postgresql13-contrib-13.1-1PGDG.rhel8.x86_64.rpm
postgresql13-devel-13.1-1PGDG.rhel8.x86_64.rpm
postgresql13-libs-13.1-1PGDG.rhel8.x86_64.rpm
postgresql13-server-13.1-1PGDG.rhel8.x86_64.rpm

依次安装。

2. 配置

初始化数据库

1
2
3
$ sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
# 主要配置文件
$ ls  /var/lib/pgsql/13/data/

配置数据库

1
2
3
# 修改默认账号密码
$ sudo su - postgres 
$ psql -c "alter user postgres with password 'newpassword'"

启用远程连接

1
2
$ sudo vi /var/lib/pgsql/13/data/postgresql.conf
listen_addresses = '*'
1
2
$ sudo vi /var/lib/pgsql/13/data/pg_hba.conf
host all all 0.0.0.0/0 md5

设置自动启动

1
2
3
$ sudo systemctl enable --now postgresql-13
# 重启服务
$ sudo systemctl restart postgresql-13

3. 连接

1
$ psql -U <dbuser> -h <serverip> -p 5432 <dbname>

4. 数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
# 创建数据库
create database xxx;
# 查看数据库
\l
# 查看表
\d
# 查看表结构
\d table;
# 切换数据库
\c db
# 修改所有者
alter database xxx owner to xxx;

5、用户操作

1
2
3
4
5
6
7
8
9
10
# 创建用户
create user xxx;
# 赋权
grant all privileges on database xxx to xxx;
# 只读权限
CREATE ROLE readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;
GRANT readaccess TO xxx;

6、备份及导入

1
2
3
4
# 备份
pg_dump -Fc -U xxx db > file.dmp
# 导入
pg_restore -d xxx file.dmp