netnr / install-PostgreSQL.sh
2021-06-02 14:50
Linux 安装 PostgreSQL
# 选择版本 (以 12 为例)
https://www.postgresql.org/download/linux/redhat/

dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm    # Install the repository RPM:
dnf -qy module disable postgresql   # 禁用内置的 PostgreSQL 模块
dnf install -y postgresql12-server  # 安装 PostgreSQL

# 初始化数据库并启用自动启动(可选)
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

# 修改密码
su postgres # 切换用户,PostgresSQL 安装后会自动创建 postgres 用户,无密码
psql -U postgres
alter user postgres with encrypted password 'Abc123....';

# 开启远程访问
vi /var/lib/pgsql/12/data/postgresql.conf   # 取消 listen_addresses 的注释,将参数值改为 *
vi /var/lib/pgsql/12/data/pg_hba.conf       # 添加行或修改行
host all all 127.0.0.1/32 trust # 本地信任访问,密码可有可无
host all all 0.0.0.0/0 md5  # 允许外网访问

systemctl restart postgresql-12 # 重启服务生效

# help
https://www.cnblogs.com/zhi-leaf/p/11432054.html



# 创建用户
create user tmpuser with password 'Abc123....';

# 创建表空间并指定用户(路径先创建且赋予权限)	chown -R postgres /package/pgsql/
CREATE TABLESPACE ts_tmp OWNER tmpuser LOCATION '/package/pgsql'

# 查询表空间、大小、路径
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) tsSize, pg_tablespace_location(oid) tsLocation FROM pg_tablespace;

# 删除表空间(须先删除表)
DROP TABLESPACE IF EXISTS ts_tmp;

# 删除表
DROP TABLE IF EXISTS tablename;

# 创建数据库指定用户、编码、空间
CREATE DATABASE tmpdb WITH OWNER = tmpuser ENCODING = utF8 TABLESPACE = ts_tmp;

# 用户授权
GRANT ALL PRIVILEGES ON DATABASE tmpdb TO tmpuser;