postgresql学习笔记--基础篇 -psql工具

--创建用户CREATEROLE pguserWITHENCRYPTED PASSWORD'pguser';--创建表空间目录mkdir-p/database/pg10/pg_tbs/tbs_mydb--创建表空间CREATETABLESPACE tbs_mydb OWNER pguser LOCATION'/database/pg10/pg_tbs/tbs_mydb';--创建数据库CREATEDATABSE mydbWITHOWNER=pguser TEMPLATE=template0 ENCODING='UTF8'TABLESPACE=tbs_mydb;--赋权GRANTALLONDATABASEmydbTOpguserWITHGRANTOPTION;GRANTALLONTABLESPACE tbs_mydbTOpguser;

1. psql 元命令介绍

1.1 \l 列出所有数据库列表

postgres=# \l

List of databases

: postgres=CTc/postgres

: ambari=CTc/postgres

: postgres=CTc/postgres

: mapred=CTc/postgres

: postgres=CTc/postgres

: postgres=CTc/postgres

(6rows)

1.2 、\db 查看表空间列表

postgres=# \db

List of tablespaces

1.3 \d查看表定义

postgres=# \dt

List of relations

public|cloud_subscriber_devices|table| postgres

public|cloud_subscribers|table| postgres

public|cloud_table_static|table| postgres

public|sxacc-device-types|table| postgres

public|sxacc-devices|table| postgres

(6 rows)

postgres=# \d cloud_table_static

Table"public.cloud_table_static"

1.4 查看表/索引占用空间大小

给测试表test插入500万数据：

postgres=#createtabletest(idintprimarykey, namevarchar(100));CREATETABLEpostgres=#insertintotest(id,name)selectn,n||'_francs'fromgenerate_series(1,5000000) n;INSERT05000000postgres=# \di+ test_pkey

List of relations

postgres=# \dt+ test

List of relations

1.5 \sf 查看函数代码

mydb#\sf random_rangeCREATEORREPLACEFUNCTIONpguser.random_range(integer,integer)

RETURNinteger LANGUAGE sqlAS$function$

SELECT($1+FLOOR(($2-$1+1)* random()))::int4;

$function$

上述\sf命令后面可以只接函数的名称，或者函数名称及输入参数类型，例如random_range(integer,integer)，Postgres支持名称相同但输入参数类型不同的函数，如果有同名函数，\sf 必须指定参数类型。

1.6 \x 设置查询结果输出

postgres=# \x

Expanded display ison.

postgres=#select*fromtest limit1;-[ RECORD 1 ]--id|1name |1_francs

1.7 -E 获取元命令对应的SQL代码

postgres:~$ psql -Epsql (9.5.14)Type "help" forhelp.postgres=# \db********* QUERY **********SELECT spcname AS"Name", pg_catalog.pg_get_userbyid(spcowner) AS"Owner", pg_catalog.pg_tablespace_location(oid) AS"Location"FROMpg_catalog.pg_tablespaceORDER BY 1;**************************List oftablespaces Name | Owner |Location------------+----------+---------- pg_default | postgres |pg_global | postgres |(2 rows)

postgres=#

1.8 -A 设置非对齐输出模式

psql执行SQL输出默认是对齐模式，例如

postgres@nancloud-onprem-06:~$ psql-c "selectdatname,dattablespacefrompg_database limit1;"

datname | dattablespace-----------+---------------template1|1663(1 row)--注意返回结果，这里有空行

以上输出，格式是对齐的，psql加上-A选项如下所示：

postgres@nancloud-onprem-06:~$ psql-A-c "selectdatname,dattablespacefrompg_database limit1;"

datname|dattablespace

template1|1663(1row) --注意返回结果，没有空行

加上-A选项以后输出的格式变成不对齐的了，并且返回结果中没有空行。

1.9 -t 只显示记录数据

-t 参数设置输出只显示数据，而不显示字段名称和返回的结果集行数。

postgres@nancloud-onprem-06:~$ psql-t-c "selectdatname,dattablespacefrompg_database limit1;"

template1 |1663

--注意返回结果，这里有空行

postgres@nancloud-onprem-06:~$ psql-At-c "selectdatname,dattablespacefrompg_database limit1;"

template1|1663 --> 注意这里没有空行postgres@nancloud-onprem-06:~$

以上在写shell 脚本非常有效。

1.10 -q 不显示输出信息

默认情况下，使用psql执行sql命令会返回多种消息，使用-q参数后将不再显示这些信息。

postgres@nancloud-onprem-06:~$ psql-f test.sqlDROPTABLECREATETABLEINSERT01postgres@nancloud-onprem-06:~$ psql-q-f test.sql--这里不显示输出信息

注意：psql 的-single-transaction 或 -l 选项支持在一个事物中执行脚本，要么脚本中所有SQL执行成功，如果其中有SQL执行失败，则文件中的所有SQL回滚。

2. psql 如何传递变量到SQL

2.1 \set 元命令方式传递变量

\set 元子命令可以设置变量，格式如下所示，name表示变量名称，value表示变量值，如果不填写value，变量值为空。

\set name value

mydb=>\setv_id2mydb=>select*fromtestwhere id:v_id;

id | name----+------2 | b

(1row)

如果想取消之前变量设置的值，\set 命令后接参数名称即可：

