Introduction to PostgreSQL online logical backup and recovery

Background
PostgreSQL logical backup, refers to online backup database data, DDL output as SQL statements, data can be in the form of SQL statements or fixed separators (row format) output.

The backup does not affect the DML operation of the Backup object by other users.

This article mainly introduces the logical backup tools provided by PostgreSQL, Pg_dump, Pg_dumpall, and the backup method of the copy command of the database.

Pg_dump

When using Pg_dump for backup, other users can perform DML (SELECT, UPDATE, DELETE, INSERT) operations at the same time without interfering with each other.

First, Pg_dump Backup program logic, source analysis.

1. A complete backup of Pg_dump is done in a transaction with a transaction isolation level of serializable or repeatable read. The code is as follows:

Executesqlstatement (Fout, "BEGIN");
if (fout->remoteversion >= 90100)
{
if (serializable_deferrable)
Executesqlstatement (Fout,
"SET TRANSACTION Isolation Level"
"SERIALIZABLE, READ only, deferrable");
Else
Executesqlstatement (Fout,
"SET TRANSACTION Isolation Level"
"Repeatable READ");
}
Else
Executesqlstatement (Fout,
"SET TRANSACTION Isolation Level SERIALIZABLE");

2. Pg_dump before backing up the data, you need to add an access share lock to the object to be backed up, as follows:

if (tblinfo[i].dobj.dump && tblinfo[i].relkind = = relkind_relation)
{
Resetpqexpbuffer (query);
Appendpqexpbuffer (Query,
"LOCK TABLE%s in ACCESS SHARE MODE",
Fmtqualifiedid (Fout,
Tblinfo[i].dobj.namespace->dobj.name,
Tblinfo[i].dobj.name));
Executesqlstatement (Fout, query->data);
}
Pg_dump locks are conflicting with DDL, such as truncate, DROP, ALTER, VACUUM full, and unqualified lock table conflicts, so these operations cannot be performed after the backup has started. Can prevent data structure changes during backup or physical deletion.

Because Pg_dump has to lock backup objects before backing up data, Pg_dump supports lock timeouts to prevent pg_dump from endless lock waits.

if (lockwaittimeout && fout->remoteversion >= 70300)
{
/*
* Arrange to fail instead of waiting forever for a table lock.
*
* Nb:this coding assumes that's only queries issued within the
* Following loop are LOCK TABLEs; Else the timeout may undesirably
* Applied to the other things too.
*/
Resetpqexpbuffer (query);
Appendpqexpbuffer (Query, "SET statement_timeout =");
Appendstringliteralconn (query, Lockwaittimeout, getconnection (Fout));
Executesqlstatement (Fout, query->data);
}
For example:

Session A:

Pg93@db-172-16-3-33-> Psql
Psql (9.3devel)
Type ' help ' for help.
digoal=# begin;
BEGIN
digoal=# TRUNCATE TABLE test;
TRUNCATE TABLE
--Do not end a transaction.

Session B:

Pg93@db-172-16-3-33-> pg_dump-f./test.dmp

Will wait until session A is a lock on test.

This wait can be seen from the third session.

Session C:

digoal=# select query,waiting from pg_stat_activity;
Query | Waiting
---------------------------------------------+---------
LOCK TABLE public.test in ACCESS SHARE MODE | T--This one was launched by Pg_dump.
TRUNCATE TABLE test; | F
Select query,waiting from pg_stat_activity; | F
(3 rows)
If you don't want to keep pg_dump waiting, you can use the--lock-wait-timeout parameter.

For example, the following command, waiting 5 seconds to obtain a lock unsuccessfully, exits Pg_dump.

Pg93@db-172-16-3-33-> pg_dump-f./test.dmp--lock-wait-timeout=5s
Pg_dump: [Archiver (DB)] Query failed:ERROR:canceling statement due to statement timeout
Pg_dump: [Archiver (DB)] query Was:lock TABLE public.test in ACCESS SHARE MODE
3. When all is ready, Pg_dump will start backing up the data.

Second, the content of the backup format:

In the case of PostgreSQL 9.3, Pg_dump supports 4 different formats:

P, plain
Default format, the backup output is readable text text. SQL that backs up text directly in the database at restore time.

C, Custom
Customizable archive format, with data compression turned on by default, the restore order of the backup objects can be adjusted while restoring, and the selected restored object is supported.
The backup is written to a file. You need to be aware of the individual file size supported by the file system.
This format must be restored using the Pg_restore command.

D, Directory
The catalog archive format, similar to the custom format, requires the use of Pg_restore restore. However, a directory is created in the directory archive format, and each table or large object corresponds to a backup output file.
Plus the TOC filename describes the details of the backup, which supports compression by default, while supporting parallel exports.

T, tar
The TAR archive format, which does not support compression while limiting the maximum of 8GB per table, also requires the use of Pg_restore restore.

Three, the entire database consistent backup example:

Note that whole-library consistency does not mean cluster consistency, and multiple databases can be created in a PostgreSQL cluster.

Pg_dump full-Library consistent backup refers to a consistent backup of a single database in a cluster, because backing up different databases requires switching connections, unable to share the snapshot between different databases, and therefore can only be single library consistent.

Exclusive options:

Use multiple--exclude-table-data=table to exclude tables that do not need to be backed up.

Use multiple--exclude-schema=schema to exclude schemas that do not need to be backed up.

Check the hash value of the backup data before backing up. Easy to restore after comparison:

Pg93@db-172-16-3-33-> Psql
Psql (9.3devel)
Type ' help ' for help.
digoal=# \dt
List of relations
Schema | Name |  Type | Owner
--------+----------------+-------+----------
Public | Pwd_dictionary | Table | Postgres
Public | Tbl_user | Table | Postgres
Public | Test | Table | Postgres
(3 rows)
digoal=# Select sum (Hashtext (T.*::text)) from Pwd_dictionary t;
Sum
------------
-719496483
(1 row)
digoal=# Select sum (Hashtext (T.*::text)) from Tbl_user t;
Sum
---------------
-131178135551
(1 row)
digoal=# Select sum (Hashtext (t.*::text)) from Test T;
Sum
-----

(1 row)
Backup command:

Backs up the Digoal library, and the DDL does not contain tablespaces. Therefore, you do not need to create the corresponding table space in advance when restoring.

Pg93@db-172-16-3-33-> pg_dump-f./digoal.dmp-f p-c-e UTF8--no-tablespaces-h 127.0.0.1-p 1999-u postgres
Delete the Digoal library.

digoal=# \c Postgres
You are are now connected to database "Postgres" as User "Postgres".
postgres=# drop Database digoal;
DROP DATABASE
Restore, execute backup SQL directly:

Pg93@db-172-16-3-33-> psql postgres postgres-f./digoal.dmp
SET
SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are are now connected to database "Digoal" as User "Postgres".
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
CREATE FUNCTION
ALTER FUNCTION
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
SET
ALTER TABLE
Setval
--------
1
(1 row)

SET
SET
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
Check the hash value after the restore, consistent with the previous backup.

Pg93@db-172-16-3-33-> Psql
Psql (9.3devel)
Type ' help ' for help.
digoal=# Select sum (Hashtext (T.*::text)) from Pwd_dictionary t;
Sum
------------
-719496483
(1 row)
digoal=# Select sum (Hashtext (T.*::text)) from Tbl_user t;
Sum
---------------
-131178135551
(1 row)
digoal=# Select sum (Hashtext (t.*::text)) from Test T;
Sum
-----

(1 row)
Four, non-whole library consistent backup examples:
Knowing the Pg_dump backup process, it can be imagined that if the database is large, the longer the Pg_dump backup, the longer it will hold the lock. This may be intolerable for databases that have DDL requirements.

So you might want to make the granularity of pg_dump smaller, not back up the whole library at once, such as backing up a data table with consistent requirements or dependencies at the same time.

As an example:

#!/bin/bash
# Environment variables
Path= $PATH: $HOME/bin
Export PATH
Export Lang=en_us.utf8
Export Pghome=/opt/pgsql
Export Ld_library_path= $PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
Export date= ' DATE + '%y%m%d%h%m '
Export path= $PGHOME/bin: $PATH:.

