从零开始：pg_dumpbinary快速入门指南

##

从零开始：pg_dumpbinary快速入门指南

1、pg_dumpbinary概述

pg_dumpbinary is a program used to dump a PostgreSQL database with data dumped in binary format. The resulting dumps must be restored using pg_restorebinary.

项目地址：https://github.com/lzlabs/pg_dumpbinary

2、pg_dumpbinary安装

这里系统是Rocky Linux 9.2，安装方法如下：

yum install -y perl-DBI perl-DBD-Pg
dnf --enablerepo=crb install perl-DateTime

[root@pgdb01 ~]# su - postgres
cd /postgresql/backup/
tar -zxvf pg_dumpbinary-2.17.tar.gz
cd pg_dumpbinary-2.17/
perl Makefile.PL
make
sudo make install

没有啥特殊要求，按照项目文档直接安装就可以

3、使用参数表

3.1、pg_dumpbinary

[postgres@pgdb01:/home/postgres]$pg_dumpbinary --help

Program used to dump a PostgreSQL database with data dumped in binary
format. The resulting dumps can be restored using pg_restorebinary.

usage: pg_dumpbinary -d dbname [options] backup_name

    backup_name   output directory where dump will be saved. Default
                  directory name is binary_bkup_YYYY-MM-DDTHH:MM:SS
                  when no output directory is provided.
options:

  -A, --attach SNAPSHOT        attach pg_dumpbinary to an existing snapshot
                               instead of creating a dedicated one.
  -C, --compress-level 0-9     speed of the gzip compression using the specified
                               digit, between 1 and 9, default to 6. Setting it
                               to 0 disable the compression.
  -d, --database DBNAME        database to dump.
  -h, --host HOSTNAME          database server host or socket directory.
  -j, --job NUM                use this many parallel jobs to dump.
  -n, --schema SCHEMA          dump the named schema(s) only.
  -N, --exclude-schema SCHEMA  do NOT dump the named schema(s).
  -p, --port PORT              database server port number, default: 5432.
  -s, --snapshot-file FILE     change the path to the snapshot information file
                               used by multiprocess. Default: /tmp/snapshot_info
  -t, --table TABLE            dump named relation.
  -T, --exclude-table TABLE    do NOT dump the named table.
  -u, --user NAME              connect as specified database user.
  -v, --version                show program version.
  -V, --verbose                display the list of tables parts of the dump.
  -w, --where                  add a filter in a WHERE clause to data export.
  --help                       show usage.
  --load-via-partition-root    dump data through partitioned table only, make
                               the COPY statements target the root of the
                               partitioning hierarchy rather than the partition.
  --with-child                 when -t or -T option are used, include or exclude
                               child and partition tables. pg_dump will be used
                               instead with options --table-and-children or
                               --exclude-table-and-children (PostgreSQL >= 16).

3.2、pg_restorebinary

[postgres@pgdb01:/home/postgres]$pg_restorebinary --help

Program used to restore a PostgreSQL binary dump done with pg_dumpbinary.
It can not be used to restore other PostgreSQL dump.

usage: pg_restorebinary [options] [-d dbname | -f outfile.sql] backup_dir

  backup_dir   directory where backup files to restore will be read.
               It must be a directory created by pg_dumpbinary.
options:

  -a, --data-only              restore only the data, no schema.
  -C, --create                 create the database before restoring into it.
  -d, --database DBNAME        database to restore, it must exists.
  -E, --exclude-ext EXTNAME    name of an extension to not restore, it can
                               be used multiple time.
  -f, --file FILENAME          specify output file for generated script. No
                               restore is performed, the resulting DDL and
                               commands are written to the file.
  -h, --host HOSTNAME          database server host or socket directory.
  -i, --info                   print information about the dump and exit.
  -j, --job NUM                use this many parallel jobs to restore.
  -n, --schema SCHEMA          restore the named schema(s) only.
  -N, --exclude-schema SCHEMA  do NOT restore the named schema(s).
  -p, --port PORT              database server port number, default: 5432.
  -t, --table TABLE            restore named relation.
  -T, --exclude-table TABLE    do NOT restore the named table.
  -u, --user NAME              connect as specified database user.
  -v, --version                show program version.
  --help                       show usage.
  --disable-triggers           disable triggers during data restore.
  --truncate                   truncate the table before importing the data.
  --schema-exists              add an IF NOT EXISTS clause to CREATE SCHEMA.
  --dump-create                print to stdout the CREATE and ALTER DATABASE
                               statements.

4、案例

4.1、pg_dumpbinary备份

[postgres@pgdb01:/home/postgres]$pg_dumpbinary -d dvdrental pdbdir
Database dvdrental dump created at 2024-05-21T22:03:44
Dumping pre data section at 2024-05-21T22:03:44
Dumping data at 2024-05-21T22:03:45
Dumping post data section at 2024-05-21T22:03:45
Dump ended at 2024-05-21T22:03:46

