ZFS snapshot used with PostgreSQL PITR or FAST degrade or PG-XC GreenPlum plproxy MPP DB's consistent backup

pg93@db-172-16-3-150-> initdb -D /ssd4/pg93/pg_root -E UTF8 --locale=C -U postgres -W
pg93@db-172-16-3-150-> cd /ssd4/pg93/pg_root
pg93@db-172-16-3-150-> cp /home/pg93/pgsql/share/recovery.conf.sample ./
pg93@db-172-16-3-150-> mv recovery.conf.sample recovery.done
pg93@db-172-16-3-150-> vi pg_hba.conf
host all all 0.0.0.0/0 md5
host replication postgres 127.0.0.1/32 trust
pg93@db-172-16-3-150-> vi postgresql.conf
listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
port = 1921                             # (change requires restart)
max_connections = 100                   # (change requires restart)
superuser_reserved_connections = 13     # (change requires restart)
unix_socket_directories = '.'   # comma-separated list of directories
tcp_keepalives_idle = 60                # TCP_KEEPIDLE, in seconds;
tcp_keepalives_interval = 10            # TCP_KEEPINTVL, in seconds;
tcp_keepalives_count = 10               # TCP_KEEPCNT;
shared_buffers = 1024MB                 # min 128kB
maintenance_work_mem = 512MB            # min 1MB
vacuum_cost_delay = 10                  # 0-100 milliseconds
vacuum_cost_limit = 10000               # 1-10000 credits
bgwriter_delay = 10ms                   # 10-10000ms between rounds
wal_level = hot_standby                 # minimal, archive, or hot_standby
synchronous_commit = off                # synchronization level;
wal_writer_delay = 10ms         # 1-10000 milliseconds
checkpoint_segments = 64                # in logfile segments, min 1, 16MB each
archive_mode = on               # allows archiving to be done
archive_command = '/usr/bin/test ! -f /ssd1/pg93/arch/%f && /bin/cp %p /ssd1/pg93/arch/%f'
max_wal_senders = 32            # max number of walsender processes
wal_keep_segments = 128         # in logfile segments, 16MB each; 0 disables
hot_standby = on                        # "on" allows queries during recovery
max_standby_archive_delay = 300s        # max delay before canceling queries
max_standby_streaming_delay = 300s      # max delay before canceling queries
wal_receiver_status_interval = 1s       # send replies at least this often
hot_standby_feedback = on               # send info from standby to prevent
effective_cache_size = 96000MB
log_destination = 'csvlog'              # Valid values are combinations of
logging_collector = on          # Enable capturing of stderr and csvlog
log_truncate_on_rotation = on           # If on, an existing log file with the
log_checkpoints = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose           # terse, default, or verbose messages
log_statement = 'ddl'                   # none, ddl, mod, all
log_timezone = 'PRC'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
datestyle = 'iso, mdy'
timezone = 'PRC'
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
default_text_search_config = 'pg_catalog.english'

pg93@db-172-16-3-150-> vi recovery.done
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=127.0.0.1 port=1922 user=postgres keepalives_idle=60'

pg93@db-172-16-3-150-> pg_ctl start -D /ssd4/pg93/pg_root

[root@db-172-16-3-150 pg93]# mkdir -p /ssd1/pg93/arch
[root@db-172-16-3-150 pg93]# chown pg93:pg93 /ssd1/pg93/arch

[root@db-172-16-3-150 ssd1]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdc1              29G  9.3G   19G  34% /
tmpfs                  48G     0   48G   0% /dev/shm
/dev/sdc3              98G   30G   63G  33% /opt
/dev/sdd1             183G  5.4G  169G   4% /ssd1
/dev/sdb1             221G   42G  168G  20% /ssd4

[root@db-172-16-3-150 ssd1]# cd /ssd1
[root@db-172-16-3-150 ssd1]# dd if=/dev/zero of=./zfs.disk1 bs=1024k count=8192 
8192+0 records in
8192+0 records out
8589934592 bytes (8.6 GB) copied, 10.1841 s, 843 MB/s
[root@db-172-16-3-150 ssd1]# cp zfs.disk1 zfs.disk2
[root@db-172-16-3-150 ssd1]# cp zfs.disk1 zfs.disk3
[root@db-172-16-3-150 ssd1]# cp zfs.disk1 zfs.disk4

[root@db-172-16-3-150 ssd1]# cd /ssd4
[root@db-172-16-3-150 ssd4]# dd if=/dev/zero of=./zfs.log1 bs=1024k count=8192
[root@db-172-16-3-150 ssd4]# cp zfs.log1 zfs.log2

