ljzsdut
GitHubToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeToggle Dark/Light/Auto modeBack to homepage
  1. Welcome to the ljzsdut's notes
  2. /
  3. 数据库系列
  4. /
  5. Postgresql
  6. /
  7. 01 Postgresql基础入门
Edit page

01 Postgresql基础入门

安装

参考文档：https://www.postgresql.org/download/linux/redhat/

# Install the repository RPM:
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
#yum install -y postgresql10-server
yum install -y postgresql10-server-10.10  #指定具体的  版本

# Optionally initialize the database and enable automatic start:
#修改数据目录
sed -i 's#Environment=PGDATA=/var/lib/pgsql/10/data/#Environment=PGDATA=/u01/service/pgsql/10/data/#g' /usr/lib/systemd/system/postgresql-10.service

# 初始化PostgreSQL
/usr/pgsql-10/bin/postgresql-10-setup initdb

#修改配置文件：监听地址(listen_addresses)
vim /u01/service/pgsql/10/data/postgresql.conf
# 修改可访问的用户ip段(重启生效)
vim /u01/service/pgsql/10/data/pg_hba.conf
#host    all             all             172.22.22.0/24           md5
#host    all             all             10.15.9.0/24             md5

systemctl enable postgresql-10
systemctl start postgresql-10

配置参数

image-20211223080744443

启动与关闭

https://blog.csdn.net/gguxxing008/article/details/83056075

psql客户端

PostgreSQL连接数据库的两种方式

PostgreSQL在安装时自带了pgAdmin和psql，pgAdmin是可视化工具，psql是命令行工具。

虽然pgAdmin操作起来会更加直观简单，但是在restore和backup db的时候，效率和性能会比较低下，如果db过于庞大，还会导致pgAdmin内存溢出。

推荐使用psql来连接数据库进行备份和恢复db，同样大小的db，使用psql来restore会比pgAdmin快上数倍！

psql连接数据库

直接连接到指定的数据库(☆) 

psql -h <dbserver_IP> -p <dbserver_port> [-d] <database_Name> -U <user>

说明：

  • -h <dbserver_IP> ：指定数据库连接地址，默认为localhost（默认使用socket文件）。客户端的连接限制在pg_hba.conf配置，详见：https://blog.csdn.net/yaoqiancuo3276/article/details/80404883

  • -p <dbserver_port> ：指定端口号，默认为5432

  • -d <database_Name> ： 指定登录后连接的数据库，可以省略-d关键字，直接使用<database_Name>。缺省值同<user>。psql登录时，必须指定一个已经存在的数据库。如果不知道有哪些库，可以指定默认创建的数据库postgres或template1。如果指定的库不存在，则会报错psql: FATAL: database "root" does not exist

  • -U <user> ：指定用户名。默认用户postgres。psql命令存在简写形式，默认为操作系统的当前用户 (linux)。例如如果当前Linux系统用户，同时也是PostgreSQL用户，则可以省略用户名(-U参数的部分)。举例来说，我的Linux系统用户名为ruanyf，且PostgreSQL数据库存在同名用户，则我以ruanyf身份登录Linux系统后，可以直接使用下面的命令登录数据库，且不需要密码。PostgreSQL数据默认会创建一个postgres的数据库用户作为数据库的管理员，密码是随机的

  • PGPASSWORD=<PWD> ：如何指定密码？ 通过声明环境变量的方式，例如PGPASSWORD=<PWD> psql -h <dbserver_IP> -p <dbserver_port> -d <database_Name> -U <db user> 如果不指定PGPASSWORD环境变量，会通过提示符提示让你输入密码。PGPASSWORD=UPK0k3d psql -h postgrepsql-stolon-proxy.common -p 5432 -U root -d postgres

  • -c "COMMAND" : 非交互式操作数据库

先登陆psql控制台，再连接指定的数据库

psql命令也可以不指定某个数据库，此时，连接的数据库默认与user名称相同。如下：

