在 Debian 12 上安装Postgresql

安装

使用APT仓库安装

使用此安装方式会自动创建系统postgres用户、初始数据库等所需的东西，可前往 安装文档 查看详细说明

以下脚本可前往 文档地址 查看最新版本

shell
1
2
3
4
5
6
7
8
9
10
11
12
# Create the file repository configuration:
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

树莓派等aarch架构可以参考如下脚本

shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# Import the repository signing key:
sudo apt-get install ca-certificates curl
sudo install -m 0755 -d /etc/apt/keyrings
sudo curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc -o /etc/apt/keyrings/pgdg.asc
sudo chmod a+r /etc/apt/keyrings/pgdg.asc

# Create the file repository configuration:
sudo sh -c 'echo "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/pgdg.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Update the package lists:
sudo apt-get update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt-get -y install postgresql

安装完毕后的相关路径如下：

  • 执行文件：/usr/lib/postgresql/{PG_VERSON}
  • 配置文件：/etc/postgresql/{PG_VERSON}/main
  • 服务脚本：/etc/init.d/postgresql（可通过 help 命令查看脚本支持的命令）

可通过以下指令重启服务：
/etc/init.d/postgresql restart

配置

迁移路径

shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
# 关闭服务
service postgresql stop

# 复制文件
# mkdir /data
# mount /dev/sdb1 /data
# mkdir /data/postgresql
cp -rf /var/lib/postgresql/16/main/ /data/postgresql/
# 删除旧文件
# rm -rf /var/lib/postgresql

# 设置用户和权限
chown -R postgres:postgres /data/postgresql/
chmod 700 /data/postgresql/

#将配置文件的数据存储路径改成新的
vi /etc/postgresql/16/main/postgresql.conf
# data_directory='/data/postgresql/main'

# 启动服务
service postgresql start

修改密码

修改默认数据库的密码

shell
1
2
# 登录postgres
sudo -u postgres psql
sql
1
ALTER USER postgres WITH PASSWORD '123456';

修改时区（非必须）

查看时区:

sql
1
2
3
4
5
postgres=# show time zone;
   TimeZone    
---------------
 Etc/UTC
(1 行记录)

查看数据库可供选择的时区:

sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
postgres=#  select * from pg_timezone_names; 
                  name                  | abbrev | utc_offset | is_dst 
----------------------------------------+--------+------------+--------
 Africa/Abidjan                         | GMT    | 00:00:00   | f
 Africa/Accra                           | GMT    | 00:00:00   | f
 Africa/Addis_Ababa                     | EAT    | 03:00:00   | f
 Africa/Algiers                         | CET    | 01:00:00   | f
 Africa/Asmara                          | EAT    | 03:00:00   | f
 Africa/Asmera                          | EAT    | 03:00:00   | f
 Africa/Bamako                          | GMT    | 00:00:00   | f
 Africa/Bangui                          | WAT    | 01:00:00   | f
 Africa/Banjul                          | GMT    | 00:00:00   | f
 Africa/Bissau                          | GMT    | 00:00:00   | f
.......................

设置时区:

sql
1
postgres=# set time zone "Asia/Shanghai";

同步修改系统用户的密码（安装时自动创建的用户，源码安装需要自行创建）

shell
1
2
3
4
5
6
# 切换至具有管理员权限的用户
sudo su root
# 删除postgres用户原有密码
sudo passwd -d postgres
# 按需求更新密码
sudo -u postgres passwd

设置远程访问

配置监听IP

使用nano、vim等文本编辑器修改 /etc/postgresql/{PG_VERSON}/main/postgresql.conf

找到以下这一段

plaintext
1
2
3
4
5
6
7
8
9
10
11
12
13
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost'		# what IP address(es) to listen on;
					# 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)
#reserved_connections = 0		# (change requires restart)

将listen_addresses前的 # 删除再将值修改为需要的内容，例如

plaintext
1
2
3
listen_addresses = '*' # 所有
listen_addresses = '192.168.3.10' # 单个ip
listen_addresses = '192.168.3.10,172.16.1.10' # 多个ip

配置密码校验

使用nano、vim等文本编辑器修改 /etc/postgresql/{PG_VERSON}/main/pg_hba.conf

找到以下这一段（一般都在此文件的最末尾）

shell
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

在末尾或觉得合适的地方进行修改，如下：

shell
1
2
3
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               scram-sha-256

其中的 scram-sha-256 可视情况修改为 trust 或 md5； 0.0.0.0/0 为所有IP段

允许部分IP段可修改为

shell
1
2
3
# IPv4 local connections:
host    all             all             192.168.3.0/24            scram-sha-256
host    all             all             172.16.0.0/16             scram-sha-256

重启

shell
1
/etc/init.d/postgresql restart

卸载Postgres

shell
1
2
3
4
5
6
7
8
9
10
# 停止数据库
sudo /etc/init.d/postgresql stop
# 移除软件包
sudo apt-get --purge remove postgresql\* 
# 删除相关配置文件
sudo rm -r /etc/postgresql/
sudo rm -r /etc/postgresql-common/
sudo rm -r /var/lib/postgresql/
sudo userdel -r postgres
sudo groupdel postgres