Skip to content

Latest commit

 

History

History
398 lines (280 loc) · 8.76 KB

postgresql命令.md

File metadata and controls

398 lines (280 loc) · 8.76 KB
远程连接
psql -h IP地址 -p 端口  -U 数据库名
# 之后会要求输入数据库密码
查看安装位置
psql -U postgres -c 'SHOW config_file'
安装

CentOS7

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 
# 服务器
sudo yum install -y postgresql12-server

# 初始化
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable --now postgresql-12
改配置
# 改密码
su - postgres 
psql -c "alter user postgres with password 'StrongPassword'" 

# 改配置
vim /var/lib/pgsql/12/data/pg_hba.conf

# Accept from anywhere
host all all 0.0.0.0/0 md5

# Accept from trusted subnet
host all all 192.168.18.0/24 md5


# 重启
systemctl restart postgresql-12
更改默认的schema
show search_path;
create schema my_schema;
grant all on schema my_schema to my_user;

-- 会话内修改
set search_path to yay;

-- 永久修改
alter database "databasename" set search_path to "yay";
访问数据库
功能 命令
列举数据库 \l
选择数据库 \c 数据库名
查看所有表 \dt 或 \dt+
查看表结构 \d 表名 或 \d+ 表名
显示字符集 \encoding
退出psgl \q
选取所有enums \dT+
展示空值 \pset null 'NULL'
关闭换行 \pset pager off
查看空间：
select pg_size_pretty(pg_database_size('库名'));
select pg_size_pretty(pg_relation_size('表名'));
select pg_size_pretty(pg_indexes_size('索引名'));

-- 查看top空间占用表
select relname, indexrelname, pg_size_pretty(pg_relation_size(relid)) as table_size, pg_size_pretty(pg_relation_size(indexrelid)) as idx_size, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where pg_relation_size(indexrelid)>100000 order by relname, pg_relation_size(indexrelid) desc;
select relname, (table_size/rows + index_size)::bigint as table_raw, pg_size_pretty(table_size/rows + index_size) as total_size, ((table_size/rows + index_size)::numeric/10.24/(25001024)/1024)::numeric(8,2) as rate,
       pg_size_pretty(table_size/rows) as table_size,
       pg_size_pretty(index_size) as index_size
from (
         select relname,
                sum(pg_relation_size(relid) ) as table_size,
                sum(pg_relation_size(indexrelid) ) as index_size,
                count(1) AS rows
         from pg_stat_user_indexes
         group by relname
     ) a
where table_size>5001024*1024 
order by table_size/rows + index_size desc;

-- 查看特定表空间占用
select relname, (table_size/rows + index_size)::bigint as table_raw, pg_size_pretty(table_size/rows + index_size) as total_size, ((table_size/rows + index_size)::numeric/10.24/(2500*1024)/1024)::numeric(8,2) as rate,
       pg_size_pretty(table_size/rows) as table_size,
       pg_size_pretty(index_size) as index_size
from (
         select relname,
                sum(pg_relation_size(relid) ) as table_size,
                sum(pg_relation_size(indexrelid) ) as index_size,
                count(1) AS rows
         from pg_stat_user_indexes
         group by relname
     ) a
where relname='latest_app_open_device';

-- 查看所有索引的大小
select indexrelname, pg_size_pretty(pg_relation_size(indexrelid))
from pg_stat_user_indexes
where schemaname = 'yay'
order by pg_relation_size(indexrelid) desc

DDL

索引重命名
alter index if exists old_name rename to new_index_name;
导出
# 导出csv
psql -c "
    \COPY (
      SELECT *
      FROM products
    )
    TO '/path/to/output.csv'
    WITH (format csv, header);
"

# 导出txt
# 保存以下命令为bash脚本
table=$1
column=*
where=$2
psql -h slave.marketdb1.tt -d putong-market -p 5432 -U postgres -c "\copy (select $column from $table where 1=1 $2) to 'txt/$table.txt' with (delimiter '|');"

scp txt/$table.txt pj:~/psql/txt/

# 执行上面脚本 + 表名 + where条件
导入
# 保存以下命令为bash脚本
psql -d putong-market -U putong -c "truncate $1"
psql -d putong-market -U putong -c "\copy $1 from 'txt/$1.txt' with (delimiter '|');"

# 执行以上脚本 + 表名
导出表结构
pg_dump -h localhost  -p 5432 -U postgres -n yay -s $table -t $table > txt/$table.sql
清理空间
vacuum
vacuum full (锁全表，清理的更深度，归还磁盘空间)
设置pg_xlog大小

修改 postgres.conf 里 wal 开头的东西

查看正在执行的sql
SELECT
procpid,
start,
now() - start AS lap,
current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE
current_query <> '<IDLE>'
ORDER BY
lap DESC;
修改编辑模式
control + command + j
在脚本中自动输入密码
  1. 在~/目录下创建隐藏文件 .pgpass
  2. 文件内容：host:port:dbname:username:password
  3. bash_profile 里 export PGPASSFILE=~/.pgpass
  4. chmod 0600 ~/.pgpass
修改表结构
ALTER TABLE
Name
ALTER TABLE -- change the definition of a table
Synopsis
ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema
where action is one of:
    ADD [ COLUMN ] column data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    DROP [ COLUMN ] [ IF EXISTS ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    ALTER [ COLUMN ] column SET DEFAULT expression
    ALTER [ COLUMN ] column DROP DEFAULT
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint [ NOT VALID ]
    ADD table_constraint_using_index
    VALIDATE CONSTRAINT constraint_name
    DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE REPLICA TRIGGER trigger_name
    ENABLE ALWAYS TRIGGER trigger_name
    DISABLE RULE rewrite_rule_name
    ENABLE RULE rewrite_rule_name
    ENABLE REPLICA RULE rewrite_rule_name
    ENABLE ALWAYS RULE rewrite_rule_name
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITH OIDS
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OF type_name
    NOT OF
    OWNER TO new_owner
    SET TABLESPACE new_tablespace
and table_constraint_using_index is:
    [ CONSTRAINT constraint_name ]
    { UNIQUE | PRIMARY KEY } USING INDEX index_name
    [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
创建用户
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    
    
---改密码
ALTER USER saber PASSWORD ' md53175af154as54df5as4d5f45sd6af';

---改库OWNER
ALTER DATABASE saber OWNER TO saber;
重置自增ID
SELECT setval('xxx_id_seq', (SELECT max(id) FROM xxx));

DML

列转行
select array(select column from table);
联表更新
UPDATE table1 SET xxx FROM table2 WHERE table1 关联 table2
排序
RANK() OVER(PARTITION BY f1 ORDER BY f2 DESC)
提取时间差
-- 提取秒
extract(EPOCH from (a.created_time-b.click_time))

-- 提取日期
date_part('day' from (a.created_time-b.click_time))
字符串拼接
SELECT concat(a, b, c) FROM table WHERE xxx;
字符串替换
SELECT replace(column, old, new) FROM table WHERE xxx;