PostgreSQL--入门

标签：00 wal PostgreSQL 入门 -- redhat79 pg archive postgres

# PostgreSQL--入门

PG介绍

PostgresSQL，简称PG或PGSQL。C和C++开发的关系型数据库系统

支持多种操作系统平台

官网：PostgreSQL: The world's most advanced open source database

二进制安装

创建用户和组

useradd -d /home/postgres -u 300 -U postgres
echo password |passwd --stdin postgres

安装依赖包

yum groupinstall "Development Tools" "Legacy UNIX Compatibility"
yum install bosin flex readline* zlib-devel gcc* gmake

解压软件

 tar zxf postgresql-15.1.tar.gz

创建目录并授权

mkdir -p /usr/local/pg15
mkdir -p /pgdata/15/data
chown -R postgres. /pgdata
chown -R postgres. /usr/local/pg15
chmod 700 /pgdata/15/data -R

系统参数调整

touch /etc/sysctl.d/99-pg.conf
vi /etc/sysctl.d/99-pg.conf
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

sysctl -p /etc/sysctl.d/99-pg.conf

vi /etc/security/limits.conf
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000

建议关闭numa，设置IO策略为deadline（机械）或者noop（SSD）

编译

cd /soft/postgresql-15.1
./configure --prefix=/usr/local/pg15 --with-pgport=1921
gmake world
gmake install-world

设置环境变量

su - postgres

