分类 PgSQL 下的文章

只导出数据，带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;
  1. 1
  2. 2