# program variables
today= ' Date +%y%m%d '
Email= "Digoal@126.com"
Bakbasedir= "/database/pgbak"
Reserve_day=4

Host= "10.10.10.10"
Port= "1921"
Role= "Postgres"

# Inconsistent backups, on a single table.
For DB in ' psql-a-q-t-H $HOST-P $PORT-u $ROLE postgres-c "Select Datname from Pg_database where Datname don't in (' PO Stgres ', ' template0 ', ' template1 ') '
Todo
Echo-e "------' date +%f\%T '----Start Backup----IP: $HOST PORT: $PORT dbname: $DB TYPE: $BAKTYPE to: $BAKBASEDIR------"

For TABLE in ' psql-a-q-t-H $HOST-P $PORT-U $ROLE $DB-C ' Select Schemaname| | '. TableName from Pg_tables where SchemaName!~ ' ^pg_ ' and SchemaName <> ' information_schema ' '  
do 
Pg_dump-f ${bakbasedir}/${db}-${table}-${today}.dmp.ing-f c-t $TABLE--lock-wait-timeout=6000-e utf8-h ${HOST}-P ${ PORT}-U ${role}-W ${db} 
If [$-ne 0]; then 
Echo-e "Backup $HOST $PORT $DB $BAKBASEDIR error \ n ' Date +%f%t ' \ n ' |mutt-s ' error:postgresql_backup ' ${email} 
echo-e '------' date +%f\%T '----ERROR backup-- --ip: $HOST PORT: $PORT dbname: $DB TABLE: $TABLE to: $BAKBASEDIR------" 
Rm-f ${bakbasedir}/${db}-${table}-${ today}.dmp.ing 
break 
fi 
MV ${bakbasedir}/${db}-${table}-${today}.dmp.ing ${BAKBASEDIR }/${db}-${table}-${today}.dmp 
Echo-e "------' date +%f\%T '----Success Backup----IP: $HOST PORT: $PORT dbname: $DB TABLE: $TABLE to: $BAKBASEDIR------" 
done 

Done

Echo-e "Find ${bakbasedir}/${db}_${table}_${today}.dmp*-daystart-mtime +${reserve_day}-delete"
Find ${bakbasedir}/${db}_${table}_${today}.dmp*-daystart-mtime +${reserve_day}-delete

# This is just a simple example of backing up only 1 tables at a time. Call Pg_dump multiple times.

# You need to consider business logic when you use it to ensure business data consistency. For example, some tables that have an association are guaranteed to be exported in the same pg_dump. Multiple-t talbename parameters.

# at the same time need to be aware of the design needs to consider the amount of single table data should not be too large, you should consider the partition table. Otherwise, the DDL operation of the single table will increase the probability of conflict with pg_dump.

# for larger databases it is recommended that you use the Pitr physical incremental backup method. I'll talk about it later.

Five, parallel backup examples:
Parallel backups require a 9.3 pg_dump first, and the server requires 9.2 and above to support pg_export_snapshot versions. Therefore, parallel backups are also consistent backups.

Using parallel backups requires n+1 a connection, n refers to the degree of parallelism specified by-j N, 1 is the primary connection, which is the connection of the state of the transaction, and other connections are imported into this transaction state.

For a previous version of 9.2, if you want to make a parallel backup to increase the speed of the backup and database consistency, do not perform DML operations on the Backup object during the backup.

First create 1000 test tables and insert test data:

digoal=# do language Plpgsql $$
digoal$# Declare
digoal$# v_sql text;
digoal$# begin
digoal$# for I in 1..1000 loop
digoal$# v_sql: = ' CREATE table Test_ ' | | i| | ' (id int, info text) ';
digoal$# Execute v_sql;
digoal$# v_sql: = ' insert into Test_ ' | | i| | ' (id,info) Select Generate_series (1,1000), ' Test ';
digoal$# Execute v_sql;
digoal$# end Loop;
digoal$# end;
digoal$# $$;
Backup, 10 degree of parallelism, backed up to the./paralleldmp directory, this directory is automatically created:

