[笔记]pg常用命令

这篇具有很好参考价值的文章主要介绍了[笔记]pg常用命令。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

数据库版本 :9.6.6

注意 :PostgreSQL中的不同类型的权限有SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE 和 USAGE。

1. 登录PG数据库

以管理员身份 postgres 登陆,然后通过

 #psql -U postgres
 #sudo -i -u postgres
 ​
 $ psql  xc_hzh_linan                    #登录xc_hzh_linan数据库
 $ psql -U postgres test               #以postgres用户身份,登录test数据库
 psql -h localhost -p 5432 -U postgress testdb

界面效果为:

 postgres@ubuntu:~$ psql
 psql (9.5.19)
 Type "help" for help.
 ​
 postgres=# 

  1. 简单说一下pgsql 的相关命令

使用反斜线作为命令前缀.

 退出    \q
 列出所有的数据库      \l
 列出所有的数据库的大小      \l+
 更改当前连接的数据库       \c
 列出当前数据库的连接信息    \connect
 列出当前数据库和连接的详细信息 \conninfo
 查看当前数据库里面的表和拥有者和表大小         \dt +
 展示所有用户           \dg
 ​
 查看所有表名的列表             \d
 获取表结构                   \d a
 展示所有用户               \du
 查看t_sms表的结构      \d t_sms 
 展示数据库里面的所有的表         \dt
 列出所有的数据库的详细信息(包括数据库大小和字符格式)         \l+
 显示用户访问权限。                            \z或\dp
 显示所有可设置的访问权限                     \h GRAN
 显示用户的对所有数据库表的详细访问权限     \dp或者\z
 确认当前连接的用户为超级用户postgres,且该用户后创建角色和数据库的权限等     #select current_user;
 在超级用户连接postgres后,设置不允许普通用户a连接数据库         #alter role a nologin;
 ​
 使用普通用户a连接数据库正常                   #\c highgo a
 ​
 查看当前连接的用户名:
 foo=#select * from current_user;
 或
 foo=#select user;
 ​
 查看所有用户名:
 foo=#\du
 ​
 数据库表的备份与恢复:
 pg_dump -h 192.168.2.242 -U postgres -p 5432 -c --if-exists -t t_sms dev_huishishuju > t_sms.db 远程备份

[笔记]pg常用命令使用案例 :

 t_gj_tzy=# \l+
                                                                    List of databases
    Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
 -----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
  postgres  | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 |                       | 7079 kB | pg_default | default administrative connection database
  t_gj_tzy  | wsgjjkzg | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/wsgjjkzg         +| 21 GB   | pg_default | 
            |          |          |            |            | wsgjjkzg=CTc/wsgjjkzg |         |            | 
  template0 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres          +| 6969 kB | pg_default | unmodifiable empty database
 ​
 PostgreSQL 查找当前数据库的所有表 select * from pg_tables where schemaname = 'public';

查看每个表的大小

 postgres=# \dt+
                                  List of relations
  Schema |            Name            | Type  |  Owner   |    Size    | Description 
 --------+----------------------------+-------+----------+------------+-------------
  public | acknowledges               | table | postgres | 0 bytes    | 
  public | actions                    | table | postgres | 16 kB      | 
  public | alerts                     | table | postgres | 8192 bytes | 
  public | application_discovery      | table | postgres | 0 bytes    | 
  public | application_prototype      | table | postgres | 8192 bytes | 
  public | application_template       | table | postgres | 40 kB      | 
  public | applications               | table | postgres | 56 kB      | 
  public | auditlog                   | table | postgres | 0 bytes    | 
  public | auditlog_details           | table | postgres | 8192 bytes | 

添加索引

 CREATE INDEX idx_ke_kid
 ​
   ON base.keyword_engine
 ​
   USING btree
 ​
   (keyword_id);

2、创建用户以及相关权限设置

创建用户

 create role zhaobsh;
 ​
 create user wzq with password '123456';                       #设置用户,并设置密码
 create database xc_hzh_linan owner xchzhlinan ;         #创建数据库以及所属用户

更改用户密码

 alter role zhaobsh with password 'Test6530';
 ​
 or
 ​
 \password zhaobsh
 ​
 ALTER USER postgres WITH PASSWORD 'postgres';

