PostgreSQL-快速启动，新手上路

朱明豪

修改于 2020-03-16 03:05:42

1.2K00

代码可运行

文章被收录于专栏：“豪”无疑问“豪”无疑问

运行总次数：0

代码可运行

快速安装启动PostgreSQL，完成基本操作。通过实践，逐步揭开PostgreSQL的面纱。

1.安装

yum install -y postgresql-server postgresql postgresql-libs

2.初始化数据库

postgresql-setup initdb

3.服务停止、服务启动

[root@mysql8 ~]# systemctl stop postgresql.service  
[root@mysql8 ~]# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
Mar 06 23:22:57 mysql8 systemd[1]: Starting PostgreSQL database server...
Mar 06 23:22:57 mysql8 systemd[1]: postgresql.service: control process exited, code=exited status=1
Mar 06 23:22:57 mysql8 systemd[1]: Failed to start PostgreSQL database server.
Mar 06 23:22:57 mysql8 systemd[1]: Unit postgresql.service entered failed state.
Mar 06 23:22:57 mysql8 systemd[1]: postgresql.service failed.
Mar 06 23:26:37 mysql8 systemd[1]: Starting PostgreSQL database server...
Mar 06 23:26:38 mysql8 systemd[1]: Started PostgreSQL database server.
Mar 06 23:26:57 mysql8 systemd[1]: Stopping PostgreSQL database server...
Mar 06 23:26:58 mysql8 systemd[1]: Stopped PostgreSQL database server.
[root@mysql8 ~]# systemctl start postgresql.service
[root@mysql8 ~]# systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
   Active: active (running) since Fri 2020-03-06 23:26:38 CST; 3s ago
  Process: 1695 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 1690 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1698 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─1698 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─1699 postgres: logger process   
           ├─1701 postgres: checkpointer process   
           ├─1702 postgres: writer process   
           ├─1703 postgres: wal writer process   
           ├─1704 postgres: autovacuum launcher process   
           └─1705 postgres: stats collector process   
Mar 06 23:26:37 mysql8 systemd[1]: Starting PostgreSQL database server...
Mar 06 23:26:38 mysql8 systemd[1]: Started PostgreSQL database server.

4.登陆数据库

[root@mysql8 ~]# su - postgres
Last login: Fri Mar  6 23:32:22 CST 2020 on pts/0
-bash-4.2$ psql
psql (9.2.24)
Type "help" for help.
postgres=#

5.新手初试

postgres=# select current_database();
current_database
------------------
postgres
(1 row)
postgres=# select current_user;
current_user
--------------
postgres
(1 row)
postgres=# select inet_server_addr(),inet_server_port();
inet_server_addr | inet_server_port
------------------+------------------
                  |                 
(1 row)
postgres=# select version();
                                                    version                                                    
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
帮助命令
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=# \h delete
Command:     DELETE
Description: delete rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING using_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
postgres=# \quit

6.DML操作，生成数据

#1.数据库创建
postgres=# create database test;
CREATE DATABASE
Time: 318.572 ms
postgres=# select * from pg_database;
  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datf
rozenxid | dattablespace |               datacl                
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+-----
---------+---------------+-------------------------------------
template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         12921 |     
    1882 |          1663 | {=c/postgres,postgres=CTc/postgres}
template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         12921 |     
    1882 |          1663 | {=c/postgres,postgres=CTc/postgres}
postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12921 |     
    1882 |          1663 |
test      |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         12921 |     
    1882 |          1663 |