Pg93@db-172-16-3-33-> pg_dump-f./paralleldmp-f d-c-e UTF8--no-tablespaces-j 10-h 127.0.0.1-p 1999-u Digoal
Output one file per table plus a TOC file.

Restore test:

First delete the Digoal library.

Pg93@db-172-16-3-33-> Psql
Psql (9.3devel)
Type ' help ' for help.
digoal=# \c Postgres Postgres
You are are now connected to database "Postgres" as User "Postgres".
postgres=# drop Database digoal;
DROP DATABASE
Restores:

Pg93@db-172-16-3-33-> pg_restore-c-H 127.0.0.1-p 1999-u postgres-d postgres-j

Check if restore:

Pg93@db-172-16-3-33-> Psql
Psql (9.3devel)
Type ' help ' for help.

digoal=# \dt
List of relations
Schema | Name |  Type | Owner
--------+----------------+-------+----------
Public | Pwd_dictionary | Table | Postgres
Public | Tbl_user | Table | Postgres
Public | Test | Table | Postgres
Public | test_1 | Table | Postgres
Public | test_10 | Table | Postgres
Public | test_100 | Table | Postgres
Public | test_1000 | Table | Postgres
Public | test_101 | Table | Postgres
Public | test_102 | Table | Postgres
Public | test_103 | Table | Postgres
Public | test_104 | Table | Postgres
Public | test_105 | Table | Postgres
Public | test_106 | Table | Postgres
Public | test_107 | Table | Postgres
Public | test_108 | Table | Postgres
Public | test_109 | Table | Postgres
Public | Test_11 | Table | Postgres
Public | test_110 | Table | Postgres
Public | test_111 | Table | Postgres
Public | test_112 | Table | Postgres
Public | test_113 | Table | Postgres
Public | test_114 | Table | Postgres
Public | test_115 | Table | Postgres
Public | test_116 | Table | Postgres
Public | test_117 | Table | Postgres
Public | test_118 | Table | Postgres
Public | test_119 | Table | Postgres
Public | Test_12 | Table | Postgres
Public | test_120 | Table | Postgres
Public | test_121 | Table | Postgres
Public | test_122 | Table | Postgres
Public | test_123 | Table | Postgres
Public | test_124 | Table | Postgres
Public | test_125 | Table | Postgres
... Slightly
Vi. Examples of TOC file customization:
Custom TOC files can be adjusted to restore the order, switch to restore the object.

First, you create the list. Use the Pg_restore-l parameter. Create a list from your directory or DMP file. The following is an example of a directory archive to create a list file:

pg93@db-172-16-3-33-> Pg_restore ~/paralleldmp-l >./toc.list
The following is a partial list file interception:

A semicolon is a comment;

Pg93@db-172-16-3-33-> less Toc.list
;
; Archive created at Mon 27 08:58:40 2013
; Dbname:digoal
; TOC entries:2026
; Compression:-1
; Dump version:1.12-0
; Format:unknown
; Integer:4 bytes
; Offset:8 bytes
; Dumped from database Version:9.3devel
; Dumped by Pg_dump Version:9.3devel
;
;
; Selected TOC Entries:
;
8744; 1262 26431 database-digoal Postgres
6; 2615 2200 Schema-public Postgres
8745; 0 0 Comment-schema Public postgres
8746; 0 0 Acl-public Postgres
7; 2615 26432 schema-test Postgres
8747; 0 0 Acl-test Postgres
1176; 3079 12536 Extension-plpgsql
8748; 0 0 Comment-extension Plpgsql
1189; 1255 26433 FUNCTION Public alter_role_pwd (name, text) postgres
1190; 1255 26434 FUNCTION Public create_role (name, text) postgres
171; 1259 26435 TABLE Public pwd_dictionary Postgres
172; 1259 26441 TABLE Public Tbl_user Postgres
173; 1259 26444 SEQUENCE Public tbl_user_id_seq Postgres
8749; 0 0 SEQUENCE owned by public Tbl_user_id_seq Postgres
174; 1259 26446 TABLE Public test Postgres
176; 1259 26457 TABLE Public test_1 Postgres
185; 1259 26511 TABLE Public test_10 Postgres
275; 1259 27051 TABLE Public test_100 Postgres
1175; 1259 32451 TABLE Public test_1000 Postgres
276; 1259 27057 TABLE Public test_101 Postgres
277; 1259 27063 TABLE Public test_102 Postgres
278; 1259 27069 TABLE Public test_103 Postgres
Intercept a line to explain:

8744; 1262 26431 database-digoal Postgres
8744 Correspondence Dumpid
1262 Correspondence Catalogid.tableoid
26431 Correspondence Catalogid.oid
DATABASE corresponding DESC

-Correspond to Te->namespace? Te->namespace: "-"
Digoal corresponding tag
Postgres corresponds to Owner
The meaning of entry in the above TOC file is intercepted from the code as follows:

Src/bin/pg_dump/pg_backup_archiver.c

void
Printtocsummary (Archive *ahx, restoreoptions *ropt)
{
Archivehandle *ah = (Archivehandle *) ahx;
Tocentry *te;
Outputcontext sav;
Char *fmtname;

Sav = Saveoutput (AH);
if (ropt->filename)
Setoutput (AH, ropt->filename, 0/* No compression * *);

ahprintf (AH, "; \ n; Archive created at%s ", CTime (&ah->createdate));
ahprintf (AH, ";     dbname:%s\n;     TOC Entries:%d\n; Compression:%d\n ",
Ah->archdbname, Ah->toccount, ah->compression);

Switch (Ah->format)
{
Case Archfiles:
Fmtname = "FILES";
Break
Case Archcustom:
Fmtname = "CUSTOM";
Break
Case Archtar:
Fmtname = "TAR";
Break
Default
Fmtname = "UNKNOWN";
}

ahprintf (AH, "; Dump Version:%d.%d-%d\n ", Ah->vmaj, Ah->vmin, Ah->vrev);
ahprintf (AH, "; Format:%s\n ", fmtname);
ahprintf (AH, "; Integer:%d bytes\n ", (int) ah->intsize);
ahprintf (AH, "; Offset:%d bytes\n ", (int) ah->offsize);
if (ah->archiveremoteversion)
ahprintf (AH, "; Dumped from database version:%s\n ",
Ah->archiveremoteversion);
if (ah->archivedumpversion)
ahprintf (AH, "; Dumped by pg_dump version:%s\n ",
Ah->archivedumpversion);

ahprintf (AH, "; \n;\n; Selected TOC entries:\n;\n ");

/* We should print DATABASE entries whether or NOT-C was specified * *
Ropt->createdb = 1;

for (Te = ah->toc->next; te!= ah->toc; te = te->next)
{
if (Ropt->verbose | | _tocentryrequired (TE, ropt, True)!= 0)
ahprintf (AH, "%d; %u%u%s%s%s\n ', Te->dumpid,
Te->catalogid.tableoid, Te->catalogid.oid,
Te->desc, Te->namespace? Te->namespace: "-",
Te->tag, Te->owner);
if (ropt->verbose && te->ndeps > 0)
{
int i;

                         ahprintf (AH,; \tdepends on: "); 
                         for (i = 0; i < te->ndeps; i++)  
                                  ahprintf (AH, "%d", Te->dependencies[i]); 
                         ahprintf (AH, "\ n "); 
               }  
       } 

if (ropt->filename)
Restoreoutput (AH, SAV);
}

Below, adjust the list file to restore according to the list file:

For example, note test_1 table creation and data restore.

; 176; 1259 26457 TABLE Public test_1 Postgres
; 7740; 0 26457 TABLE DATA public test_1 Postgres
And then adjust the order

275; 1259 27051 TABLE Public test_100 Postgres
1175; 1259 32451 TABLE Public test_1000 Postgres
Adjusted to

1175; 1259 32451 TABLE Public test_1000 Postgres
275; 1259 27051 TABLE Public test_100 Postgres

Save Toc.list

Delete Database digoal:

digoal=# \c Postgres
You are are now connected to database "Postgres" as User "Postgres".
postgres=# drop Database digoal;
DROP DATABASE
Restores:

pg93@db-172-16-3-33-> pg_restore-h 127.0.0.1-p 1999-u postgres-c-D postgres-j 10-l./toc.list-v & Ety. /restore.log 2>&1
To view the Restore.log log, note that the order adjustment is in effect:

Pg_restore:processing Item 1175 TABLE test_1000
Pg_restore:creating TABLE test_1000
Pg_restore:processing Item 275 TABLE test_100
Pg_restore:creating TABLE test_100
Enter the database at the same time view test_1 table is not restored.

digoal=# \d test_1
Did not find any relation named "Test_1".
Pg_dumpall:

Pg_dumpall is primarily used to back up global data, such as the DDL of Tablespaces, to create a user's DDL.

To export a script that creates a user and creates a table space:

Pg93@db-172-16-3-33-> pg_dumpall-g-H 127.0.0.1-p 1999-u postgres-f./global.dmp
The contents are as follows:

Pg93@db-172-16-3-33-> Cat Global.dmp
--
--PostgreSQL Database cluster dump
--

SET client_encoding = ' UTF8 ';
SET standard_conforming_strings = on;

--
--Roles
--

CREATE role Client1;
ALTER role client1 with Nosuperuser INHERIT nocreaterole nocreatedb LOGIN noreplication PASSWORD ' md596bdd340a56d9ab24058 1edede7a13c6 ';
CREATE role Digoal;
ALTER role Digoal with Nosuperuser INHERIT nocreaterole nocreatedb LOGIN noreplication PASSWORD ' md5c08bdd942d14da5ede9d9 Cef2b17ef9c ';
CREATE role GP1;
ALTER role GP1 with Nosuperuser INHERIT nocreaterole nocreatedb nologin;
CREATE role New;
ALTER role New with Nosuperuser INHERIT nocreaterole nocreatedb nologin noreplication PASSWORD ' md54a5ca2a5e9aaed4c781e7d 72d7fe945f ';
CREATE role Postgres;
ALTER role Postgres with Superuser INHERIT createrole createdb LOGIN REPLICATION PASSWORD ' md53175bce1d3201d16594cebf9d7e B3f9d ';
CREATE role Sslcertgroup;
ALTER role Sslcertgroup with Nosuperuser INHERIT nocreaterole nocreatedb nologin;
CREATE role U4;
ALTER role U4 with Nosuperuser INHERIT nocreaterole nocreatedb nologin noreplication PASSWORD ' md5bbc9d1a9f9e201c9c9d3c15 3F85771CC ';

--
--Role Memberships
--

GRANT GP1 to Client1 granted by Postgres;
GRANT Sslcertgroup to Client1 granted by Postgres;
GRANT Sslcertgroup to Digoal granted by Postgres;
GRANT Sslcertgroup to Postgres granted by Postgres;

--
--Tablespaces
--

CREATE tablespace tbs_digoal OWNER postgres LOCATION '/pgdata/digoal/1921/data03/pg93/1999/tbs_digoal ';

--
--Per-database role Settings
--

ALTER role Postgres in the DATABASE postgres SET work_mem to ' 10240MB ';

--
--PostgreSQL Database cluster dump complete
--
COPY:
In addition to exporting the database using the commands above, you can use SQL statements to export data, as well as to use it in combination with pipelines.

For example:

Export:

digoal=# copy test_10 to '/home/pg93/test_10.dmp ' with the CSV header;
COPY 1000
digoal=# Select sum (Hashtext (T.*::text)) from Test_10 t;
Sum
------------
-432745392
(1 row)
digoal=# truncate TEST_10;
TRUNCATE TABLE
Import:

digoal=# copy test_10 from '/home/pg93/test_10.dmp ' with the CSV header;
COPY 1000
digoal=# Select sum (Hashtext (T.*::text)) from Test_10 t;
Sum
------------
-432745392
(1 row)

Summary

1.

If the database is very large, SQL that conflicts with access share locks will be in the waiting state when making a pg_dump backup.

Wait until the end of the Pg_dump (the release of the lock needs to wait for the top-level transaction block to end).

This is something that needs special attention.