只导出数据，带insert和字段

sudo -u postgres pg_dump -U postgres -d $1 --column-inserts --data-only -t 表名1  -t 表名2   -f $2.sql

导入

#!/bin/bash
sudo -u postgres psql -U postgres -d $1 < $2

不区分大小写索引建立

drop index ems.sys_user_user_name;
create unique index   cclv_player_user_name     on ems.sys_user USING btree (LOWER(user_name));

字段不区分大小写

sudo tee /etc/yum.repos.d/pgdg-redhat-all.repo > /dev/null <<EOF
[pgdg-common]
name=PostgreSQL common RPMs for RHEL / Rocky \$releasever - \$basearch
baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-\$releasever-\$basearch
enabled=1
gpgcheck=0

[pgdg14]
name=PostgreSQL 14 for RHEL / Rocky \$releasever - \$basearch
baseurl=https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-\$releasever-\$basearch
enabled=1
gpgcheck=0
EOF




sudo dnf makecache -y

sudo tee /etc/yum.repos.d/pgdg-redhat-all.repo > /dev/null <<EOF
[pgdg-common]
name=PostgreSQL common RPMs for RHEL / Rocky $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1

[pgdg14]
name=PostgreSQL 14 for RHEL / Rocky $releasever - $basearch
baseurl=https://download.postgresql.org/pub/repos/yum/14/redhat/rhel-$releasever-$basearch
enabled=1
gpgcheck=1
EOF

sudo dnf install -y postgresql14-contrib

sudo -u postgres psql -d cclv_data -c "CREATE EXTENSION IF NOT EXISTS citext;"

ALTER TABLE ems.sys_user 
ALTER COLUMN user_name TYPE citext USING user_name::citext;

excel增加表分区SQL

excel文件：表分区创建.xlsx

添加表分区

#删除旧索引 并重命名
ALTER TABLE xdata_klines_5m DROP CONSTRAINT pk_okx_candles_1m;
ALTER TABLE xdata_klines_5m RENAME TO xdata_klines_5m_00;

create table xdata_klines_5m (
   b_id                 INT2                 not null default 0,
   t_uid                INT8                 not null default 0,
   o                    DECIMAL(20,10)       null default 0.00,
   h                    DECIMAL(20,10)       null default 0.00,
   l                    DECIMAL(20,10)       null default 0.00,
   c                    DECIMAL(20,10)       null default 0.00,
   vol                  DECIMAL(20,2)        null default 0.00,
   vol_b                DECIMAL(20,2)        null default 0.00,
   vol_u                DECIMAL(20,2)        null default 0.00,
   ts                   TIMESTAMP            null,
   constraint PK_OKX_KLINES_5M primary key (b_id, t_uid)
) partition by range (t_uid);

ALTER TABLE xdata_klines_5m  ATTACH PARTITION xdata_klines_5m_00 FOR VALUES FROM (MINVALUE) TO (8418861);

create table xdata_klines_5m_04 partition of xdata_klines_5m FOR VALUES FROM (8500321) TO (8629921);
create table xdata_klines_5m_05 partition of xdata_klines_5m FOR VALUES FROM (8500321) TO (8629921);
create table xdata_klines_5m_06 partition of xdata_klines_5m FOR VALUES FROM (8500321) TO (8629921);

后期增加表分区

ALTER TABLE xdata_klines_5m ATTACH PARTITION xdata_klines_5m_01 FOR VALUES FROM (MINVALUE) TO (8241121);
ALTER TABLE xdata_klines_5m ATTACH PARTITION xdata_klines_5m_02 FOR VALUES FROM (8241121) TO (8500321);
ALTER TABLE xdata_klines_5m ATTACH PARTITION xdata_klines_5m_03 FOR VALUES FROM (8500321) TO (8629921);

删除表分区

ALTER TABLE xdata_klines_5m DETACH PARTITION xdata_klines_5m_02;
ALTER TABLE xdata_klines_5m DETACH PARTITION xdata_klines_5m_03;

chown postgres:postgres /data/pgsql/backup/

安装部署

sudo dnf update
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql14-server
#初始化
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl start postgresql-14

#修改数据库路径
sudo systemctl stop postgresql-14
sudo mv /var/lib/pgsql/14/data /data/pgsql
sudo ln -s /data/pgsql /var/lib/pgsql/14/data

#服务器IP绑定
#vi /data/pgsql/postgresql.conf 
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /data/pgsql/postgresql.conf 

#IP访问限制
#vi /data/pg_hba.conf
echo "host    all             all             192.168.1.0/24        md5" >> /data/pgsql/pg_hba.conf

#重新启动服务
sudo systemctl start postgresql-14
sudo systemctl enable postgresql-14
systemctl status postgresql-14

#密码修改
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'new_password';"

#防火墙
systemctl stop firewalld &&  systemctl disable firewalld

#常用命令
echo "sudo -u postgres pg_dump -U postgres -d $1 -f $1.sql" > /data/pgsql/backup/export.sh 
echo "sudo -u postgres psql -U postgres -d xxx_data < $1" > /data/pgsql/backup/inport.sh 
echo "sudo -u postgres psql -d xx_data" > /data/ssh/pgsql.sh 

参考地址