# docker 安装 12.8
docker run --restart=always --name=pg \
    -e POSTGRES_PASSWORD=<MySecretPassword> \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v /package/pgdb:/var/lib/postgresql/data \
    -p 5432:5432 -d postgres:12.8
    
# help
https://hub.docker.com/_/postgres

# 选择版本 （以 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


# CentOS8 ref: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-8
dnf module list postgresql
dnf module enable postgresql:15
dnf install postgresql-server

postgresql-setup --initdb

systemctl start postgresql
systemctl enable postgresql

sudo -i -u postgres
psql
ALTER USER postgres PASSWORD 'Abc123.'; # 改密


# 创建用户
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;