psql -h <dbserver_IP> -p <dbserver_port> -U <db user>

这时候登陆成功后会进入psql的命令台，此时可以跑一些数据库备份、创建数据库或者连接数据库之类的操作。

在psql的命令台输入\c <database_Name>，接着按下回车键，即可连接到对应的数据库，如下：

postgres=# \c cbx6_dev
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
You are now connected to database "cbx6_dev" as user "postgres".
cbx6_dev=#

怎么切换到不同的数据库

如果已经连接到一个数据库了，这时候想切换到另一个数据库怎么办？很简单，还是跑这个\c的命令即可。

\c DBNAME

退出psql控制台 

\q

基础使用

首次登录使用操作系统用户认证，登录命令行客户端： 

[root@vm-3-39 ~]# su - postgres
-bash-4.2$ psql
psql (10.10)
Type "help" for help.

postgres=#

查看帮助 

postgres=# \h create user 
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

postgres=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [FILE] or ;         execute query (and send results to file or |pipe)
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store results in psql variables
  \gx [FILE]             as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds

Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands

Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \s [FILE]              display history or save it to file
  \w FILE                write query buffer to file

Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [STRING]         write string to standard output
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [STRING]        write string to query output stream (see \o)

Conditional
  \if EXPR               begin conditional block
  \elif EXPR             alternative within current conditional block
  \else                  final alternative within current conditional block
  \endif                 end conditional block

Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \dA[+]  [PATTERN]      list access methods
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \dD[S+] [PATTERN]      list domains
  \ddp    [PATTERN]      list default privileges
  \dE[S+] [PATTERN]      list foreign tables
  \det[+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[S+] [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl                    list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S]  [PATTERN]      list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \drds [PATRN1 [PATRN2]] list per-database role settings
  \dRp[+] [PATTERN]      list replication publications
  \dRs[+] [PATTERN]      list replication subscriptions
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[S+] [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dx[+]  [PATTERN]      list extensions
  \dy     [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+]  FUNCNAME       show a function's definition
  \sv[+]  VIEWNAME       show a view's definition
  \z      [PATTERN]      same as \dp

Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset [NAME [VALUE]]   set table output option
                         (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|
                         footer|format|linestyle|null|numericlocale|pager|
                         pager_min_lines|recordsep|recordsep_zero|tableattr|title|
                         tuples_only|unicode_border_linestyle|
                         unicode_column_linestyle|unicode_header_linestyle})
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)

Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "postgres")
  \conninfo              display information about current connection
  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user

Operating System
  \cd [DIR]              change the current working directory
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell

Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

Large Objects
  \lo_export LOBOID FILE
  \lo_import FILE [COMMENT]
  \lo_list
  \lo_unlink LOBOID      large object operations

创建用户(或者说是role) 

#创建超级管理员root用户

postgres=# create role root superuser encrypted password '123456' login replication createdb createrole;
CREATE ROLE

# 查看role
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser, Create role, Create DB, Replication             | {}


# 创建普通用户
  • superuser #超级管理员权限
  • login # 登录权限
  • replication #复制权限
  • createdb #创建数据库权限
  • createrole #创建角色权限
  • encrypted password ‘123456’ #设置用户密码

创建数据库

创建testdb数据库，并设置root数据库属于root用户：

# 创建数据库
postgres=# create database testdb owner root;
CREATE DATABASE

查看数据库 

#查看数据库
postgres=# \d

查看表结构 

#查看表结构：desc
postgres=# \d user

查看表 

# 查看数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 root      | root     | 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
(4 rows)

切换数据库： 

\c DBNAME

查看当前连接的信息（connetion用户、数据库） 

\c

逻辑备份还原

pg_dump备份

逻辑备份，导出为sql文件。只能备份单个数据库，不能导出role和表空间相关信息

-F t：备份为二进制格式(tar)，压缩存储，对于非文本备份需要使用pg_restore进行恢复，并且可被 pg_ restore 用于精细还原(选择性还原)