(4 rows)
Time: 0.460 ms
#2.切换数据库
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
test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \c test
You are now connected to database "test" as user "postgres".
#3.删除数据库
test=# drop database test;
ERROR:  cannot drop the currently open database
Time: 0.744 ms
test=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database test;
DROP DATABASE
Time: 108.722 ms
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)
4.创建表
zhumh=# CREATE TABLE COMPANY(
zhumh(#    ID INT PRIMARY KEY     NOT NULL,
zhumh(#    NAME           TEXT    NOT NULL,
zhumh(#    AGE            INT     NOT NULL,
zhumh(#    ADDRESS        CHAR(50),
zhumh(#    SALARY         REAL
zhumh(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "company_pkey" for table "company"
CREATE TABLE
Time: 15.591 ms
zhumh=# CREATE TABLE DEPARTMENT(
zhumh(#    ID INT PRIMARY KEY      NOT NULL,
zhumh(#    DEPT           CHAR(50) NOT NULL,
zhumh(#    EMP_ID         INT      NOT NULL
zhumh(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "department_pkey" for table "department"
CREATE TABLE
Time: 10.073 ms
zhumh=# \d
           List of relations
Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
public | company    | table | postgres
public | department | table | postgres
(2 rows)
#查询表及表结构
zhumh=# \d company
       Table "public.company"
Column  |     Type      | Modifiers
---------+---------------+-----------
id      | integer       | not null
name    | text          | not null
age     | integer       | not null
address | character(50) |
salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
5.删除表
zhumh=# drop table company,department;
DROP TABLE
Time: 12.968 ms
zhumh=# \d
No relations found.
6.表数据操作
zhumh=# CREATE TABLE COMPANY(
zhumh(#    ID INT PRIMARY KEY     NOT NULL,
zhumh(#    NAME           TEXT    NOT NULL,
zhumh(#    AGE            INT     NOT NULL,
zhumh(#    ADDRESS        CHAR(50),
zhumh(#    SALARY         REAL,
zhumh(#    JOIN_DATE      DATE
zhumh(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "company_pkey" for table "company"
CREATE TABLE
Time: 7.278 ms
zhumh=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1
Time: 9.043 ms
zhumh=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');
INSERT 0 1
Time: 0.813 ms
zhumh=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );
INSERT 0 1
Time: 2.300 ms
zhumh=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');
INSERT 0 2
Time: 0.730 ms
zhumh=# SELECT * FROM company;
id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  2 | Allen |  25 | Texas                                              |        | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  20000 |
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
(5 rows)
zhumh=# SELECT * FROM company where id=1;
id | name | age |                      address                       | salary | join_date  
----+------+-----+----------------------------------------------------+--------+------------
  1 | Paul |  32 | California                                         |  20000 | 2001-07-13
(1 row)
zhumh=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;
UPDATE 1
Time: 2.168 ms
zhumh=# DELETE FROM COMPANY WHERE ID = 2;
DELETE 1
Time: 1.975 ms
zhumh=# SELECT * FROM COMPANY LIMIT 4;
id | name  | age |                      address                       | salary | join_date  
----+-------+-----+----------------------------------------------------+--------+------------
  1 | Paul  |  32 | California                                         |  20000 | 2001-07-13
  4 | Mark  |  25 | Rich-Mond                                          |  65000 | 2007-12-13
  5 | David |  27 | Texas                                              |  85000 | 2007-12-13
  3 | Teddy |  23 | Norway                                             |  15000 |
(4 rows)
Time: 0.650 ms
基础操作参考信息：https://www.runoob.com/postgresql/postgresql-tutorial.html

7.了解你的数据库

1.服务程序运行时间
postgres=# select date_trunc('second',current_timestamp - pg_postmaster_start_time()) as uptime;
  uptime  
----------
00:20:06
(1 row)
2.查询数据库下有多少张表
postgres=# select count(*) from information_schema.tables where table_schema not in ('information_schema','pg_catalog');  
count
-------
     0
(1 row)
Time: 6.106 ms
postgres=# select count(*) from information_schema.tables where table_schema = 'information_schema';     
count
-------
    66
(1 row)
3.数据库占用空间
postgres=# select pg_database_size(current_database());
pg_database_size
------------------
          6657144
(1 row)
Time: 0.955 ms
postgres=# select sum(pg_database_size(datname)) from pg_database;
   sum    
----------
26286724
(1 row)
4.表大小
postgres=# select pg_relation_size('pg_authid');                         
pg_relation_size
------------------
             8192
(1 row)
Time: 0.515 ms
postgres=# select pg_total_relation_size('pg_authid');
pg_total_relation_size
------------------------
                  73728
(1 row)
Time: 0.448 ms
postgres=# \dt+ pg_authid
                        List of relations
   Schema   |   Name    | Type  |  Owner   | Size  | Description
------------+-----------+-------+----------+-------+-------------
pg_catalog | pg_authid | table | postgres | 40 kB |
(1 row)
5.最大表
postgres=# select table_name ,pg_relation_size(table_schema || '.' ||table_name ) as size
postgres-# from information_schema.tables
postgres-# where table_schema = 'pg_catalog'
postgres-# order by size desc
postgres-# limit 10;
   table_name   |  size  
----------------+--------
pg_proc        | 507904
pg_depend      | 376832
pg_attribute   | 344064
pg_description | 245760
pg_collation   | 237568
pg_statistic   | 122880
pg_operator    | 114688
pg_rewrite     |  98304
pg_class       |  65536
pg_type        |  65536
(10 rows)
6.表的行数
postgres=# select count(*) from pg_proc;
count
-------
  2490
(1 row)
快速预估行数
postgres=# select (case when reltuples > 0 then
postgres(#         pg_relation_size('pg_proc')*reltuples/(8192*relpages)
postgres(#         else 0
postgres(#         end)::bigint as estimated_row_count
postgres-# from pg_class
postgres-# where oid = 'pg_proc'::regclass;
estimated_row_count
---------------------
                2490
(1 row)
Time: 0.711 ms
7.列出数据库扩展模块
postgres=# select * from pg_extension;
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
---------+----------+--------------+----------------+------------+-----------+--------------
plpgsql |       10 |           11 | f              | 1.0        |           |
(1 row)