PostgreSQL基础备份与 WAL 日志备份完整代码实践

# 启用 wal 归档
archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f'
# 设置 wal 保留策略（可选，但推荐）
wal_keep_size = 1gb
# 启用复制连接（用于 pg_basebackup）
max_wal_senders = 10

# type  database        user            address                 method
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            md5
host    replication     all             192.168.1.0/24          md5

mkdir -p /path/to/wal_archive
chown postgres:postgres /path/to/wal_archive
chmod 700 /path/to/wal_archive

sudo systemctl restart postgresql

show archive_mode;
show archive_command;

pg_basebackup -h localhost -u replicator -d /backup/base_$(date +%y%m%d) -ft -z -p

log:  archived wal file "00000001000000000000000a" to "/path/to/wal_archive/00000001000000000000000a"

# 1. 停止服务
sudo systemctl stop postgresql
# 2. 清空并恢复基础备份
rm -rf $pgdata/*
tar -xzf /backup/base_20240501/base.tar.gz -c $pgdata
# 3. 创建 recovery.signal
touch $pgdata/recovery.signal
# 4. 配置恢复参数（追加到 postgresql.conf）
cat >> $pgdata/postgresql.conf <<eof
restore_command = 'cp /path/to/wal_archive/%f %p'
recovery_target_time = '2024-05-01 14:30:00'
eof
# 5. 启动服务
sudo systemctl start postgresql

create user backup_user with replication login password 'secure_password';

import java.io.*;
import java.time.localdatetime;
import java.time.format.datetimeformatter;
public class postgresbackup {
    public static void performbasebackup(string host, string username, string password, string backupdir) {
        try {
            // 构建备份目录名（含时间戳）
            string timestamp = localdatetime.now().format(datetimeformatter.ofpattern("yyyymmdd_hhmmss"));
            string targetdir = backupdir + "/base_" + timestamp;
            // 创建目录
            new file(targetdir).mkdirs();
            // 构建命令（注意：密码通过 .pgpass 文件或环境变量传递更安全）
            processbuilder pb = new processbuilder(
                "pg_basebackup",
                "-h", host,
                "-u", username,
                "-d", targetdir,
                "-ft", "-z", "-p", "-xs"
            );
            // 设置环境变量（可选）
            pb.environment().put("pgpassword", password);
            process process = pb.start();
            // 读取输出
            try (bufferedreader reader = new bufferedreader(new inputstreamreader(process.getinputstream()))) {
                string line;
                while ((line = reader.readline()) != null) {
                    system.out.println("[pg_basebackup] " + line);
                }
            }
            int exitcode = process.waitfor();
            if (exitcode == 0) {
                system.out.println("✅ 基础备份成功完成: " + targetdir);
            } else {
                system.err.println("❌ 基础备份失败，退出码: " + exitcode);
            }
        } catch (exception e) {
            e.printstacktrace();
        }
    }
    public static void main(string[] args) {
        performbasebackup("localhost", "backup_user", "secure_password", "/backup");
    }
}

import java.sql.*;
public class forcewalswitch {
    private static final string url = "jdbc:postgresql://localhost:5432/postgres";
    private static final string user = "admin";
    private static final string password = "admin_password";
    public static void switchwal() {
        string sql = "select pg_switch_wal();";
        try (connection conn = drivermanager.getconnection(url, user, password);
             statement stmt = conn.createstatement();
             resultset rs = stmt.executequery(sql)) {
            if (rs.next()) {
                string newsegment = rs.getstring(1);
                system.out.println("🔄 wal 段已切换至: " + newsegment);
            }
        } catch (sqlexception e) {
            system.err.println("❌ 切换 wal 失败: " + e.getmessage());
        }
    }
    public static void main(string[] args) {
        switchwal();
    }
}

public class createrestorepoint {
    public static void createrestorepoint(string name) {
        string sql = "select pg_create_restore_point(?);";
        try (connection conn = drivermanager.getconnection(url, user, password);
             preparedstatement pstmt = conn.preparestatement(sql)) {
            pstmt.setstring(1, name);
            try (resultset rs = pstmt.executequery()) {
                if (rs.next()) {
                    string lsn = rs.getstring(1);
                    system.out.println("📍 恢复点 '" + name + "' 已创建，lsn: " + lsn);
                }
            }
        } catch (sqlexception e) {
            system.err.println("❌ 创建恢复点失败: " + e.getmessage());
        }
    }
    public static void main(string[] args) {
        createrestorepoint("before_batch_job");
    }
}

recovery_target_name = 'before_batch_job'

# 删除 7 天前的 wal
find /wal_archive -name "*.partial" -delete
find /wal_archive -type f -mtime +7 -delete
# 删除 4 周前的基础备份
find /backup -name "base_*" -type d -mtime +28 -exec rm -rf {} +

ls -l /backup/base_20240501/backup_label

pg_waldump /wal_archive/00000001000000000000000a | head -n 5

test ! -f /wal_archive/%f && cp %p /wal_archive/%f

pg_basebackup -d /backup -ft -z -z9

-- 创建仅用于备份的用户
create user backup_user with replication login;
-- 不授予任何数据库权限！