postgres=# \help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres-# \? General \copyright show PostgreSQL usage and distribution terms \crosstabview [COLUMNS] execute query and display result in crosstab \errverbose show most recent error message at maximum verbosity \g [(OPTIONS)] [FILE] execute query (and send result 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 result 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
## 修改postgres用户密码 [root@Rocky8-2 ~]# psql postgres=# alter user postgres with password '123456'; ## 修改监听地址和端口 [root@Rocky8-2 ~]# vim /pgsql/data/postgresql.conf listen_addresses = '*' # what IP address(es) to listen on; #listen_addresses = 'localhost'# what IP address(es) to listen on; ## 在pg_hba.conf中添加一行 host all all 0.0.0.0/0 md5
[root@Rocky8-2 ~]# vim /pgsql/data/pg_hba.conf # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 0.0.0.0/0 md5 ## 重启服务 [root@Rocky8-2 ~]# su - postgres [postgres@Rocky8-2 ~]$ pg_ctl restart -mf waiting for server to shut down.... done server stopped waiting for server to start....2023-09-11 17:24:21.580 CST [1611] LOG: starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit 2023-09-11 17:24:21.581 CST [1611] LOG: listening on IPv4 address "0.0.0.0", port 5432 2023-09-11 17:24:21.581 CST [1611] LOG: listening on IPv6 address "::", port 5432 2023-09-11 17:24:21.583 CST [1611] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2023-09-11 17:24:21.588 CST [1614] LOG: database system was shut down at 2023-09-11 17:24:21 CST 2023-09-11 17:24:21.592 CST [1611] LOG: database system is ready to accept connections done server started ## 查看监听端口 [postgres@Rocky8-2 ~]$ ss -ntl State Recv-Q Send-Q Local Address:Port Peer Address:Port Process LISTEN 0 128 0.0.0.0:111 0.0.0.0:* LISTEN 0 128 0.0.0.0:22 0.0.0.0:* LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:111 [::]:* LISTEN 0 128 [::]:22 [::]:* LISTEN 0 244 [::]:5432 [::]:* ## 在远程机器测试连接 [root@Rocky8-1 ~]# psql -h 10.0.0.12 -p 5432 -U postgres Password for user postgres: psql (15.3) Type "help" for help.
postgres=#
设置显示信息的格式
1 2 3 4 5 6 7 8 9 10
## 调整横向和纵向显示 postgres=# \x Expanded display is on. ## 显示命令执行的时长 postgres=# timing on ## 显示详细信息，可打印出报出问题的源代码位置 postgres-# \set VERBOSITY verbose
postgres=# \c testdb1 You are now connected to database "testdb1" as user "postgres". testdb1=# create table tb2 (id serial primary key,name text); CREATE TABLE
testdb1=# \d tb2; Table "public.tb1" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------------------------------- id | integer | | not null | nextval('tb1_id_seq'::regclass) name | text | | | Indexes: "tb1_pkey" PRIMARY KEY, btree (id) testdb1=# insert into tb2 (name) values ('wang');
testdb1=# select * from tb2;
testdb1=# update tb2 set name='zhang' where id=2;
testdb1=# delete from tb2 where id=2;
testdb1=# drop table tb1;
查看数据库用户
1 2 3 4 5 6 7 8 9 10 11
postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \dg List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
事务管理
1 2 3 4 5 6 7 8 9 10 11 12 13 14
## 开始事务 BEGIN testdb1-# \h begin Command: BEGIN Description: start a transaction block Syntax: BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] ## 提交事务 COMMIT | END ## 取消事务 需要在关闭自动提交状态下：\set AUTOCOMMIT OFF ROLLBACK ## 查看事务ID select txid_current();
