[root@wallet01 ~]# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm --no-check-certificate
[root@wallet01 ~]# rpm -ivh pgdg-centos96-9.6-3.noarch.rpm

[root@wallet01 ~]# useradd postgres
[root@wallet01 ~]# id postgres
uid=501(postgres) gid=501(postgres) groups=501(postgres)

[root@wallet01 ~]# rpm -e postgresql postgresql-devel

[root@wallet01 ~]# yum install -y postgresql96-server.x86_64 postgresql96-contrib.x86_64
[root@wallet01 ~]# service postgresql-9.6 initdb
Initializing database:                                     [  OK  ]

[root@wallet01 ~]# vi /var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses = '0.0.0.0'
port = 5432 

[root@wallet01 ~]# service postgresql-9.6 start
Starting postgresql-9.6 service:                           [  OK  ]

[root@wallet01 ~]# service postgresql-9.6 status
postgresql-9.6 (pid  2146) is running...

[root@wallet01 ~]# netstat -tunlp | grep postmaster
tcp        0      0 0.0.0.0:5432        0.0.0.0:*        LISTEN      2414/postmaster

[root@wallet01 ~]# vi /etc/profile
PATH=$PATH:/usr/pgsql-9.6/bin

[root@wallet01 ~]# su - postgres
[postgres@wallet01 ~]$ psql
psql (9.6.12)
Type "help" for help.
postgres=# \l
                                  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
(3 rows)


参数解析
listen_address:服务器监听客户端连接的TPC/IP地址

port:服务器监听的TPC端口

max_connections:允许和数据库连接的最大并发连接数

superuser_reserved_connections:为超级用户连接而保留的连接数

tcp_keepalives_idle:在一个TCP连接中空闲多长时间后会发送一个keepalive报文(建议值180)

tcp_keepalives_interval:在一个空闲TCP连接中,在发送第一个keepalive报文后如果在该参数指定的时间间隔内没有收到对
端的响应报文,则开始发送第二个keepalive报文(建议值10)

tcp_keepalives_count:在一个空闲TCP连接中,在发送keepalive报文后,如果一直没有收到对端的响应报文,最多发送该参数
指定次数报文后,认为TCP连接已中断(建议值3)

shared_buffers:数据库实例可使用的共享内存区域

temp_buffers:每个数据库会话使用的临时内存区,只用于访问临时表,在服务进程中分配的,属于本地内存

work_mem:排序与散列操作在使用临时磁盘文件之前可使用的内存区域,属于本地内存

maintenance_work_mem:在维护性操作中可使用的内存区域

wal_level:决定有多少信息可写入WAL日志中

fsync:决定是否使用fsync系统调用,将文件系统中的脏页写到物理磁盘

synchronous_commit:提交一个事务是否需要等待将WAL日志写入磁盘后在返回

wal_sync_method:指定向磁盘写WAL日志的方法

full_page_writes:数据库实例会在检查点之后对页面第一次修改时将整个页面写入WAL日志

wal_buffers:WAL日志使用的内存区域,属于数据库实例共享内存

log_destination:stderr,csvlog,syslog

log_directory:日志输出的路径

log_filename:日志文件名

log_rotation_age:日志超过多长时间,就生成一个新的文件

log_rotation_size:日志超过多大,就生成一个新的文件

log_truncate_on_rotation:当生成的新文件的文件名已存在,是否覆盖同名旧文件


[postgres@king01 ~]$ psql -h 192.168.1.201 -p 5432 tpcc tpcc            
Password for user tpcc: 
psql (9.6.12)
Type "help" for help.
tpcc=> \l
                                  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
 tpcc      | tpcc     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

tpcc=> \dt
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | customer   | table | tpcc
 public | district   | table | tpcc
 public | history    | table | tpcc
 public | item       | table | tpcc
 public | new_order  | table | tpcc
 public | order_line | table | tpcc
 public | orders     | table | tpcc
 public | stock      | table | tpcc
 public | warehouse  | table | tpcc