[root@db-172-16-3-150 ssd4]# ll /dev/disk/by-id/|grep sda
lrwxrwxrwx 1 root root  9 May 16 21:48 ata-OCZ-REVODRIVE3_OCZ-Z2134R0TLQBNE659 -> ../../sda
lrwxrwxrwx 1 root root 10 May 16 21:48 ata-OCZ-REVODRIVE3_OCZ-Z2134R0TLQBNE659-part1 -> ../../sda1
lrwxrwxrwx 1 root root  9 May 16 21:48 scsi-SATA_OCZ-REVODRIVE3_OCZ-Z2134R0TLQBNE659 -> ../../sda
lrwxrwxrwx 1 root root 10 May 16 21:48 scsi-SATA_OCZ-REVODRIVE3_OCZ-Z2134R0TLQBNE659-part1 -> ../../sda1
lrwxrwxrwx 1 root root  9 May 16 21:48 wwn-0x5e83a97e827c316e -> ../../sda
lrwxrwxrwx 1 root root 10 May 16 21:48 wwn-0x5e83a97e827c316e-part1 -> ../../sda1

[root@db-172-16-3-150 ssd4]# zpool create zptest /ssd1/zfs.disk1 /ssd1/zfs.disk2 /ssd1/zfs.disk3 /ssd1/zfs.disk4 log mirror /ssd4/zfs.log1 /ssd4/zfs.log2 cache /dev/disk/by-id/wwn-0x5e83a97e827c316e-part1

[root@db-172-16-3-150 ssd4]# zfs create zptest/pg93
[root@db-172-16-3-150 ssd4]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdc1              29G  9.3G   19G  34% /
tmpfs                  48G     0   48G   0% /dev/shm
/dev/sdc3              98G   30G   63G  33% /opt
/dev/sdd1             183G   38G  137G  22% /ssd1
/dev/sdb1             221G   56G  154G  27% /ssd4
zptest                 32G  128K   32G   1% /zptest
zptest/pg93            32G  128K   32G   1% /zptest/pg93

[root@db-172-16-3-150 ssd4]# chown -R pg93:pg93 /zptest/pg93

pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1921 -U postgres postgres
psql (9.3.3)
Type "help" for help.
postgres=# select pg_start_backup(now()::text);
 pg_start_backup 
-----------------
 0/2000028
(1 row)

postgres=# \q
  
pg93@db-172-16-3-150-> cp -r /ssd4/pg93/pg_root /zptest/pg93/
pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1921 -U postgres postgres
psql (9.3.3)
Type "help" for help.

postgres=# select pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/20000F0
(1 row)

pg93@db-172-16-3-150-> cd /zptest/pg93/pg_root/
pg93@db-172-16-3-150-> vi postgresql.conf 
port = 1922
# archive_command = '/usr/bin/test ! -f /ssd1/pg93/arch/%f && /bin/cp %p /ssd1/pg93/arch/%f'  # 不注释也没有关系, standby不会触发archive主进程

src/backend/postmaster/postmaster.c
                /* If we have lost the archiver, try to start a new one */
                if (XLogArchivingActive() && PgArchPID == 0 && pmState == PM_RUN)
                        PgArchPID = pgarch_start();

typedef enum
{
        PM_INIT,                                        /* postmaster starting */
        PM_STARTUP,                                     /* waiting for startup subprocess */
        PM_RECOVERY,                            /* in archive recovery mode */
        PM_HOT_STANDBY,                         /* in hot standby mode */
        PM_RUN,                                         /* normal "database is alive" state */
        PM_WAIT_BACKUP,                         /* waiting for online backup mode to end */
        PM_WAIT_READONLY,                       /* waiting for read only backends to exit */
        PM_WAIT_BACKENDS,                       /* waiting for live backends to exit */
        PM_SHUTDOWN,                            /* waiting for checkpointer to do shutdown
                                                                 * ckpt */
        PM_SHUTDOWN_2,                          /* waiting for archiver and walsenders to
                                                                 * finish */
        PM_WAIT_DEAD_END,                       /* waiting for dead_end children to exit */
        PM_NO_CHILDREN                          /* all important children have exited */
} PMState;


pg93@db-172-16-3-150-> mv recovery.done recovery.conf
pg93@db-172-16-3-150-> vi recovery.conf
primary_conninfo = 'host=127.0.0.1 port=1921 user=postgres keepalives_idle=60'

pg93@db-172-16-3-150-> rm -f postmaster.pid
pg93@db-172-16-3-150-> pg_ctl start -D /zptest/pg93/pg_root

pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1921 -U postgres postgres
psql (9.3.3)
Type "help" for help.

postgres=# create table test(id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=# create or replace function f_test(v_id int) returns void as $$
declare
begin
  update test set info=md5(random()::text),crt_time=now() where id=v_id;
  if not found then 
    insert into test values (v_id, md5(random()::text), now());
  end if;
  exception when SQLSTATE '23505' then
    return;
