【PostgreSQL】数据库部署-创新互联
[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另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
当前名称：【PostgreSQL】数据库部署-创新互联
网页链接：http://www.zsjierui.cn/article/djgojd.html