CentOS7.6下使用yum快速安装postgresql12.4

1.pg下载地址:

下载地址如下:

https://yum.postgresql.org/12/redhat/rhel-7-x86_64/repoview/postgresqldbserver12.group.html

具体的下载文件如下:

https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm

https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-12.4-1PGDG.rhel7.x86_64.rpm

https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-libs-12.4-1PGDG.rhel7.x86_64.rpm

https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-contrib-12.4-1PGDG.rhel7.x86_64.rpm

2.安装rpm包

上传上面下载的所有rpm软件包,使用yum进行本地安装:

[root@zabbix pg12]# yum localinstall -y *
Loaded plugins: fastestmirror
Examining postgresql12-12.4-1PGDG.rhel7.x86_64.rpm: postgresql12-12.4-1PGDG.rhel7.x86_64
Marking postgresql12-12.4-1PGDG.rhel7.x86_64.rpm to be installed
Examining postgresql12-contrib-12.4-1PGDG.rhel7.x86_64.rpm: postgresql12-contrib-12.4-1PGDG.rhel7.x86_64
Marking postgresql12-contrib-12.4-1PGDG.rhel7.x86_64.rpm to be installed
Examining postgresql12-libs-12.4-1PGDG.rhel7.x86_64.rpm: postgresql12-libs-12.4-1PGDG.rhel7.x86_64
Marking postgresql12-libs-12.4-1PGDG.rhel7.x86_64.rpm to be installed
Examining postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm: postgresql12-server-12.4-1PGDG.rhel7.x86_64
Marking postgresql12-server-12.4-1PGDG.rhel7.x86_64.rpm to be installed
...
Dependency Installed:
  libicu.x86_64 0:50.1.2-17.el7              libxslt.x86_64 0:1.1.28-5.el7                     perl.x86_64 4:5.16.3-293.el7                perl-Carp.noarch 0:1.26-244.el7              perl-Encode.x86_64 0:2.51-7.el7              
  perl-Exporter.noarch 0:5.68-3.el7          perl-File-Path.noarch 0:2.09-2.el7                perl-File-Temp.noarch 0:0.23.01-3.el7       perl-Filter.x86_64 0:1.49-3.el7              perl-Getopt-Long.noarch 0:2.40-3.el7         
  perl-HTTP-Tiny.noarch 0:0.033-3.el7        perl-PathTools.x86_64 0:3.40-5.el7                perl-Pod-Escapes.noarch 1:1.04-293.el7      perl-Pod-Perldoc.noarch 0:3.20-4.el7         perl-Pod-Simple.noarch 1:3.28-4.el7          
  perl-Pod-Usage.noarch 0:1.63-3.el7         perl-Scalar-List-Utils.x86_64 0:1.27-248.el7      perl-Socket.x86_64 0:2.010-4.el7            perl-Storable.x86_64 0:2.45-3.el7            perl-Text-ParseWords.noarch 0:3.29-4.el7     
  perl-Time-HiRes.x86_64 4:1.9725-3.el7      perl-Time-Local.noarch 0:1.2300-2.el7             perl-constant.noarch 0:1.27-2.el7           perl-libs.x86_64 4:5.16.3-293.el7            perl-macros.x86_64 4:5.16.3-293.el7          
  perl-parent.noarch 1:0.225-244.el7         perl-podlators.noarch 0:2.5.1-3.el7               perl-threads.x86_64 0:1.87-4.el7            perl-threads-shared.x86_64 0:1.43-6.el7     

Complete!

3.初始化数据库

安装完成之后,PGHOME默认的安装目录为/usr/pgsql-12.如果想要修改PGDATA安装目录,则需要修改postgresql-12.service里面的PGDATA目录,我们这里将PGDATA设置到/data/pgsql-12里.
需要先修改目录的权限:

[root@zabbix pg12]# chown -R postgres:postgres /data/pgsql-12

[root@zabbix pg12]# vi /usr/lib/systemd/system/postgresql-12.service
Environment=PGDATA=/data/pgsql-12

使用postgresql-12-setup来初始化数据库:

[root@zabbix pg12]# /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

设置为开机自动启动:

[root@zabbix pg12]# systemctl enable postgresql-12
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
[root@zabbix pg12]# systemctl start postgresql-12

4.修改环境变量

登录到postgres用户,修改PGDATA环境变量,并且将PGHOME添加到PATH中

su -  postgres

vi .bash_profile
export PGDATA=/data/pgsql-12
export PGHOME=/usr/pgsql-12
export PATH=$PGHOME/bin:$PATH
export PS1="[\u@\H \w]$"

5.打开远程登录

首先修改一下postgres的密码:

[postgres@zabbix /data/pgsql-12]$psql
psql (12.4)
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# alter user postgres with password 'postgres';
ALTER ROLE

修改postgresql.conf,监听所有地址:

su - postgres
cd $PGDATA
vi postgresql.conf

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

修改pg_hba.conf, 在最后添加允许远程登录:

host    all             all             0.0.0.0/0               md5

重启postgresql:

可以使用
systemctl restart postgresql-12
或者:
pg_ctl stop -m fast
pg_ctl start

在windows上进行测试:

C:\Users\brent>psql -h192.168.56.51 -Upostgres -W
Password:
psql (12.4)
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)

这样,一个快速的pgsql就搭建完成了!