end;
$$ language plpgsql strict;
CREATE FUNCTION

pg93@db-172-16-3-150-> cd ~
pg93@db-172-16-3-150-> vi test.sql
\setrandom id 1 500000
select f_test(:id);

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -h 127.0.0.1 -p 1921 -U postgres -c 16 -j 8 -T 3000 postgres

# zfs snapshot zptest/pg93@`date +%F%T`

[root@db-172-16-3-150 ~]# zfs list -t snapshot
NAME                             USED  AVAIL  REFER  MOUNTPOINT
zptest/pg93@2014-05-1721:54:55   143M      -   185M  -
zptest/pg93@2014-05-1723:17:23  99.0M      -  3.65G  -
zptest/pg93@2014-05-1723:18:11  5.10M      -  3.65G  -
zptest/pg93@2014-05-1723:35:32   214M      -  3.65G  -

[root@db-172-16-3-150 ~]# zfs clone zptest/pg93@2014-05-1723:18:11 zptest/pg93_clone1
[root@db-172-16-3-150 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdc1              29G  9.3G   19G  34% /
tmpfs                  48G     0   48G   0% /dev/shm
/dev/sdc3              98G   30G   63G  33% /opt
/dev/sdd1             183G   41G  133G  24% /ssd1
/dev/sdb1             221G   39G  171G  19% /ssd4
zptest                 24G  128K   24G   1% /zptest
zptest/pg93            28G  3.7G   24G  14% /zptest/pg93
zptest/pg93_clone1     28G  3.7G   24G  14% /zptest/pg93_clone1

[root@db-172-16-3-150 ~]# su - pg93
pg93@db-172-16-3-150-> cd /zptest/pg93_clone1/pg_root
port = 1923
# archive_command  #注释

pg93@db-172-16-3-150-> vi recovery.conf
restore_command = 'cp /ssd1/pg93/arch/%f %p'            # e.g. 'cp /mnt/server/archivedir/%f %p'
recovery_target_time = '2014-05-17 23:19:00.203219+08'
recovery_target_timeline = 'latest'
pause_at_recovery_target = true
standby_mode = on

pg93@db-172-16-3-150-> rm -f postmaster.pid
pg93@db-172-16-3-150-> pg_ctl start -D /zptest/pg93_clone1/pg_root
server starting
pg93@db-172-16-3-150-> LOG:  00000: redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
LOCATION:  SysLogger_Start, syslogger.c:649

pg93@db-172-16-3-150-> psql -h 127.0.0.1 -p 1923 -U postgres postgres
psql (9.3.3)
Type "help" for help.
postgres=# select max(crt_time) from test;
            max            
---------------------------
 2014-05-17 23:19:00.20281
(1 row)

pg93@db-172-16-3-150-> pg_ctl stop -m fast -D /zptest/pg93_clone1/pg_root
[root@db-172-16-3-150 ~]# zfs destroy zptest/pg93_clone1

[root@db-172-16-3-150 ssd1]# cd /ssd1
[root@db-172-16-3-150 ssd1]# dd if=/dev/zero of=./zfs.disk5 bs=1024k count=8192
[root@db-172-16-3-150 ssd1]# zpool add zptest /ssd1/zfs.disk5
[root@db-172-16-3-150 ssd1]# zpool status zptest
  pool: zptest
 state: ONLINE
  scan: none requested
config:

        NAME                            STATE     READ WRITE CKSUM
        zptest                          ONLINE       0     0     0
          /ssd1/zfs.disk1               ONLINE       0     0     0
          /ssd1/zfs.disk2               ONLINE       0     0     0
          /ssd1/zfs.disk3               ONLINE       0     0     0
          /ssd1/zfs.disk4               ONLINE       0     0     0
          /ssd1/zfs.disk5               ONLINE       0     0     0
        logs
          mirror-4                      ONLINE       0     0     0
            /ssd4/zfs.log1              ONLINE       0     0     0
            /ssd4/zfs.log2              ONLINE       0     0     0
        cache
          wwn-0x5e83a97e827c316e-part1  ONLINE       0     0     0

errors: No known data errors
[root@db-172-16-3-150 ssd1]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdc1              29G  9.3G   19G  34% /
tmpfs                  48G     0   48G   0% /dev/shm
/dev/sdc3              98G   30G   63G  33% /opt
/dev/sdd1             183G   53G  122G  30% /ssd1
/dev/sdb1             221G   39G  171G  19% /ssd4
zptest                 29G  128K   29G   1% /zptest
zptest/pg93            33G  3.7G   29G  12% /zptest/pg93
zptest/pg93_clone1     33G  3.7G   29G  12% /zptest/pg93_clone1