PostgreSQL--入门
PG介绍
PostgresSQL，简称PG或PGSQL。C和C++开发的关系型数据库系统
支持多种操作系统平台
官网：
二进制安装
创建用户和组
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
有用的系统函数
浙公网安备 33010602011771号