-F p：备份为文本(plain text)，大库不推荐。默认值

# 格式：pg_dump [OPTION]... [DBNAME]
pg_dump DBANEM > backup.sql # 重定向可以使用-f参数指定文件代替
pg_dump DBANEM | gzip backup.sql 

#以上语句使用了默认参数，等同于
pg_dump -F p -f backup.sql -C -E UTF8 -h 127.0.0.1 -U postgres DBANEM

#并行备份
pg_dump -Fd. -j4 -f testdb.p.dump testsb

psql恢复 

# 1. 创建数据库
create database  DBANEM;

# 2. 导入
psql DBANEM < backup.sql

toc选择性恢复 

# 备份
pg_dump -F t -f testdb.dmp -C -E UTF8  -h 1270.0.0.1 -U postgres testdb

# 根据二进制备份文件生成toc文件
pg_release -l -f testdb.toc testdb.dmp

# 修改toc文件，用‘;’号注释掉不用还原的内容：
;202; 1259 73746 TABLE public t1 postgres
203; 1259 73749 TABLE public t2 postgres
;3034; 0 73746 TABLE DATA public t1 postgres
3035; 0 73749 TABLE DATA public t2 postgres

# 以toc文件列表做恢复
pg_restore -F t -L testdb.toc -d testdb testdb.dmp  #会发现屏蔽的表没有被恢复

pg_dumpall备份

可以备份所有的数据库，包含角色、表空间，而且恢复不需要事先建库；

pg_dumpall > all.sql
psql < all.sql

物理备份（热备）

物理热备需要开启归档日志。

开启归档日志

为持久保存日志文件，需要开启日志归档模式。 在该模式下，可丢弃日志文件被删除前，被拷贝到指定目录。在postgres.conf配置文件中设置三个参数:

wal_level=replica
archive_ mode=on
archive_command='cp %p /home/postgres/arch/%f'

%p表示pg wal的目录路径和日志文件名
%f 表示日志文件名。
日志被拷贝到/home/postgres/arch/目录下

或者通过命令来设置 ：
aleter system set wal_level=replica;
aleter system set archive_ mode=on;
aleter system set archive_command='cp %p /home/postgres/arch/%f';

pg_basebackup -D backupdir -Fp 

pg_basebackup -Ft -Pv -Xf -z -Z5 -D /path/to/backupdir

使用pg_basebackup进行数据库热备份与恢复

该备份工具无需执行pg_start_backup()和pg_stop_backup()函数，而且备份素服和数据都比手动的备份快

  • 备份：产生压缩的tar包，-Ft参数指定：
pg_basebackup -D bk1 -Ft -z -P  #此备份花费时间比较长，但是节省空间
  • 备份：产生与源文件一样的格式，即原样格式，-Fp参数指定：
pg_basebackup -D bk2 -Fp -P  #此备份方式很快，但是不节省空间
  • 恢复：

1、模拟：主机断电，导致数据库损坏

