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=# \c You are now connected to database "postgres" as user "postgres". postgres=# \conninfo You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
## 使用数据库 postgres=# \c testdb1 You are now connected to database "testdb1" as user "postgres". testdb1=#
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();