devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

简明postgresql安装和配置

作者：从前慢现在也慢 | 2024-07-18 11:20:38

赞

踩

postgresql安装配置

因前端代码连接pg的需要，又参考之前的文档重新安装了一遍postgresql，想一次性过还是有点小问题，涉及防火墙、配置文件的修改等等。

1、直接通过yum安装postgresql-server和postgresql-contrib。

  1. [root@localhost lib]# yum install postgresql-server
  2. Loaded plugins: fastestmirror
  3. Determining fastest mirrors
  4. Could not get metalink https://mirrors.fedoraproject.org/metalink?repo=epel-7&arch=x86_64 error was
  5. 12: Timeout on https://mirrors.fedoraproject.org/metalink?repo=epel-7&arch=x86_64: (28, 'Operation timed out after 30001 milliseconds with 0 out of 0 bytes received')
  6.  * base: mirrors.cqu.edu.cn
  7.  * epel: mirrors.tuna.tsinghua.edu.cn
  8.  * extras: mirrors.cqu.edu.cn
  9.  * updates: mirrors.cqu.edu.cn
  10. base                                                                                               | 3.6 kB  00:00:00     
  11. extras                                                                                             | 2.9 kB  00:00:00     
  12. mysql-connectors-community                                                                         | 2.6 kB  00:00:00     
  13. mysql-tools-community                                                                              | 2.6 kB  00:00:00     
  14. mysql80-community                                                                                  | 2.6 kB  00:00:00     
  15. https://packagecloud.io/rabbitmq/erlang/el/7/x86_64/repodata/repomd.xml: [Errno 12] Timeout on https://d28dx6y1hfq314.cloudfront.net/828/1039/el/7/x86_64/repodata/f529b4a78e15bfff91a18ffa428f84e44173906fd12ad40ad2c914dfbba4ad7b-repomd.xml?t=1671159862_40278d5a1b11edb691c5e24a1e2778007e7413cc: (28, 'Resolving timed out after 30535 milliseconds')
  16. Trying other mirror.
  17. rabbitmq_erlang/x86_64/signature                                                                   |  833 B  00:00:00     
  18. rabbitmq_erlang/x86_64/signature                                                                   | 1.8 kB  00:00:00 !!! 
  19. rabbitmq_erlang-source/signature                                                                   |  819 B  00:00:00     
  20. rabbitmq_erlang-source/signature                                                                   |  951 B  00:00:00 !!! 
  21. rabbitmq_rabbitmq-server/x86_64/signature                                                          |  833 B  00:00:00     
  22. rabbitmq_rabbitmq-server/x86_64/signature                                                          | 1.8 kB  00:00:00 !!! 
  23. rabbitmq_rabbitmq-server-source/signature                                                          |  836 B  00:00:00     
  24. rabbitmq_rabbitmq-server-source/signature                                                          | 1.0 kB  00:00:00 !!! 
  25. updates                                                                                            | 2.9 kB  00:00:00     
  26. wandisco-git                                                                                       | 2.9 kB  00:00:00     
  27. updates/7/x86_64/primary_db                                                                        |  18 MB  00:00:03     
  28. Resolving Dependencies
  29. --> Running transaction check
  30. ---> Package postgresql-server.x86_64 0:9.2.24-8.el7_9 will be installed
  31. --> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-8.el7_9 for package: postgresql-server-9.2.24-8.el7_9.x86_64
  32. --> Processing Dependency: postgresql(x86-64) = 9.2.24-8.el7_9 for package: postgresql-server-9.2.24-8.el7_9.x86_64
  33. --> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-server-9.2.24-8.el7_9.x86_64
  34. --> Running transaction check
  35. ---> Package postgresql.x86_64 0:9.2.24-8.el7_9 will be installed
  36. ---> Package postgresql-libs.x86_64 0:9.2.24-8.el7_9 will be installed
  37. --> Finished Dependency Resolution
  38.  
  39. Dependencies Resolved
  40.  
  41. ==========================================================================================================================
  42.  Package                           Arch                   Version                           Repository               Size
  43. ==========================================================================================================================
  44. Installing:
  45.  postgresql-server                 x86_64                 9.2.24-8.el7_9                    updates                 3.8 M
  46. Installing for dependencies:
  47.  postgresql                        x86_64                 9.2.24-8.el7_9                    updates                 3.0 M
  48.  postgresql-libs                   x86_64                 9.2.24-8.el7_9                    updates                 235 k
  49.  
  50. Transaction Summary
  51. ==========================================================================================================================
  52. Install  1 Package (+2 Dependent packages)
  53.  
  54. Total download size: 7.1 M
  55. Installed size: 33 M
  56. Is this ok [y/d/N]: y
  57. Downloading packages:
  58. (1/3): postgresql-libs-9.2.24-8.el7_9.x86_64.rpm                                                   | 235 kB  00:00:00     
  59. (2/3): postgresql-9.2.24-8.el7_9.x86_64.rpm                                                        | 3.0 MB  00:00:00     
  60. (3/3): postgresql-server-9.2.24-8.el7_9.x86_64.rpm                                                 | 3.8 MB  00:00:02     
  61. --------------------------------------------------------------------------------------------------------------------------
  62. Total                                                                                     2.7 MB/s | 7.1 MB  00:00:02     
  63. Running transaction check
  64. Running transaction test
  65. Transaction test succeeded
  66. Running transaction
  67.   Installing : postgresql-libs-9.2.24-8.el7_9.x86_64                                                                  1/3 
  68.   Installing : postgresql-9.2.24-8.el7_9.x86_64                                                                       2/3 
  69.   Installing : postgresql-server-9.2.24-8.el7_9.x86_64                                                                3/3 
  70.   Verifying  : postgresql-server-9.2.24-8.el7_9.x86_64                                                                1/3 
  71.   Verifying  : postgresql-libs-9.2.24-8.el7_9.x86_64                                                                  2/3 
  72.   Verifying  : postgresql-9.2.24-8.el7_9.x86_64                                                                       3/3 
  73.  
  74. Installed:
  75.   postgresql-server.x86_64 0:9.2.24-8.el7_9                                                                               
  76.  
  77. Dependency Installed:
  78.   postgresql.x86_64 0:9.2.24-8.el7_9                        postgresql-libs.x86_64 0:9.2.24-8.el7_9                       
  79.  
  80. Complete!
  81. [root@localhost lib]# yum install postgresql-contrib
  82. Loaded plugins: fastestmirror
  83. Loading mirror speeds from cached hostfile
  84. epel/x86_64/metalink                                                                               |  10 kB  00:00:00     
  85.  * base: mirrors.cqu.edu.cn
  86.  * epel: mirror.01link.hk
  87.  * extras: mirrors.cqu.edu.cn
  88.  * updates: mirrors.cqu.edu.cn
  89. epel                                                                                               | 4.7 kB  00:00:00     
  90. (1/3): epel/x86_64/group_gz                                                                        |  98 kB  00:00:00     
  91. (2/3): epel/x86_64/updateinfo                                                                      | 1.0 MB  00:00:00     
  92. (3/3): epel/x86_64/primary_db                                                                      | 7.0 MB  00:00:01     
  93. Resolving Dependencies
  94. --> Running transaction check
  95. ---> Package postgresql-contrib.x86_64 0:9.2.24-8.el7_9 will be installed
  96. --> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-9.2.24-8.el7_9.x86_64
  97. --> Running transaction check
  98. ---> Package uuid.x86_64 0:1.6.2-26.el7 will be installed
  99. --> Finished Dependency Resolution
  100.  
  101. Dependencies Resolved
  102.  
  103. ==========================================================================================================================
  104.  Package                            Arch                   Version                          Repository               Size
  105. ==========================================================================================================================
  106. Installing:
  107.  postgresql-contrib                 x86_64                 9.2.24-8.el7_9                   updates                 553 k
  108. Installing for dependencies:
  109.  uuid                               x86_64                 1.6.2-26.el7                     base                     55 k
  110.  
  111. Transaction Summary
  112. ==========================================================================================================================
  113. Install  1 Package (+1 Dependent package)
  114.  
  115. Total download size: 608 k
  116. Installed size: 1.9 M
  117. Is this ok [y/d/N]: y
  118. Downloading packages:
  119. (1/2): uuid-1.6.2-26.el7.x86_64.rpm                                                                |  55 kB  00:00:00     
  120. (2/2): postgresql-contrib-9.2.24-8.el7_9.x86_64.rpm                                                | 553 kB  00:00:00     
  121. --------------------------------------------------------------------------------------------------------------------------
  122. Total                                                                                     1.4 MB/s | 608 kB  00:00:00     
  123. Running transaction check
  124. Running transaction test
  125. Transaction test succeeded
  126. Running transaction
  127.   Installing : uuid-1.6.2-26.el7.x86_64                                                                               1/2 
  128.   Installing : postgresql-contrib-9.2.24-8.el7_9.x86_64                                                               2/2 
  129.   Verifying  : uuid-1.6.2-26.el7.x86_64                                                                               1/2 
  130.   Verifying  : postgresql-contrib-9.2.24-8.el7_9.x86_64                                                               2/2 
  131.  
  132. Installed:
  133.   postgresql-contrib.x86_64 0:9.2.24-8.el7_9                                                                              
  134.  
  135. Dependency Installed:
  136.   uuid.x86_64 0:1.6.2-26.el7                                                                                              
  137.  
  138. Complete!
  139.  
  140. [root@localhost lib]# rpm -aq|grep postgres
  141. postgresql-libs-9.2.24-8.el7_9.x86_64
  142. postgresql-contrib-9.2.24-8.el7_9.x86_64
  143. postgresql-9.2.24-8.el7_9.x86_64
  144. postgresql-server-9.2.24-8.el7_9.x86_64

