openEuler22.03通过yum安装postgresql及使用

1.在系统上搜索有无postgresql的安装包

[root@localhost ~]# yum search postgresql
Last metadata expiration check: 0:47:44 ago on Wed 08 Feb 2023 01:37:57 PM CST.
========================================================== Name & Summary Matched: postgresql ==========================================================
postgresql.x86_64 : PostgreSQL client programs
postgresql.src : PostgreSQL client programs
collectd-postgresql.x86_64 : PostgreSQL plugin for collectd
freeradius-postgresql.x86_64 : Postgresql support of the FreeRADIUS package
pcp-pmda-postgresql.x86_64 : Performance Co-Pilot (PCP) metrics for PostgreSQL
postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql-debuginfo.x86_64 : Debug information for package postgresql
postgresql-debugsource.x86_64 : Debug sources for package postgresql
postgresql-docs.x86_64 : Extra documentation for PostgreSQL
postgresql-jdbc.noarch : Postgresql JDBC Driver
postgresql-jdbc.src : Postgresql JDBC Driver
postgresql-jdbc-help.noarch : Documents for postgresql-jdbc
postgresql-jdbc-javadoc.noarch : API docs for postgresql-jdbc
postgresql-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql-server-devel.x86_64 : PostgreSQL development header files and libraries
postgresql-static.x86_64 : Statically linked PostgreSQL libraries
postgresql-test.x86_64 : The test suite distributed with PostgreSQL
postgresql-test-rpm-macros.noarch : Convenience RPM macros for build-time testing against PostgreSQL server
proftpd-postgresql.x86_64 : Module to add PostgreSQL support to the ProFTPD FTP server
qt5-qtbase-postgresql.x86_64 : PostgreSQL driver for Qt5's SQL classes
============================================================= Summary Matched: postgresql =======================

2.安装Pgsql

[root@localhost ~]# yum search postgresql
Last metadata expiration check: 0:47:44 ago on Wed 08 Feb 2023 01:37:57 PM CST.
========================================================== Name & Summary Matched: postgresql ==========================================================
postgresql.x86_64 : PostgreSQL client programs
postgresql.src : PostgreSQL client programs
collectd-postgresql.x86_64 : PostgreSQL plugin for collectd
freeradius-postgresql.x86_64 : Postgresql support of the FreeRADIUS package
pcp-pmda-postgresql.x86_64 : Performance Co-Pilot (PCP) metrics for PostgreSQL
postgresql-contrib.x86_64 : Extension modules distributed with PostgreSQL
postgresql-debuginfo.x86_64 : Debug information for package postgresql
postgresql-debugsource.x86_64 : Debug sources for package postgresql
postgresql-docs.x86_64 : Extra documentation for PostgreSQL
postgresql-jdbc.noarch : Postgresql JDBC Driver
postgresql-jdbc.src : Postgresql JDBC Driver
postgresql-jdbc-help.noarch : Documents for postgresql-jdbc
postgresql-jdbc-javadoc.noarch : API docs for postgresql-jdbc
postgresql-llvmjit.x86_64 : Just-in-time compilation support for PostgreSQL
postgresql-plperl.x86_64 : The Perl procedural language for PostgreSQL
postgresql-plpython3.x86_64 : The Python3 procedural language for PostgreSQL
postgresql-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
postgresql-server.x86_64 : The programs needed to create and run a PostgreSQL server
postgresql-server-devel.x86_64 : PostgreSQL development header files and libraries
postgresql-static.x86_64 : Statically linked PostgreSQL libraries
postgresql-test.x86_64 : The test suite distributed with PostgreSQL
postgresql-test-rpm-macros.noarch : Convenience RPM macros for build-time testing against PostgreSQL server
proftpd-postgresql.x86_64 : Module to add PostgreSQL support to the ProFTPD FTP server
qt5-qtbase-postgresql.x86_64 : PostgreSQL driver for Qt5's SQL classes
============================================================= Summary Matched: postgresql =======================

3.启动报错，根据提示运行如下命令再启动

[root@localhost ~]# /usr/bin/postgresql-setup --initdb
 * Initializing database in '/var/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
 [root@localhost ~]# systemctl start postgresql
[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: active (running) since Wed 2023-02-08 14:40:01 CST; 1s ago
    Process: 22212 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
   Main PID: 22214 (postmaster)
      Tasks: 8 (limit: 2304)
     Memory: 14.1M
     CGroup: /system.slice/postgresql.service
             ├─22214 /usr/bin/postmaster -D /var/lib/pgsql/data
             ├─22216 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─22218 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─22219 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─22220 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             ├─22221 "postgres: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" ""
             ├─22222 "postgres: stats collector " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
             └─22223 "postgres: logical replication launcher " "" "" ""
​
Feb 08 14:40:01 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
Feb 08 14:40:01 localhost.localdomain postmaster[22214]: 2023-02-08 14:40:01.509 CST [22214] LOG:  redirecting log output to logging collector process
Feb 08 14:40:01 localhost.localdomain postmaster[22214]: 2023-02-08 14:40:01.509 CST [22214] HINT:  Future log output will appear in directory "log".
Feb 08 14:40:01 localhost.localdomain systemd[1]: Started PostgreSQL database server.

4.创建用户和数据库并授权

[root@localhost ~]# su - postgres
[postgres@localhost ~]$ psql
psql (13.3)
Type "help" for help.
​
postgres=# 
postgres=# create user test_user with password 'abc123';
CREATE ROLE
postgres=# create database test_db owner test_user;
CREATE DATABASE
postgres=# grant all privileges on database test_db to test_user;
GRANT

5.退出数据库

postgres=# \q
[postgres@localhost ~]$ 

6.开启远程访问

[postgres@localhost ~]$ vi /var/lib/pgsql/data/postgresql.conf
# - Connection Settings -
​
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
[postgres@localhost ~]$ vi /var/lib/pgsql/data/pg_hba.conf(新增md5这行)
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             0.0.0.0/0               md5
[root@localhost ~]# systemctl restart postgresql

此时可用过数据库连接工具连接