rm -rf $PGDATA/*

2、使用tar包恢复数据：

tar xf bk1/base.tar.gz -C /usr/local/pg12.0/data
或
cp -rf bk2/* $PGDATA

3、在postgres.conf文件中添加如下2行：

restore_command = 'cp /home/postgres/arch/%f %p'
recovery_target_timeline = 'latest'

4、启动数据库

pg_ctl start

用户操作

https://www.cnblogs.com/zhoujinyi/p/10939715.html?ivk_sa=1024320u

#创建用户
create user user1 with password 'PASSWORD';

#修改用户密码
alter user user1 with password 'password';

#数据库授权：授予指定用户指定数据库的权限
grant all privileges on database mydb to user1;
#但此时用户还是对表读写的权限，需要继续授权。注意需要先切换至指定数据库上进行表授权操作
\c mydb
grant all privileges on all tables in schema public to user1;
#grant select,update,insert on all tables in schema public to test;

#回收权限
revoke all privileges on database mydb from user1;
\c mydb
revoke all privileges on all tables in schema public from user1;

# 删除用户
drop user user1;

# 查看用户
\du

数据库权限：

GRANT create ON DATABASE mydb TO myuser;

schema权限：

-- 测试环境
create database "rcyj";
create user blockchain with password 'Oq22j0LR58';
create schema if not exists public;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO blockchain;

ALTER SCHEMA myschema OWNER TO blockchain;
GRANT select,update,insert,delete ON ALL TABLES IN SCHEMA myschema TO blockchain;

对象权限(表、视图等)：

GRANT select,update,insert,delete ON public.user TO blockchain;

查看帮助 

\? #查看控制台帮助
\h #查看SQL 帮助
\h create user  #查看具体的帮助


\l          #查看数据库列表
\d 或 \dt   #查看当前库的table列表
\d t1       #describe
\du         #查看用户列表
\c          #切换数据库
\x          #列显示，相当于mysql中的\G

日志

日志种类

  • $PGDATA/log：运行日志（pg10之前为$PGDATA/pg_log）
  • $PGDATA/pg_wal：重做日志（pg10之前为$PGDATA/pg_xlog）ß
  • $PGDATA/pg_xact：事务提交日志（pg10之前为$PGDATA/pg_clog）
  • 服务器日志，可以在启动的时候指定，比如pg_ctl start -l ./alert.log

运行日志参数

image-20211223075333804

控制文件

控制文件中记录了数据库的一些元数据信息。可以通过pg_controldata $PGDATA查看控制文件的内容。

root@harbor-postgresql-stolon-keeper-0:/# pg_controldata /stolon-data/postgres
pg_control version number:            1002
Catalog version number:               201707211
Database system identifier:           6830771696093171762
Database cluster state:               in archive recovery
pg_control last modified:             Wed 22 Dec 2021 05:05:16 PM UTC
Latest checkpoint location:           10/3A711C28
Prior checkpoint location:            10/350BC518
Latest checkpoint's REDO location:    10/3A711BF0
Latest checkpoint's REDO WAL file:    00000019000000100000003A
Latest checkpoint's TimeLineID:       25
Latest checkpoint's PrevTimeLineID:   25
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:54738525
Latest checkpoint's NextOID:          176207
Latest checkpoint's NextMultiXactId:  1056344
Latest checkpoint's NextMultiOffset:  2113963
Latest checkpoint's oldestXID:        549
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  54738525
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Wed 22 Dec 2021 03:31:55 PM UTC
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     10/3ADBCA60
Min recovery ending loc's timeline:   25
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              600
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            25f1e985f7e3550cd25634b4aa6fcdfe55529331519b2afd61a2124f2d37f331

image-20211223081748571

image-20211223081805623

数据文件

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

page物理位置

page的物理位置在$PADATA/BASE/DATABASE_OID/PG_CLASS.RELFILENODE

csf=# select relfilenode from pg_class where relname='csf_user';
 relfilenode
-------------
     1083454
(1 row)

csf=# select pg_relation_filepath('csf_user');
 pg_relation_filepath
----------------------
 base/16388/1083454
(1 row)

csf=# show data_directory;
    data_directory
-----------------------
 /stolon-data/postgres
(1 row)

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

Online WAL日志(redo)

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

设置Online WAL日志

pg提供如下参数控制wal旧志的大小

max_wal_size 1GB
min_wal_size 80MB

  • max_wal_size(integer)

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

  • min_wal size(integer)

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

wal位置

wal在$PGDATA/pg_wal下。10之前为pg_xlog

root@common-postgresql-stolon-keeper-0:/# cd /stolon-data/postgres

root@common-postgresql-stolon-keeper-0:/stolon-data/postgres# ls -l pg_wal/
total 540680
-rw------- 1 stolon stolon 16777216 Dec 22 09:41 000000450000003A000000EF
-rw------- 1 stolon stolon 16777216 Dec 22 10:13 000000450000003A000000F0
-rw------- 1 stolon stolon 16777216 Dec 22 10:28 000000450000003A000000F1
-rw------- 1 stolon stolon 16777216 Dec 22 10:29 000000450000003A000000F2
-rw------- 1 stolon stolon 16777216 Dec 22 10:30 000000450000003A000000F3
-rw------- 1 stolon stolon 16777216 Dec 22 10:31 000000450000003A000000F4
-rw------- 1 stolon stolon 16777216 Dec 22 10:33 000000450000003A000000F5
-rw------- 1 stolon stolon 16777216 Dec 22 10:35 000000450000003A000000F6
-rw------- 1 stolon stolon 16777216 Dec 22 10:57 000000450000003A000000F7
-rw------- 1 stolon stolon 16777216 Dec 22 12:22 000000450000003A000000F8
-rw------- 1 stolon stolon 16777216 Dec 22 13:53 000000450000003A000000F9
-rw------- 1 stolon stolon 16777216 Dec 22 15:36 000000450000003A000000FA
-rw------- 1 stolon stolon 16777216 Dec 22 17:09 000000450000003A000000FB
-rw------- 1 stolon stolon 16777216 Dec 22 18:44 000000450000003A000000FC
-rw------- 1 stolon stolon 16777216 Dec 22 20:16 000000450000003A000000FD
-rw------- 1 stolon stolon 16777216 Dec 22 21:41 000000450000003A000000FE
-rw------- 1 stolon stolon 16777216 Dec 22 23:04 000000450000003A000000FF
-rw------- 1 stolon stolon 16777216 Dec 23 00:36 000000450000003B00000000
-rw------- 1 stolon stolon 16777216 Dec 23 00:37 000000450000003B00000001
-rw------- 1 stolon stolon 16777216 Dec 21 21:11 000000450000003B00000002
-rw------- 1 stolon stolon 16777216 Dec 22 08:45 000000450000003B00000003
-rw------- 1 stolon stolon 16777216 Dec 22 03:18 000000450000003B00000004
-rw------- 1 stolon stolon 16777216 Dec 22 00:13 000000450000003B00000005
-rw------- 1 stolon stolon 16777216 Dec 22 06:51 000000450000003B00000006
-rw------- 1 stolon stolon 16777216 Dec 22 08:03 000000450000003B00000007
-rw------- 1 stolon stolon 16777216 Dec 22 01:42 000000450000003B00000008
-rw------- 1 stolon stolon 16777216 Dec 22 08:21 000000450000003B00000009
-rw------- 1 stolon stolon 16777216 Dec 21 22:34 000000450000003B0000000A
-rw------- 1 stolon stolon 16777216 Dec 22 07:41 000000450000003B0000000B
-rw------- 1 stolon stolon 16777216 Dec 22 08:15 000000450000003B0000000C
-rw------- 1 stolon stolon 16777216 Dec 22 06:30 000000450000003B0000000D
-rw------- 1 stolon stolon 16777216 Dec 22 04:59 000000450000003B0000000E
-rw------- 1 stolon stolon 16777216 Dec 22 09:20 000000450000003B0000000F
-rw------- 1 stolon stolon     2997 Dec 17 09:31 00000045.history
drwx------ 2 stolon stolon     4096 Dec 23 00:36 archive_status

Wal命名格式

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

00000001 00000000 00000001
时间线    逻辑id    物理id

查看wal时间 

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

postgres=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification
--------------------------+----------+------------------------
 00000044.history         |     2952 | 2021-12-13 17:50:30+00
 00000045.history         |     2997 | 2021-12-17 09:30:52+00
 000000450000003B00000007 | 16777216 | 2021-12-21 22:34:41+00
 000000450000003B0000000A | 16777216 | 2021-12-22 00:13:29+00
 000000450000003B0000000E | 16777216 | 2021-12-22 01:42:41+00
 000000450000003B00000003 | 16777216 | 2021-12-22 03:18:28+00
 000000450000003B0000000D | 16777216 | 2021-12-22 04:59:57+00
 000000450000003B00000006 | 16777216 | 2021-12-22 06:30:52+00
 000000450000003B00000009 | 16777216 | 2021-12-22 06:51:58+00
 000000450000003B00000002 | 16777216 | 2021-12-22 07:41:34+00
 000000450000003B00000008 | 16777216 | 2021-12-22 08:03:24+00
 000000450000003B00000004 | 16777216 | 2021-12-22 08:15:33+00
 000000450000003B00000005 | 16777216 | 2021-12-22 08:21:47+00
 000000450000003B0000000C | 16777216 | 2021-12-22 08:45:18+00
 000000450000003B0000000B | 16777216 | 2021-12-22 09:20:41+00
 000000450000003A000000EF | 16777216 | 2021-12-22 09:41:24+00
 000000450000003A000000F0 | 16777216 | 2021-12-22 10:13:41+00
 000000450000003A000000F1 | 16777216 | 2021-12-22 10:28:56+00
 000000450000003A000000F2 | 16777216 | 2021-12-22 10:29:31+00
 000000450000003A000000F3 | 16777216 | 2021-12-22 10:30:31+00
 000000450000003A000000F4 | 16777216 | 2021-12-22 10:31:57+00
 000000450000003A000000F5 | 16777216 | 2021-12-22 10:33:23+00
 000000450000003A000000F6 | 16777216 | 2021-12-22 10:35:53+00
 000000450000003A000000F7 | 16777216 | 2021-12-22 10:57:56+00
 000000450000003A000000F8 | 16777216 | 2021-12-22 12:22:21+00
 000000450000003A000000F9 | 16777216 | 2021-12-22 13:53:36+00
 000000450000003A000000FA | 16777216 | 2021-12-22 15:36:19+00
 000000450000003A000000FB | 16777216 | 2021-12-22 17:09:02+00
 000000450000003A000000FC | 16777216 | 2021-12-22 18:44:02+00
 000000450000003A000000FD | 16777216 | 2021-12-22 20:16:19+00
 000000450000003A000000FE | 16777216 | 2021-12-22 21:41:09+00
 000000450000003A000000FF | 16777216 | 2021-12-22 23:04:05+00
 000000450000003B00000000 | 16777216 | 2021-12-23 00:36:39+00
 000000450000003B00000001 | 16777216 | 2021-12-23 00:40:37+00
(34 rows)

切换wal 

postgres=# select pg_switch_wal();

pg_waldump可以查看wal具体内容。

arch wal日志

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

  • wal_level=replica（pg11+默认已经开启replica，建议值） 该参数的可选的值有minimal，replica和logical，wal的级别依次增高，在wal的信息也越多。由于minimali这一级别的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”表示将要归档的wa|文件包含完整路径的信息的文件名，用“%”代表不包含路径信息的wa|文件的文件名

注意：wal level和archive mode参数修改都需要重新启动数据库才可以生效。而修改archive_command则不需要。所以一般配置新系统时，无论当时是否需要归档，这要建议将这两个参数开启

开启配置参数 

archive_mode = on
wal_level = replica
archive_command='test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  #涉及的目录需手动创建，属主为postgres

重启数据库 

pg_ctl restart [-mf]

测试 

insert into t1 values (generate_series(1,1000));   -- 插入1000行数据
select pg_switch_wal();   -- 手动切换归档日志

备份

需要备份什么？

  • 数据
  • 归档日志

备份方式

  • 逻辑备份

    • pg_dump
    • pg_dumpall

  • 物理备份

    • pg_basebackup

pg_basebackup物理备份

可以实现物理热备。

支持远程备份。

备份 

pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -Upostgres -h 10.0.0.51 -p 1921 -R

-D ：指定备份目录，提前创建，而且必须为空。
-F: 指定备份文件存储格式，t为tar格式，p为明文
-Pv: 显示备份过程，--progress  --verbose
-R:  write configuration for replication  生成一个从库的配置文件

恢复——不完整恢复

没有应用online wal，所以是不完整恢复

[postgres@oldguo pg_backup]$ ls
base.tar pg_wal.tar
# 模拟数据库损坏
[postgres@oldguo pg_backup]$ pg_ctl stop -mf
[postgres@oldguo pg_backup]$ rm -rf $PGDATA/*
[postgres@oldguo pg_backup]$ rm -rf /archive/*

#拷贝数据
[postgres@oldguo pg_backup]$ tar xf base.tar -C $PGDATA/

#恢复归档
[postgres@oldguo pg_backup]$ tar xf pg_wal.tar -C /archive
#修改配置文件，添加2行配置
[postgres@oldguo pg_backup]$ vim $PGDATA/postgresql.auto.conf   #10版本之前名称为recovery.conf
restore_command = 'cp /archive/%f ‰p'
recovery_target = 'immediate'   # immediate：及时恢复，恢复到最新的。

#启动
[postgres@oldguo pg_backup]$ pg_ctl start  #此时数据库处于一个readonly模式

postgres=# select pg_wal_replay_resume();  #切换数据库从readonly状态到正常状态

PITR——完整恢复

场景介绍：

全备+归档日志+在线日志

寻找恢复点：pg_waldump

模拟：

#测试数据
oldguo=#create table pit(id int);
CREATE TABLE
oldguo=#insert into pit values (1);
INSERT 0 1
oldguo=#insert into pit values (11);
INSERT 0 1
oldguo=#insert into pit values (111);
INSERT 0 1


# 全备
pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -Upostgres -h 10.0.0.51 -p 1921 -R

#测试数据
oldguo=#insert into pit values (2);
INSERT 0 1
oldguo=#insert into pit values (22);
INSERT 0 1
oldguo=#insert into pit values (222);
INSERT 0 1

# 模拟误操作：删除库
oldguo=# drop database pit;

恢复：

首先查看要恢复到哪个事务id（即xid）：

pg_waldump /archive/xxxx  #找到xid，例如xid=533

image-20211228084750216

[postgres@oldguo pg_backup]$ pg_ctl stop -mf
[postgres@oldguo pg_backup]$ rm -rf $PGDATA/*
[postgres@oldguo pg_backup]$ rm -rf /archive/*

#拷贝数据
[postgres@oldguo pg_backup]$ tar xf base.tar -C $PGDATA/

#恢复归档
[postgres@oldguo pg_backup]$ tar xf pg_wal.tar -C /archive
#修改配置文件，添加2行配置
[postgres@oldguo pg_backup]$ vim $PGDATA/postgresql.auto.conf   #10版本之前名称为recovery.conf
restore_command = 'cp /archive/%f ‰p'
recovery_target_xid = '533'  #恢复时间点：恢复到事务533
# recovery_target_name
# recovery_target_time
# recovery_target_lsn
# ... 其他恢复方式信息可以查看pg的配置文件中的介绍


#启动
[postgres@oldguo pg_backup]$ pg_ctl start  #此时数据库处于一个readonly模式

postgres=# select pg_wal_replay_resume();  #切换数据库从readonly状态到正常状态

创建恢复点：适用于进行危险操作之前。类似于快照 postgres=#select pg_create_restore_point(‘oldguo-before-delete’); #创建恢复点，结合recovery_target_name恢复。

流复制(主从复制)

角色IP端口
master
standby

1、配置文件

master节点 

#创建用户
create role replica with replication login password '123456';
alter user replica with password '123456';

#修改pg_hba.conf
host replication replica 0.0.0.0/0 md5

#修改配置：
wa1_leve1=replica 	#这个是设置主为wal的主机
max_wal_senders=5 		#这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments=128		#设置流复制保留的最多的xlog数目（归档日志个数？）
wal_sender_timeout=60s		#设置流复制主机发送数据的超时时间
max_connections=200			#一般读多于写的应用从库的最大连接数要比较大
	#下面是从库相关配置
hot_standby=on				#说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay=30s			#数据流备份的最大延迟时间
wal_receiver_status_interval=lOs		#多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback=on			#如果有错误的数据复制，是否向主进行反馈
wal_log_hints=on     # also do full page writes of non-critical updates

# 重启
pg_ctl restart

standby节点 

#清空数据和归档
[postgres@oldguo-s ~]rm -rf /pgdata/12/data/*
[postgres@oldguo-s ~]rm -rf /archive/*

#备份主库数据到备库
[postgres@oldguo-s ~]pg_basebackup -D /pgdata/pg_backup/-Ft -Pv -Upostgres -h 10.0.0.51 -p 1921 -R

#解压数据
[postgres@oldguo-s pg_backup]$tar xf base.tar -C $PGDATA
[postgres@oldguo-s pg_backup]$tar xf pg_wal.tar -C /archive/
 
#修改standby.signal文件：
standby_mode ='on'  #开启从库的standby模式 （只读）

#修改postgresql.conf文件：
primary_conninfo='host=10.0.0.51 port=1921 user=replica password=123456'

recovery_target_timeline=latest  #默认
max_connections=120  #大于等于主节点，正式环境应当重新考虑此值的大小
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'
1og_fi1ename='postgresq1-%Y-%m-%d_‰H%M%S.1og'

#上面的postgresql.conf可以不修改primary_conninfo，而修改postgresql.auto.conf
restore_command='cp /archive/%f ‰p'
primary_conninfo ='user=postgresl password=123456 host=10.0.0.51 port=1921 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

#启动从库
pg_ctl start  #自动完成主从

2、监控状态 

#主库
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;

#备库
psql -c "\x" -c "SELECT FROM pg_stat_wal_receiver;"  #费交互模式，“\x”列式输出

image-20211229075937487

image-20211229080022660

有用的系统函数 

查看当前日志文件1sn位置：
select pg_current_xlog_location();
select pg_current_wal_lsn();

当前xlog buffer中的insert位置，注意和上面pg_current_xlog_location()的区别：
select pg_current_xlog_insert_location();

查看某个1sn对应的日志名：
select pg_xlogfile_name(lsn);
select pg_walfile_name(lsn);

查看某个1sn在日志中的偏移量：
select pg_xlogfile_name_offset('lsn');
select pg_walfile_name_offset('lsn');

查看两个1sn位置的差距：
select pg_xlog_location_diff('lsn','lsn');
select pg_wal_lsn_diff('lsn','lsn');

查看备库接收到的1sn位置：
select pg_last_xlog_receive_location();
select pg_last_wal_receive_lsn();

查看备库回放的1sn位置：
select pg_last_xlog_relay_location();
select pg_last_xact_replay_timestamp();

创建还原点：
select pg_create_restore_point('20201111');

查看表的数据文件路径，filenode：
select pg_relation_filepath('test'::regclas);
select pg_relation_filenode('test');

查看表的oid：
select 'test'::regclass::oid;

查看当前会话pid：
select pg_backend_pid();

生成序列：
select gernate_series (1,8,2);

生成uuid （pg13新特性）
select gen_random_uuid();

重载配置文件信息：
select pg_reload_conf();

查看数据库启动时间：
select pg_postmaster_start_time();

查看用户表、列等权限信息：
select has_any_column_privilege(user,table,privilege);
select has_any_column_privilege(table,privilege);
select has_column_privilege(user,table,column,privilege);
select has_table_privilege(user,table,privilege);

查看当前快照信息：
select txid_current_snapshot();

切换一个运行日志：
select pg_rotate_logfile();

暂停、恢复回放进程：
select pg_xlog_replay_pause();
select pg_xlog_replay_resume();

导出一个快照：
select pg_export_snapshot();

查看对象的大小信息：
select pg_relation_size();
select pg_table_size();
select pg_total_relation_size();

物理、逻辑复制槽：
pg_create/drop_physical_replication_slot(slotname);
pg_create_logical_replication_slot(slotname,decodingname);