注:

  • 密码postgres要用引号引起来

  • 命令最后有分号

修改数据库 属主

 alter database "GSCloud1903" owner to zhaobsh;
 ALTER ROLE user_4 WITH CREATEROLE;            /*赋予创建角色的权限*/  
 alter database "GSCloud1903" rename to zhaobsh;              --修改schema的名称

授权给用户 PostgreSQL中预定义了许多不同类型的内置权限,如:SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE。

 grant all on database "GSCloud1903" to zhaobsh;                          #赋予用户zhaobsh数据库所有权限
 ​
 grant UPDATE ON database "GSCloud1903" to zhaobsh;             # 赋予用户zhaobsh数据库更新权限
 ​
 /* 赋给用户表的所有权限 */
 GRANT ALL ON tablename TO user; 
 ​
 /* 赋给用户数据库的所有权限 */
 GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
 ​
 /* 撤销用户权限 */
 REVOKE privileges ON tablename FROM user;

连接用户和数据库 使用创建的用户能登录查看相应数据库,则可进行下一步。

 [root@localhost ~]# psql -h localhost -U monitoring -p 54321 wiseucmsg

3、撤销用户权限

撤销用户 runoob权限:

 =# REVOKE ALL ON COMPANY FROM runoob;                 

删除用户:

 runoobdb=# drop user runoob;

设置是 supseruser 以及 登录权限

 alter user zhaobsh superuser login

修改指定用户指定权限

 alter user postgres superuser createrole createdb replication;
 注意:如果出现 FATAL: role '...' is not permitted to log in.  的错误
 ​
 alter user postgres superuser createrole createdb replication login;

4、查看用户权限

1、查看某用户的表权限

 select * from information_schema.table_privileges where grantee='user_name';

2、查看usage权限表

 select * from information_schema.usage_privileges where grantee='user_name';

3、查看存储过程函数相关权限表

 select * from information_schema.routine_privileges where grantee='user_name';

4、建用户授权

 create user user_name;
 alter user user_name with password '';
 alter user user_name with CONNECTION LIMIT  20;#连接数限制
 123

创建数据库

 CREATE DATABASE dbname;
 ​
 postgres=# create database dbtest owner username; -- 创建数据库指定所属者
 CREATE DATABASE

或者

 $ createdb -h localhost -p 5432 -U postgres runoobdb
 password ******

将数据库得权限,全部赋给某个用户

 postgres=# grant all on database dbtest to username; -- 将dbtest所有权限赋值给username
 GRANT
 ​
 设置是 supseruser 以及 登录权限
 alter user zhaobsh superuser login

在pg数据库中创建只读用户可以采用如下方法。大体实现就是将特定schema的相关权限赋予只读用户

 --创建用户
 CREATE USER readonly WITH ENCRYPTED PASSWORD '123456';
 ​
 --设置用户默认开启只读事务
 ALTER USER readonly SET default_transaction_read_only = ON;
 ​
 --设置用户登录权限
 ​
 ALTER USER readonly WITH login;
 ​
 --将schema中usage权限赋予给readonly用户,访问所有已存在的表
 GRANT usage ON SCHEMA xyh TO readonly;
 ​
 --将schema中表的查询权限赋予给readonly用户,访问所有已存在的表
 GRANT SELECT ON ALL tables IN SCHEMA xyh TO readonly;
 ​
 --未来访问xyh模式下所有新建的表:
 ALTER DEFAULT privileges IN SCHEMA xyh GRANT SELECT ON tables TO readonly;
 ​
  
 ​
 --查数据库
 ​
 select * from pg_database;
 ​
 --查模式
 ​
 select exists(select 1 from pg_namespace where nspname='xyh'); 

5、 修改linux系统中postgres用户的密码

PostgreSQL会创建一个默认的linux用户postgres,修改该用户密码的方法如下: 步骤一:删除用户postgres的密码

 sudo  passwd -d postgres

步骤二:设置用户postgres的密码

 sudo -u postgres passwd

系统提示输入新的密码

 Enter new UNIX password: 
 ​
 Retype new UNIX password: 
 ​
 passwd: password updated successfully

6、使用pgAdmin客户端 工具创建数据库

