PostgreSQL

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql16-server
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb
sudo systemctl enable postgresql-16
sudo systemctl start postgresql-16

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql12-server
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

# 安装完成后，会有一个默认账号密码：postgres/postgres，权限是超级管理员，可以进行登录；

# rpm包安装
https://yum.postgresql.org/12/redhat/rhel-7-x86_64/repoview/postgresqldbserver12.group.html
wget https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-server-12.20-2PGDG.rhel7.x86_64.rpm
wget https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.20-2PGDG.rhel7.x86_64.rpm
wget https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-12.20-2PGDG.rhel7.x86_64.rpm
wget https://yum.postgresql.org/12/redhat/rhel-7-x86_64/postgresql12-libs-12.20-2PGDG.rhel7.x86_64.rpm

# 二进制安装
wget https://get.enterprisedb.com/postgresql/postgresql-10.23-1-linux-x64-binaries.tar.gz
useradd postgres
password postgres
# 指定安装到/opt目录下
tar xvf postgresql-10.23-1-linux-x64-binaries.tar.gz -C /opt
# 设置目录权限
mkdir -p /opt/pgsql/data
mkdir -p /opt/pgsql/log
chown -R postgres.postgres /opt/pgsql
# 后续的设置使用前面新建的postgres用户操作
su - postgres
# 环境变量
vim ~/.bash_profile
# 增加如下内容
export PG_HOME=/opt/pgsql
export PGDATA=${PG_HOME}/data
export PATH=${PG_HOME}/bin:$PATH
# 重新载入环境变量
source ~/.bash_profile

# 初始化数据库
# 使用默认地址/opt/pgsql/data初始化
${PG_HOME}/bin/initdb -E utf8

#### 设置监听IP和Port
vim ${PGDATA}/postgresql.conf

# 设置监听所有IP(这是一个非常宽松的限制，生产环境慎用)
listen_addresses = '*'
port = 5432

#listen_addresses = '192.168.0.0/24'

#### 设置数据库白名单
vi ${PGDATA}/pg_hba.conf

# 增加如下一条记录，允许用户密码模式(这是一个非常宽松的限制，生产环境慎用)
host    all     all     0.0.0.0/0   md5

# 启动停止命令
${PG_HOME}/bin/pg_ctl start

# 停止
${PG_HOME}/bin/pg_ctl stop

# 重新载入配置文件(不需要重启)
${PG_HOME}/bin/pg_ctl reload

# 命令行连接
# 使用postgres用户作为超级用户登录
psql -U postgres -d postgres

# 新建DB和USER
# 新建一个测试用用户
create user demo with password 'demo';

# 新建一个测试用DB并分配给指定用户
create database demo with encoding='utf8' owner=demo;

# 修改用户密码
alter user demo password 'xxx';

# 远程连接
psql -h <服务器IP或域名> -U <用户名> -d <数据库名> -p <端口号>
这里是具体的参数解释：
-h 用于指定服务器的IP地址或域名。
-U 用于指定数据库用户。
-d 用于指定要连接的数据库名。
-p 用于指定服务器上PostgreSQL监听的端口号

user add postgres
passwd postgres

wget https://ftp.postgresql.org/pub/source/v12.20/postgresql-12.20.tar.gz

yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility"
yum install -y bison flex readline* zlib-devel gcc* gmake

mkdir -p /usr/local/pg12
mkdir -p /pgdata/12/data
chown -R postgres. /pgdata
chown -R postgres. /usr/local/pg12
chmod 700 /pgdata/12/data -R

vim /etc/sysctl.conf 

kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

sysctl -p

# 建议关闭numa，设置IO策略未deadline（机械）或者noop（SSD）
numactl --show
dmesg | grep -i numa
cat /proc/cmdline
# 在bios中将numa关闭：
grubby --update-kernel=/boot/vmlinuz-$(uname -r) --args="numa=off"
# 或编辑/etc/default/grub，在在GRUB_CMDLINE_LINUX一栏添加numa=off；
# 重新生成 /etc/grub2.cfg 配置文件：
grub2-mkconfig -o /etc/grub2.cfg
# 重启服务器生效。
# 如果发现已经启用了numa又不能重启，也可以调整OS参数去修改
vim /etc/sysctl.conf
vm.zone_reclaim_mode = 0
kernel.numa_balancing=0
numactl --interleave=all pg_ctl start -D $PGDATA
# 查看IO策略，此处以xvda为例：
cat /sys/block/xvda/queue/scheduler
# 显示为deadline或noop都可以（算法不同，noop更倾向于饿死读而优先写，适合SSD）。避免使用cfq。
echo noop > /sys/block/xvda/queue/scheduler
# 重启后会失效，如果想持久化则需要将命令放在/etc/rc.local中，或者直接修改grub：
vim /etc/default/grub
# 在GRUB_CMDLINE_LINUX一栏添加elevator=noop，再重新生成grub2.cfg配置文件，方法类似上面关闭numa 或者：
grubby --update-kernel=ALL --args="elevator=noop"
# 重启生效。

