PostgreSQL部署 初始化 配置远程

参考：

https://blog.csdn.net/m0_59586152/article/details/125136342

一. 安装

1. ubuntu换源后即可apt安装

1
2
# ubuntu安装
sudo apt install postgresql postgresql-contrib

2.centos

image-20230627134336951

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
1、导入yum源
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2、安装PostgreSQL服务
sudo yum install -y postgresql12 postgresql12-server

安装PostgreSQL 11就是 ： yum install postgresql12 postgresql12-server 
安装PostgreSQL 9.5就是： yum install postgresql95 postgresql95-server 
依此类推 
3、初始化数据库
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb 
#Initializing database ...OK

4、启动PostgreSQL服务
#启动PostgreSQL服务
sudo systemctl start postgresql-12
#设置PostgreSQL服务为开机启动
sudo systemctl enable postgresql-12
9.x版本的服务名是postgresql-9.x
##############简单部署脚本##############
#!/bin/bash
#环境：centos7
#软件：PostgreSQL
install_pgsql() {
    echo -e "\033[36m ==============导入yum源 \033[0m"
    sleep 6
    sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    echo -e "\033[36m ========================安装PostgreSQL服务+初始化数据库 \033[0m"
    sudo yum install -y postgresql12 postgresql12-server
    sudo /usr/pgsql-12/bin/postgresql-12-setup initdb 
    [ $? = 0 ] && echo -e "\033[36m =============PostgreSQL安装完成 \033[0m"  || echo -e "\033[33m PostgreSQL安装失败 \033[0m"
    systemctl start  postgresql-12
    systemctl status postgresql-12
}
main(){
    install_pgsql
}
main

二. postgresql配置远程

ubuntu20.04文件位置：/etc/postgresql/10/main/pg_hba.conf
修改配置允许远程连接

1.连接数修改成500，生产可根据实际情况修改。配置使用md5方式认证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 配置pg_hba.conf文件
vim /etc/postgresql/10/main/pg_hba.conf
# 添加如下信息到IPv4 local connections之后
host    all             all             0.0.0.0/0               md5
# 修改连接数
vim /etc/postgresql/10/main/postgresql.conf
# 修改如下
-------------------------------------------------
max_connection = 1000
# 超级用户保留的连接默认为3可根据需要更改
superuser_reserved_connections=6
# postgres 设置 idle 状态连接回收， 该状态默认不开启；postgres.conf idle_in_transaction_session_timeout=20000; 修改为idle 状态 20s 后回收该连接； 修改后重启
idle_in_transaction_session_timeout=20000
-------------------------------------------------

2.修改配置文件

默认情况下，PostgreSQL 服务器仅仅监听本地网络接口：127.0.0.1，
配置 PostgreSQL 服务器允许远程访问，编辑【postgresql.conf】文件
为了允许远程访问你的PostgreSQL 服务器。

1
2
3
4
# 添加 listen_addresses = '*'
vim /etc/postgresql/10/main/postgresql.conf
打开配置文件【postgresql.conf】
并且在【CONNECTIONS AND AUTHENTICATION】一节添加【listen_addresses = '*'】。

3.重启 postgresql 生效

1
sudo service postgresql restart

此时便可以远程连接pgsql。

image-20230627143356429

image-20230627142148640

image-20230627135639198

4.验证 （ 成功）

image-20230627143237993

三. pgsql使用

psql 控制台命令：除了前面已经用到的 \password命令（设置密码）和 \q命令（退出）以外，控制台还提供一系列其他命令。
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

1.初始化

可以使用以下命令更改任何用户（包括 postgres）的密码：
输入 \du 命令查看 PostgreSQL 用户：

1
2
3
4
5
6
# 更改密码
ALTER USER postgres WITH PASSWORD 'my_password';  更改密码
# 建议新建一个zabbix用户并给他开通超级管理员权限
# 建议你另外创建一个用户（不建议使用默认的 postgres 用户）。为此，请使用以下命令：
CREATE USER my_user WITH PASSWORD 'my_password'; 
ALTER USER my_user WITH SUPERUSER;            （添加超级用户管理员权限）

2.连库操作

两种方式访问 PostgreSQL 服务器
为了以 postgres 用户身份登录 PostgreSQL 服务器，首先切换用户，然后使用 psql工具访问 PostgreSQL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1. sudo su - postgres  切换用户
 
2. psql  进入 PostgreSQL Shell
 
3. \q    退出 PostgreSQL Shell
 
# 你也可以不切换用户，而使用 sudo 命令访问 PostgreSQL。
sudo -u postgres psql
注：通常 postgres 用户仅应用于本地使用，正式生产环境可以使用 postgres 账户重新创建账户，授权角色信息并指定db数据库；
# 如果创建的用户不想使用，可以使用 DROP 命令删除用户：
DROP USER my_user;  删除用户
# 要使用其他用户登录，使用 \q 命令退出，然后使用以下命令登录：
psql -U my_user  
# 可以使用 -d 参数直接连接数据库：
psql -U my_user -d my_db
例子： psql -U zabbix -d zabbix
# 你可以使用其他已存在的用户调用 PostgreSQL。例如，我使用 ubuntu 登录，从终端执行以下命名：
psql -U ubuntu -d postgres

结语祝好！