pgAdmin 工具提供了完整操作数据库的功能: [笔记]pg常用命令

7、数据库的导入导出

导入整个数据库

 psql -U username databasename < /data/dum.sql -- 用户名和数据库名
 pg_dump ga_zj_taizhou.sql | psql wang_wang

或者

 进入具体的数据库导入
 postgres@ubuntu:~$ psql
 psql (9.6.6)
 Type "help" for help.
 ​
 postgres=# \c wang_wang     
 You are now connected to database "wang_wang" as user "postgres".
 wang_wang=# \i ga_zj_taizhou.sql

[笔记]pg常用命令注意 : 因为是从其他地方备份出来的sql文件,从一个数据库导入另一台数据库可能会报下面的错误

 \i /root/test.sql            #导入数据库数据
 psql:ga_zj_taizhou.sql:1408597: ERROR:  role "pm" does not exist
 psql:ga_zj_taizhou.sql:1408598: ERROR:  role "laoyw" does not exist

解决办法 : 格式:create user 自定义用户名称 superuser;

 postgres=# create user root superuser ;

然后\q退出数据库,再实现psql -d your_db -f sql_path 例如:

 # psql -d mz -f /usr/mydownloads/proj_llmrs.sql 

8、忘记postgres用户密码怎么办?

环境:

 Ubuntu 16.04
 postgres : apt-get install postgresql 9.5版本

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

 local   all             postgres                                peer
 ​
 # TYPE  DATABASE        USER            ADDRESS                 METHOD
 ​
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
 host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:
 host    all             all             ::1/128                 md5

重启postgressql服务。

 ~$ systemctl restart postgresq

免密码登录

 localhost:~$ psql -U postgres -h 127.0.0.1
 psql (9.5.12)
 SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
 Type "help" for help.
 postgres=#

修改密码并退出

 postgres=# alter user postgres with password '123';
 ALTER ROLE
 postgres=# \q

修改回认证并退出: trust改为md5

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

 # IPv4 local connections:
 host    all             all             127.0.0.1/32            trust

重启并登陆:

 parallels@parallels-vm:~$ systemctl restart postgresql
 parallels@parallels-vm:~$ psql -U postgres -h 127.0.0.1
 Password for user postgres: 
 psql (9.5.12)
 SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
 Type "help" for help.

9. Ubuntu系统

创建新用户

 root@ubuntu:~# useradd wang
 root@ubuntu:~# passwd wang
 Enter new UNIX password: 
 Retype new UNIX password: 
 passwd: password updated successfully

常见报错 : 没有家目录,新用户登陆错误

 root@ubuntu:~# su - wang
 No directory, logging in with HOME=/
 $ 

Linux授予某个用户对某个文件夹的读写权限

 $ sudo chmod -R 755 /data/data1
 ​
 $ chown -R user1.user1 1.txt
 查看目录的权限
 data# ls -l 

备份数据 从外网拷贝到本地 scp -r *.sql zhaobsh@192.187.100.1:/data/data1

导入数据 登录数据库 postgres@dbmaser:~$ psql -U wsgjjkzg -d t_gj_tzy < /data/data1/t_gj_tzy.20191029235826.sql

查看导入的数据 su - postgresl \c GSCloud1903 #切换到GSCloud1903 postgres=# \c t_gj_tzy You are now connected to database “t_gj_tzy” as user “postgres”.

\c dt+ #查看当前数据库里面的表和拥有者和表大小

清空数据库中所有数据

 //删除public模式以及模式里面所有的对象
 DROP SCHEMA public CASCADE;
 //创建public模式
 CREATE SCHEMA public;

清除全表,建议使用truncate

2种方法都只删数据,保留表结构

方法一 ,适用数据量较小的情况

 delete from  tablename;
 

方法二,适合删除大量数据,速度快

 TRUNCATE TABLE tablename;
 ​
 若该表有外键,要用级联方式删所有关联的数据
 ​
 TRUNCATE TABLE tablename CASCADE;
 12345
 test03=# select * from test;  
  id | info | crt_time   
 ----+------+----------  
 (0 rows) 

注意 :

 //当表没有其他关系时
 TRUNCATE TABLE tablename;
 //当表中有外键时,要用级联方式删所有关联的数据
 TRUNCATE TABLE tablename CASCADE;

