PostgreSQL的常见参数和技巧

[postgres@host01 ~]$ 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.


Report bugs to <pgsql-bugs@postgresql.org>.
复制

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


postgres=# \c yx
You are now connected to database "yx" as user "postgres".
yx=# \d
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'table' WHEN 'I' THEN 'index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************


          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | student | table | postgres
 public | yx      | table | postgres
(2 rows)
复制

[postgres@host01 ~]$ psql yx -c "select * from student;"
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
(1 row)
复制

[postgres@host01 ~]$ psql yx -c "select * from student;select count(1) from student;"
 count 
-------
     1
(1 row)
复制

[postgres@host01 ~]$ psql yx -c "insert into student values(2,'bbbb',1024);insert into student values(3,'cccc',1025);select * from student;"
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
  2 | bbbb                             | 1024 
  3 | cccc                             | 1025 
(3 rows)
复制

[postgres@host01 ~]$ psql yx -c "select * from student;" -c "select count(1) from student;"
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
  2 | bbbb                             | 1024 
  3 | cccc                             | 1025 
(3 rows)


 count 
-------
     3
(1 row)
复制

[postgres@host01 ~]$ cat test.sql
select * from student;
select count(1) from student;
复制

[postgres@host01 ~]$ psql yx -f test.sql
 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
  2 | bbbb                             | 1024 
  3 | cccc                             | 1025 
(3 rows)


 count 
-------
     3
(1 row)
复制

[postgres@host01 ~]$ cat test.sql 
select * from student where id=:1;
select * from student where id=:b;
复制

[postgres@host01 ~]$ psql yx -f test.sql -v 1=2 -v b=1
 id |               name               | number 
----+----------------------------------+--------
  2 | bbbb                             | 1024 
(1 row)


 id |               name               | number 
----+----------------------------------+--------
  1 | aaa                              | 1023 
(1 row)
复制

[postgres@host01 ~]$ cat test.sql 
select * from :tab1 where id=2;
select * from :tab2 where id=1;
复制

[postgres@host01 ~]$ psql yx -f test.sql -v tab1=student -v tab2=t1
 id |               name               | number 
----+----------------------------------+--------
  2 | bbbb                             | 1024 
(1 row)


 id | name 
----+------
  1 | yx
(1 row)
复制

[postgres@host01 ~]$ psql -V
psql (PostgreSQL) 11.2


[postgres@host01 ~]$ psql -l
Password for user postgres: 
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 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
 yx        | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)


[postgres@host01 ~]$ psql -d yx
Password for user postgres: 
psql (11.2)
Type "help" for help.


yx=# select current_database();
 current_database 
------------------
 yx
(1 row)
复制

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)
复制

[postgres@host01 ~]$ ls $PGDATA/pg_hba.conf
/pgdata/pg_hba.conf
复制

# TYPE  DATABASE        USER            ADDRESS                 METHOD


# "local" is for Unix domain socket connections only
local   all             all                                     password
#local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.12.10/24        trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
复制

# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.
复制

[postgres@host01 ~]$ pg_ctl reload
server signaled
复制