mydb=>\setv_id

通过\set 元命令设置变量的一个典型应用场景是使用pgbench进行压力测试。

2.2 psql 的-v 参数传递变量

样例：写一个select_1.sql 脚本

select*fromtestwhereid:v_id;

通过psql接-v传递变量，并执行脚本

postgre# psql-v v_id=1mydb pguser-f select_1.sql

id | name-----+-------1| a

(1row)

2.3 使用psql定制日常维护脚本

先介绍 .psqlrc文件，如果psql没有带-X 选项，psql尝试读取和执行用户~/.psqlrc 启动文件中的命令，结合这个文件能够方便地预先定制维护脚本。

2.3.1.查询活动会话

cloud=#selectpid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),queryfrompg_stat_activitywherepid<>pg_backend_pid()andstate='active'orderbyquery_startdesc;

pid |usename|datname|application_name|client_addr|age| query-------+-------------+---------+------------------------+-------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------6634|replication||walreceiver|10.2.38.108||6636|replication||walreceiver|10.2.39.66||30315|calixcloud|cloud|PostgreSQL JDBC Driver|10.2.42.249|00:00:00.419892|SELECT subscriber_id,service_addr,street,city,state,country,postcode FROMcloud_subscribersWHERElatISNULLandlonISNULLANDservice_addrISNOTNULLLIMIT17791|calixcloud|cloud|PostgreSQL JDBC Driver|10.2.35.36|00:00:00.602242|UPDATE"sxacc-net-perf-test" t SETinfo=jsonb_set(t.info,'{worker}','"gcs4-us.calix.com.8339"',true)FROM(SELECTt.infoFROM"sxacc-net-perf-test"t WHERE(t.info->>'nextRunTime')::jsonb<'{"$date":1576820536640}'AND(t.info->'worker')::jsonbISNULLLIMIT1) t1 WHERE t.info->>'_id'=t1.info->>'_id'RETURNING t1.*9747|calixcloud|cloud|PostgreSQL JDBC Driver|10.2.36.242|00:00:00.979992|UPDATE"sxacc-net-perf-test" t SETinfo=jsonb_set(t.info,'{worker}','"gcs3-us.calix.com.32325"',true)FROM(SELECTt.infoFROM"sxacc-net-perf-test t WHERE(t.info->>'nextRunTime')::jsonb<'{"$date":1576820536261}'AND(t.info->'worker')::jsonbISNULLLIMIT1) t1WHEREt.info->>'_id'=t1.info->>'_id'RETURNING t1.*8615|calixcloud|cloud|PostgreSQL JDBC Driver|10.2.35.127|00:00:01.071608|UPDATE"sxacc-net-perf-test" t SETinfo=jsonb_set(t.info,'{worker}','"gcs2-us.calix.com.1277"',true)FROM(SELECTt.infoFROM"sxacc-net-perf-test"t WHERE(t.info->>'nextRunTime')::jsonb<'{"$date":1576820536171}'AND(t.info->'worker')::jsonbISNULLLIMIT1) t1 WHERE t.info->>'_id'=t1.info->>'_id'RETURNING t1.*14164|postgres|cloud|psql||00:05:52.667716| VACUUM (VERBOSE, ANALYZE) "sxacc-files";

(7rows)

active：任务正在执行

idle：后台进程为空闲状态，等待后续客户端发出命令

idle in transaction：后台进程正在事务中，并不是指正在执行SQL

idle in transaction（aborted）：和idle in transaction 类似，只是事务中的部分SQL异常

vim ~/.psqlrc

-- 设置活动会话

\set active_session 'select pid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),query from pg_stat_activity where pid<>pg_backend_pid() and state=\'active\' order by query_start desc;'

之后重新连接数据库，执行:active_session即可。

postgres#:active_session

-------+-------------+---------+------------------------+-------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

6634 | replication | | walreceiver | 10.2.38.108 | |

6636 | replication | | walreceiver | 10.2.39.66 | |

30315 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.42.249 | 00:00:00.419892 | SELECT subscriber_id,service_addr,street,city,state,country,postcode FROM cloud_subscribers WHERE lat IS NULL and lon IS NULL AND service_addr IS NOTNULL LIMIT 1

7791 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.35.36 | 00:00:00.602242 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs4-us.calix.com.8339"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test"t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536640}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1 WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*

9747 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.36.242 | 00:00:00.979992 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs3-us.calix.com.32325"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536261}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*

8615 | calixcloud | cloud | PostgreSQL JDBC Driver | 10.2.35.127 | 00:00:01.071608 | UPDATE "sxacc-net-perf-test" t SET info = jsonb_set(t.info,'{worker}','"gcs2-us.calix.com.1277"',true) FROM (SELECT t.info FROM "sxacc-net-perf-test"t WHERE (t.info->>'nextRunTime')::jsonb < '{"$date":1576820536171}' AND (t.info->'worker')::jsonb IS NULL LIMIT 1) t1 WHERE t.info->>'_id' = t1.info->>'_id' RETURNING t1.*

(7 rows)

2.3.2.查询等待事件

-- check wait events

\set wait_event 'select pid,application_name,client_addr,age(clock_timestamp(),query_start),state,wait_event_type,wait_event from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event_type;'