清除所有超过1w条数据的表数据。

 SELECT * FROM count_em_all(10000) AS r ORDER BY r.num_rows DESC;
 

扩展 在 mysql中,只需要执行:

 TRUNCATE table_name;
 

即可,数据会情况,而且自增id也会变回0;

但在 postgresql 则稍有不同,因为 postgresql 的自增id是通过序列 sequence来完成的,

所以情况数据后,还需要还原序列 sequence:

 TRUNCATE bigtable, fattable RESTART IDENTITY;
 

官方文档:PostgreSQL: Documentation: 9.2: TRUNCATE

另一种方式:(因为版本不同,可能命令不同)

 truncate table table_name;
 alter sequence seq_name start 1;
 

postgres数据库中删除指定的数据的sql语句

查询数据库中default_table表中的name字段为空的条件

 SELECT * from default_table WHERE name is null;
 

删除数据库中default_table表的name字段为空的数据

 DELETE  FROM default_table WHERE  name is null;  
 

创建索引语句

 create index i_jdbc_sql_record_zh01 on t_jdbc_sql_record(c_bh_group,dt_zxsj,N_CHECKSTATUS,C_PSSQL_HASH);
 alter table t_jdbc_sql_content add constraint t_jdbc_sql_content_pkey primary key (C_BH);
 ​
 123

异步提交和unlogged table

 -- 异步提交,更改完重启数据库
  alter system set synchronous_commit to off;
  -- unlogged table 
  create unlogged table t_jdbc_sql_record 
  ...
  create unlogged table t_jdbc_sql_content 
 123456

POSTGRESQL 数据库导入导出

 导入整个数据库
 ​
 psql -U postgres(用户名)  数据库名(缺省时同用户名) < /data/dum.sql
 ​
 导出整个数据库
 1.导出数据库:方式一:pg_dump -U postgres -f c:\db.sqlpostgis
 ​
 方式二:pg_dump -Upostgres postgis > c:\db.sql
 ​
 pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)   >/data/dum.sql
 ​
 导出某个表
 ​
 pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)  -t table(表名) >/data/dum.sql
 ​
 导入具体表:
 ​
 psql -d postgis -f c:\ dump.sqlpostgres
 ​
 压缩方法
 ​
 一般用dump导出数据会比较大,推荐使用xz压缩
 ​
 压缩方法  xz dum.sql 会生成 dum.sql.xz 的文件
 ​
 ​
 xz压缩数据倒数数据库方法
 ​
 xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)
 1234567891011121314151617181920212223242526272829

查看postgresql数据库用户系统权限、对象权限的方法

1、查看某用户的系统权限

 SELECT * FROM pg_roles WHERE rolname='postgres';

[笔记]pg常用命令 2、查看某用户的表权限

 select * from information_schema.table_privileges where grantee='postgres';

3、查看某用户的usage权限

 select * from information_schema.usage_privileges where grantee='postgres';

4、查看某用户在存储过程函数的执行权限

 select * from information_schema.routine_privileges where grantee='postgres';

5、查看某用户在某表的列上的权限

 select * from information_schema.column_privileges where grantee='postgres';

6、查看当前用户能够访问的数据类型

 select * from information_schema.data_type_privileges ;

7、查看用户自定义类型上授予的USAGE权限

 select * from information_schema.udt_privileges where grantee='postgres';

工作中常见用法 :

1.查看运行时间长的sql

 select pid,datname,usename,client_addr,query_start,now()-query_start as time_run, query from pg_stat_activity
 where state='active' order by time_run desc limit 20;

2.查看指定表空间下的表和索引

 SELECT  c.relname, t.spcname FROM  pg_class c left JOIN pg_tablespace t ON c.reltablespace = t.oid WHERE t.spcname = 'data1tbs';

默认表空间的话where判断 c.reltablespace=0 或 t.spcname is null

3、查看指定pid需要的锁状态

 select pc.relname,pl.* from pg_locks pl left join pg_class pc on pc.relfilenode=pl.relation where pl.pid=13912

4、查看未使用索引

 select schemaname,relname,indexrelname,idx_scan from pg_stat_user_indexes where idx_scan=0 and schemaname='public' and indexrelname not like '%_pkey'

