PostgreSQL常用命令

PostgreSQL常用命令

𝓓𝓸𝓷 Lv6
  2024-07-10 10:05:45 2024-07-10 10:05:45 Created   2024-07-20 05:15:41 2024-07-20 05:15:41 Updated    

PostgreSQL命令, PG命令

1
2
3
4
5
[postgres@server ~]$ psql -p 1314
psql (10.18)
Type "help" for help.

postgres=# \q
1. -h -H
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
--- -h表示要连接到的主机，或用于指定socket文件路径 
--- -H表示以html格式输出文件信息


[postgres@server ~]$ psql -h localhost
psql (10.12)
Type "help" for help.

[postgres@server ~]$ psql -h 192.168.1.136
psql (10.12)
Type "help" for help.

postgres=# \q


[postgres@server ~]$ ls -la /tmp/ |grep 5432
srwxrwxrwx   1 postgres postgres    0 Aug 14 16:49 .s.PGSQL.5432
-rw-------   1 postgres postgres   48 Aug 14 16:49 .s.PGSQL.5432.lock
[postgres@server ~]$ 
[postgres@server ~]$ 
[postgres@server ~]$ psql -h /tmp
psql (10.12)
Type "help" for help.




[postgres@server ~]$ psql -H
psql (10.12)
Type "help" for help.

postgres=# select 1;
<table border="1">
  <tr>
    <th align="center">?column?</th>
  </tr>
  <tr valign="top">
    <td align="right">1</td>
  </tr>
</table>
<p>(1 row)<br />
</p>

2. -p
1
2
3
4
5
6
--- -p指定端口

[postgres@server ~]$ psql -p 5432
psql (10.12)
Type "help" for help.

3.-U
1
2
3
4
5
6
--- -U表示用户名

[postgres@server ~]$ psql -U postgres
psql (10.12)
Type "help" for help.

4. -d
1
2
3
4
5
6
7
--- -d表示数据库名

[postgres@server ~]$ psql -U admin -d postgres
psql (10.12)
Type "help" for help.

postgres=>
5. -c
1
2
3
4
5
6
7
8
--- -c表示psql客户端以非交互方式执行单行命令

[postgres@server ~]$ psql -c "select 1"
 ?column? 
----------
        1
(1 row)

6. -e
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--- -e表示输出结果中包含执行SQL的语句

[postgres@server ~]$ psql -e
psql (10.12)
Type "help" for help.

postgres=# select 1;
select 1;
 ?column? 
----------
        1
(1 row)


7. -E
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--- -E将隐藏的SQL显示出来

[postgres@server ~]$ psql -E
psql (10.12)
Type "help" for help.

postgres=# select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 tbs_test   |    16391 |        | 
(3 rows)

postgres=# \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 | 
 tbs_test   | admin    | /opt/pgdata
(3 rows)

8. -f
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
--- -f执行指定文件中的SQL语句 

[postgres@server ~]$ more /tmp/result.txt
select current_timestamp;

select current_user;

[postgres@server ~]$ psql -U admin -d postgres -f /tmp/result.txt
       current_timestamp       
-------------------------------
 2023-08-14 15:11:45.577231+08
(1 row)

 current_user 
--------------
 admin
(1 row)


[postgres@server ~]$ psql -p1314 -f /tmp/psql.txt  -e
           List of tablespaces
    Name    |  Owner   |    Location     
------------+----------+-----------------
 hr_data    | postgres | /pgdata/hr_data
 pg_default | postgres | 
 pg_global  | postgres | 
(3 rows)

select * from pg_user
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil 
| useconfig 
----------+----------+-------------+----------+---------+--------------+----------+----------
+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          
| 
 hr       |    16402 | f           | f        | f       | f            | ******** |          
| 
(2 rows)

9. -F
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
*--- -F使用分割符提示, 该参数与-A一起使用

[postgres@server ~]$ psql -A -F'--------------->'
psql (10.12)
Type "help" for help.

postgres=# select 1;
?column?
1
(1 row)

postgres=# select 1,2,3;
?column?--------------->?column?--------------->?column?
1--------------->2--------------->3
(1 row)


[postgres@server ~]$ psql -p1314 -A -F '|'
psql (10.18)
Type "help" for help.

postgres=# select 1,2,3;
?column?|?column?|?column?
1|2|3
(1 row)
10. -l -L
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
--- -l列出实例中的数据库
--- -L发送会话日志记录到文件

[postgres@server ~]$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 mydb111   | admin    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)


[postgres@server ~]$ psql -L /tmp/session.log
psql (10.12)
Type "help" for help.

postgres=# select 1;
 ?column? 
----------
        1
(1 row)

