PostgreSQL跨版本升级的方法技巧与问题排查

select version();

postgresql 11.22 on x86_64-pc-linux-gnu, compiled by gcc (gcc) 4.8.5 20150623 (red hat 4.8.5-44), 64-bit

# 逻辑备份（推荐用于跨版本）
pg_dumpall -u postgres > full_backup_20240501.sql

# 或针对单个数据库
pg_dump -u myuser -d mydb > mydb_backup.sql

# ubuntu 示例
sudo apt install postgresql-16

sudo pg_createcluster 16 main --port=5433
# 使用不同端口避免冲突

pg_dump -u postgres -p 5432 -d mydb -fc > mydb.dump
# -fc 表示自定义格式，支持并行恢复

-- 连接到新实例（端口 5433）
create database mydb;
create user myuser with password 'secret';
grant all privileges on database mydb to myuser;

pg_restore -u postgres -p 5433 -d mydb -j 4 mydb.dump
# -j 4 表示使用 4 个并行进程加速

spring:
  datasource:
    url: jdbc:postgresql://localhost:5433/mydb
    username: myuser
    password: secret
    hikari:
      maximum-pool-size: 20

-- 恢复前执行
set session_replication_role = 'replica';

-- 恢复后执行
set session_replication_role = 'origin';

sudo apt install postgresql-16

sudo systemctl stop postgresql@11-main

sudo pg_createcluster 16 main --port=5433
sudo systemctl stop postgresql@16-main  # 确保未运行

sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/11/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-datadir=/var/lib/postgresql/11/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --check

sudo -u postgres /usr/lib/postgresql/16/bin/pg_upgrade \
  --old-bindir=/usr/lib/postgresql/11/bin \
  --new-bindir=/usr/lib/postgresql/16/bin \
  --old-datadir=/var/lib/postgresql/11/main \
  --new-datadir=/var/lib/postgresql/16/main \
  --link  # 使用硬链接加速（节省磁盘空间）

sudo systemctl start postgresql@16-main

./analyze_new_cluster.sh

./delete_old_cluster.sh

<dependency>
    <groupid>org.postgresql</groupid>
    <artifactid>postgresql</artifactid>
    <version>42.7.3</version>
</dependency>

sudo apt install postgis postgresql-16-postgis-3

create extension postgis;

create user app_user with login password 'xxx';

pg_restore --no-owner -d mydb mydb.dump

select setval('users_id_seq', (select max(id) from users));

timezone = 'asia/shanghai'
lc_time = 'en_us.utf-8'

9.6 → 10 → 11 → 12 → 13 → 14

@testcontainers
@springboottest
class userrepositorytest {

    @container
    static postgresqlcontainer<?> postgres = new postgresqlcontainer<>("postgres:16")
            .withdatabasename("testdb")
            .withusername("test")
            .withpassword("test");

    @dynamicpropertysource
    static void configureproperties(dynamicpropertyregistry registry) {
        registry.add("spring.datasource.url", postgres::getjdbcurl);
        registry.add("spring.datasource.username", postgres::getusername);
        registry.add("spring.datasource.password", postgres::getpassword);
    }

    @test
    void shouldsaveandfinduser() {
        user user = new user("alice", "alice@example.com");
        userrepository.save(user);
        assertthat(userrepository.findbyid(user.getid())).ispresent();
    }
}

-- 在新版本中启用 auto_explain
load 'auto_explain';
set auto_explain.log_min_duration = 0;
set auto_explain.log_analyze = true;

-- 执行关键查询
select * from orders where customer_id = 123;

@test
void concurrentinsertshouldnotcausedeadlock() throws interruptedexception {
    executorservice executor = executors.newfixedthreadpool(10);
    countdownlatch latch = new countdownlatch(10);

    for (int i = 0; i < 10; i++) {
        executor.submit(() -> {
            try {
                userservice.createuser("user" + threadlocalrandom.current().nextint());
            } finally {
                latch.countdown();
            }
        });
    }

    latch.await();
    assertthat(userrepository.count()).isequalto(10);
}

#!/bin/bash
set -e

old_version=11
new_version=16
db_name=myapp
backup_file="/backups/${db_name}_$(date +%y%m%d).dump"

echo "🚀 starting postgresql upgrade from $old_version to $new_version"

# 1. 备份
echo "💾 creating backup..."
pg_dump -u postgres -p 5432 -d $db_name -fc > $backup_file

# 2. 安装新版本（ubuntu）
echo "📦 installing postgresql $new_version..."
sudo apt update
sudo apt install -y postgresql-$new_version

# 3. 初始化新集群
echo "initstructuring new cluster..."
sudo pg_createcluster $new_version main --port=5433

# 4. 创建数据库和用户
echo "👥 setting up roles..."
sudo -u postgres psql -p 5433 -c "create database $db_name;"
sudo -u postgres psql -p 5433 -c "create user appuser with password 'secret';"
sudo -u postgres psql -p 5433 -c "grant all privileges on database $db_name to appuser;"

# 5. 恢复数据
echo "🔄 restoring data..."
pg_restore -u postgres -p 5433 -d $db_name -j 4 $backup_file

# 6. 验证
echo "🔍 validating..."
row_count=$(psql -u postgres -p 5433 -d $db_name -t -c "select count(*) from users;")
echo "total users: $row_count"

echo "✅ upgrade completed successfully!"

analyze verbose;

reindex database mydb;

# docker-compose.yml
services:
  postgres-old:
    image: postgres:11
    volumes:
      - pgdata:/var/lib/postgresql/data
  postgres-new:
    image: postgres:16
    volumes:
      - pgdata-new:/var/lib/postgresql/data

# 停止新实例
sudo systemctl stop postgresql@16-main

# 启动旧实例
sudo systemctl start postgresql@11-main