5、查看指定表的索引

 select schemaname,relname,indexrelname,idx_scan from pg_stat_user_indexes where schemaname='public' and relname='visited_article'

6、某个pid在等待的锁的持有者

 select pl1.*,pl2.pid,pa.usename,pa.query from pg_locks pl1 left join pg_locks pl2 on pl1.transactionid=pl2.transactionid and pl2.granted
 left join pg_stat_activity pa on pl2.pid=pa.pid where pl1.pid=21641 and not pl1.granted

7、查看所有锁的状态

 select
      pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
      pg_stat_activity.usename,substr(pg_stat_activity.query,1,30), pg_stat_activity.query_start,
      age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.pid
    from pg_stat_activity,pg_locks left
      outer join pg_class on (pg_locks.relation = pg_class.oid) 
    where pg_locks.pid=pg_stat_activity.pid order by query_start limit 100;

8、查看指定schema 里所有的索引大小,按从大到小的顺序排列

 select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;
 ​

9、查询所有表大小,并且按从大到小排序

 select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables ORDER BY size desc;

10、查询指定模式下面所有表大小,并按表大小排序

 select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables where schemaname = 'authevent'  ORDER BY size desc;
 

–查看DB大小

 select pg_size_pretty(pg_database_size('ve'));  
 

–查看表大小(不含索引等信息)

 select pg_relation_size('user');                      
 select pg_size_pretty(pg_relation_size('use'))  
 

–查看每个DB的使用情况(读,写,缓存,更新,事务等)

 select * from pg_stat_database
 

–查看索引的使用情况

 select * from pg_stat_user_indexes;

–查看表所对应的数据文件路径与大小

 SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';
 1

–查看表空间大小

 select pg_tablespace_size('pg_default');

714 境内采集器

 SELECT  ip FROM asset_server where is_server=1 and  ip  not like '%163.106.130.%' and ip  not like '132.117.354.%' and idc_id not in(16,9)  and ip not IN('161.10.84.11','180.132.214.110')

27 大服务器

 SELECT  ip FROM asset_server where is_server=1 and  (ip   like '%183.106.110.%' or ip  like '122.227.254.%'  or ip  IN('80.102.214.130'))

24 境外采集器

 SELECT  ip FROM asset_server where is_server=1 and   idc_id in(16,9) and ip not in('57.39.42.18','143.331.144.190','113.10.10.11')

1 阿里云

 SELECT  ip FROM asset_server where  ip  IN('132.10.34.121')

4 v,pn 与浏览器专用 37.69.82.18(“浏览器代理服务器”) “183.211.104.114”

 SELECT  * FROM asset_server where is_server=1 and   idc_id in(16,9) and ip in('37.69.82.18','183.211.104.114')

4.6.5 排除境,外 阿里云

    SELECT  * FROM asset_server where is_server=1  and idc_id not in(16,9)  and ip not IN('132.110.194.141')

4.6.7 --统计全网服务器(按机房)

 select COUNT(ass.ip),ass.idc_id,idc.name  from  asset_server  ass  left join asset_serveridc idc on idc.id=ass.idc_id where ass.is_server=1 group by ass.idc_id,idc.name;

4.6.8 联合状态查询

–查询内存1g的

 select ass.ip,ast.memtotal from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
 on ass.id=ast.sid_id where ass.is_server=1 and ast.memtotal<1200 order by ass.ip;

