Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2F542650F0D for ; Tue, 1 Jul 2008 06:21:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 65487-05-3 for ; Tue, 1 Jul 2008 06:21:06 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail136038.authsmtp.com (outmail136038.authsmtp.com [62.13.136.38]) by postgresql.org (Postfix) with ESMTP id 12930651043 for ; Tue, 1 Jul 2008 05:34:25 -0300 (ADT) Received: from mail-c188.authsmtp.com (mail-c188.authsmtp.com [62.13.128.25]) by punt3.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m618YH4Q009836; Tue, 1 Jul 2008 09:34:17 +0100 (BST) Received: from [192.168.0.3] (85-211-229-155.dyn.gotadsl.co.uk [85.211.229.155]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m618YGqO053586; Tue, 1 Jul 2008 09:34:16 +0100 (BST) Subject: Re: [HACKERS] odd output in restore mode From: Simon Riggs To: Andrew Dunstan Cc: Bruce Momjian , Dave Page , List pgsql-patches In-Reply-To: <48696C3F.4060901@dunslane.net> References: <200806302213.m5UMDCW22128@momjian.us> <48696C3F.4060901@dunslane.net> Content-Type: multipart/mixed; boundary="=-164IMHCRF9KjEoikjW5N" Date: Tue, 01 Jul 2008 09:39:08 +0100 Message-Id: <1214901548.3845.544.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 X-Server-Quench: 7e389967-4748-11dd-aecc-001871e930f4 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksachtFaARbZ0xd HGQLW1xEUFR7Wmd/ ag0fZQBDYEtPQQdo WVZIR1dNFgBqBAMB SEMYUxEbAEQ5eHtz YE5kEHFZX0V+O0Eo QUZdHGsBeGNnbGgC UUEMIx4FJVAZYx9F a1h4VSYEYmVRYjQC El17DBAYFhZyEh9g CgYBIRoVXFoTAyJ0 RhZKBzg0AUofSm02 KBArQidh X-Authentic-SMTP: 61633235383639.cat.dmpriest.net.uk:1078/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/2 X-Sequence-Number: 6697 --=-164IMHCRF9KjEoikjW5N Content-Type: text/plain Content-Transfer-Encoding: 7bit On Mon, 2008-06-30 at 19:29 -0400, Andrew Dunstan wrote: > > Bruce Momjian wrote: > > Dave Page wrote: > > > >> On Sun, May 18, 2008 at 1:38 PM, Andrew Dunstan wrote: > >> > >>> I don't know how you tested, > >>> > >> Copy a large file across a relatively slow network, and check the size > >> on the destination drive before it finishes. > >> > >> > >>> but could you please repeat the test with > >>> GnuWin32's cp.exe? If it doesn't preallocate the space then I think our way > >>> forward is reasonably clear: > >>> > >> It does not pre-allocate. > >> > >> > >>> . we recommend its use for Windows archive_command settings > >>> . we provide the delay kluge as switchable behaviour on Windows instead of > >>> having it always on. > >>> > >> Sounds reasonable to me. > >> > > > > Are there any changes we need to make here? > > > > > > Yes. Simon has promised a patch to do the above. Patch implements * recommendation to use GnuWin32 cp on Windows * provide "holdtime" delay, default 0 (on all platforms) * default stays same on Windows="copy" to ensure people upgrading don't get stung Patch should be backpatched to 8.3, plus to CVS HEAD. We should recommend in next 8.3 release notes that people use "-p" or "-l" rather than just letting it default. Will add permalink to Wiki when patch appears in archives. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support --=-164IMHCRF9KjEoikjW5N Content-Disposition: attachment; filename=pg_standby_win32.v1.patch Content-Type: text/x-patch; name=pg_standby_win32.v1.patch; charset=UTF-8 Content-Transfer-Encoding: 7bit Index: contrib/pg_standby/pg_standby.c =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/contrib/pg_standby/pg_standby.c,v retrieving revision 1.12 diff -c -r1.12 pg_standby.c *** contrib/pg_standby/pg_standby.c 17 May 2008 01:28:21 -0000 1.12 --- contrib/pg_standby/pg_standby.c 1 Jul 2008 08:18:50 -0000 *************** *** 44,49 **** --- 44,50 ---- /* Options and defaults */ int sleeptime = 5; /* amount of time to sleep between file checks */ + int holdtime = 0; /* amount of time to wait once file appears full */ int waittime = -1; /* how long we have been waiting, -1 no wait * yet */ int maxwaittime = 0; /* how long are we prepared to wait for? */ *************** *** 67,75 **** char exclusiveCleanupFileName[MAXPGPATH]; /* the file we need to * get from archive */ ! #define RESTORE_COMMAND_COPY 0 ! #define RESTORE_COMMAND_LINK 1 ! int restoreCommandType; #define XLOG_DATA 0 #define XLOG_HISTORY 1 --- 68,83 ---- char exclusiveCleanupFileName[MAXPGPATH]; /* the file we need to * get from archive */ ! #define RESTORE_COMMAND_CP 0 ! #define RESTORE_COMMAND_LN 1 ! #define RESTORE_COMMAND_WIN32_MKLINK 2 ! #define RESTORE_COMMAND_WIN32_COPY 3 ! #ifdef WIN32 ! int restoreCommandType = RESTORE_COMMAND_WIN32_COPY; ! #else ! int restoreCommandType = RESTORE_COMMAND_CP; ! #endif ! #define XLOG_DATA 0 #define XLOG_HISTORY 1 *************** *** 113,142 **** { #ifdef WIN32 snprintf(WALFilePath, MAXPGPATH, "%s\\%s", archiveLocation, nextWALFileName); switch (restoreCommandType) { ! case RESTORE_COMMAND_LINK: SET_RESTORE_COMMAND("mklink", WALFilePath, xlogFilePath); ! case RESTORE_COMMAND_COPY: ! default: SET_RESTORE_COMMAND("copy", WALFilePath, xlogFilePath); break; ! } ! #else ! snprintf(WALFilePath, MAXPGPATH, "%s/%s", archiveLocation, nextWALFileName); ! switch (restoreCommandType) ! { ! case RESTORE_COMMAND_LINK: ! #if HAVE_WORKING_LINK SET_RESTORE_COMMAND("ln -s -f", WALFilePath, xlogFilePath); break; ! #endif ! case RESTORE_COMMAND_COPY: ! default: SET_RESTORE_COMMAND("cp", WALFilePath, xlogFilePath); break; } - #endif /* * This code assumes that archiveLocation is a directory You may wish to --- 121,148 ---- { #ifdef WIN32 snprintf(WALFilePath, MAXPGPATH, "%s\\%s", archiveLocation, nextWALFileName); + #else + snprintf(WALFilePath, MAXPGPATH, "%s/%s", archiveLocation, nextWALFileName); + #endif switch (restoreCommandType) { ! case RESTORE_COMMAND_WIN32_MKLINK: SET_RESTORE_COMMAND("mklink", WALFilePath, xlogFilePath); ! case RESTORE_COMMAND_WIN32_COPY: SET_RESTORE_COMMAND("copy", WALFilePath, xlogFilePath); break; ! case RESTORE_COMMAND_LN: SET_RESTORE_COMMAND("ln -s -f", WALFilePath, xlogFilePath); break; ! case RESTORE_COMMAND_CP: SET_RESTORE_COMMAND("cp", WALFilePath, xlogFilePath); break; + default: + fprintf(stderr, "pg_standby: unknown restore command\n"); + fflush(stderr); + exit(2); + break; } /* * This code assumes that archiveLocation is a directory You may wish to *************** *** 175,191 **** } else if (stat_buf.st_size == XLOG_SEG_SIZE) { - #ifdef WIN32 - /* ! * Windows reports that the file has the right number of bytes * even though the file is still being copied and cannot be opened ! * by pg_standby yet. So we wait for sleeptime secs before * attempting to restore. If that is not enough, we will rely on * the retry/holdoff mechanism. */ ! pg_usleep(sleeptime * 1000000L); ! #endif nextWALFileType = XLOG_DATA; return true; } --- 181,196 ---- } else if (stat_buf.st_size == XLOG_SEG_SIZE) { /* ! * Some utilities report that the file has the right number of bytes * even though the file is still being copied and cannot be opened ! * by pg_standby yet. So we wait for holdtime secs before * attempting to restore. If that is not enough, we will rely on * the retry/holdoff mechanism. */ ! if (holdtime > 0) ! pg_usleep(holdtime * 1000000L); ! nextWALFileType = XLOG_DATA; return true; } *************** *** 441,448 **** --- 446,455 ---- fprintf(stderr, " -d generate lots of debugging output (testing only)\n"); fprintf(stderr, " -k NUMFILESTOKEEP if RESTARTWALFILE not used, removes files prior to limit (0 keeps all)\n"); fprintf(stderr, " -l links into archive (leaves file in archive)\n"); + fprintf(stderr, " -p always uses GNU compatible 'cp' command on all platforms\n"); fprintf(stderr, " -r MAXRETRIES max number of times to retry, with progressive wait (default=3)\n"); fprintf(stderr, " -s SLEEPTIME seconds to wait between file checks (min=1, max=60, default=5)\n"); + fprintf(stderr, " -h HOLDTIME seconds to wait once file appears full (min=0, max=60, default=0)\n"); fprintf(stderr, " -t TRIGGERFILE defines a trigger file to initiate failover (no default)\n"); fprintf(stderr, " -w MAXWAITTIME max seconds to wait for a file (0=no limit)(default=0)\n"); fflush(stderr); *************** *** 463,474 **** (void) signal(SIGINT, sighandler); (void) signal(SIGQUIT, sighandler); ! while ((c = getopt(argc, argv, "cdk:lr:s:t:w:")) != -1) { switch (c) { case 'c': /* Use copy */ ! restoreCommandType = RESTORE_COMMAND_COPY; break; case 'd': /* Debug mode */ debug = true; --- 470,488 ---- (void) signal(SIGINT, sighandler); (void) signal(SIGQUIT, sighandler); ! while ((c = getopt(argc, argv, "cdh:k:lpr:s:t:w:")) != -1) { switch (c) { case 'c': /* Use copy */ ! #ifdef WIN32 ! restoreCommandType = RESTORE_COMMAND_WIN32_COPY; ! #else ! restoreCommandType = RESTORE_COMMAND_CP; ! #endif ! break; ! case 'p': /* Use cp */ ! restoreCommandType = RESTORE_COMMAND_CP; break; case 'd': /* Debug mode */ debug = true; *************** *** 483,489 **** } break; case 'l': /* Use link */ ! restoreCommandType = RESTORE_COMMAND_LINK; break; case 'r': /* Retries */ maxretries = atoi(optarg); --- 497,511 ---- } break; case 'l': /* Use link */ ! #ifdef WIN32 ! restoreCommandType = RESTORE_COMMAND_WIN32_MKLINK; ! #else ! #if HAVE_WORKING_LINK ! restoreCommandType = RESTORE_COMMAND_LN; ! #else ! restoreCommandType = RESTORE_COMMAND_CP; ! #endif ! #endif break; case 'r': /* Retries */ maxretries = atoi(optarg); *************** *** 503,508 **** --- 525,539 ---- exit(2); } break; + case 'h': /* Hold time */ + holdtime = atoi(optarg); + if (holdtime < 0 || holdtime > 60) + { + fprintf(stderr, "usage: pg_standby -h holdtime incorrectly set\n"); + usage(); + exit(2); + } + break; case 't': /* Trigger file */ triggerPath = optarg; if (CheckForExternalTrigger()) Index: doc/src/sgml/standby.sgml =================================================================== RCS file: /home/sriggs/pg/REPOSITORY/pgsql/doc/src/sgml/standby.sgml,v retrieving revision 1.1 diff -c -r1.1 standby.sgml *** doc/src/sgml/standby.sgml 10 Nov 2007 23:30:46 -0000 1.1 --- doc/src/sgml/standby.sgml 1 Jul 2008 08:29:16 -0000 *************** *** 136,141 **** --- 136,149 ---- + -p + + + always use cp command on any platform + + + + -r maxretries *************** *** 160,165 **** --- 168,182 ---- + -h holdtime + + the number of seconds to wait once a file appears full. + This is required with some utilities, such as Win32 Copy, which + sets the filesize before it has completed the copy process. + Default=0 + + + -t triggerfile the presence of the triggerfile will cause recovery to end *************** *** 228,234 **** *not* in the restore_command, in 8.2, 8.1, 8.0 on Windows. ! restore_command = 'pg_standby -c -d -s 5 -w 0 -t C:\pgsql.trigger.5442 ..\archive %f %p 2>> standby.log' --- 245,251 ---- *not* in the restore_command, in 8.2, 8.1, 8.0 on Windows. ! restore_command = 'pg_standby -c -d -s 5 -h 5 -w 0 -t C:\pgsql.trigger.5442 ..\archive %f %p 2>> standby.log' *************** *** 238,248 **** --- 255,272 ---- use a copy command to restore WAL files from archive produce logfile output in standby.log sleep for 5 seconds between checks for next WAL file is full + sleep for 5 second after the file appears full never timeout if file not found stop waiting when a trigger file called C:\pgsql.trigger.5442 appears + + On Windows you are advised to use GNUWin32's cp utility. The cp utility + does not set filesize until file copying is complete, so there is no + need to use the -h option to introduce additional delays to ensure + correctness. See + --=-164IMHCRF9KjEoikjW5N--