PostgreSQL 入门

￡神魔★判官ぃ 2022-11-13 13:26 109阅读 0赞

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86\_64/pgdg-redhat-repo-latest.noarch.rpm

yum install -y postgresql13-server

### 初始化自定义数据目录： ###

\# cp /lib/systemd/system/postgresql-13.service /etc/systemd/system/postgresql-13-product.service  
   
\# 初始化数据目录  
\# /usr/pgsql-13/bin/postgresql-13-setup initdb postgresql-13-product  
Initializing database ... OK  
   
\# ll /data  
总用量 0  
drwxr-xr-x. 3 root root 16 10月 17 16:31 pgsql-product

### 修改配置文件 ###

vim /data/pgsql-product/13/data/postgresql.conf  
\# 以下为为修改项：  
listen\_addresses = '0.0.0.0'        \# what IP address(es) to listen on;  
port = 5432             \# (change requires restart)  
max\_connections = 1000          \# (change requires restart)  
superuser\_reserved\_connections = 5  \# (change requires restart)  
shared\_buffers = 8192MB         \# min 128kB  
work\_mem = 16MB             \# min 64kB  
maintenance\_work\_mem = 512MB        \# min 1MB  
vacuum\_cost\_delay = 0           \# 0-100 milliseconds (0 disables)  
max\_worker\_processes = 128      \# (change requires restart)  
max\_parallel\_maintenance\_workers = 8    \# taken from max\_parallel\_workers  
max\_parallel\_workers\_per\_gather = 16    \# taken from max\_parallel\_workers  
fsync = off             \# flush data to disk for crash safety  
commit\_delay = 1000         \# range 0-100000, in microseconds  
commit\_siblings = 100           \# range 1-1000  
checkpoint\_completion\_target = 0.8  \# checkpoint target duration, 0.0 - 1.0  
effective\_cache\_size = 16GB  
datestyle = 'iso, mdy'  
lc\_messages = 'en\_US.UTF-8'         \# locale for system error message  
lc\_monetary = 'en\_US.UTF-8'         \# locale for monetary formatting  
lc\_numeric = 'en\_US.UTF-8'          \# locale for number formatting  
lc\_time = 'en\_US.UTF-8'             \# locale for time formatting  
default\_text\_search\_config = 'pg\_catalog.english'  
vim /data/pgsql-product/13/data/pg\_hba.conf  
\# 允许10.100.0.0（公司内部网络）访问postgresql  
\# TYPE  DATABASE        USER            ADDRESS                 METHOD  
   
\# "local" is for Unix domain socket connections only  
local   all             all                                     peer  
\# IPv4 local connections:  
host    all             all             127.0.0.1/32            scram-sha-256  
host    all             all             10.100.0.0/16            scram-sha-256  \# 增加一条配置  
\# IPv6 local connections:  
host    all             all             ::1/128                 scram-sha-256  
\# Allow replication connections from localhost, by a user with the  
\# replication privilege.  
local   replication     all                                     peer  
host    replication     all             127.0.0.1/32            scram-sha-256  
host    replication     all             ::1/128                 scram-sha-256

## 卸载 ##

\[root@postgis /\]\# yum remove postgresql13-server

[https://blog.csdn.net/weixin\_39909366/article/details/110960242][https_blog.csdn.net_weixin_39909366_article_details_110960242]

[https://blog.csdn.net/sinat\_26594945/article/details/107715171][https_blog.csdn.net_sinat_26594945_article_details_107715171]

连接数据库, 默认的用户和数据库是postgres  
psql -U user -d dbname

切换数据库,相当于mysql的use dbname  
\\c dbname  
列举数据库，相当于mysql的show databases  
\\l  
列举表，相当于mysql的show tables  
\\dt  
查看表结构，相当于desc tblname,show columns from tbname  
\\d tblname

\\di 查看索引

创建数据库：   
create database \[数据库名\];   
删除数据库：   
drop database \[数据库名\]

\--设置模式  
set search\_path to firsoul;  
\--默认只列出当前模式下的表  
\\d  
\--列出常用模式  
\\dn  
\--列出所有模式  
\\dnS  
\--查看表的大小  
select pg\_size\_pretty(pg\_relation\_size('test'));  
\--or  
\\dt+ test  
\--info  
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' END as "Type",  
  pg\_catalog.pg\_get\_userbyid(c.relowner) as "Owner",  
  pg\_catalog.pg\_size\_pretty(pg\_catalog.pg\_table\_size(c.oid)) as "Size",  
  pg\_catalog.obj\_description(c.oid, 'pg\_class') as "Description"  
FROM pg\_catalog.pg\_class c  
     LEFT JOIN pg\_catalog.pg\_namespace n ON n.oid = c.relnamespace  
WHERE c.relkind IN ('r','p','s','')  
      AND n.nspname !~ '^pg\_toast'  
  AND c.relname OPERATOR(pg\_catalog.~) '^(test)$'  
  AND pg\_catalog.pg\_table\_is\_visible(c.oid)  
ORDER BY 1,2;  
\--查看模式大小,表，索引  
select schemaname ,round(sum(pg\_total\_relation\_size(schemaname||'.'||tablename))/1024/1024) "Size\_MB" from pg\_tables where schemaname='public' group by 1;  
select schemaname ,round(sum(pg\_total\_relation\_size(schemaname||'.'||tablename))/1024/1024) "Size\_MB" from pg\_tables where schemaname='public' group by 1;  
select schemaname ,round(sum(pg\_total\_relation\_size(schemaname||'.'||indexname))/1024/1024) "Size\_MB" from pg\_indexes where schemaname='public' group by 1;  
\--查询数据库大小  
select datname,pg\_size\_pretty(pg\_database\_size(datname)) from pg\_database;

[https_blog.csdn.net_weixin_39909366_article_details_110960242]: https://blog.csdn.net/weixin_39909366/article/details/110960242
[https_blog.csdn.net_sinat_26594945_article_details_107715171]: https://blog.csdn.net/sinat_26594945/article/details/107715171