Re: FW: Setting up of PITR system. - Mailing list pgsql-admin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

Rajesh Kumar Mallah wrote:
| it takes a while (i dont know how much) for the .backup file to get
| archived and appear in the wal archive area. thats why i prefer to
| use the wal log filename (000000010000000A00000068) instead of
| something like 000000010000000A00000068.0A348A45.backup.
|
| Do you see any problem in the current approach ?
|  i have seen it working fine till now.

I do, to be honest. The WAL location counter accounts for 4294967295
positions and while I'm certain that's WAY more than the average number
of transactions that go into a WAL, quite a number of small ones can
certainly happen before a WAL is rolled over, and until then, you're
dealing with the same log file.

If two backups happen in that period of time for whatever reason, you're
going to have a false positive by looking into ${WAL_ARCHIVE} and
searching just for the WAL name, so including the location in the search
of a WAL fragment is certainly necessary. Infact, going purely by
chance, the probability of hitting the same location in two different
log files in two subsequent backups is much lower than hitting the same
WAL twice.

| can you suggest the sane/recommended way to get the destination
| folders?( i was thinking find $PGDATADIR/pg_tblspc -type l -printf
| "%???????" ) or do i parse output of ls !

Well, one excellent idea is certainly Alvaro's - the readlink utility
can do that for you in no time:

~  $ find ${PGDATADIR}/pg_tblspc/ -type l -exec readlink {} \;
~  /export/works/space/zlxmon/tblspc
~  ...

The other option you have is piping '\db' into psql and have it display
the list of tablespaces on standard output (you can use ~/.pgpass to
supress password prompting):

~  $ echo '\db' | psql -A -F: -t template1
~  pg_default:postgres:
~  pg_global:postgres:
~  zlxmon_ts:gregab:/export/work/space/zlxmon/tblspc
~  ...

By reformatting this slightly you can also get the desired result and
most importantly, it doesn't rely on how postgres' objects are organized
in the main database cluster directory:

~  $ echo '\db' | psql -A -F: -t template1 | cut -f3 -d: | grep -v '^$'
~  /export/work/space/zlxmon/tblspc
~  ...

It's all options for you, of course. Use whichever you prefer.

| if above is done i see the script INPUT/OUTPUT'ACTIVITY as below
|
| INPUTS: PGDATADIR , WAL_ARCHIVE_DIR , LOCAL DUMP DIRECTORY
| OUTPUT: n/a
| ACTIVITY:
|
| 1. it shall take a base backup of PGDATADIR (minus pg_xlog) and
|     all tablespaces into *LOCAL* DUMP Directory

I think you can list that as an output. :)

| 2. Frees disk space by removing unwanted LOG files in WAL_ARCHIVE_DIR

Perhaps moving the old log files into a father backup directory and
having them stick around for a period of time before removing them isn't
a bad idea either, just in case something goes wrong with your latest
backup. You could go about that using find as well; see the -ctime
predicate in find(1).

Kind regards,
- --
~    Grega Bremec
~    gregab at p0f dot net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFEMLxYfu4IwuB3+XoRA1zZAJ9y7HquxYLH5l0rYff6+cvpU+9lVACfVNG0
PPJZu8IPMBR5j/xPy1+CFDs=
=G3Bt
-----END PGP SIGNATURE-----