pgsql pitr

< ==== A. Static =====
< This is very primitive example, because we don't have background
< updating while doing backup of pg cluster.
< * 1. Create test directory where to play and *stop* your primary postmaster to be safe, we''ll play with separate pg cluster.
< * 2. Create temporal pg cluster in directory ''t1''
< cd test
< initdb -D ./t1
< * 3. Create ''test'' database
< pg_ctl start -D ./t1
< createdb test
< psql test -c 'create table a (i int);'
< psql test -c '\d a'
< Table "public.a"
< Column | Type | Modifiers
< --------+---------+-----------
< i | integer |
< * 4. Populate some data
< for ((i=0;i<100;i++)) do echo $i; done | psql test -c 'copy a from stdin;'
< * 5. copy ''t1'' -> ''t2'', shutdown postmaster and restart with new pg cluster
< /bin/cp -a ./t1 t2
< pg_ctl -D ./t1 stop
< pg_ctl -D ./t2 start
< * 6. Check ''test'' database
< pg@zen:~/test$ psql test -c 'select count(*) from a;'
< count
< -------
< 100
< (1 row)
< Now, you can repeat steps 4.,5.,6. but change ''t1'' to ''t2''.
< At step 6. you should have
< pg@zen:~/test$ psql test -c 'select count(*) from a;'
< count
< -------
< 200
< (1 row)
< ==== B. Dynamic example =====
< Now, we start some process in background to update ''test database'' and
< copy ''t1'' cluster to ''t2''. Background process time to time commits
< updates, so we have a chance to copy some already commited changes.
< I wrote very simple perl script *bgupdate.pl*. After each inserts it
< sleeps, also it commits every 10 changes.
< <pre>
< #!/usr/bin/perl
< use strict;
< use DBI;
< my $dbh = DBI->connect("dbi:Pg:dbname=test;", "","",
< { RaiseError => 1, AutoCommit => 0 }) or die $DBI::errstr;
< my $sth = $dbh->prepare("insert into a values(?)") or die $DBI::errstr;
< for(my $i=0; $i<100; $i++) {
< my $rv = $sth->execute($i) or die $DBI::errstr;
< $dbh->commit if !($i % 10);
< sleep 2;
< }
<
< $sth->finish;
< $dbh->commit;
< $dbh->disconnect;
< </pre>
< * 1. again, shutdown all postmasters, create test directory ''test''
< * 2. Create temporal pg cluster in directory ''t1''
< cd test
< initdb -D ./t1
< * 3. Create ''test'' database
< pg_ctl start -D ./t1
< createdb test
< psql test -c 'create table a (i int);'
< psql test -c '\d a'
< Table "public.a"
< Column | Type | Modifiers
< --------+---------+-----------
< i | integer |
< * 4. run ''bgupdate.pl'' in background
< perl bgupdate.pl &
< * 5. wait 20-30 seconds and copy ''t1'' to ''t2''
< sleep 25 && cp -a ./t1 t2
< * 5. run second postmaster at port 5433
<
< rm t2/postmaster.pid
< pg_ctl start -D ./t2 -o "-p 5433"
< * 6. Check if ''t2'' cluster is in working state
< psql -p 5433 test -c 'select count(*) from a;'
< count
< -------
< 11
< (1 row)
< * 7. Shutdown ''t2'' cluster and repeat steps 5.,6.,7.
< pg_ctl stop -D ./t2 -o "-p 5433"
< sleep 25 && cp -a ./t1 t2
< rm t2/postmaster.pid
< psql -p 5433 test -c 'select count(*) from a;'
< count
< -------
< 41
< (1 row)