安装pg
检查安装源
[root@hecs-213273 data]# yum search postgresql
Last metadata expiration check: 0:41:54 ago on Fri 04 Aug 2023 05:13:15 PM CST.
======================================================================= Name & Summary Matched: postgresql =======================================================================
postgresql.x86_64 : PostgreSQL client programs
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-docs.x86_64 : Extra documentation for PostgreSQL
postgresql-jdbc.noarch : 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-odbc.x86_64 : Official postgreSQL ODBC driver
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
qt5-qtbase-postgresql.x86_64 : PostgreSQL driver for Qt5's SQL classes
============================================================================ Name Matched: postgresql ============================================================================
postgresql-odbc-devel.x86_64 : Various testsuite files for psqlodbc
========================================================================== Summary Matched: postgresql ===========================================================================
apr-util-pgsql.x86_64 : The PostgreSQL DBD driver of apr-util.
libpq.x86_64 : A share library of PostgreSQL
libpq-devel.x86_64 : Development files for building PostgreSQL client tools
perl-DBD-Pg.x86_64 : DBD::Pg-PostgreSQL database driver for the DBI module
php-pgsql.x86_64 : A PostgreSQL database module for PHP
postfix-pgsql.x86_64 : Postfix PostgreSQL map support
python-psycopg2-doc.x86_64 : Documentation for psycopg python PostgreSQL database adapter
python3-psycopg2.x86_64 : PostgreSQL database adapter for the Python programming language
python3-psycopg2-tests.x86_64 : A testsuite for PostgreSQL database adapter for the Python programming language
redland-pgsql.x86_64 : Redland PostgreSQL storage
[root@hecs-213273 data]#
执行安装 yum install postgresql-server postgresql-server会附带安装上postgresql的客户端，因此只需执行这一条指令。
安装完后查看一下安装位置，安装版本：
[root@hecs-213273 ~]# which psql
/usr/bin/psql
[root@hecs-213273 ~]# which postgresql-setup
/usr/bin/postgresql-setup
[root@hecs-213273 ~]# psql --version
psql (PostgreSQL) 13.3
初始化数据库
安装后不能直接启动数据库，需要先执行初始化
postgresql-setup initdb
初始化之后会生成postgresql相关配置文件和数据库文件，都放在/var/lib/pgsql/data目录下
[root@hecs-213273 data]# pwd
/var/lib/pgsql/data
[root@hecs-213273 data]# ls
base log pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts
current_logfiles pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid
global pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf
启动数据库
service postgresql start
查看数据库状态
[root@hecs-213273 ~]# service postgresql status
Redirecting to /bin/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 2023-08-04 14:32:08 CST; 21min ago
Process: 31895 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 31898 (postmaster)
Tasks: 8 (limit: 21652)
Memory: 15.4M
CGroup: /system.slice/postgresql.service
├─ 31898 /usr/bin/postmaster -D /var/lib/pgsql/data
├─ 31899 "postgres: logger " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 31901 "postgres: checkpointer " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 31902 "postgres: background writer " "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 31903 "postgres: walwriter " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
├─ 31904 "postgres: autovacuum launcher " "" "" "" "" "" "" "" "" "" "" "" ""
├─ 31905 "postgres: stats collector " "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
└─ 31906 "postgres: logical replication launcher " "" "" ""
Aug 04 14:32:08 hecs-213273 systemd[1]: Starting PostgreSQL database server...
Aug 04 14:32:08 hecs-213273 postmaster[31898]: 2023-08-04 14:32:08.839 CST [31898] LOG: redirecting log output to logging collector process
Aug 04 14:32:08 hecs-213273 postmaster[31898]: 2023-08-04 14:32:08.839 CST [31898] HINT: Future log output will appear in directory "log".
Aug 04 14:32:08 hecs-213273 systemd[1]: Started PostgreSQL database server.
[root@hecs-213273 ~]#
查看一下端口，可以看到5432端口已经启用
[root@hecs-213273 ~]# netstat -nat
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp 0 208 192.168.0.71:22 61.154.108.138:23295 ESTABLISHED
tcp6 0 0 :::80 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
tcp6 0 0 ::1:5432 :::* LISTEN
[root@hecs-213273 ~]#
登录数据库
默认已经为我们建立了postgres账户和postgres数据库，目前不支持直接使用root账户登录需要切换到postgres账户下再登录
[root@hecs-213273 ~]# su - postgres
Last login: Fri Aug 4 14:45:43 CST 2023 on pts/1
-bash: TMOUT: readonly variable
[postgres@hecs-213273 ~]$ psql postgres
psql (13.3)
Type "help" for help.
postgres=# exit
为了让root可以直接访问postgres账户下的数据库，需要修改/var/lib/pgsql/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
把local类型连接的method从peer改为trust，重启数据库后就可以直接用psql -U postgres访问数据库
[root@hecs-213273 ~]# service postgresql restart
Redirecting to /bin/systemctl restart postgresql.service
[root@hecs-213273 ~]# psql -U postgres
psql (13.3)
Type "help" for help.
postgres=#
远程连接数据库
为了让外部ip能访问本机的postgresql数据库，需要修改/var/lib/pgsql/data/postgresql.conf文件中的listen_address属性为"*"，并且在/var/lib/pgsql/data/pg_hba.conf中新增一行配置
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to listen on;
listent_addresses = '*'
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
host all all 0.0.0.0/0 md5
重启数据库服务以生效配置
service postgresql restart
查看数据库状态，可以看到数据库监听本机的地址发生了变化，不再是127.0.0.1了
[root@hecs-213273 data]# netstat -nat
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp 0 208 192.168.0.71:22 61.154.108.138:25214 ESTABLISHED
tcp6 0 0 :::80 :::* LISTEN
tcp6 0 0 :::22 :::* LISTEN
tcp6 0 0 :::5432 :::* LISTEN
[root@hecs-213273 data]#
远程登录的时候要用密码登录，所以还需要修改密码，可以执行\password指令或者sql语句修改密码，假设我们使用密码为postgres
postgres=# \password
Enter new password:
Enter it again:
postgres=#
或者
alter user postgres with password 'postgres'
用navcat premium连接数据库成功。
