1 下载、安装及数据库初始化
下载安装postgresql官方yam仓库,使用yum install命令完成
[root@localhost ~]yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
下载安装postgresql15-server
[root@localhost ~]yum install -y postgresql15-server Error: Package: postgresql15-server-15.1-1PGDG.rhel7.x86_64 (pgdg15) Requires: libzstd.so.1()(64bit) Error: Package: postgresql15-15.1-1PGDG.rhel7.x86_64 (pgdg15) Requires: libzstd.so.1()(64bit) Error: Package: postgresql15-15.1-1PGDG.rhel7.x86_64 (pgdg15) Requires: libzstd >=1.4.0 You could try using --skip-broken to work around the problem You could try running: rpm -Va--nofiles--nodigest
有一个依赖包（libzstd）需要手动安装安装以下，版本不能低于1.4.0
[root@localhost ~]# wget https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/libzstd-1.5.2-1.el7.x86_64.rpm[root@localhost ~]# rpm -ivh libzstd-1.5.2-1.el7.x86_64.rpm
再次安装postgresql15-server后，进行数据库初始化。
[root@localhost ~]/usr/pgsql-15/bin/postgresql-15-setup initdb
设置数据库开机自动启动后启动数据库
[root@localhost ~]systemctl enable postgresql-15 [root@localhost ~]systemctl start postgresql-15
2 登录数据库及数据库的简单使用
检查postgresql数据库的操作系统用户
[root@localhost ~]# cat /etc/passwd postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
切换至数据库用户
[root@localhost ~]# su - postgres-bash-4.2$
登录数据库
-bash-4.2$ psql psql (15.1) Type "help"for help.
显示现有数据库
postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+----------+----------+-------------+-------------+------------+-----------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (3 rows)
退出到操作系统下，查看postgresql的后台进程
[root@localhost ~]# top -b -u postgres -d 1 -n 1 -ctop-03:17:36 up 54 min, 1 user, load average: 0.00, 0.01, 0.05 Tasks: 116 total, 2 running, 114 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 12028356 total, 10859232 free, 481980 used, 687144 buff/cache KiB Swap: 6160380 total, 6160380 free, 0 used. 11264344 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2070 postgres 2004012521736815920 S 0.0 0.1 0:00.04 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/d+2072 postgres 2002530842148720 S 0.0 0.0 0:00.00 postgres: logger 2073 postgres 2004014042316812 S 0.0 0.0 0:00.00 postgres: checkpointer 2074 postgres 20040138833681872 S 0.0 0.0 0:00.37 postgres: background writer 2076 postgres 20040138862724780 S 0.0 0.1 0:00.08 postgres: walwriter 2077 postgres 20040287233561588 S 0.0 0.0 0:00.00 postgres: autovacuum launcher 2078 postgres 20040285230881356 S 0.0 0.0 0:00.00 postgres: logical replication launcher [root@localhost ~]# ps -fu postgres UID PID PPID C STIME TTY TIME CMD postgres 10101001:02 ? 00:00:00 /usr/pgsql-15/bin/postmaster -D /var/lib/pgsql/15/data/ postgres 10671010001:02 ? 00:00:00 postgres: logger postgres 10761010001:02 ? 00:00:00 postgres: checkpointer postgres 10771010001:02 ? 00:00:00 postgres: background writer postgres 10891010001:02 ? 00:00:00 postgres: walwriter postgres 10901010001:02 ? 00:00:00 postgres: autovacuum launcher postgres 10911010001:02 ? 00:00:00 postgres: logical replication launcher
看一下数据库状态
-bash-4.2$ exportPATH=$PATH:/usr/pgsql-15/bin -bash-4.2$ pg_ctl status pg_ctl: server is running (PID: 1010) /usr/pgsql-15/bin/postgres "-D""/var/lib/pgsql/15/data/"
重新创建数据库，连接数据库，创建表
postgres=# create database test; CREATE DATABASE postgres=# \c test You are now connected to database "test" as user "postgres". test=# create table test (id int, name varchar(20)); CREATE TABLE test=# insert into test values (1, 'test'); INSERT 01test=# select * from test; id | name ----+------1 | test (1 row)
远程连接数据库，首先创建用户，授予权限
CREATE ROLE postgres=# alter database test owner to test; ALTER DATABASE test=# alter table test owner to test; ALTER TABLE
这里直接把test数据库和test表的owner更换为新建的用户，由于数据库的owner是在创建表之后改的，表的owner也需要改一下。
编辑一下数据库data目录里的pg_hba.conf.conf,加入下面一行
host test all 192.168.0.0/16 md5
第三列的all允许用户连接所有的数据库，这样test用户就可以从192.168网段的任一地址连接到数据库了
