pgsql 搭建

1.yum安装

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql12-server

2.启动pgsql

/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

3.修改数据目录

vim /usr/lib/systemd/system/postgresql-12.service
Environment=PGDATA=/usr/local/pgsql/12/data/

mkdir -pv  /usr/local/pgsql/12/
mv /var/lib/pgsql/12/data  /usr/local/pgsql/12
systemctl daemon-reload
systemctl restart postgresql-12

4.配置环境变量

vim /etc/profile
export PG_HOME=/usr/pgsql-12
export PGDATA=/usr/local/pgsql/12/data
export PATH=$PG_HOME/bin:$PATH
export LD_LIBRARY_PATH=$PG_HOME/lib
export MANPATH=$PG_HOME/share/man:$MANPATH

source  /etc/profile

5.修改配置

vim $PGDATA/postgresql.conf
#数据库服务监听IP地址，默认只监听localhost，外部无法访问。修改为 *，允许外部访问数据库服务
listen_addresses = '*'
#数据库服务监听端口
port = 5432
#默认100，连接数限制根据实际业务需求修改
max_connections = 5000
vim $PGDATA/pg_hba.conf
#末尾添加下面类容，不限制任何主机并允许远程登录：
host    all             all             0.0.0.0/0               md5
systemctl  restart postgresql-12

6.数据库操作

su - postgres
psql
alter user postgres with encrypted password 'password';
create database "my-database";
create role my_username with login;
alter user my_username with encrypted password 'password';
grant all on database "my-database" to my_username;
\c my-database
grant all on all tables in schema public to my_username; 
grant usage, select on all sequences in schema public to my_username;