2、初始化数据库，并启动和查看postgresql

  1. [root@localhost lib]# postgresql-setup initdb
  2. Initializing database ... OK
  3.  
  4. [root@localhost lib]# systemctl start postgresql
  5. [root@localhost lib]# systemctl status postgresql
  6. ● postgresql.service - PostgreSQL database server
  7.    Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
  8.    Active: active (running) since Fri 2022-12-16 11:04:56 CST; 11s ago
  9.   Process: 28364 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  10.   Process: 28358 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
  11.  Main PID: 28367 (postgres)
  12.    CGroup: /system.slice/postgresql.service
  13.            ├─28367 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
  14.            ├─28368 postgres: logger process   
  15.            ├─28370 postgres: checkpointer process   
  16.            ├─28371 postgres: writer process   
  17.            ├─28372 postgres: wal writer process   
  18.            ├─28373 postgres: autovacuum launcher process   
  19.            └─28374 postgres: stats collector process   
  20.  
  21. Dec 16 11:04:55 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
  22. Dec 16 11:04:56 localhost.localdomain systemd[1]: Started PostgreSQL database server.
  23. [root@localhost lib]# systemctl enable postgresql
  24. Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.

3、开启访问端口和启用防火墙

小插曲提示firewalld is not running，需要启动防火墙

  1. [root@localhost lib]# firewall-cmd --permanent --add-port=5432/tcp
  2. FirewallD is not running
  3. [root@localhost lib]# systemctl start firewalld.service
  4. [root@localhost lib]# systemctl enable firewalld.service
  5. [root@localhost lib]# systemctl status firewalld
  6. ● firewalld.service - firewalld - dynamic firewall daemon
  7.    Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
  8.    Active: active (running) since Fri 2022-12-16 11:08:34 CST; 16s ago
  9.      Docs: man:firewalld(1)
  10.  Main PID: 28993 (firewalld)
  11.    CGroup: /system.slice/firewalld.service
  12.            └─28993 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
  13.  
  14. Dec 16 11:08:33 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
  15. Dec 16 11:08:34 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.
  16. [root@localhost lib]# firewall-cmd --permanent --add-port=5432/tcp
  17. success
  18. [root@localhost lib]# firewall-cmd --reload
  19. success

