文章目录
- 客户端工具
- 一、使用psql连接数据库
- 二、psql元命令的介绍
- 三、psql导入、导出表数据
- 四、psql语法和选项介绍
- 五、psql执行sql脚本
- 六、psql如何传递变量到sql
- 七、使用psql定制日常维护脚本
- 八、psql亮点功能
客户端工具
一、使用psql连接数据库
psql postgres postgres
[root@oracle ~]# su - pgsql
[pgsql@oracle ~]$psql postgres postgres
psql (13.4)
Type "help" for help.
第一个postgres表示库名
第二个postgres表示用户名
1.1.创建用户
create role pguser with encrypted password 'pguser';
create user lhc with password 'lhc';
1.2.创建表空间目录
mkdir -p /data/postgresql/pgdata/tablespace/ts_lhc
1.3.创建表空间
create tablespace ts_lhc owner lhc location '/data/postgresql/pgdata/tablespace/ts_lhc';
1.4.创建数据库
create database lhcdb with owner = lhc tablespace = ts_lhc;
create database lhcdb
with owner = pguser
template = template0
encoding = 'UTF8'
tablespace = ts_lhc;
ALTER DATABASE lhcdb SET TABLESPACE ts_lhc;
1.5.赋权
grant all privileges on database lhcdb to lhc with grant option;
grant all on tablespace ts_lhc to lhc;
grant all on database mydb to pguser with grant option;
1.6.创建schema
create schema lhc;
grant usage on schema lhc to lhc;
create schema lhcPrivate authorization lhc;
create schema authorization lhc;
1.7.远程连接数据库
[pgsql@oracle ~]$psql -U lhc -h 10.206.132.17 -p 5432 -d postgres
Password for user lhc:
psql (13.4)
Type "help" for help.
查看Schema
postgres=# \dn
List of schemas
Name | Owner
------------+----------
lhc | lhc
lhcprivate | lhc
public | postgres
(3 rows)
二、psql元命令的介绍
2.1.\l查看数据库列表
postgres@[local:/data/postgresql/pgdata]:5432=#\l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
lhcdb | lhc | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres-#
postgres-# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace |
Description
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
lhcdb | lhc | UTF8 | en_US.utf8 | en_US.utf8 | =Tc/lhc +| 7729 kB | ts_lhc |
| | | | | lhc=C*T*c*/lhc | | |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | 7877 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7729 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +| 7729 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
2.2.\db查看表空间列表
postgres-# \db
List of tablespaces
Name | Owner | Location
------------+----------+-------------------------------------------
pg_default | postgres |
pg_global | postgres |
ts_lhc | lhc | /data/postgresql/pgdata/tablespace/ts_lhc
(3 rows)
2.3.\d查看表的定义
create table lhc.test1 (id int,name text) tablespace ts_lhc;
create table lhc.test2 (id int,name varchar(50)) tablespace ts_lhc;
alter table lhc.test1 add primary key (id);
[pgsql@oracle ~]$psql -U lhc -h 10.206.132.17 -p 5432 -d postgres
Password for user lhc:
psql (13.4)
Type "help" for help.
postgres=> SELECT * FROM pg_catalog.pg_tables WHERE tableowner = 'lhc';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
lhc | test1 | lhc | ts_lhc | f | f | f | f
lhc | test2 | lhc | ts_lhc | f | f | f | f
(2 rows)
postgres@10.206.132.17:5432=>alter table lhc.test1 add primary key (id);
ALTER TABLE
postgres@10.206.132.17:5432=>\d test1
Table "lhc.test1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
name | text | | |
Indexes:
"test1_pkey" PRIMARY KEY, btree (id)
Tablespace: "ts_lhc"
2.4.查看表、索引占用空间的大小
给测试表lhc.test1插入500百万数据
insert into lhc.test1 (id,name) select n,n||'_francs' from generate_series(1,5000000) n;
postgres@10.206.132.17:5432=>insert into lhc.test1 (id,name) select n,n||'_francs' from generate_series(1,5000000) n;
INSERT 0 5000000
查看表大小\dt+ lhc.test1
postgres@10.206.132.17:5432=>\dt+ lhc.test1
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+-------+-------------+--------+-------------
lhc | test1 | table | lhc | permanent | 249 MB |
(1 row)
查看索引大小\di+ test1_pkey
postgres@10.206.132.17:5432=>\di+ test1_pkey
List of relations
Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+------------+-------+-------+-------+-------------+--------+-------------
lhc | test1_pkey | index | lhc | test1 | permanent | 107 MB |
(1 row)
2.5.\sf查看函数代码
create or replace function lhc.random_range(integer,integer)
returns integer
language sql
as $function$
select ($1 + floor(($2 - $1 + 1) * random()))::int4;
$function$
SELECT lhc.random_range(1,10);
postgres@10.206.132.17:5432=>SELECT lhc.random_range(1,10);
random_range
--------------
6
(1 row)
postgres@10.206.132.17:5432=>\sf lhc.random_range
CREATE OR REPLACE FUNCTION lhc.random_range(integer, integer)
RETURNS integer
LANGUAGE sql
AS $function$
select ($1 + floor(($2 - $1 + 1) * random()))::int4;
$function$
postgres@10.206.132.17:5432=>
2.6.设置查询结果输出
postgres@10.206.132.17:5432=>select * from lhc.test1 limit 1;
id | name
----+----------
1 | 1_francs
(1 row)
postgres@10.206.132.17:5432=>\x
Expanded display is on.
postgres@10.206.132.17:5432=>select * from lhc.test1 limit 1;
-[ RECORD 1 ]--
id | 1
name | 1_francs
2.7.获取元命令对应的SQL代码
psql -U lhc -h 10.206.132.17 -p 5432 -d postgres -E
[pgsql@oracle ~]$psql -U lhc -h 10.206.132.17 -p 5432 -d postgres -E
psql (13.4)
Type "help" for help.
postgres@10.206.132.17:5432=>\db
********* QUERY **********
SELECT spcname AS "Name",
pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace
ORDER BY 1;
**************************
List of tablespaces
Name | Owner | Location
------------+----------+-------------------------------------------
pg_default | postgres |
pg_global | postgres |
ts_lhc | lhc | /data/postgresql/pgdata/tablespace/ts_lhc
(3 rows)
2.8.?元命令列出所有的元命令
postgres@10.206.132.17:5432=>\?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [(OPTIONS)] [FILE] execute query (and send results to file or |pipe);
\g with no arguments is equivalent to a semicolon
\gdesc describe result of query, without executing it
\gexec execute query, then execute each value in its result
\gset [PREFIX] execute query and store results in psql variables
\gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
Help
\? [commands] show help on backslash commands
\? options show help on psql command-line options
\? variables show help on special variables
\h [NAME] help on syntax of SQL commands, * for all commands
2.9.便捷的HELP命令
\h create tablespace
\h元命令后面不接任何sql命令则会列出所有的sql命令。
postgres@10.206.132.17:5432=>\h create tablespace
Command: CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
LOCATION 'directory'
[ WITH ( tablespace_option = value [, ... ] ) ]
URL: https://www.postgresql.org/docs/13/sql-createtablespace.html
2.10.列出所有的schema
postgres=> \dn
List of schemas
Name | Owner
------------+----------
lhc | lhc
lhcprivate | lhc
public | postgres
(3 rows)
三、psql导入、导出表数据
psql支持文件数据导入到数据库，也支持数据库表数据导出到文件中。
copy和\copy命令都支持这两类操作，但两者有以下区别:
(1)copy命令是sql命令，\copy是元命令。
(2)copy命令必须具有superuser超级权限，而\copy元命令不需要superuser权限。
(3)copy命令读取或写入数据库服务端主机上的文件，而\copy元命令是从psql客户端主机读取或写入文件。
(4)从性能看，大数据量导出到文件或大文件数据导入数据库，copy比\copy性能高。
3.1.使用copy命令导入导出数据
导入
postgres=> \d lhc.test1
Table "lhc.test1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
name | text | | |
Tablespace: "ts_lhc"
/home/pgsql/testData/test_copy_lhc_test1.txt
[pgsql@oracle testData]$cat test_copy_lhc_test1.txt
1 a
2 b
3 c
4 d
5 e
select count(*) from lhc.test1;
postgres@10.206.132.17:5432=#select count(*) from lhc.test1;
count
-------
5
(1 row)
copy lhc.test1 from '/home/pgsql/testData/test_copy_lhc_test1.txt';
postgres=# copy lhc.test1 from '/home/pgsql/testData/test_copy_lhc_test1.txt';
COPY 5
postgres=# select * from lhc.test1;
id | name
----+------
1 | a
2 | b
3 | c
4 | d
5 | e
(5 rows)
[pgsql@oracle testData]$psql postgres lhc
psql (13.4)
Type "help" for help.
copy命令必须使用超级用户
postgres=> copy lhc.test1 from '/home/pgsql/testData/test_copy_lhc_test1.txt';
ERROR: must be superuser or a member of the pg_read_server_files role to COPY from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.'
导出
copy lhc.test1 to '/home/pgsql/testData/export_copy_lhc_test1.txt';
copy lhc.test1 to '/home/pgsql/testData/export_copy_lhc_test1.csv';
copy lhc.test1 to '/home/pgsql/testData/export_copy_lhc_test1.csv' with csv header;
with csv header是指导出格式为csv格式并且显示字段名称。
如何仅导出表的一部分数据呢？
copy (select * from lhc.test1 limit 3) to '/home/pgsql/testData/export_limit_lhc_test1.txt';
将表数据输出到标准输出，而且不需要超级用户权限
copy lhc.test1 to stdout;
postgres@10.206.132.17:5432=#copy lhc.test1 to stdout;
1 a
2 b
3 c
4 d
5 e
3.2.使用命令\copy元命令导入导出数据
导出
psql postgres lhc
\copy lhc.test1 to '/home/pgsql/testData/export_copy_test1.txt';
[pgsql@oracle ~]$psql postgres lhc
psql (13.4)
Type "help" for help.
postgres@[local:/data/postgresql/pgdata]:5432=>\copy lhc.test1 to '/home/pgsql/testData/export_copy_test1.txt';
COPY 5
[pgsql@oracle testData]$cat export_copy_test1.txt
1 a
2 b
3 c
4 d
5 e
导入
[pgsql@oracle testData]$cat test_copy_test1.txt
6 f
test_copy_test1.txt
\copy lhc.test1 from '/home/pgsql/testData/test_copy_test1.txt';
postgres=# \copy lhc.test1 from '/home/pgsql/testData/test_copy_test1.txt';
COPY 1
postgres=# select * from lhc.test1;
id | name
----+------
1 | a
2 | b
3 | c
4 | d
5 | e
6 | f
(6 rows)
注意:小表\copy;大表copy.
四、psql语法和选项介绍
4.1.-A设置非对齐输出模式
psql -c "select * from lhc.test1 where id = 1" postgres lhc
[pgsql@oracle testData]$psql -c "select * from lhc.test1 where id = 1" postgres lhc
id | name
----+------
1 | a
(1 row)
[pgsql@oracle testData]$psql -A -c "select * from lhc.test1 where id = 1" postgres lhc
id|name
1|a
(1 row)
4.2.-t只显示记录数据
psql -t -c "select * from lhc.test1 where id = 1" postgres lhc
[pgsql@oracle testData]$psql -t -c "select * from lhc.test1 where id = 1" postgres lhc
1 | a
[pgsql@oracle testData]$psql -A-t -c "select * from lhc.test1 where id = 1" postgres lhc
[pgsql@oracle testData]$psql -A -t -c "select * from lhc.test1 where id = 1" postgres lhc
1|a
[pgsql@oracle testData]$psql -At -c "select * from lhc.test1 where id = 1" postgres lhc
1|a
4.3.-q不显示输出信息
psql lhcdb lhc
创建schema
create schema lhcPrivate authorization lhc;
create schema authorization lhc;
vim test_q.sql
create table lhc.test_q(id int);
truncate table lhc.test_q;
insert into lhc.test_q values (1);
insert into lhc.test_q values (2);
[pgsql@oracle testData]$psql -q lhcdb lhc -f test_q.sql
五、psql执行sql脚本
5.1.psql执行sql脚本
c选项支持在操作系统层面通过psql向数据库发起sql命令
-c后接执行的sql命令，可以使用单引号或双引号
psql -c "select current_user"
psql -At -c "select current_user"
[pgsql@oracle testData]$psql -c "select current_user"
current_user
--------------
postgres
(1 row)
[pgsql@oracle testData]$psql -At -c "select current_user"
postgres
-f参数导入此脚本
cd /home/pgsql/testData
vim test3.sql
create table lhc.test3(id int4);
insert into lhc.test3 values (1);
insert into lhc.test3 values (2);
insert into lhc.test3 values (3);
psql postgres lhc -f /home/pgsql/testData/test3.sql
[pgsql@oracle testData]$psql postgres lhc -f /home/pgsql/testData/test3.sql
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
六、psql如何传递变量到sql
6.1.\set元命令方式传递变量
lhcdb=> \set v_id 2
lhcdb=> select * from test_q where id = :v_id;
id
----
2
(1 row)
--取消变量
lhcdb=> \set v_id
6.2.psql的-v参数传递变量
[pgsql@oracle testData]$cat test_1.sql
select * from lhc.test_q where id=:v_id;
[pgsql@oracle testData]$psql -v v_id=1 lhcdb lhc -f test_1.sql
id
----
1
(1 row)
七、使用psql定制日常维护脚本
7.1.定制日常维护脚本:查询会话
vim ~/.psqlrc
\set select_session 'SELECT pid,usename,datname,query,client_addr,state FROM pg_catalog.pg_stat_activity WHERE pid <> pg_backend_pid() ORDER BY query;'
使用
[pgsql@oracle ~]$psql -U postgres -h 10.206.132.17 -p 5432 -d postgres -E
psql (13.4)
Type "help" for help.
postgres@10.206.132.17:5432=#:select_session
state指进程的状态，主要值为:
active:后台进程正在执行的SQL.
idle:后台进程为空闲状态，等待后续客户端发出的命令.
idle in transaction:后台进程正在事务中，并不是指正在执行SQL.
idle in transaction(aborted):和idle in transaction状态类似，只是事务中的部分SQL异常.
7.2.定制日常维护脚本:查询等待事件
\set wait_event 'SELECT pid,usename,datname,query,client_addr,wait_event_type,wait_event FROM pg_catalog.pg_stat_activity WHERE pid <> pg_backend_pid() AND wait_event IS NOT NULL ORDER BY wait_event_type;'
7.3.定制日常维护脚本:查询数据库连接数
\set connections 'SELECT datname,usename,client_addr,count(*) count FROM pg_catalog.pg_stat_activity WHERE pid <> pg_backend_pid() GROUP BY 1,2,3 ORDER BY 1,2,4 DESC;'
postgres@10.206.132.17:5432=#:connections
datname | usename | client_addr | count
----------+----------+--------------+-------
postgres | postgres | 10.210.44.68 | 2
| postgres | | 1
| | | 4
(3 rows)
八、psql亮点功能
8.1.\timing显示SQL执行时间
\timing
lhcdb=> \timing
Timing is on.
lhcdb=> select * from test_q;
id
----
1
2
(2 rows)
Time: 0.603 ms
lhcdb=> \timing
Timing is off.
8.2.\watch反复执行当前sql
以秒为单位，默认为2秒
lhcdb=> select now();
now
-------------------------------
2021-09-07 16:13:38.240021+08
(1 row)
lhcdb=> \watch 1
Tue 07 Sep 2021 04:13:55 PM CST (every 1s)
now
-------------------------------
2021-09-07 16:13:55.320362+08
(1 row)
Tue 07 Sep 2021 04:13:56 PM CST (every 1s)
now
-------------------------------
2021-09-07 16:13:56.339286+08
(1 row)
Tue 07 Sep 2021 04:13:57 PM CST (every 1s)
tab键自动补全
不管用
8.3.psql客户端提示符
vim ~/.psqlrc
\set PROMPT1 '%/@%M:%>%R%#'
%/:当前数据库名称
%M:数据库服务器别名，不是指主机名，显示的是psql的-h参数设置的值；
当连接建立在Unix域套接字上时则是[local].
%>:数据库服务器的端口号.
%n:数据库会话用户名.
%#:如果是超级用户则显示"#"，其他用户显示">".
%p:当前数据库连接的后台进程号.
%R:在PROMPT1中通常显示"=",如果进程被断开则显示"!".
[pgsql@oracle ~]$psql -U postgres -h 10.206.132.17 -d postgres -p 5432
Password for user postgres:
psql (13.4)
Type "help" for help.
postgres@10.206.132.17:5432=#
PROMPT1是指当psql等待新命令发出时的常规提示符，这个提示符使用得最多.