win、ubuntu 环境安装 postgresql 15

Posted on Edited on In Views:

1、win10 或 Windows Server 2022 Datacenter

下载地址：https://www.enterprisedb.com/download-postgresql-binaries

1.1、执行安装

1）解压，并添加 pgsql\bin 到环境变量。

2）新建目录 C:\pgsql\data，执行 initdb -U postgres -A password -E UTF-8 -W -D C:\pgsql\data，该步骤将提示输入密码。

3）执行 pg_ctl -D "C:/pgsql/data" start 启动服务。

4）执行 psql -U postgres 登录，输入之前步骤的密码。

注意：控制台方式启动服务，当关闭控制台时，服务随即终止。若要保持后台启动，需要注册服务。

5）注册服务：以管理员身份执行 pg_ctl register -N PostgreSQL -D "C:/pgsql/data"，再执行 net start PostgreSQL

2、ubuntu 22.04

2.1、官方文档

文档地址：https://www.postgresql.org/download/linux/ubuntu/ （version：20230726）

Linux downloads (Ubuntu) Ubuntu

PostgreSQL is available in all Ubuntu versions by default. However, Ubuntu “snapshots” a specific version of PostgreSQL that is then supported throughout the lifetime of that Ubuntu version. Other versions of PostgreSQL are available through the PostgreSQL apt repository.

PostgreSQL Apt Repository

If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.

The PostgreSQL Apt Repository supports the current versions of Ubuntu:

  • kinetic (22.10, non-LTS)
  • jammy (22.04, LTS)
  • focal (20.04, LTS)
  • bionic (18.04, LTS)

on the following architectures:

  • amd64
  • arm64 (18.04 and newer; LTS releases only)
  • i386 (18.04 and older)
  • ppc64el (LTS releases only)

To use the apt repository, follow these steps:

1
2
3
4
5
6
7
8
9
10
11
12
# Create the file repository configuration:
sudo sh -c 'echo "deb http://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-15

For more information about the apt repository, including answers to frequent questions, please see the PostgreSQL Apt Repository page on the wiki.

Included in distribution

Ubuntu includes PostgreSQL by default. To install PostgreSQL on Ubuntu, use the apt-get (or other apt-driving) command:

1
apt-get install postgresql-12

The repository contains many different packages including third party addons. The most common and important packages are (substitute the version number as required):

2.2、执行 pg-15.3 安装

执行 sudo apt-get -y install postgresql-15 将安装 15.3

2.3、查看服务

执行 sudo systemctl status postgresql 查看服务运行情况：

1
2
3
4
5
postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset:>
     Active: active (exited) since Tue 2023-07-25 09:55:14 CST; 12min ago
   Main PID: 4625 (code=exited, status=0/SUCCESS)
        CPU: 2ms

2.3、查看路径

执行 ps -ef | grep postgres | grep -v 'grep' 显示如下内容：

1
2
3
4
5
6
postgres    5671       1  0 09:55 ?        00:00:00 /usr/lib/postgresql/15/bin/postgres -D /var/lib/postgresql/15/main -c config_file=/etc/postgresql/15/main/postgresql.conf
postgres    5672    5671  0 09:55 ?        00:00:00 postgres: 15/main: checkpointer
postgres    5673    5671  0 09:55 ?        00:00:00 postgres: 15/main: background writer
postgres    5675    5671  0 09:55 ?        00:00:00 postgres: 15/main: walwriter
postgres    5676    5671  0 09:55 ?        00:00:00 postgres: 15/main: autovacuum launcher
postgres    5677    5671  0 09:55 ?        00:00:00 postgres: 15/main: logical replication launcher

可获知以下信息：

  • 安装目录：/usr/lib/postgresql/15/bin/postgres
  • 初始化数据库的数据目录：/var/lib/postgresql/15/main
  • 配置文件路径：/etc/postgresql/15/main/postgresql.conf

2.4、登录

1）切换用户：执行 su - postgres

注意：如果提示 su：认证失败，需要执行 sudo passwd postgres 设置密码再切换，输入设置的密码即可。

2）输入 psql 登录

如果本地登录需要切换新增的角色，要在 pg_hba.conf 配置认证方式。例如：local all all md5

3、通用部分

3.1、创建角色

1
CREATE ROLE name [ [ WITH ] option [ ... ] ]

option 属性

  • 登录权限（LOGIN ），具有该属性的角色才能连接数据库。
  • 创建数据库（CREATEDB），具有该属性的角色才能够创建数据库（超级用户除外）。
  • 创建角色（CREATEROLE），具有该属性的角色才能够创建其他角色（超级用户除外）。
  • 启动复制（REPLICATION），具有该属性的角色才能够启动流复制（超级用户除外）。
  • 密码（PASSWORD），只有当用户连接数据库使用的客户端认证方法要求提供密码时，密码属性才有意义。password 和 md5 认证方法需要使用密码。
  • 超级用户（SUPERUSER），数据的超级用户可以避开所有的权限检查，只验证登录权限。

例 1，创建角色并赋予【登录、创建数据库、密码】属性：create role abc123 login createdb password 'abc123';

例 2，修改角色属性：alter role <role_name> login createdb;

注：可执行 \du 查看当前全部角色

3.2、查看版本

执行 psql --version 查看当前版本：

1
psql (PostgreSQL) 15.3 (Ubuntu 15.3-1.pgdg22.04+1)

3.3、远程登录

  • 修改 /etc/postgresql/15/main/pg_hba.conf，按需增加配置，如：host all all 0.0.0.0/0 md5
  • 修改配置文件 postgresql.conf，将 listen_addresses = 'localhost' 修改为 listen_addresses = '*'
  • 执行 sudo systemctl restart postgresql 重启服务

注意：即使修改配置文件，还应保证防火墙放行。尤其是 win 环境，需要在防火墙入站规则添加 5432 端口的放行规则。

3.3.1、pg_hba.conf 配置参数

1）TYPE（主机类型）

  • local，unix-domain 的 socket 连接
  • host，TCP/IP socket
  • hostssl，SSL 加密的 TCP/IP socket

2）DATABASE（数据库名称）

all、sameuser、samerole、replication、数据库名称，或者多个数据库名称用 逗号

注意：all 不匹配 replication

3）USER（用户名称）

all、一个用户名、一组用户名，多个用户时，可以用逗号隔开，或者在用户名称前缀 +，在 USER 和 DATABASE 字段，也可以写一个单独的文件名称用 @ 前缀，该文件包含数据库名称或用户名称。

4）ADDRESS（地址范围）

该参数可以为 主机名称 或者 IP/32(IPV4) 或 IP/128(IPV6)，主机名称以 . 开头，samehost 或 samenet 匹配任意 ip 地址

5）METHOD（验证方式）

  • trust，无需密码验证可直接连接访问
  • reject，拒绝访问
  • md5，以 md5 加密
  • password，明文传输密码
  • scram-sha-256
  • gss
  • sspi
  • ident
  • peer
  • pam
  • ldap
  • radius
  • cert

注意：若为 password 则发送的为明文密码

4、问题集锦

4.1、UTC 时间与系统时间偏差 8 小时

解决方案：

1）显式指定 PRC

1
select now() at time zone 'PRC';

结果：2023-07-30 16:01:17.285747

2）进一步指定格式

1
select to_char(now() at time zone 'PRC','YYYY-MM-DD hh24:mi:ss');

结果：2023-07-30 16:03:34