4、进入postgresql进行测试

先通过psql进入pg，修改口令

  1. [root@localhost lib]# su - postgres
  2. -bash-4.2$ psql -U postgres
  3. psql (9.2.24)
  4. Type "help" for help.
  5. postgres=# ALTER USER postgres WITH PASSWORD '*******';
  6. ALTER ROLE
  7. postgres=# ^Z
  8. [1]+  Stopped                 psql -U postgres
  9. -bash-4.2$

5、通过Navicat客户端配置pg连接，无法访问数据库

6、进入数据库，查看监听地址，发线默认是localhost监听

  1. -bash-4.2$ psql -U postgres      
  2. psql (9.2.24)
  3. Type "help" for help.
  4.  
  5. postgres=# SHOW listen_addresses;
  6.  listen_addresses 
  7. ------------------
  8.  localhost
  9. (1 row)

7、修改postgresql.conf和pg_hba.conf文件，再重启

  1. -bash-4.2$ cd /var/lib/pgsql/data
  2.  
  3. -bash-4.2$ vi postgresql.conf 
  4. # -----------------------------
  5. # PostgreSQL configuration file
  6. # -----------------------------
  7. #
  8. listen_addresses = '*'          # what IP address(es) to listen on;
  9.  
  10.  
  11. -bash-4.2$ vi pg_hba.conf 
  12. # PostgreSQL Client Authentication Configuration File
  13. # IPv4 local connections:
  14. host    all             all             127.0.0.1/32            ident
  15. host    all             all             0.0.0.0/0             md5
  16. "pg_hba.conf" 90L, 4302C written
  17. "postgresql.conf" 578L, 19836C written
  18.  
  19. [root@localhost ~]# systemctl restart postgresql

8、再次通过Navicat客户端配置pg连接，访问数据库就OK了

本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/845340
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号