Re: File based Incremental backup v7

Hi Marco,

2015-01-27 19:04 GMT+01:00 Marco Nenciarini <marco(dot)nenciarini(at)2ndquadrant(dot)it
>:

> I've done some test and it looks like that FSM nodes always have
> InvalidXLogRecPtr as LSN.
>
> Ive updated the patch to always include files if all their pages have
> LSN == InvalidXLogRecPtr
>
> Updated patch v7 attached.
>
> Regards,
> Marco
>
> --
> Marco Nenciarini - 2ndQuadrant Italy
> PostgreSQL Training, Services and Support
> marco(dot)nenciarini(at)2ndQuadrant(dot)it | www.2ndQuadrant.it
>

I've tried again to replay a new test of the incremental backup introducing
a new tablespace after a base backup, considering the version 7 of the
patch and the new version of the restore script attached in
http://www.postgresql.org/message-id/54C7CDAD.6060900@2ndquadrant.it:

# define here your work dir
WORK_DIR='/home/gbroccolo/pgsql'

# preliminary steps
rm -rf /tmp/data /tmp/tbls tbls/ backups/

# create a test db and a backup repository
psql -c "DROP DATABASE IF EXISTS pgbench"
psql -c "CREATE DATABASE pgbench"
pgbench -U postgres -i -s 5 -F 80 pgbench
mkdir -p backups

# a first base backup with pg_basebackup
BASE=$(mkdir -vp backups/$(date '+%d%m%y%H%M') | awk -F'[’‘]' '{print $2}')
echo "start a base backup: $BASE"
mkdir -vp $BASE/data
pg_basebackup -v -F p -D $BASE/data -x -c fast

# creation of a new tablespace, alter the table "pgbench_accounts" to
set the new tablespace
mkdir -p $WORK_DIR/tbls
CREATE_CMD="CREATE TABLESPACE tbls LOCATION '$WORK_DIR/tbls'"
psql -c "$CREATE_CMD"
psql -c "ALTER TABLE pgbench_accounts SET TABLESPACE tbls" pgbench

# Doing some work on the database
pgbench -U postgres -T 120 pgbench

# a second incremental backup with pg_basebackup specifying the new
location for the tablespace through the tablespace mapping
INCREMENTAL=$(mkdir -vp backups/$(date '+%d%m%y%H%M') | awk -F'[’‘]'
'{print $2}')
echo "start an incremental backup: $INCREMENTAL"
mkdir -vp $INCREMENTAL/data $INCREMENTAL/tbls
pg_basebackup -v -F p -D $INCREMENTAL/data -x -I $BASE/data -T
$WORK_DIR/tbls=$WORK_DIR/$INCREMENTAL/tbls -c fast

# restore the database
./pg_restorebackup.py -T $WORK_DIR/$INCREMENTAL/tbls=/tmp/tbls
/tmp/data $BASE/data $INCREMENTAL/data
chmod 0700 /tmp/data/
echo "port=5555" >> /tmp/data/postgresql.conf
pg_ctl -D /tmp/data start

now the restore works fine and pointing to tablespaces are preserved also
in the restored instance:

gbroccolo(at)arnold:~/pgsql (master %)$ psql -c "\db+"
List of tablespaces
Name | Owner | Location | Access
privileges | Options | Size | Description
------------+----------+----------------------------+-------------------+---------+--------+-------------
pg_default | postgres | |
| | 37 MB |
pg_global | postgres | |
| | 437 kB |
tbls | postgres | /home/gbroccolo/pgsql/tbls |
| | 80 MB |
(3 rows)

gbroccolo(at)arnold:~/pgsql (master %)$ psql -p 5555 -c "\db+"
List of tablespaces
Name | Owner | Location | Access privileges | Options |
Size | Description
------------+----------+-----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 37 MB |
pg_global | postgres | | | | 437 kB |
tbls | postgres | /tmp/tbls | | | 80 MB |
(3 rows)

Thanks Marco for your reply.

Giuseppe.
--
Giuseppe Broccolo - 2ndQuadrant Italy
PostgreSQL Training, Services and Support
giuseppe(dot)broccolo(at)2ndQuadrant(dot)it | www.2ndQuadrant.it