PostgreSQL部署与使用

PostgreSQL是一个开源数据库，以其强大的功能、标准合规性、可靠性和扩展性受到广泛认可和采用。PostgreSQL遵循SQL标准，支持NoSQL数据类型（JSON/XML/hstore）递归查询、全文搜索、GIS地理信息处理等，能够应对各种复杂数据分析需求。广泛应用于互联网网站、企业级应用后端、数据仓库与分析平台、位置应用系统、复杂数据对象处理等应用场景

通过包管理器部署

本例子使用ubuntu安装，参考官网教程：ubuntu安装postgresql

如果要卸载，可执行如下几条命令：

apt remove --purge postgresql* #--purge可以完全移除包以及配置文件
rm -rf /var/log/postgresql*
rm -rf /var/lib/postgresql*
apt autoremove                 #删除与postgresql相关依赖

安装完成后，查看服务情况，如图：

从上图看出PostgreSQL服务的状态显示为Active: active (exited)可能意味着服务已经启动并成功执行了其主要功能，但在执行过程中没有持续运行的进程。这通常出现在“伞式”服务（如PostgreSQL）中，伞式服务会启动一个或多个子进程来执行实际的工作，而主进程（即systemd认为的“服务”）在启动子进程后就会退出。因此，尽管主进程已经退出，但子进程仍在运行，提供数据库服务，postgresql就是一个伞式服务，因此postgresql主服务启动postgresql@14-main.service后退出，由子进程提供服务

执行命令ps -ef |grep postgresql即可查看配置文件所在位置，如图：

postgresql默认监听地址为127.0.0.1，如果要修改监听所有地址，可修改listen_addresses即可，编辑postgresql.conf，如图：

注：修改端口也是修改此配置文件中的5432端口为其他端口即可

配置允许通过客户端远程连接，编辑pg_hba.conf文件，新增内容如下：

#文件中自带的其他的host all all 127.0.0.1这种可以注释掉
host     all            all             0.0.0.0/0               md5

最后重启即可 systemctl restart ‘postgresql*’

二进制安装

源码下载地址：https://www.postgresql.org/ftp/source/，源码需要编译为二进制文件，然后才可以部署

1、首先安装依赖包，命令如下：

yum install readline-devel libicu-devel python36-devel python36

2、执行命令编译postgresql，如下：

./configure --prefix=/usr/local/postgresql16.3 --with-perl --with-python
make && make install

安装后，在目录postgresql16.3下可以看到编译后的文件，如图：

3、创建用户和用户组，给安装目录赋权，命令如下：

groupadd postgres
useradd -m -g postgres postgres
chown postgres:postgres -R /usr/local/postgresql16.3

4、创建数据目录，并设置属主以及属组，如下：

mkdir -p /data/postgresql/data
chown -R postgres:postgres /data/postgresql/data

5、初始化数据库，首先登录postgres，如图：

su - postgres
/usr/local/postgresql16.3/bin/initdb -D /data/postgresql/data -E UTF8 --locale=en_US.UTF-8

初始化完成后，可以看到数据目录下内容如图：

如果配置监听地址或者修改端口，参考包管理器部署中的修改即可，这里不再重复

6、启动postgresql，执行如下命令：

/usr/local/postgresql16.3/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/data/postgresql.log start

启动后查看输出的日志信息postgresql.log，内容如图：

停止方法：

/usr/local/postgresql16.3/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/data/postgresql.log stop

注意：pg_ctl要使用全路径，否则可能会出现一些问题

7、编写服务启动文件，通过systemctl托管，编辑postgresql.service，内容如下：

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/usr/local/postgresql16.3/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/data/postgresql.log start
ExecStop=/usr/local/postgresql16.3/bin/pg_ctl -D /data/postgresql/data -l /data/postgresql/data/postgresql.log stop
RemainAfterExit=on
User=postgres
Group=postgres

[Install]
WantedBy=multi-user.target

注：将编译后的文件(/usr/local/postgresql16.3)直接复制到新的机器上进行初始化可能会提示缺少库，需要执行命令安装即可yum install readline-devel libicu-devel

使用

postgresql安装后，会默认在Linux主机创建用户postgres，此用户可以直接登录postgresql，并且是超级管理员用户

首先切换到postgres，然后执行命令psql，登录postgresql，最后执行命令查看数据库信息，如图：

从上图看出，默认包含三个数据库postgres、template0、template1，所有者都是postgres

查看当前用户信息，可以看到只有一个postgres，并且是超级管理员，如图：

1、给用户postgres设置一个密码，按照上面方法登录postgresql，执行如下命令设置：

alter user postgres with password '密码';

修改pg_hba.conf，将postgres用户的认证方式从peer改为md5，如图：

如果要创建的其他用户也要通过密码登录，那么将postgres那行注释掉，然后将下面的全部都是all的那行最后修改为md5即可，如图：

md5和peer的区别：

md5 需要使用密码进行认证，而 peer 不需要密码，它依赖于本地系统的用户认证，也就是说在Linux本地创建了一个用户abc，然后在postgresql中也同样创建了一个abc，那么如果配置了peer认证，此时通过abc登录就不需要输入密码了

再次通过postgres登录postgresql，可以看到提示输入密码认证，如图：

注：如果忘记了postgres密码，可修改pg_hba.conf文件，将md5改为peer或者trust，然后即可无密码登录

2、创建一个新用户和一个新数据库，将数据库权限赋予用户，如下：

create user test_user with password '123456#';
create database test_db owner test_user;
grant all privileges on database test_db to test_user;

在命令行通过新创建的用户和密码来连接postgresql的test_db库，如图：

3、登录就已经在库test_db中了，通过create命令创建表，id为主键，如图：

• serial：表示自增

创建完成后，执行\d student命令即可查看表结构，如图：

4、通过drop 命令删除表，可使用如下命令：

drop table tablename

5、执行INSERT、UPDATE、DELETE语句来修改表，首先执行INSERT插入数据，如图：

insert into student (number,student_name,age,math_score) values(101,'zhangsan',18,99);

执行UPDATE命令修改表中数据，如图：

执行DELETE删除表中数据，如图：

注：因为id为自增序列，删除后需要重置序列，否则再次插入数据将从id为2开始，使用如下命令重置:

ALTER SEQUENCE student_id_seq RESTART WITH 1;

student_id_seq是哪里来的，执行\d命令即可看到，student表对应的就是student_id_seq，如图：

注：使用\h create table等方式可以查看postgresql中各种命令用法