vim .bash_profile
export PGDATA=/pgdata/15/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg15
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export DATE=`date "+%Y%m%d-%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres

source .bash_profile

[postgres@redhat79 ~]$ id
uid=300(postgres) gid=1001(postgres) groups=1001(postgres) 

[postgres@redhat79 ~]$ psql --version
psql (PostgreSQL) 15.1

初始化数据库

su - postgres
#简易初始化
initdb -D /pgdata/15/data -W
#生产建议
initdb -A md5 -D $PGDATA -E utf8 --locale=C -W

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /pgdata/15/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /pgdata/15/data -l logfile start

启动关闭

手动方式

pg_ctl -D /pgdata/15/data -l logfile start
pg_ctl -D /pgdata/15/data stop -ms   #quit after all clients have disconnected
pg_ctl -D /pgdata/15/data stop -mf   #quit directly, with proper shutdown (default)
pg_ctl -D /pgdata/15/data stop -mi   #quit without complete shutdown; will lead to recovery on restart
pg_ctl restart -mf

脚本方式

/soft/postgresql-15.1/contrib/start-scripts/linux

连接数据库

[postgres@redhat79 ~]$ pg_ctl -D /pgdata/15/data -l logfile start
waiting for server to start.... done
server started
[postgres@redhat79 ~]$ psql
Password for user postgres:
psql (15.1)
Type "help" for help.

#简单测试
postgres=# create database goldtree;
CREATE DATABASE
postgres=# create table t1 (id int);
CREATE TABLE
postgres=# insert into t1 values(1);
INSERT 0 1
postgres=# select * from t1;
 id
----
  1
(1 row)

postgres=# \c goldtree
You are now connected to database "goldtree" as user "postgres".

goldtree=# create table t1 (id int);
CREATE TABLE
goldtree=# insert into t1 values(100);
INSERT 0 1
goldtree=# insert into t1 values(101);
INSERT 0 1
goldtree=# select * from t1;
 id
-----
 100
 101
(2 rows)

goldtree=# \l
                                            List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
 goldtree  | postgres | UTF8     | C       | C     |            | libc            |
 postgres  | postgres | UTF8     | C       | C     |            | libc            |
 template0 | postgres | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |          |          |         |       |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     |            | libc            | =c/postgres          +
           |          |          |         |       |            |                 | postgres=CTc/postgres
(4 rows)

基础管理

连接管理

连接命令

[postgres@redhat79 ~]$ psql -d postgres -h 10.0.0.200 -p 1921 -U postgres -W
Password:
psql: error: connection to server at "10.0.0.200", port 1921 failed: Connection                                                        refused
        Is the server running on that host and accepting TCP/IP connections?

#刚装完数据库不允许这样登录，只允许本地登录。

PG访问控制

#pg_hba.conf文件是pg实例的访问控制文件
[postgres@redhat79 ~]$ cd $PGDATA

[postgres@redhat79 data]$ vim pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             10.0.0.200/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

#规则自上至下，匹配就停止。

#配置listener
[postgres@redhat79 data]$ vim postgresql.conf
# - Connection Settings -

listen_addresses = '0.0.0.0'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 1921                            # (change requires restart)


#修改前
[postgres@redhat79 data]$ netstat -lantp|grep postgres
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:1921          0.0.0.0:*               LISTEN      28600/postgres
tcp6       0      0 ::1:1921                :::*                    LISTEN      28600/postgres

#修改后
#重启数据库
[postgres@redhat79 data]$ pg_ctl restart -mf
[postgres@redhat79 data]$ netstat -lantp|grep postgres
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:1921            0.0.0.0:*               LISTEN      29664/postgres

#连接测试
[postgres@redhat79 ~]$ psql -d postgres -h 10.0.0.200 -p 1921 -U postgres -W
Password:
psql (15.1)
Type "help" for help.

#成功了

用户管理

用户功能

用来登录数据库实例，管理数据库对象

用户创建方法

create user #创建的用户可以连接数据库

create role #创建的角色不可以直接连接数据库

drop user xxxx #删除用户

alter user xxx #更新用户配置

\du #查看用户信息

例子

create user test1 with password 'test1';
create user admin with superuser login password 'admin';
create role test2 with login password 'test2' valid until '2022-12-30';
create role admin with superuser login password 'admin';
create user repl replication login encrypted password 'repl';

drop user test1;

alter user test1 with password '12345';


#查看帮助
.\help create user;

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test1     |                                                            | {}
 test2     | Password valid until 2022-12-30 00:00:00+08                | {}

权限管理

权限级别

cluster权限：实例权限通过pg_hba.conf配置

database权限：通过grant和revoke操作schema配置

TBS权限：通过grant和revoke操作表，物化视图，索引，临时表配置

schema权限：通过grant和revoke操作模式下的对象配置

object权限：通过grant和revoke配置

逻辑结构

权限定义

database权限设置

grant create on database goldtree to test1;

schema权限

alter schema abc owner to abc;

grant select,insert,update,delete on all tables in schema abc to abc

object权限

grant select,insert,update,delete on a.b to u;

常用基础命令介绍

\?

\l

\d

\d t1

\c goldtree

\help

\help create user

\du

\x

体系结构介绍

C/S结构

PG是C/S结构

体系结构概览

体系结构=实例+存储结构

实例=进程+内存结构

实例结构

进程结构

[postgres@redhat79 ~]$ ps -ef|grep postgres
root       2410   2360  0 09:21 pts/0    00:00:00 su - postgres
postgres   2411   2410  0 09:21 pts/0    00:00:00 -bash
postgres   2488      1  0 09:22 ?        00:00:00 /usr/local/pg15/bin/postgres -D /pgdata/15/data
postgres   2489   2488  0 09:22 ?        00:00:00 postgres: checkpointer
postgres   2490   2488  0 09:22 ?        00:00:00 postgres: background writer
postgres   2492   2488  0 09:22 ?        00:00:00 postgres: walwriter
postgres   2493   2488  0 09:22 ?        00:00:00 postgres: autovacuum launcher
postgres   2494   2488  0 09:22 ?        00:00:00 postgres: logical replication launcher
postgres   8994   2411  0 10:58 pts/0    00:00:00 ps -ef
postgres   8995   2411  0 10:58 pts/0    00:00:00 grep --color=auto postgres

进程说明

Postmaster进程，提供监听，连接协议，验证功能

Session Processors，会话进程，用户一旦验证成功就会fork一个新的进程

backgroud write 进程。主要负责内存数据页修改

sysloger进程主要负责数据库状态信息收集和统计

CKPT进程检查点进程

WALW进程 walwriter进程，wal（redo）日志管理

ARCH进程归档日志管理

内存结构

存储结构

重点文件介绍

日志文件种类

$PGDATA/log 运行日志

$PGDATA/pg_wal redo日志

$PGDATA/pg_xact 事务提交日志

服务器日志，可以在启动时指定，比如pg_ctl -l ./alter.log start

运行日志参数

csv日志入库存储

控制文件

pg_controldata $PGDATA可以查看控制文件的内容

数据文件

pg中，每个索引和表格都是一个单独的文件，pg中叫做page，默认是每个大于1G的page会被分割pg_class_relfilenode.1这样的文件，page的大小在initdb的时候指定（--with-segsize）

page的物理位置

page的物理位置在$PGDATA/BASE/DATABASE_OID/PG_CLASS_RELFILENODE

postgres=# select relfilenode from pg_class where relname='t1';
 relfilenode
-------------
       24577
(1 row)

postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath
----------------------
 base/5/24577
(1 row)

postgres=# show data_directory;
 data_directory
-----------------
 /pgdata/15/data
(1 row)

[postgres@redhat79 ~]$ ls -ltr $PGDATA/base/5/24577
-rw-------. 1 postgres postgres 8192 Nov 30 14:48 /pgdata/15/data/base/5/24577

需要注意的是，pg_class.relfilenode类似dba_objects.data_object_id,truncate表之后relfilenode会变。对应的物理文件名也会变

Online WAL日志

关于Online WAL日志

这个日志存在的目的是为了保证崩溃后的安全，如果系统崩溃，可以“重放”从最后一次检查点以来的日志锁来恢复数据库的一致性。

但是存在日志膨胀的问题

设置Online WAL日志

max_wal_size = 1GB

min_wal_size = 80MB

max_wal_size(integer)

在自动WAL检查点之间允许WAL增长到的最大尺寸。这是一个软限制，在特殊的情况下WAL尺寸可能会超过max_wal_size，例如在重度负载下，archive_command失败或者高的wal_keep_segments设置，如果指定值时没有设置单位，则以MB为单位。默认是1GB，增加这个参数可能导致崩溃恢复所需的时间。这个参数只能在postgresql.conf或者服务器命令中设置

min_wal_szie(integer)

只要WAL磁盘用量保持在这个设置下，在检查点对旧的WAL文件总是被回收以便未来使用，而不是直接被删除。这可以被用来确保有足够的WAL空间被保留来应对WAL使用的高峰，例如运行大型的批处理任务，如果指定值时没有单位，以MB字节为单位。默认是80MB。这个参数只能在postgresql.conf或者服务器命令行中设置。

wal位置

wal在$PGDATA/pg_wal下。

[postgres@redhat79 pg_wal]$ pwd
/pgdata/15/data/pg_wal
[postgres@redhat79 pg_wal]$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Dec  2 16:39 000000010000000000000001
drwx------. 2 postgres postgres        6 Nov 30 14:34 archive_status

wal命名格式

文件名称为16进制的24个字节组成，每8个字符一组，每组的意义如下：

00000001 00000000 00000001

时间线逻辑id 物理id

查看wal时间

postgres=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name
--------------------------
 000000010000000000000001
(1 row)

postgres=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification
--------------------------+----------+------------------------
 000000010000000000000001 | 16777216 | 2022-12-06 09:58:48+08
(1 row)

切换wal日志

postgres=# select pg_switch_wal();    #切换wal log
 pg_switch_wal
---------------
 0/19670D8
(1 row)

postgres=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification
--------------------------+----------+------------------------
 000000010000000000000001 | 16777216 | 2022-12-06 10:05:55+08
 000000010000000000000002 | 16777216 | 2022-12-06 10:05:55+08
(2 rows)

pg_waldump查看wal

pg_waldump可以查看wal的具体内容

ARCH WAL log

在生产环境，为了保证数据高可用性，通常需要设置归档，所谓的归档，其实就是把pg_wal里面的日志备份出来，当系统故障后可以通过归档的日志文件对数据进行恢复。配置归档需要开启如下参数：

wal_level = replica (pg11默认已开启replica)

该参数的可选的值有minimal,replica和logical，wal的级别依次增高，在wal的信息也越多，由于minimal这一级别的wal不包含从基础的备份和wal日志重建数据的足够信息，在该模式下无法开启wal日志归档。

archive_mode = on

上述参数为on，表示打开归档备份，可选的参数为on，off，always。默认值为off，所以要手动打开。

archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

该参数的默认值是一个空字符串，他的值可以是一条shell命令或者一个复杂的shell脚本，在shell脚本或命令中可以用%p表示将要归档的wal文件包含完整路径的信息的文件名，用%f代表不包含路径信息的wal文件的文件名。

注意：wal_level和archive_mode参数修改都需要重新启动数据库才能生效，修改archive_command也需要。所以一般配置新系统时，无论当时是否需要归档，都建议将这些参数开启。

参数配置

vim $PGDATA/postgresql.conf
# - Archiving -

archive_mode = on             # enables archiving; off, on, or always
                                # (change requires restart)
#archive_library = ''           # library to use to archive a logfile segment
                                # (empty string indicates archive_command should
                                # be used)
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'           # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables


#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

wal_level = replica                    # minimal, replica, or logical

重启数据库

pg_ctl restart -mf

插入数据查看归档

insert into t1 values (generate_series(1,1000));
select pg_switch_wal(); #切归档

备份恢复

需要备份什么

数据

WAL日志

归档日志

备份方式

逻辑导出备份

pg_dump

pg_dumpall

#逻辑导出测试：
[postgres@redhat79 ~]$ pg_dump -d postgres >/tmp/postgres.sql
Password:
[postgres@redhat79 ~]$

#导出的文件
[postgres@redhat79 tmp]$ ls -l postgres.sql
-rw-rw-r--. 1 postgres postgres 4727 Dec  7 10:31 postgres.sql

#文件内容
--
-- PostgreSQL database dump
--

-- Dumped from database version 15.1
-- Dumped by pg_dump version 15.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;
--
-- Name: t1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t1 (
    id integer
);


ALTER TABLE public.t1 OWNER TO postgres;

--
-- Data for Name: t1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t1 (id) FROM stdin;
1
200
1
........

#恢复测试
[postgres@redhat79 tmp]$ psql </tmp/postgres.sql
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
2022-12-07 10:34:35.458 CST [2923] ERROR:  relation "t1" already exists
2022-12-07 10:34:35.458 CST [2923] STATEMENT:  CREATE TABLE public.t1 (
            id integer
        );
ERROR:  relation "t1" already exists    #原来的表还在，冲突了
ALTER TABLE
COPY 1002

#删除t1表，再测试
[postgres@redhat79 tmp]$ psql
Password for user postgres:
psql (15.1)
Type "help" for help.

postgres=# drop table t1;
DROP TABLE

[postgres@redhat79 tmp]$ psql </tmp/postgres.sql
Password for user postgres:
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 1002
#恢复成功

物理备份

pg_basebackup #可实现热备功能

pg_basebackup物理备份工具应用

备份

[postgres@redhat79 tmp]$pg_basebackup -D /pg_backup -Ft -Pv -Upostgres -h 10.0.0.200 -p 1921 -R

Ft 格式是tar包

Pv 显示详细过程

-D 目的目录

-U 用户

-h 服务器IP

-p 服务端口

-R

#测试
[postgres@redhat79 pg_backup]$ pwd
/pg_backup
[postgres@redhat79 pg_backup]$ ll
total 0

#开始备份
[postgres@redhat79 pg_backup]$ pg_basebackup -D /pg_backup -Ft -Pv -Upostgres -h 10.0.0.200 -p 1921 -R
2022-12-07 10:45:35.182 CST [3071] FATAL:  no pg_hba.conf entry for replication connection from host "10.0.0.200", user "postgres", no encryption

#有报错，解决一下
#修改pg_hba.conf
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5 
host    replication     all             0.0.0.0/0                 md5  #添加

#重启PG生效
[postgres@redhat79 pg_backup]$ pg_ctl restart

#测试备份
[postgres@redhat79 pg_backup]$ pg_basebackup -D /pg_backup -Ft -Pv -Upostgres -h 10.0.0.200 -p 1921 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
2022-12-07 10:54:40.394 CST [3195] LOG:  checkpoint starting: force wait
2022-12-07 10:54:40.446 CST [3195] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.001 s, sync=0.003 s, total=0.053 s; sync files=2, longest=0.002 s, average=0.002 s; distance=16384 kB, estimate=16384 kB
pg_basebackup: checkpoint completed                                     #启动一个检查点，把脏数据写到磁盘
pg_basebackup: write-ahead log start point: 0/10000060 on timeline 1    #记录wal log起始点
pg_basebackup: starting background WAL receiver                         #启动后台Wal接受进程
pg_basebackup: created temporary replication slot "pg_basebackup_3216"  #创建临时复制文件
30538/30538 kB (100%), 1/1 tablespace                                   #备份内容
pg_basebackup: write-ahead log end point: 0/10000138                    #记录wal log结束点
pg_basebackup: waiting for background process to finish streaming ...   
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

#看看备份出来的文件
[postgres@redhat79 pg_backup]$ ls -l
total 47108
-rw-------. 1 postgres postgres   181243 Dec  7 10:54 backup_manifest
-rw-------. 1 postgres postgres 31271424 Dec  7 10:54 base.tar
-rw-------. 1 postgres postgres 16778752 Dec  7 10:54 pg_wal.tar

恢复测试

#模拟数据库损坏
#删除数据库数据文件
[postgres@redhat79 pg_backup]$ pg_ctl stop -mi
[postgres@redhat79 pg_backup]$ rm -rf $PGDATA/
[postgres@redhat79 pg_backup]$ ls -l $PGDATA/
ls: cannot access /pgdata/15/data/: No such file or directory
[postgres@redhat79 pg_backup]$ cd  $PGDATA/
-bash: cd: /pgdata/15/data/: No such file or directory

#删除归档文件  
[postgres@redhat79 pg_backup]$ cd /archive/
[postgres@redhat79 archive]$ ls
000000010000000000000004  000000010000000000000008  00000001000000000000000C  000000010000000000000010
000000010000000000000005  000000010000000000000009  00000001000000000000000D  000000010000000000000010.00000060.backup
000000010000000000000006  00000001000000000000000A  00000001000000000000000E
000000010000000000000007  00000001000000000000000B  00000001000000000000000F
[postgres@redhat79 archive]$ rm -rf *
[postgres@redhat79 archive]$ ls

#重建$PGDATA
[postgres@redhat79 pg_backup]$ mkdir -p /pgdata/15/data

#恢复数据
#恢复数据文件
[postgres@redhat79 archive]$ cd /pg_backup/
[postgres@redhat79 pg_backup]$ ls
backup_manifest  base.tar  pg_wal.tar
[postgres@redhat79 pg_backup]$ tar -xf base.tar -C $PGDATA/
[postgres@redhat79 pg_backup]$ tar -xf pg_wal.tar -C /archive/

#检查数据文件和日志
[postgres@redhat79 pg_backup]$ cd /archive/
[postgres@redhat79 archive]$ ls
000000010000000000000010
[postgres@redhat79 archive]$ ll
total 16384
-rw-------. 1 postgres postgres 16777216 Dec  7 10:54 000000010000000000000010
[postgres@redhat79 archive]$ cd $PGDATA
[postgres@redhat79 data]$ pwd
/pgdata/15/data
[postgres@redhat79 data]$ ls
backup_label  pg_hba.conf    pg_replslot   pg_subtrans  pg_xact
base          pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf
global        pg_logical     pg_snapshots  pg_twophase  postgresql.conf
pg_commit_ts  pg_multixact   pg_stat       PG_VERSION   standby.signal
pg_dynshmem   pg_notify      pg_stat_tmp   pg_wal       tablespace_map
#数据文件恢复回来了

#修改配置文件
-rw-------. 1 postgres postgres   330 Dec  7 10:54 postgresql.auto.conf
-rw-------. 1 postgres postgres 29453 Dec  6 11:03 postgresql.conf

vim postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres password=password channel_binding=disable host=10.0.0.200 port=1921 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
restore_command = 'cp /archive/%f %p'    #添加
recovery_target = 'immediate'            #添加

touch $PGDATA/recovery.signal    #记录恢复过程

#启动PG

[postgres@redhat79 ~]$ pg_ctl start
waiting for server to start....2022-12-08 09:46:24.365 CST [2448] LOG:  starting PostgreSQL 15.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2022-12-08 09:46:24.366 CST [2448] LOG:  listening on IPv4 address "0.0.0.0", port 1921
2022-12-08 09:46:24.369 CST [2448] LOG:  listening on Unix socket "/tmp/.s.PGSQL.1921"
2022-12-08 09:46:24.505 CST [2451] LOG:  database system was shut down in recovery at 2022-12-07 17:28:44 CST
cp: cannot stat ‘/archive/00000002.history’: No such file or directory
2022-12-08 09:46:24.516 CST [2451] LOG:  entering standby mode
2022-12-08 09:46:24.962 CST [2451] LOG:  restored log file "000000010000000000000010" from archive
.2022-12-08 09:46:25.487 CST [2451] LOG:  redo starts at 0/10000060
cp: cannot stat ‘/archive/000000010000000000000011’: No such file or directory   #crash生成了一个新的log，但是备份里面没有
2022-12-08 09:46:25.504 CST [2451] LOG:  consistent recovery state reached at 0/10000138
2022-12-08 09:46:25.504 CST [2451] LOG:  recovery stopping after reaching consistency
2022-12-08 09:46:25.504 CST [2451] LOG:  pausing at the end of recovery
2022-12-08 09:46:25.504 CST [2451] HINT:  Execute pg_wal_replay_resume() to promote.
2022-12-08 09:46:25.504 CST [2448] LOG:  database system is ready to accept read-only connections
 done
server started

#连接DB 执行select pg_wal_replay_resume();停止归档重放；
#DB恢复完成

PITR实战应用

场景介绍

每天23：00 全备份，周二下午14：00误删除数据，如何恢复？

1 恢复全备数据

2 归档恢复，备份归档 + 23：00--14：00区间的归档 + 在线wal

实验准备

postgres=# create table pit(id int);
CREATE TABLE
postgres=# insert into pit values(1);
INSERT 0 1
postgres=# insert into pit values(11);
INSERT 0 1
postgres=# insert into pit values(111);
INSERT 0 1
postgres=# select * from pit;
 id
-----
   1
  11
 111
(3 rows)

数据备份

[postgres@redhat79 pg_backup]$ pg_basebackup -D /pg_backup -Ft -Pv -Upostgres -h 10.0.0.200 -p 1921 -R

数据恢复

恢复全备
[postgres@redhat79 pg_backup]$ tar -xf base.tar -C $PGDATA/
[postgres@redhat79 pg_backup]$ tar -xf pg_wal.tar -C /archive/




vim postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
#primary_conninfo = 'user=postgres password=password channel_binding=disable host=10.0.0.200 port=1921 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
restore_command = 'cp /archive/%f %p'    #添加
recovery_target_xid = 'xxx'            #添加 id 通过pg_waldump查看

#[postgres@redhat79 archive]$ pg_waldump 000000010000000000000003

touch $PGDATA/recovery.signal    #记录恢复过程

#启动PG

[postgres@redhat79 ~]$ pg_ctl start

postgres=# select pg_wal_replay_resume(); #结束恢复


注意：
危险操作前，可以先自己创建一个保存点
select pg_create_restore_point('before-delete-pit');

备份恢复步骤

备份步骤

select pg_switch_wal();
cd /pg_backup && rm -rf *
[postgres@redhat79 pg_backup]$ pg_basebackup -D /pg_backup -Ft -Pv -Upostgres -h 10.0.0.200 -p 1921 -R

维护操作前先打个tag

select pg_create_restore_point('before-delete-pit');

恢复操作

cd $PGDATA && rm -rf *
tar -xf base.tar -C $PGDATA/
tar -xf pg_wal.tar -C /archive/
cd $PGDATA && vim postgresql.auto.conf
添加 restore_command = 'cp /archive/%f %p' 和 recovery_target_name = 'before-delete-pit'
touch $PGDATA/recovery.signal
启动PG pg_ctl start
postgres=# select pg_wal_replay_resume();

流复制

基础环境多实例

角色	IP	port
master	10.0.0.200	1921
standby	10.0.0.31	1921

设置配置文件

Master节点

创建数据库账户

postgres=# create role replica with replication login password 'password';
CREATE ROLE
postgres=# alter user replica with password 'password';
ALTER ROLE

修改pg_hba.conf

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             0.0.0.0/0            md5
host    replication     replica             0.0.0.0/0            md5 #添加
host    replication     all             ::1/128                 md5

修改配置 postgressql.conf

wal_level = replica # 这个设置主为wal得主机
max_wal_senders = 5 # 这个设置了可以最多有几个流复制连接
max_replication_slots = 128 # 设置流复制保留得最多得xlog数目
wal_sender_timeout = 60s #设置流复制主机发送数据得超时时间
max_connections = 200 #一般读多于写的应用从库的最大连接数要比较大
hot_standby = on # 说明这台服务器不仅仅用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主库报告一次从库的状态
hot_standby_feedback = on #如果有错误的数据库复制，是否向主库进行反馈
wal_log_hints = on #

standby 节点

清空数据和归档

su - postgres
rm -rf $PGDATA
rm -rf /archive/*

备份主数据库到备份库

#从库
pg_basebackup -D /pg_backup -Ft -Pv -Upostgres -h 10.0.0.200 -p 1921 -R
tar -xf base.tar -C $PGDATA/
tar -xf pg_wal.tar -C /archive/

修改参数

#从库
cd $PGDATA
vim standby.signal
standby_mode = 'on'

vim postgresql.conf
primary_conninfo = 'host=10.0.0.200 port=1921 user=replica password=password'
recovery_target_timeline = 'latest'
max_connections = 200
hot_standby = on 
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s # 多久向主库报告一次从库的状态
hot_standby_feedback = on
max_wal_senders = 15
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'


vim postgresql.auto.conf
restore_command = 'cp /archive/%f %p'
primary_conninfo = 'user=postgres password=password channel_binding=disable host=10.0.0.200 port=1921 sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any'


启动PG

监控状态

#主库
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
 pid  |   state   | client_addr | sync_priority | sync_state
------+-----------+-------------+---------------+------------
 4364 | streaming | 10.0.0.31   |             0 | async
(1 row)



#从库
[postgres@redhat86 data]$ psql -c "\x" -c "select * from pg_stat_wal_receiver;"
Password for user postgres:
Expanded display is on.
-[ RECORD 1 ]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 65521
status                | streaming
receive_start_lsn     | 0/6000000
receive_start_tli     | 1
written_lsn           | 0/6000060
flushed_lsn           | 0/6000060
received_tli          | 1
last_msg_send_time    | 2022-12-12 15:52:21.792004+08
last_msg_receipt_time | 2022-12-12 15:52:22.144633+08
latest_end_lsn        | 0/6000060
latest_end_time       | 2022-12-12 15:51:51.700459+08
slot_name             |
sender_host           | 10.0.0.200
sender_port           | 1921
conninfo              | user=postgres password=******** channel_binding=disable dbname=replication host=10.0.0.200 port=1921 fallback_application_name=walreceiver sslmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres target_session_attrs=any

有用的系统函数

标签：00,wal,PostgreSQL,入门,--,redhat79,pg,archive,postgres
From： https://www.cnblogs.com/goldtree358/p/16978546.html