PostgreSQL backup and recovery - online logical backup & recovery

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 %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);
}

;176; 1259 26457 TABLE public test_1 postgres
;7740; 0 26457 TABLE DATA public test_1 postgres

275; 1259 27051 TABLE public test_100 postgres
1175; 1259 32451 TABLE public test_1000 postgres

1175; 1259 32451 TABLE public test_1000 postgres
275; 1259 27051 TABLE public test_100 postgres

digoal=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database digoal;
DROP DATABASE

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 ~/paralleldmp >./restore.log 2>&1

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

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

pg93@db-172-16-3-33-> pg_dumpall -g -h 127.0.0.1 -p 1999 -U postgres -f ./global.dmp

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 'md596bdd340a56d9ab240581edede7a13c6';
CREATE ROLE digoal;
ALTER ROLE digoal WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5c08bdd942d14da5ede9d9cef2b17ef9c';
CREATE ROLE gp1;
ALTER ROLE gp1 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;
CREATE ROLE new;
ALTER ROLE new WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md54a5ca2a5e9aaed4c781e7d72d7fe945f';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md53175bce1d3201d16594cebf9d7eb3f9d';
CREATE ROLE sslcertgroup;
ALTER ROLE sslcertgroup WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION;
CREATE ROLE u4;
ALTER ROLE u4 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5bbc9d1a9f9e201c9c9d3c153f85771cc';


--
-- 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 DATABASE postgres SET work_mem TO '10240MB';


--
-- PostgreSQL database cluster dump complete
--

digoal=# copy test_10 to '/home/pg93/test_10.dmp' with csv header;
COPY 1000
digoal=# select sum(hashtext(t.*::text)) from test_10 t;
    sum     
------------
 -432745392
(1 row)
digoal=# truncate test_10;
TRUNCATE TABLE

digoal=# copy test_10 from '/home/pg93/test_10.dmp' with csv header;
COPY 1000
digoal=# select sum(hashtext(t.*::text)) from test_10 t;
    sum     
------------
 -432745392
(1 row)