PostgreSQL数据库升级的完整流程与注意事项

# 查看当前 postgresql 版本
psql -c "select version();"
# 查看数据目录位置
psql -c "show data_directory;"
# 查看安装路径
which pg_ctl

# 使用 pg_basebackup 创建基础备份
pg_basebackup -h localhost -u replicator -d /backup/pg_basebackup_$(date +%y%m%d) -ft -z -p

# 全库逻辑备份（推荐用于中小型数据库）
pg_dumpall -h localhost -u postgres -f /backup/full_backup_$(date +%y%m%d).sql

# ubuntu/debian 示例
sudo apt update
sudo apt install postgresql-14 postgresql-client-14
# centos/rhel 示例
sudo dnf install postgresql14-server postgresql14

# 通常安装包会自动初始化，若未初始化：
sudo -u postgres /usr/pgsql-14/bin/initdb -d /var/lib/pgsql/14/data

sudo systemctl stop postgresql-13

sudo -u postgres /usr/pgsql-14/bin/pg_upgrade \
    --old-bindir=/usr/pgsql-13/bin \
    --new-bindir=/usr/pgsql-14/bin \
    --old-datadir=/var/lib/pgsql/13/data \
    --new-datadir=/var/lib/pgsql/14/data \
    --check

sudo -u postgres /usr/pgsql-14/bin/pg_upgrade \
    --old-bindir=/usr/pgsql-13/bin \
    --new-bindir=/usr/pgsql-14/bin \
    --old-datadir=/var/lib/pgsql/13/data \
    --new-datadir=/var/lib/pgsql/14/data \
    --link  # 使用硬链接加速（需同一文件系统）

sudo systemctl start postgresql-14
# 运行 pg_upgrade 生成的 analyze_new_cluster.sh
sudo -u postgres /var/lib/pgsql/14/data/analyze_new_cluster.sh

sudo apt install postgresql-14
sudo pg_ctlcluster 14 main start  # debian/ubuntu

# 导出为自定义格式（推荐，支持并行恢复）
pg_dump -h localhost -u postgres -fc mydb > /backup/mydb.dump
# 或导出为纯 sql（便于查看和修改）
pg_dump -h localhost -u postgres mydb > /backup/mydb.sql

create database mydb;
create user myapp with password 'secret';
grant all privileges on database mydb to myapp;

# 自定义格式导入（支持并行）
pg_restore -h localhost -u postgres -d mydb -j 4 /backup/mydb.dump
# sql 格式导入
psql -h localhost -u postgres -d mydb -f /backup/mydb.sql

select name, default_version, installed_version from pg_available_extensions where installed_version is not null;

# ubuntu 安装 postgis 3.3 for postgresql 14
sudo apt install postgis postgresql-14-postgis-3
# 在数据库中启用
create extension postgis;

-- 在新数据库中先创建旧版 postgis
create extension postgis version '3.2.0';

-- 然后升级到新版
alter extension postgis update to '3.3.0';

-- 测试 postgis 函数
select st_astext(st_point(1, 2));

<dependency>
    <groupid>org.postgresql</groupid>
    <artifactid>postgresql</artifactid>
    <version>42.6.0</version> <!-- 2023年最新版，支持 pg 15 -->
</dependency>

// 标准 jdbc 连接
string url = "jdbc:postgresql://localhost:5432/mydb";
properties props = new properties();
props.setproperty("user", "myapp");
props.setproperty("password", "secret");
props.setproperty("ssl", "false"); // 生产环境应启用 ssl
try (connection conn = drivermanager.getconnection(url, props)) {
    system.out.println("connected to postgresql " + conn.getmetadata().getdatabaseproductversion());
}

import org.junit.jupiter.api.test;
import java.sql.*;
import static org.junit.jupiter.api.assertions.*;
public class postgresqlupgradetest {
    private static final string db_url = "jdbc:postgresql://localhost:5432/testdb";
    private static final string user = "testuser";
    private static final string pass = "testpass";
    @test
    public void testbasiccrud() throws sqlexception {
        try (connection conn = drivermanager.getconnection(db_url, user, pass)) {
            // 创建测试表
            try (statement stmt = conn.createstatement()) {
                stmt.execute("drop table if exists upgrade_test");
                stmt.execute("create table upgrade_test (id serial primary key, name text, created_at timestamp default now())");
            }
            // 插入数据
            try (preparedstatement pstmt = conn.preparestatement(
                    "insert into upgrade_test (name) values (?) returning id")) {
                pstmt.setstring(1, "test record");
                try (resultset rs = pstmt.executequery()) {
                    asserttrue(rs.next());
                    int id = rs.getint("id");
                    asserttrue(id > 0);
                }
            }
            // 查询数据
            try (statement stmt = conn.createstatement();
                 resultset rs = stmt.executequery("select count(*) from upgrade_test")) {
                asserttrue(rs.next());
                assertequals(1, rs.getint(1));
            }
            // 更新数据
            try (preparedstatement pstmt = conn.preparestatement(
                    "update upgrade_test set name = ? where id = ?")) {
                pstmt.setstring(1, "updated name");
                pstmt.setint(2, 1);
                assertequals(1, pstmt.executeupdate());
            }
            // 删除数据
            try (statement stmt = conn.createstatement()) {
                assertequals(1, stmt.executeupdate("delete from upgrade_test"));
            }
        }
    }
    @test
    public void testjsonbsupport() throws sqlexception {
        // 验证 jsonb 类型（pg 9.4+ 支持，但新版本有增强）
        try (connection conn = drivermanager.getconnection(db_url, user, pass);
             statement stmt = conn.createstatement()) {
            stmt.execute("drop table if exists json_test");
            stmt.execute("create table json_test (data jsonb)");
            stmt.execute("insert into json_test values ('{\"key\": \"value\", \"num\": 42}')");
            try (resultset rs = stmt.executequery(
                    "select data->>'key' as key_val, (data->>'num')::int as num_val from json_test")) {
                asserttrue(rs.next());
                assertequals("value", rs.getstring("key_val"));
                assertequals(42, rs.getint("num_val"));
            }
        }
    }
    @test
    public void testpartitioning() throws sqlexception {
        // 验证声明式分区（pg 10+ 引入，后续版本增强）
        try (connection conn = drivermanager.getconnection(db_url, user, pass);
             statement stmt = conn.createstatement()) {
            stmt.execute("drop table if exists sales");
            stmt.execute("""
                create table sales (
                    id serial,
                    sale_date date not null,
                    amount numeric
                ) partition by range (sale_date)
                """);
            stmt.execute("""
                create table sales_2023 partition of sales
                for values from ('2023-01-01') to ('2024-01-01')
                """);
            stmt.execute("insert into sales (sale_date, amount) values ('2023-06-15', 100.50)");
            try (resultset rs = stmt.executequery("select count(*) from sales_2023")) {
                asserttrue(rs.next());
                assertequals(1, rs.getint(1));
            }
        }
    }
}