tar -xf postgresql-12.20.tar.gz
cd postgresql-12.20

./configure --prefix=/usr/local/pg12 --with-pgport=5432

#全部可用功能编译
gmake world

#把所有编译好的进行安装（建目录+拷贝）
gmake install -world

su postgres
cd ~
vim .bash_profile

export PGDATA=/pgdata/12/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg12
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres

source .bash_profile

psql --version

su - postgres
initdb -D /pgdata/12/data -w
initdb -A md5 -D $PGDATA -E utf8 --locale=C -W    # 生产库建议
-A 是加密方式
-D 是数据目录
-E 是字符集 （默认即utf8）
# 执行之后会提示输入密码。

# 启停数据库
# 可以按照初始化打印的结果来启动，完整命令：
pg_ctl -D /pgdata/12/data/ -l logfile start
# 如果配了环境变量也可以直接pg_ctl start来启动，因为pgdata能识别到；
# 重启：pg_ctl restart
# 关闭：pg_ctl stop
# 通过pg_ctl --help可以查看完整的选项，
# 其中关闭和重启选项中的shutdown modes包括smart、fast、immediate；
# smart是最安全的关闭方法，等待所有客户端连接关闭之后才关闭；
# fast是用的生产中最多的，自动杀掉连接，回滚未完成事务；
# immediate相当于kill -9；
# 对应的语句：
pg_ctl -D /pgdata/12/data/  stop -ms
pg_ctl -D /pgdata/12/data/  stop -mf
pg_ctl -D /pgdata/12/data/  stop -mi
# 此外启动数据库还可以通过脚本启动，需要切换回root用户执行
/opt/postgresql-12.12/contrib/start-scripts/linux
# 使用此脚本需要修改脚本中prefix、PGDATA等内容。

psql
\l
create database test1 with owner=postgres encoding='UTF8' template=template0 lc_collate='C' lc_ctype='C' allow_connections=true;
drop database test1;
\c test1;
create table t1(id int);
insert into t1 values(1);
select * from t1;
# 其中\l查看所有库，\c使用某库，\d或\dt查看当前库下的表。

# 远程连接还需配置实力级别的访问控制：
cd $PGDATA
vim pg_hba.conf
host DBname username 10.10.10.0/24 md5
host    all             all             0.0.0.0/0               md5

vim postgresql.conf
listen_addresses = '0.0.0.0'
# 重启后生效：
pg_ctl restart -mf

# 用超管postgres以TCP/IP方式登录：
psql -d postgres -h 10.10.10.51 -p 5432 -U postgres
# 优化配置
postgresql.conf配置文件，调整以下参数（优化连接数和性能的）
max_connections值建议设置成1000以上
shared_buffers值建议设置成操作系统总内存的1/4
work_mem值建议设置成8MB
cd /var/lib/pgsql/14/data
vim postgresql.conf
编辑postgresql.conf文件，追加以下参数（根据实际追加）：
log_filename='postgres-%d.log'
log_rotation_age=1440
log_rotation_size=2097150
log_truncate_on_rotation=on
log_destination = 'stderr'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
log_statement = 'ddl'
log_duration=off
lc_messages='C'
log_min_duration_statement = 2000
max_connections=1500
shared_buffers=4096MB
work_mem=8MB
listen_addresses='*'

# 查看psql命令行客户端相关命令：
\?

# 查看sql相关语句：
\h

# 列举库名，相当于show databases：
\l
# 其中默认库有postgres、template0、template1；

# 切换到某库，相当于use
\c db1

# 查看库下的表：
\d
\dt

# 看t1表结构：
\d t1

# 查看某语句的帮助，例如查看create user语句的帮助：
\help create user

# 查看用户权限信息：
\du

# PG中元数据存在pg_开头的表中，如：
select * from pg_tables where schemaname not in ('pg_catalog','information_schema');

# 如将查询竖着显示，先执行\x，再执行查询；类似MySQL中的\G；
\x
# dbeaver连接驱动使用postgresql-42.5.0.jar即可
# nextcloud-postgresql
psql -d nextcloud -h 172.20.223.208 -p 5432 -U nextcloud  lDLYPk35FvF7P48pSrjRw16O5W7NlXJ1wHWMqlXiB4m4miKV9c

pg_dump -U nextcloud -h localhost -p 5432 -F c -b -v -C -f /tmp/nextcloud.bakcup nextcloud
pg_restore -U postgres -h localhost -p 5432 -C -d postgres -v /var/lib/postgresql/data/bak//nextcloud.bakcup 

GRANT ALL PRIVILEGES ON DATABASE nextcloud TO postgres;
GRANT ALL PRIVILEGES ON TABLE oc_migrations TO postgres;

GRANT ALL PRIVILEGES ON ALL DATABASES TO postgres;

REVOKE CONNECT ON DATABASE dbname FROM PUBLIC;
GRANT CONNECT ON DATABASE dbname TO PUBLIC;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO postgres;