PSQL的使用

psql使用
-bash-4.2$ psql --help
psql is the PostgreSQL interactive terminal.

Usage:
psql [OPTION]… [DBNAME [USERNAME]]

General options:
-c, --command=COMMAND run only single command (SQL or internal) and exit
-d, --dbname=DBNAME database name to connect to (default: “postgres”)
-f, --file=FILENAME execute commands from file, then exit
-l, --list list available databases, then exit
-v, --set=, --variable=NAME=VALUE
set psql variable NAME to VALUE
(e.g., -v ON_ERROR_STOP=1)
-V, --version output version information, then exit
-X, --no-psqlrc do not read startup file (~/.psqlrc)
-1 (“one”), --single-transaction
execute as a single transaction (if non-interactive)
-?, --help[=options] show this help, then exit
–help=commands list backslash commands, then exit
–help=variables list special variables, then exit

Input and output options:
-a, --echo-all echo all input from script
-b, --echo-errors echo failed commands
-e, --echo-queries echo commands sent to server
-E, --echo-hidden display queries that internal commands generate
-L, --log-file=FILENAME send session log to file
-n, --no-readline disable enhanced command line editing (readline)
-o, --output=FILENAME send query results to file (or |pipe)
-q, --quiet run quietly (no messages, only query output)
-s, --single-step single-step mode (confirm each query)
-S, --single-line single-line mode (end of line terminates SQL command)

Output format options:
-A, --no-align unaligned table output mode
-F, --field-separator=STRING
field separator for unaligned output (default: “|”)
-H, --html HTML table output mode
-P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command)
-R, --record-separator=STRING
record separator for unaligned output (default: newline)
-t, --tuples-only print rows only
-T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border)
-x, --expanded turn on expanded table output
-z, --field-separator-zero
set field separator for unaligned output to zero byte
-0, --record-separator-zero
set record separator for unaligned output to zero byte

Connection options:
-h, --host=HOSTNAME database server host or socket directory (default: “local socket”)
-p, --port=PORT database server port (default: “5432”)
-U, --username=USERNAME database user name (default: “postgres”)
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)

For more information, type “?” (for internal commands) or “\help” (for SQL commands) from within psql, or consult the psql section in the PostgreSQL documentation.

psql使用
psql –h hostname –p 5432 –d dbname –U username –W

[root@node201 ~]# su - postgres
Last login: Sat Apr 3 10:13:03 CST 2021 on pts/1
-bash-4.2$ psql -h localhost -p5432 -dtest
psql: FATAL: Ident authentication failed for user “postgres”
-bash-4.2$

编辑文件/etc/postgresql/8.4/main/pg_hba.conf并用md5或trust替换ident或peer，具体取决于您是否希望它在您自己的计算机上要求输入密码。
解决办法：
[root@node201 ~]# vim /var/lib/pgsql/9.6/data/pg_hba.conf #将下面三行注释取消，同时将ident和peer改成trust

replication privilege.

local replication postgres trust
host replication postgres 127.0.0.1/32 trust
host replication postgres ::1/128 trust

改完后重启，还是不能登录，查看日志如下：
[root@node201 ~]# tail /var/lib/pgsql/9.6/data/pg_log/postgresql-Sat.log -f
…
< 2021-04-03 11:06:13.846 CST > LOG: could not connect to Ident server at address “::1”, port 113: Connection refused
< 2021-04-03 11:06:13.846 CST > FATAL: Ident authentication failed for user “postgres”
< 2021-04-03 11:06:13.846 CST > DETAIL: Connection matched pg_hba.conf line 84: “host all all ::1/128 ident”

解决办法 ：
修改84行内容为trust：

IPv6 local connections:

host all all ::1/128 trust

重启重新登录：
[root@node201 ~]# systemctl restart postgresql-9.6

-bash-4.2$ psql -h localhost -p5432 -dtest
psql (9.6.21)
Type “help” for help.

查询当前时间：
$ psql -c “SELECT current_time”
-bash-4.2$ psql -h localhost -p5432 -dtest -c “select current_time” #用这种方式可以去执行一些批处理
timetz