# log4j2.xml 片段
<logger name="org.springframework.jdbc" level="debug"/>
<logger name="com.zaxxer.hikari" level="debug"/>

-- 分析整个数据库
analyze;
-- 或针对关键大表
analyze verbose large_table;

# 使用 pg_controldata 检查控制文件版本
pg_controldata /var/lib/pgsql/14/data
# 检查配置差异
diff /var/lib/pgsql/13/data/postgresql.conf /var/lib/pgsql/14/data/postgresql.conf

# selinux 上下文修复（centos/rhel）
sudo semanage fcontext -a -t postgresql_db_t "/var/lib/pgsql/14(/.*)?"
sudo restorecon -r /var/lib/pgsql/14
# apparmor 配置（ubuntu）
sudo nano /etc/apparmor.d/usr.sbin.postgresql-14
# 添加数据目录路径

-- 检查时区设置
show timezone;
-- 测试时间转换
select '2023-01-01 12:00:00+00'::timestamptz;

// 正确处理带时区的时间
offsetdatetime odt = resultset.getobject("created_at", offsetdatetime.class);
localdatetime ldt = odt.tolocaldatetime(); // 转换为本地时间

-- 查看复制槽
select * from pg_replication_slots;
-- 升级前暂停复制，升级后重建槽
select pg_drop_replication_slot('my_slot');
-- 升级后
select pg_create_logical_replication_slot('my_slot', 'pgoutput');

# 升级后运行
vacuumdb --all --analyze-in-stages

---
- name: postgresql major version upgrade
  hosts: db_servers
  become: yes
  vars:
    pg_old_version: "13"
    pg_new_version: "14"
    pg_data_dir_old: "/var/lib/pgsql/{{ pg_old_version }}/data"
    pg_data_dir_new: "/var/lib/pgsql/{{ pg_new_version }}/data"
  tasks:
    - name: backup current database
      shell: pg_dumpall -u postgres > /backup/pg_full_{{ ansible_date_time.iso8601 }}.sql
      register: backup_result
    - name: install new postgresql version
      package:
        name: "postgresql-{{ pg_new_version }}"
        state: present
    - name: initialize new cluster
      command: /usr/pgsql-{{ pg_new_version }}/bin/initdb -d {{ pg_data_dir_new }}
      args:
        creates: "{{ pg_data_dir_new }}/pg_version"
    - name: stop old cluster
      systemd:
        name: "postgresql-{{ pg_old_version }}"
        state: stopped
    - name: run pg_upgrade check
      command: >
        /usr/pgsql-{{ pg_new_version }}/bin/pg_upgrade
        --old-bindir=/usr/pgsql-{{ pg_old_version }}/bin
        --new-bindir=/usr/pgsql-{{ pg_new_version }}/bin
        --old-datadir={{ pg_data_dir_old }}
        --new-datadir={{ pg_data_dir_new }}
        --check
      register: pg_upgrade_check
      ignore_errors: yes
    - name: fail if check fails
      fail:
        msg: "pg_upgrade check failed!"
      when: pg_upgrade_check.rc != 0
    - name: run pg_upgrade
      command: >
        /usr/pgsql-{{ pg_new_version }}/bin/pg_upgrade
        --old-bindir=/usr/pgsql-{{ pg_old_version }}/bin
        --new-bindir=/usr/pgsql-{{ pg_new_version }}/bin
        --old-datadir={{ pg_data_dir_old }}
        --new-datadir={{ pg_data_dir_new }}
        --link
    - name: start new cluster
      systemd:
        name: "postgresql-{{ pg_new_version }}"
        state: started
        enabled: yes

// flyway 配置示例
@configuration
public class flywayconfig {
    @bean
    public flyway flyway(datasource datasource) {
        return flyway.configure()
                .datasource(datasource)
                .locations("classpath:db/migration")
                .load();
    }
}