【postgresql系列----pg安装以及pg_rman使用】

一、pg的安装

1、如果机器不能上网，那就下载源码进行编译安装
2、解压
tar -xvf postgresql-14.0.tar.gz
3、新建postgres用户
groupadd postgres
useradd -g postgres postgres
passwd postgres
4、安装依赖包
yum install *zlib*
yum install *libreadline*
5、编译安装
./configure
make && make install
6、配置化境变量
vim /etc/profile
export PATH=$PATH:/usr/local/pgsql/bin
7、新建postgresql的数据目录
mkdir /pgdata
chown postgres:postgres /pgdata
su - postgres
8、初始化数据库
initdb -D /pgdata -U postgres
9、启动数据库
pg_ctl -D /pgdata start
10、验证是否可登录
psql

二、pg_rman的安装和应用

1、登录git下载源码包(注意这个与pg的版本要匹配哈，如果不匹配可能会有问题)
https://github.com/ossc-db/pg_rman/releases
2、解压
tar -xvf pg_rman-1.3.15-pg14.tar.gz
3、安装依赖包
yum install -y bison flex readline-devel zlib-deve1 zlib zlib-devel gcc
4、编译安装(需要先安装postgresql)
make && make install
5、新建备份目录
mkdir /pg_rman_backup/{fullbackup,walbackup,pg_srvlog} -p
chown -R postgres:postgres /pg_rman_backup
6、修改环境变量
vim etc/profile
export BACKUP_PATH=/pg_rman_backup/fullbackup
export ARCLOG_PATH=/pg_rman_backup/walbackup
export SRVLOG_PATH=/pg_rman_backup/pg_srvlog
7、postgresql开启归档模式，并重启pg
vim postgresql.conf
archive_mode = on
archive_command = 'DATE=`date +%Y%m%d`; DIR="/pg_rman_backup/walbackup/"; (test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f'

%p 表示xlog文件名$PGDATA的相对路径, 如pg_xlog/00000001000000190000007D

%f 表示xlog文件名, 如00000001000000190000007D

8、pg_rman初始化
pg_rman -B /pg_rman_backup/fullbackup -D /pgdata -A /pg_rman_backup/walbackup init
9、新建个表
postgres=# create database test;
CREATE DATABASE
postgres=# \d
Did not find any relations.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table test(id int);
CREATE TABLE
test=# insert into test values(1);
INSERT 0 1
test=# select * from test;
id
----
1
(1 row)

10、执行全备
[postgres@ops-dev-app ~]$ pg_rman backup --backup-mode=full --with-serverlog --progress
INFO: copying database files
Processed 1274 of 1274 files, skipped 0
INFO: copying archived WAL files
Processed 3 of 3 files, skipped 0
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.


11、增量备份
test=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |

test=# insert into test values(10);
INSERT 0 1
test=# select * from test;
id
----
1
10

[postgres@ops-dev-app ~]$ pg_rman backup --backup-mode incremental --progress --compress-data
INFO: copying database files
Processed 1274 of 1274 files, skipped 1244
INFO: copying archived WAL files
Processed 6 of 6 files, skipped 3
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.




11、备份校验


[postgres@ops-dev-app ~]$ pg_rman validate
INFO: validate: "2023-07-28 14:00:08" backup and archive log files by CRC
INFO: backup "2023-07-28 14:00:08" is valid



12、查看备份集
[postgres@ops-dev-app ~]$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2023-07-28 14:00:08 2023-07-28 14:00:10 INCR 33kB 1 OK
2023-07-28 09:54:24 2023-07-28 09:54:26 FULL 58MB 1 OK