11:22:11.273859+08
(1 row)

执行批外理：
-bash-4.2$ cat test.sql
select current_time

-bash-4.2$ psql -h localhost -p5432 -dtest -f test.sql
timetz

11:25:54.569173+08
(1 row)

查看帮助
postgres=# help #查看帮助

postgres=# \h DELETE #查看delete的用法

test=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, …] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING from_item [, …] ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, …] ]

psql -d postgresql_book -c “DROP TABLE IF EXISTS dross; CREATE SCHEMA staging;”

postgres=# \timing on #开启计时
Timing is on.
postgres=# select count(*) from pg_tables; #执行命令，查看命令执行时间
count

62

(1 row)

Time: 22.846 ms

postgres=# \set autocommit off #关闭自动提交

postgres=# create table test(id int); #创建一个表
CREATE TABLE
Time: 27.924 ms
postgres=# insert into test values(1); #插入值
INSERT 0 1
Time: 2.355 ms
postgres=# rollback;
WARNING: there is no transaction in progress
ROLLBACK
Time: 11.426 ms
postgres=# select * from test;
id

1
(1 row)

Time: 0.970 ms

#好像失败了，原因是上面的\set autocommit off中的autocommit必须大写；

UPDATE census.facts SET short_name = ‘this is a mistake’;
ROLLBACK;
COMMIT;

设置变量
\set eav ‘EXPLAIN ANALYZE VERBOSE’ #设置变量eav；
:eav SELECT COUNT(*) FROM pg_tables;

设置历史记录
\set HISTSIZE 10

\set HISTFILE ~/.psql_history- :HOST - :DBNAME

\l 可以显示当前数据库服务器中所有的数据库名
\dt 列出当前数据库中数据表
\du (查看用户)
\dt: 查看所有自己创建的表
\dt+: 查看所有自己创建的表，显示表的相关内容占的磁盘大小

\dt+ pg_catalog.pg_t*
postgres=# \dt+ pg_catalog.pg_t*
List of relations
Schema | Name | Type | Owner | Size | Description
------------±-----------------±------±---------±-----------±------------
pg_catalog | pg_tablespace | table | postgres | 40 kB |
pg_catalog | pg_transform | table | postgres | 0 bytes |
pg_catalog | pg_trigger | table | postgres | 8192 bytes |
pg_catalog | pg_ts_config | table | postgres | 40 kB |
pg_catalog | pg_ts_config_map | table | postgres | 48 kB |
pg_catalog | pg_ts_dict | table | postgres | 40 kB |
pg_catalog | pg_ts_parser | table | postgres | 40 kB |
pg_catalog | pg_ts_template | table | postgres | 40 kB |
pg_catalog | pg_type | table | postgres | 104 kB |
(9 rows)

postgres=# \dt *.
List of relations
Schema | Name | Type | Owner
--------------------±------------------------±------±---------
information_schema | sql_features | table | postgres
information_schema | sql_implementation_info | table | postgres
information_schema | sql_languages | table | postgres
information_schema | sql_packages | table | postgres
information_schema | sql_parts | table | postgres
information_schema | sql_sizing | table | postgres
information_schema | sql_sizing_profiles | table | postgres
pg_catalog | pg_aggregate | table | postgres
pg_catalog | pg_am | table | postgres
…
pg_catalog | pg_user_mapping | table | postgres
public | test | table | postgres
(63 rows)

postgres=# \dt test
List of relations
Schema | Name | Type | Owner
--------±-----±------±---------
public | test | table | postgres
(1 row)

\d+ pg_ts_dict

将数据库拷贝出来：
postgres=# copy test to ‘/tmp/test.txt’;
COPY 3
Time: 0.742 ms

#查看
[root@node201 ~]# ls -al /tmp/test.txt
-rw-r–r-- 1 postgres postgres 6 Apr 3 12:18 /tmp/test.txt
[root@node201 ~]# cat /tmp/test.txt
1
3
4