postgres=# \db
         List of tablespaces
    Name    |  Owner   |  Location   
------------+----------+-------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | admin    | /opt/pgdata
(3 rows)

postgres=# \q


[postgres@server ~]$ more /tmp/session.log 
********* QUERY **********
select 1;
**************************

 ?column? 
----------
        1
(1 row)

         List of tablespaces
    Name    |  Owner   |  Location   
------------+----------+-------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs_test   | admin    | /opt/pgdata
(3 rows)
11. -o
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--- -o将输出结果输出到指定文件，只有结果，不带查询语句

[postgres@server ~]$ psql -o /tmp/out.txt
psql (10.12)
Type "help" for help.

postgres=# select 1;
postgres=# select * from pg_tablespace;
postgres=# \q

[postgres@server ~]$ more /tmp/out.txt 
 ?column? 
----------
        1
(1 row)

  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 tbs_test   |    16391 |        | 
(3 rows)

12. -E
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
--- -E将隐藏的SQL显示出来

[postgres@server ~]$ psql -E
psql (10.12)
Type "help" for help.

postgres=# select * from pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions 
------------+----------+--------+------------
 pg_default |       10 |        | 
 pg_global  |       10 |        | 
 tbs_test   |    16391 |        | 
(3 rows)

postgres=# \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 | 
 tbs_test   | admin    | /opt/pgdata
(3 rows)

13. -q
1
2
3
4
5
6
7
8
9
--- -q静默方式登录, 不会显示登录信息

[postgres@server ~]$ psql -q 
postgres=# select 1;
 ?column? 
----------
        1
(1 row)

14. -s
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--- -s每次执行SQL语句之前，需要按回车键确认后才能执行

[postgres@server ~]$ psql -s
psql (10.12)
Type "help" for help.

postgres=# select 1;
***(Single step mode: verify command)*******************************************
select 1;
***(press return to proceed or enter x and return to cancel)********************

 ?column? 
----------
        1
(1 row)

postgres=# select * from pg_user;
***(Single step mode: verify command)*******************************************
select * from pg_user;
***(press return to proceed or enter x and return to cancel)********************

15.-b
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
---输出执行错误的命令语句

[postgres@server ~]$ psql
psql (14.12)
Type "help" for help.

postgres=# select 1%;
ERROR:  syntax error at or near ";"
LINE 1: select 1%;
                 ^

[postgres@server ~]$ psql -b
psql (14.12)
Type "help" for help.

postgres=# select 1%;
ERROR:  syntax error at or near ";"
LINE 1: select 1%;
                 ^
STATEMENT:  select 1%;
16.-v
1
2
3
--- -v设置系统参数

[postgres@server ~]$ psql -v HISTSIZE = '500'
17.-w -W
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--- -w不需要密码提示符(默认)
--- -W强制需要密码提示符

[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# \q
[postgres@server ~]$ psql -w
psql (10.12)
Type "help" for help.

postgres=# \q
[postgres@server ~]$ psql -W
Password: 
psql (10.12)
Type "help" for help.
18. -z
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--- -z显示字段之间没有分割

[postgres@server ~]$ psql
psql (10.12)
Type "help" for help.

postgres=# select 1 as col1, 2 as col2;
 col1 | col2 
------+------
    1 |    2
(1 row)

[postgres@server ~]$ psql -A
psql (10.12)
Type "help" for help.

postgres=# select 1 as col1, 2 as col2;
col1|col2
1|2
(1 row)
postgres=# \q


[postgres@server ~]$ psql -A -z
psql (10.12)
Type "help" for help.

postgres=# select 1 as col1, 2 as col2;
col1col2
12
(1 row)


  • Title: PostgreSQL常用命令
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-10 10:05:45
  • Updated at : 2024-07-20 05:15:41
  • Link: https://www.zhangdong.me/postgresql-command.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
PostgreSQL常用元命令 PostgreSQL常用元命令 PostgreSQL配置127.0.0.1登录 PostgreSQL配置127.0.0.1登录 Centos7.9安装Postgresql Centos7.9安装Postgresql
推荐文章
PostgreSQL常用元命令 PostgreSQL常用元命令 PostgreSQL配置127.0.0.1登录 PostgreSQL配置127.0.0.1登录
评论
  1. 1. -h -H
  2. 2. -p
  3. 3.-U
  4. 4. -d
  5. 5. -c
  6. 6. -e
  7. 7. -E
  8. 8. -f
  9. 9. -F
  10. 10. -l -L
  11. 11. -o
  12. 12. -E
  13. 13. -q
  14. 14. -s
  15. 15.-b
  16. 16.-v
  17. 17.-w -W
  18. 18. -z