–查询内存1g的 排除香港

 select count(ass.ip) from  asset_server ass inner join (select max(id),sid_id, cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
 on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ast.memtotal<1200;
 ​
 select ass.ip,ast.memtotal from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
 on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ast.memtotal<1200 order by ass.ip;

–查询内存1g的 排除香港 部署有totalspider的

 select count(ass.ip) from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
 on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ass.describe LIKE  '%totalspider%' and ast.memtotal<1200;

–查询内存大于1g 小于2g 排除香港的机器

 psql -h db.www.qwq.com -p 5432 -U yun -c "select ip, ast.memtotal from asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal from asset_serverstatus group by sid_id,memtotal ) as ast
 on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ast.memtotal>1200 and ast.memtotal<3000;"

查询机房归属地在上海的(代号42)的内存大小

 psql yunwei -c " select ass.ip,ast.memtotal from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal from asset_serverstatus group by sid_id,memtotal ) as ast on ass.id=ast.sid_id where ass.is_server=1 and ast.memtotal < 1024 and ass.idc_id=42 order by ass.ip;"

1、 postgres@ubuntu:~$ psql -U wzq -d template0 psql: FATAL: Peer authentication failed for user “wzq”

其中peer authentication 比较注目,很明显,错误和权限有关联。

参考链接PostgreSQL 修改设置数据库的默认用户以及权限. :PostgreSQL 修改设置数据库的默认用户以及权限. - 济南小老虎 - 博客园:PostgreSQL 修改设置数据库的默认用户以及权限._weixin_30265103的博客-CSDN博客

PostgreSQL清空表并保留表结构、清空数据库还原数据库为新建时的状态的方法 :PostgreSQL清空表并保留表结构、清空数据库还原数据库为新建时的状态的方法_路人甲JIA的博客-CSDN博客_pgsql 清空表

PostgreSQL 如何实现批量更新、删除、插入 :PostgreSQL 如何实现批量更新、删除、插入-阿里云开发者社区

postgresql 删除单个表的数据 :postgresql 删除单个表的数据_kmust20093211的博客-CSDN博客_postgresql 删除一条数据

postgresql批量删除表 :postgresql批量删除表_weixin_30384217的博客-CSDN博客

修改postgres密码 :修改postgres密码_渡人先渡己的博客-CSDN博客_postgres修改密码

查看postgresql数据库用户系统权限、对象权限的方法_PostgreSQL_脚本之家

[笔记]pg常用命令

10. pg数据制造

1. 分区表数据批量造数据

1.1 启动PostreSQL

 docker pull postgre
 ​
 docker run -e POSTGRES_PASSWORD=123456 -p 5432:5432 --name potgre postgres:latest
 ​
 psql -h 127.0.0.1 -p 5432 -U postgres -d postgres

下面的例子我们将为把订单 orders 表使用分区表来实现。order 中含有 id、user_id、create_time 三个属性,并根据 create_time 按照月份进行分区。

1.2 创建父表

 CREATE TABLE orders (id serial,user_id int4,create_time timestamp(0)) PARTITION BY RANGE(create_time);

1.3 创建分区表

 CREATE TABLE orders_history PARTITION OF orders FOR VALUES FROM ('2000-01-01') TO ('2020-03-01');
 ​
 CREATE TABLE orders_202003 PARTITION OF orders FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');
 ​
 CREATE TABLE orders_202004 PARTITION OF orders FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
 ​
 CREATE TABLE orders_202005 PARTITION OF orders FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
 ​
 CREATE TABLE orders_202006 PARTITION OF orders FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');

1.4 在分区上创建索引

 CREATE INDEX order_idx_history_create_time ON orders_history USING btree(create_time);
 ​
 CREATE INDEX order_idx_202003_create_time ON orders_202003 USING btree(create_time);
 ​
 CREATE INDEX order_idx_202004_create_time ON orders_202004 USING btree(create_time);
 ​
 CREATE INDEX order_idx_202005_create_time ON orders_202005 USING btree(create_time);
 ​
 CREATE INDEX order_idx_202006_create_time ON orders_202006 USING btree(create_time);

1.5 向分区表中插入数据

按照分钟进行数据的插入,如果需要创建海量数据可以把 1 minute 换成 1 millisecond(毫秒)或者 second(秒)文章来源地址https://www.toymoban.com/news/detail-487877.html

 INSERT INTO orders (user_id, create_time) select 1000, generate_series('2020-01-01'::date, '2020-05-31'::date, '1 minute');

11. ADB 批量

 1. 创建表
 CREATE TABLE orders (
 id serial,
 user_id int4,
 create_time DATE
 ) PARTITION BY RANGE (create_time) (START (date '1992-01-01') INCLUSIVE END (date '2000-01-01'));
 2. 插入数据
 INSERT INTO orders (user_id, create_time) select 1000, generate_series('1992-01-01'::date, '1992-04-01'::date, '1 minute');

到了这里,关于[笔记]pg常用命令的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • springboot 数据库版本升级管理常用解决方案

    目录 一、前言 1.1 单独执行初始化sql 1.2 程序自动执行 二、数据库版本升级管理问题

    2024年02月13日
    浏览(54)
  • MySQL 数据库常用命令大全(完整版)

    MySQL命令是用于与MySQL数据库进行交互和操作的命令。这些命令可以用于各种操作,包括连接到数据库、选择数据库、创建表、插入数据、查询数据、删除数据等。 默认端口号:3306 查看服务器版本:select version(); 或者 cmd命令 mysql -verison 登录数据库:mysql -uroot -p 退出数据库:

    2024年02月10日
    浏览(52)
  • pg数据库授权

    创建用户角色 此处说明,在pg库里面用户和角色的概念其实无区别 授权命令 授权参数说明 权限撤销 如果用户需要创建表的权限对该角色进行全部授权之后,发现建表的时候报错说什么当前用户是一个readonly的会话可以进行下面操作进行修改 参考连接:https://blog.csdn.net/eagl

    2024年02月11日
    浏览(48)
  • pg数据库安装

    pg数据库安装 下载对应的安装包 安装服务 使用安装包直接安装 rpm -ivh postgresql13-libs-13.1-3PGDG.rhel7.x86_64.rpm rpm -ivh postgresql13-13.1-3PGDG.rhel7.x86_64.rpm rpm -ivh postgresql13-server-13.1-3PGDG.rhel7.x86_64.rpm rpm -ivh postgresql13-contrib-13.1-3PGDG.rhel7.x86_64.rpm 配置用户数据存储目录日志目录 [root@rocketm

    2023年04月19日
    浏览(46)
  • 【MongoDB】数据库、集合、文档常用CRUD命令

    目录 一、数据库操作 1、创建数据库操作 2、查看当前有哪些数据库 3、查看当前在使用哪个数据库 4、删除数据库 二、集合操作 1、查看有哪些集合 2、删除集合 3、创建集合 三、文档基本操作 1、插入数据 2、查询数据 3、删除数据 4、修改数据 四、文档分页查询 五、文档其

    2024年02月13日
    浏览(43)
  • GaussDB查看数据库、表空间、表、schema大小以及常用的数据库元命令

    元命令 l+ 可以快速查看

    2024年02月11日
    浏览(54)
  • pg数据库和mysql区别

    PostgreSQL (通常称为 PG) 和 MySQL 都是广泛使用的关系型数据库管理系统 (RDBMS)。虽然它们都是用于存储和管理数据的关系数据库,但它们在一些方面有很大的区别,如下所述: 数据类型:PostgreSQL 支持更多的数据类型,如范围类型、网络地址类型、JSON 和 XML 数据类型等,而 My

    2024年03月12日
    浏览(51)
  • 使用docker部署pg数据库

    使用 Docker 部署 PostgreSQL 数据库是一种常见的做法,它提供了方便、可移植和可重复的方式来运行数据库。下面是一个简单的示例,用于在 Docker 中部署 PostgreSQL 数据库: 首先,确保您已经安装了 Docker 并正确配置了 Docker 环境。 打开终端或命令提示符,并执行以下命令来下载

    2024年02月10日
    浏览(47)
  • 【PostgreSQL】连接pg数据库Schema切换

    由于PostgreSQL数据库模式(schema)存在多个,原先的表单是默认采用public但是查询表和字段时候有查询所有未进行过滤,导致数据库连接失败、查表字段也为空(空即查询服务端异常错误) 数据库连接配置 添加参数补充 ?currentSchema=dwd 譬如: username=root;password=XXXX;url=jdbc:postg

    2024年02月11日
    浏览(50)
  • Flink CDC实时同步PG数据库

    JDK:1.8 Flink:1.16.2 Scala:2.11 Hadoop:3.1.3 github地址:https://github.com/rockets0421/FlinkCDC-PG.git  1、更改配置文件postgresql.conf # 更改wal日志方式为logical wal_level = logical # minimal, replica, or logical # 更改solts最大数量(默认值为10),flink-cdc默认一张表占用一个slots max_replication_slots = 20 # m

    2024年02月13日
    浏览(64)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包