[postgres@pgdb01:/home/postgres]$cd pdbdir/
[postgres@pgdb01:/home/postgres/pdbdir]$ll
total 840
-rw-r--r-- 1 postgres postgres   2317 May 21 22:03 data-public.actor.bin.gz
-rw-r--r-- 1 postgres postgres  21684 May 21 22:03 data-public.address.bin.gz
-rw-r--r-- 1 postgres postgres    223 May 21 22:03 data-public.category.bin.gz
-rw-r--r-- 1 postgres postgres   6841 May 21 22:03 data-public.city.bin.gz
-rw-r--r-- 1 postgres postgres   1142 May 21 22:03 data-public.country.bin.gz
-rw-r--r-- 1 postgres postgres  15310 May 21 22:03 data-public.customer.bin.gz
-rw-r--r-- 1 postgres postgres  17217 May 21 22:03 data-public.film_actor.bin.gz
-rw-r--r-- 1 postgres postgres  79935 May 21 22:03 data-public.film.bin.gz
-rw-r--r-- 1 postgres postgres   3138 May 21 22:03 data-public.film_category.bin.gz
-rw-r--r-- 1 postgres postgres  14844 May 21 22:03 data-public.inventory.bin.gz
-rw-r--r-- 1 postgres postgres    128 May 21 22:03 data-public.language.bin.gz
-rw-r--r-- 1 postgres postgres 206027 May 21 22:03 data-public.payment.bin.gz
-rw-r--r-- 1 postgres postgres 333472 May 21 22:03 data-public.rental.bin.gz
-rw-r--r-- 1 postgres postgres    192 May 21 22:03 data-public.staff.bin.gz
-rw-r--r-- 1 postgres postgres     59 May 21 22:03 data-public.store.bin.gz
-rw-r--r-- 1 postgres postgres    425 May 21 22:03 data-sequences.lst
-rw-r--r-- 1 postgres postgres     42 May 21 22:03 meta-public.actor.txt
-rw-r--r-- 1 postgres postgres     75 May 21 22:03 meta-public.address.txt
-rw-r--r-- 1 postgres postgres     29 May 21 22:03 meta-public.category.txt
-rw-r--r-- 1 postgres postgres     36 May 21 22:03 meta-public.city.txt
-rw-r--r-- 1 postgres postgres     31 May 21 22:03 meta-public.country.txt
-rw-r--r-- 1 postgres postgres    101 May 21 22:03 meta-public.customer.txt
-rw-r--r-- 1 postgres postgres     29 May 21 22:03 meta-public.film_actor.txt
-rw-r--r-- 1 postgres postgres     32 May 21 22:03 meta-public.film_category.txt
-rw-r--r-- 1 postgres postgres    148 May 21 22:03 meta-public.film.txt
-rw-r--r-- 1 postgres postgres     42 May 21 22:03 meta-public.inventory.txt
-rw-r--r-- 1 postgres postgres     29 May 21 22:03 meta-public.language.txt
-rw-r--r-- 1 postgres postgres     62 May 21 22:03 meta-public.payment.txt
-rw-r--r-- 1 postgres postgres     80 May 21 22:03 meta-public.rental.txt
-rw-r--r-- 1 postgres postgres    101 May 21 22:03 meta-public.staff.txt
-rw-r--r-- 1 postgres postgres     49 May 21 22:03 meta-public.store.txt
-rw-r--r-- 1 postgres postgres  21667 May 21 22:03 post-data.dmp
-rw-r--r-- 1 postgres postgres  28214 May 21 22:03 pre-data.dmp

这里的meta数据其实是列名

[postgres@pgdb01:/home/postgres]$cat pdbdir/meta-public.actor.txt
actor_id
first_name
last_name
last_update

表数据存在data-public.actor.bin.gz中

4.2、pg_restorebinary恢复

[postgres@pgdb01:/home/postgres]$psql
psql (15.4)
Type "help" for help.

postgres=# \l
                                            List of databases
   Name    |  Owner   | Encoding | Collate | Ctype | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+---------+-------+------------+-----------------+-----------------------
 db01      | postgres | UTF8     | C       | C     |            | libc            |
 dvdrental | 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
(5 rows)

postgres=# drop database dvdrental;
DROP DATABASE
postgres=# create database dvdrental;
CREATE DATABASE
postgres=# \q
[postgres@pgdb01:/home/postgres]$pg_restorebinary -d dvdrental pdbdir
Restoring pre data section into database dvdrental
Restoring post data section.
[postgres@pgdb01:/home/postgres]$psql dvdrental -c 'select * from actor limit 1'
 actor_id | first_name | last_name |      last_update
----------+------------+-----------+------------------------
        1 | Penelope   | Guiness   | 2013-05-26 14:47:57.62
(1 row)

这里奇怪的是-C 参数不能用，所以只能在数据库中创建。

4.3、其他

-j, --job NUM                use this many parallel jobs to restore.