X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 91DF811F66F7 for ; Mon, 3 Apr 2006 01:16:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15788-05 for ; Mon, 3 Apr 2006 01:16:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 3875B11F66E4 for ; Mon, 3 Apr 2006 01:16:08 -0300 (ADT) Received: from [10.0.1.4] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Sun, 2 Apr 2006 22:15:49 -0600 In-Reply-To: References: <442B93DD.4020305@p0f.net> <442E1945.4030006@p0f.net> <442FDC7F.9010001@p0f.net> Mime-Version: 1.0 (Apple Message framework v749.3) Content-Type: multipart/signed; micalg=sha1; boundary=Apple-Mail-2-374304050; protocol="application/pkcs7-signature" Message-Id: <747E2906-E928-4E4F-A45C-24C62B766430@clickspace.com> Cc: "Grega Bremec" , pgsql-admin@postgresql.org From: Brendan Duddridge Subject: Re: Setting up of PITR system. Date: Sun, 2 Apr 2006 22:15:44 -0600 To: Rajesh Kumar Mallah X-Mailer: Apple Mail (2.749.3) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110] X-Spam-Score: 0.11 X-Spam-Level: X-Archive-Number: 200604/27 X-Sequence-Number: 21196 --Apple-Mail-2-374304050 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Whenever you get a finalized script including all the enhancements you've been talking about, I would really love to have a copy as I'm sure lots of people here would. I think this is something that should be included in the standard distribution. Can I make a couple of suggestions? 1. Include a mail option to send the admin an email when the backup succeeds or fails. 2. Call pg_stop_backup() if the script fails for whatever reason. You can't run the script twice unless you stop the backup. I ran the script a few times to see if I can get a PITR backup system going and due to directory permissions and such and during basic testing, I had to manually call pg_stop_backup() after the script exits abnormally. Thanks very much for building this script and sharing it with us. Many of us don't have the skills to write such a script. I'm a Java programmer, but I have little bash scripting skills, so this is very much appreciated. ____________________________________________________________________ Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@clickspace.com ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 2, 2006, at 10:14 AM, Rajesh Kumar Mallah wrote: > On 4/2/06, Grega Bremec wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: RIPEMD160 >> >> Rajesh Kumar Mallah wrote: >> | >> | Instead of taking a round about method i am using the content of >> the >> | file, (This was also suggested by Andy at somepoint) >> >> After reading the docs again, that's what I would do as well, >> indeed. :) >> >> | lately i feel that we should not be discussing the topic over here >> | becoz it has less to do with postgresql and more of bash. >> >> I've been considering that seriously in the very first post I >> wrote, but >> since there seems to be a lot of people interested in a working, >> flexible WAL archiving script, I decided to keep it on the list. >> It is >> an administration issue, afterall. I will stand corrected if someone >> feels we're clogging their mailboxes. >> >> | ############################################################## >> | #START WAL LOCATION: E/A9145E4 (file 000000010000000E0000000A) >> | #CHECKPOINT LOCATION: E/A92939C >> | #START TIME: 2006-04-01 14:36:48 IST >> | #LABEL: base_backup_01-04-2006-14-36-45 >> | ############################################################### >> | >> | BACKUP_LABEL=$DATADIR/backup_label >> | # get the like containing line START WAL LOCATION >> | >> | START_LINE=`grep -i "START WAL LOCATION" $BACKUP_LABEL` >> | # strip something like 'START WAL LOCATION: E/A9145E4 (file ' >> from begin. >> | START_LINE=${START_LINE/#START*file /} >> | # strip ')' from end. >> | START_LINE=${START_LINE/%)/} >> | # REF_FILE_NUM is something like 000000010000000A00000068 >> | REF_FILE_NUM=$START_LINE >> >> Why not go for the entire filename? > > > 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. > > Another area i was thinking to improve this script was to > make it dig out all the tablespace folders to be archived by looking > into PGDATADIR/pg_tblspc . This shall make the script more generic. > > pg_tblspc as contents like below: > > $ ls -l > total 0 > lrwxrwxrwx 1 postgres postgres 19 Mar 27 21:45 16391 -> /mnt/ > indexspace_new > lrwxrwxrwx 1 postgres postgres 18 Mar 27 21:45 16392 -> /mnt/ > bigtables_new > > 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 ! > > 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 > 2. Frees disk space by removing unwanted LOG files in WAL_ARCHIVE_DIR > > i am waiting for my another machine to get fitted with 2 more drives > so that i can test/develop scripts for the restoration part and modify > the script for remote base backups and archiving. that shall > happen in a week or so. > > thanks for your support till now. > > Regds > Rajesh Kumar Mallah. > > >> >> Kind regards, >> - -- >> ~ Grega Bremec >> ~ gregab at p0f dot net >> -----BEGIN PGP SIGNATURE----- >> Version: GnuPG v1.4.0 (GNU/Linux) >> >> iD8DBQFEL9x/fu4IwuB3+XoRA3IgAJ9Qn7dYsNhv3e9f+P64mJoiz+s77gCeLELY >> 4xAxFb3Ncd8RHWkBbgyag7U= >> =7MXQ >> -----END PGP SIGNATURE----- >> > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > --Apple-Mail-2-374304050 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGIDCCAtkw ggJCoAMCAQICAw84ujANBgkqhkiG9w0BAQQFADBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0EwHhcNMDUwNzI5MjAxMTQzWhcNMDYwNzI5MjAxMTQzWjBIMR8wHQYDVQQD ExZUaGF3dGUgRnJlZW1haWwgTWVtYmVyMSUwIwYJKoZIhvcNAQkBFhZicmVuZGFuQGNsaWNrc3Bh Y2UuY29tMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqWfMCdFgKzXroGNqJuU3eyDC K/Gxts3mtmIdgcHGmUjRKdlBSfkmcCBUDPJIt4QQc91sY3h8Itg0EAsF+1yFECU6afn/1SEAHSDK 6Q86PclF58/Dux2FYNGpeIAw/lisZ2UCyIYoUiWtEDKCBq5jDuYzcGkFS5Csz+/rEL+BHTXQVqnS nUdUaXu9xdZn4wgjB/n65UEYRqr0LGN53CGsFcA9uC+ViS7WyBtzJcP02LMNT+cAZ7TKHw4Q/ZeG 5ptPgQHLTD2wza3GMbPQ4fYK6aNPA+lVzslLSCjyQpg55gVyQQlV0k/5zU5Q5m48ZtqwOqN+fPYd eIB7H5y1SFLd/wIDAQABozMwMTAhBgNVHREEGjAYgRZicmVuZGFuQGNsaWNrc3BhY2UuY29tMAwG A1UdEwEB/wQCMAAwDQYJKoZIhvcNAQEEBQADgYEAT+6hGvStHoRO0OCHlJev31BpUlvPSpbYVKJN i5kCMF164sSX7j0IRvcyU6DfUuW7samTluXJbANyuX1ZIpXulGen5EEc2NV7eU/rMz6ExklMShTh ++azcvmMjOzTuqXi3ZsIjwzBBhqCB0U++kYlMlHEYla2qKLFbXY6PiH0a8swggM/MIICqKADAgEC AgENMA0GCSqGSIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBl MRIwEAYDVQQHEwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQL Ex9DZXJ0aWZpY2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29u YWwgRnJlZW1haWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5j b20wHhcNMDMwNzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UE ChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFs IEZyZWVtYWlsIElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftO ucqZWh5owHUEcJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9x VsuvPAsH5/EfkTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAe ZBlyYLf7AgMBAAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJo dHRwOi8vY3JsLnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8E BAMCAQYwKQYDVR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3 DQEBBQUAA4GBAEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9 reFhYsPZOhl+hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAa WzVNd+NWIXiC3CEZNd4ksdMdRv9dX2VPMYIC5zCCAuMCAQEwaTBiMQswCQYDVQQGEwJaQTElMCMG A1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNv bmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujAJBgUrDgMCGgUAoIIBUzAYBgkqhkiG9w0BCQMx CwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNjA0MDMwNDE1NDVaMCMGCSqGSIb3DQEJBDEW BBQ92yh7n8up00Pc+Sl3Qb572QGVHjB4BgkrBgEEAYI3EAQxazBpMGIxCzAJBgNVBAYTAlpBMSUw IwYDVQQKExxUaGF3dGUgQ29uc3VsdGluZyAoUHR5KSBMdGQuMSwwKgYDVQQDEyNUaGF3dGUgUGVy c29uYWwgRnJlZW1haWwgSXNzdWluZyBDQQIDDzi6MHoGCyqGSIb3DQEJEAILMWugaTBiMQswCQYD VQQGEwJaQTElMCMGA1UEChMcVGhhd3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMj VGhhd3RlIFBlcnNvbmFsIEZyZWVtYWlsIElzc3VpbmcgQ0ECAw84ujANBgkqhkiG9w0BAQEFAASC AQAtBaOTRut2qX//8azR6GjzB+X0gyIn1FLL898HPMrN3XqbV9ZT/YoBnynfGazJSy+QcqIs//Do ajZlp3J+h1IAPmvrkswoddSePoRfOYj/BOCj/Pn4fvvyc2eIQLtzXxjQ4tCeDCbNZutXdxYf3WR2 xoDNdX5L0MezBVE9KlA4DON85Ynmx8b4hpObwg3QyaSI5LUM/QtohNeqZ7becCKGC2LLXUm4gKFH m3M/1Z492hBF3Likvl6yco4GJ3eAzcblaFFXj5RkdWcOo+zWAn1vgGRunaSuBplwyZGnTuzeLkzo eRBnOj5nZGtxRqYzZSx1rfm6GNaWVPwM19/z+z05AAAAAAAA --Apple-Mail-2-374304050--