(9 rows)

tpcc=> \di
                  List of relations
 Schema |     Name      | Type  | Owner |   Table    
--------+---------------+-------+-------+------------
 public | customer_i1   | index | tpcc  | customer
 public | customer_i2   | index | tpcc  | customer
 public | district_i1   | index | tpcc  | district
 public | item_i1       | index | tpcc  | item
 public | new_order_i1  | index | tpcc  | new_order
 public | order_line_i1 | index | tpcc  | order_line
 public | orders_i1     | index | tpcc  | orders
 public | orders_i2     | index | tpcc  | orders
 public | stock_i1      | index | tpcc  | stock
 public | warehouse_i1  | index | tpcc  | warehouse
(10 rows)

tpcc=> \df
                                                                        List of functions
 Schema |    Name     | Result data type |                                             Argument data types                                        
      |  Type  
--------+-------------+------------------+--------------------------------------------------------------------------------------------------------
------+--------
 public | dbms_random | integer          | integer, integer                                                                                       
      | normal
 public | delivery    | integer          | integer, integer                                                                                       
      | normal
 public | neword      | numeric          | integer, integer, integer, integer, integer, integer                                                   
      | normal
 public | ostat       | SETOF record     | integer, integer, integer, integer, character varying                                                  
      | normal
 public | payment     | integer          | integer, integer, integer, integer, numeric, integer, numeric, character varying, character varying, nu
meric | normal
 public | slev        | integer          | integer, integer, integer                                                                              
      | normal
(6 rows)

tpcc=> \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

tpcc=> \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)

tpcc=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {tpcc}
 tpcc      |                                                            | {}
 
tpcc=> \dp
                               Access privileges
 Schema |    Name    | Type  | Access privileges | Column privileges | Policies 
--------+------------+-------+-------------------+-------------------+----------
 public | customer   | table |                   |                   | 
 public | district   | table |                   |                   | 
 public | history    | table |                   |                   | 
 public | item       | table |                   |                   | 
 public | new_order  | table |                   |                   | 
 public | order_line | table |                   |                   | 
 public | orders     | table |                   |                   | 
 public | stock      | table |                   |                   | 
 public | warehouse  | table |                   |                   | 
(9 rows)

tpcc=> \pset border 2
Border style is 2.

tpcc=> \timing on
Timing is on.
tpcc=> select * from warehouse where w_zip = '763011111';
+------+------------+--------+-----------+------------------+----------------+-----------------+---------+-----------+
| w_id |   w_ytd    | w_tax  |  w_name   |    w_street_1    |   w_street_2   |     w_city      | w_state |   w_zip   |
+------+------------+--------+-----------+------------------+----------------+-----------------+---------+-----------+
|    1 | 3000000.00 | 0.1100 | IONcZx68P | ejAv76uu9u1W0Vso | hTm1kjwNGHt20A | fRpzxLGAhOMU7UY | 9P      | 763011111 |
+------+------------+--------+-----------+------------------+----------------+-----------------+---------+-----------+
(1 row)

Time: 3.507 ms

tpcc=> \x
Expanded display is on.
tpcc=> select * from warehouse where w_zip = '763011111';
+-[ RECORD 1 ]------------------+
| w_id       | 1                |
| w_ytd      | 3000000.00       |
| w_tax      | 0.1100           |
| w_name     | IONcZx68P        |
| w_street_1 | ejAv76uu9u1W0Vso |
| w_street_2 | hTm1kjwNGHt20A   |
| w_city     | fRpzxLGAhOMU7UY  |
| w_state    | 9P               |
| w_zip      | 763011111        |
+------------+------------------+

Time: 0.732 ms

\i 执行外部文件中的SQL命令

\set AUTOCOMMIT off 关闭自动提交

\set ECHO_HIDDEN on 显示某一个命令实际执行的SQL