快速安装启动PostgreSQL，完成基本操作。通过实践，逐步揭开PostgreSQL的面纱。
1.安装
代码语言：javascript复制yum install -y postgresql-server postgresql postgresql-libs2.初始化数据库
代码语言：javascript复制postgresql-setup initdb3.服务停止、服务启动
代码语言：javascript复制[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.登陆数据库
代码语言：javascript复制[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.新手初试
代码语言：javascript复制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=# quit6.DML操作，生成数据
代码语言：javascript复制#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.html7.了解你的数据库
代码语言：javascript复制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)
