Re: [HACKERS] pg_dump --comment?

Поиск
Список
Период
Сортировка
Искать

Re: [HACKERS] pg_dump --comment?

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
Jon Jensen wrote:
> On Fri, 28 May 2004, Christopher Kings-Lynne wrote:
> 
> > > I've encountered a situation where I'd like to store some information 
> > > about the database when I do a pg_dump. For instance, the timestamp of 
> > > the dump. And some other information that I pull from the database.
> > 
> > I think every dump should dump the timestamp regardless...
> 
> That would cause me a lot of trouble. Every night I do a pg_dump on all my
> databases to a temporary file. Then I use cmp to compare that dump to last
> night's dump. If they're identical I just delete the new dump so that only
> the old one remains, with its original timestamp. That way rsync doesn't
> see any change, and doesn't waste any time comparing it when we do
> backups. It's also handy to see the last day the dump changed by looking
> at the file's timestamp.
> 
> Granted, this is only of interest on databases that don't change at all, 
> but on a multi-user system we have a surprising number of databases that 
> don't change at all for days (alongside the ones that change all the time, 
> of course).
> 
> However, I would like to see an option to include the timestamp if someone 
> wants it.

The following patch adds start/stop times for pg_dump and pg_dumpall
when verbose output is selected:

	--
	-- PostgreSQL database cluster dump
	-- Started on 2004-06-04 01:01:35 EDT
	--
	
	--
	-- PostgreSQL database dump
	-- Started on 2004-06-04 01:01:36 EDT
	--
	
	--
	-- PostgreSQL database dump complete
	-- Completed on 2004-06-04 01:01:36 EDT
	--
	
	--
	-- PostgreSQL database cluster dump complete
	-- Completed on 2004-06-04 01:01:36 EDT
	--

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.70
diff -c -c -r1.70 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml	31 May 2004 13:37:52 -0000	1.70
--- doc/src/sgml/ref/pg_dump.sgml	4 Jun 2004 04:58:18 -0000
***************
*** 403,409 ****
         
  	Specifies verbose mode.  This will cause
  	pg_dump to output detailed object
!         comments in the dump file, and progress messages to standard error.
         
        
       
--- 403,410 ----
         
  	Specifies verbose mode.  This will cause
  	pg_dump to output detailed object
!         comments in the dump file, start and stop times, and progress 
!         messages to standard error.
         
        
       
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.43
diff -c -c -r1.43 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml	29 Nov 2003 19:51:39 -0000	1.43
--- doc/src/sgml/ref/pg_dumpall.sgml	4 Jun 2004 04:58:18 -0000
***************
*** 192,199 ****
        
         
  	Specifies verbose mode.  This will cause
! 	pg_dumpall to print progress
! 	messages to standard error.
         
        
       
--- 192,200 ----
        
         
  	Specifies verbose mode.  This will cause
! 	pg_dumpall to output start and stop
!         times in the dump file, and progress messages to standard error.
!         It will also enable verbose output in pg_dump</>.
         
        
       
Index: src/bin/pg_dump/pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.87
diff -c -c -r1.87 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c	19 May 2004 21:21:26 -0000	1.87
--- src/bin/pg_dump/pg_backup_archiver.c	4 Jun 2004 04:58:21 -0000
***************
*** 28,33 ****
--- 28,34 ----
  
  #include 
  #include 
+ #include 
  #include 
  
  #include "pqexpbuffer.h"
***************
*** 202,208 ****
  	if (ropt->filename || ropt->compression)
  		sav = SetOutput(AH, ropt->filename, ropt->compression);
  
! 	ahprintf(AH, "--\n-- PostgreSQL database dump\n--\n\n");
  
  	/*
  	 * Establish important parameter values right away.
--- 203,218 ----
  	if (ropt->filename || ropt->compression)
  		sav = SetOutput(AH, ropt->filename, ropt->compression);
  
! 	ahprintf(AH, "--\n-- PostgreSQL database dump\n");
! 	if (AH->public.verbose)
! 	{
! 		char buf[256];
! 		time_t now = time(NULL);
! 
!  		if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
! 			ahprintf(AH, "-- Started on %s\n", buf);
! 	}
! 	ahprintf(AH, "--\n\n");
  
  	/*
  	 * Establish important parameter values right away.
***************
*** 385,391 ****
  		}
  	}
  
! 	ahprintf(AH, "--\n-- PostgreSQL database dump complete\n--\n\n");
  }
  
  /*
--- 395,410 ----
  		}
  	}
  
! 	ahprintf(AH, "--\n-- PostgreSQL database dump complete\n");
! 	if (AH->public.verbose)
! 	{
! 		char buf[256];
! 		time_t now = time(NULL);
! 
!  		if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
! 			ahprintf(AH, "-- Completed on %s\n", buf);
! 	}
! 	ahprintf(AH, "--\n\n");
  }
  
  /*
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.36
diff -c -c -r1.36 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c	3 Jun 2004 00:07:36 -0000	1.36
--- src/bin/pg_dump/pg_dumpall.c	4 Jun 2004 04:58:22 -0000
***************
*** 21,26 ****
--- 21,27 ----
  #include "strdup.h"
  #endif
  #include 
+ #include 
  
  #include "getopt_long.h"
  
***************
*** 219,226 ****
  
  	conn = connectDatabase("template1", pghost, pgport, pguser, force_password);
  
! 	printf("--\n");
! 	printf("-- PostgreSQL database cluster dump\n");
  	printf("--\n\n");
  	printf("\\connect \"template1\"\n\n");
  
--- 220,234 ----
  
  	conn = connectDatabase("template1", pghost, pgport, pguser, force_password);
  
! 	printf("--\n-- PostgreSQL database cluster dump\n");
! 	if (verbose)
! 	{
! 		char buf[256];
! 		time_t now = time(NULL);
! 
!  		if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
! 			printf("-- Started on %s\n", buf);
! 	}
  	printf("--\n\n");
  	printf("\\connect \"template1\"\n\n");
  
***************
*** 239,246 ****
  
  	PQfinish(conn);
  
! 	printf("--\n");
! 	printf("-- PostgreSQL database cluster dump complete\n");
  	printf("--\n\n");
  
  	exit(0);
--- 247,261 ----
  
  	PQfinish(conn);
  
! 	printf("--\n-- PostgreSQL database cluster dump complete\n");
! 	if (verbose)
! 	{
! 		char buf[256];
! 		time_t now = time(NULL);
! 
!  		if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
! 			printf("-- Completed on %s\n", buf);
! 	}
  	printf("--\n\n");
  
  	exit(0);

Re: [HACKERS] pg_dump --comment?

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
Tom Lane wrote:
> Bruce Momjian  writes:
> > The following patch adds start/stop times for pg_dump and pg_dumpall
> 
> What happens in a pg_dump -Fc / pg_restore scenario?

Uh, do those markers not appear in that dump type?  I thought they did.

I just tried pg_restore -v and while I see the lines, they are changing
every time I run it, meaning it isn't stored in the file but is showing
current time.  I looked in the dump file and I don't see the dates
either.

So, in non-ascii format, I need to dump a comment record and read it
back on restore?  Yuck.  Anyone want to tackle that?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] pg_dump --comment?

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
Tom Lane wrote:
> Bruce Momjian  writes:
> > Tom Lane wrote:
> >> What happens in a pg_dump -Fc / pg_restore scenario?
> 
> > So, in non-ascii format, I need to dump a comment record and read it
> > back on restore?  Yuck.  Anyone want to tackle that?
> 
> Yes.  Hacking the output routines alone is almost never the right way
> to do things in pg_dump.
> 
> My feeling is that if anyone cares to tackle this, it would be
> appropriate to add a record type that carries a user-supplied
> comment, and then people could do something like
> 	pg_dump --comment "generated on `date`"
> if they want to have a timestamp.  I don't want a timestamp put
> into the file by default, because that creates problems for
> comparing dump files.

OK, new patch.  This puts start/stop times into dumps if you use -v
during dumping.  It creates a new node type:
	
	--
	-- PostgreSQL database dump
	--
	
	SET client_encoding = 'SQL_ASCII';
	SET check_function_bodies = false;
	
	--
	-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner:
	--
	
	-- Started on 2004-06-05 00:41:25 EDT

	...

	--
	-- Name: DUMP TIMESTAMP; Type: DUMP TIMESTAMP; Schema: -; Owner:
	--
	
	-- Completed on 2004-06-05 00:44:28 EDT
	
	
	--
	-- PostgreSQL database dump complete
	--

One issue is that the system dumps certain settings into the output file
in ascii dumps, but pg_restore sets them on its own during the restore. 
Because the timestamp can't be done during pg_restore, it has to be a
special node.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.70
diff -c -c -r1.70 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml	31 May 2004 13:37:52 -0000	1.70
--- doc/src/sgml/ref/pg_dump.sgml	5 Jun 2004 04:22:15 -0000
***************
*** 403,409 ****
         
  	Specifies verbose mode.  This will cause
  	pg_dump to output detailed object
!         comments in the dump file, and progress messages to standard error.
         
        
       
--- 403,410 ----
         
  	Specifies verbose mode.  This will cause
  	pg_dump to output detailed object
!         comments in the dump file, start and stop times, and progress 
!         messages to standard error.
         
        
       
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.43
diff -c -c -r1.43 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml	29 Nov 2003 19:51:39 -0000	1.43
--- doc/src/sgml/ref/pg_dumpall.sgml	5 Jun 2004 04:22:15 -0000
***************
*** 192,199 ****
        
         
  	Specifies verbose mode.  This will cause
! 	pg_dumpall to print progress
! 	messages to standard error.
         
        
       
--- 192,200 ----
        
         
  	Specifies verbose mode.  This will cause
! 	pg_dumpall to output start and stop
!         times in the dump file, and progress messages to standard error.
!         It will also enable verbose output in pg_dump</>.
         
        
       
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.373
diff -c -c -r1.373 pg_dump.c
*** src/bin/pg_dump/pg_dump.c	3 Jun 2004 00:07:36 -0000	1.373
--- src/bin/pg_dump/pg_dump.c	5 Jun 2004 04:22:21 -0000
***************
*** 32,37 ****
--- 32,38 ----
  #ifdef HAVE_TERMIOS_H
  #include 
  #endif
+ #include 
  
  #ifndef HAVE_STRDUP
  #include "strdup.h"
***************
*** 163,168 ****
--- 164,170 ----
  static const char *fmtQualifiedId(const char *schema, const char *id);
  static int	dumpBlobs(Archive *AH, void *arg);
  static void dumpDatabase(Archive *AH);
+ static void dumpTimestamp(Archive *AH, char *msg);
  static void dumpEncoding(Archive *AH);
  static const char *getAttrName(int attrnum, TableInfo *tblInfo);
  static const char *fmtCopyColumnList(const TableInfo *ti);
***************
*** 598,603 ****
--- 600,608 ----
  	 * in a safe order.
  	 */
  
+ 	if (g_fout->verbose)
+ 		dumpTimestamp(g_fout, "Started on");
+ 
  	/* First the special encoding entry. */
  	dumpEncoding(g_fout);
  
***************
*** 615,620 ****
--- 620,628 ----
  		dumpDumpableObject(g_fout, dobjs[i]);
  	}
  
+ 	if (g_fout->verbose)
+ 		dumpTimestamp(g_fout, "Completed on");
+ 
  	/*
  	 * And finally we can do the actual output.
  	 */
***************
*** 1280,1285 ****
--- 1288,1322 ----
  	destroyPQExpBuffer(dbQry);
  	destroyPQExpBuffer(delQry);
  	destroyPQExpBuffer(creaQry);
+ }
+ 
+ 
+ /*
+  * dumpTimestamp
+  */
+ static void
+ dumpTimestamp(Archive *AH, char *msg)
+ {
+ 	char buf[256];
+ 	time_t now = time(NULL);
+ 
+ 	if (strftime(buf, 256, "%Y-%m-%d %H:%M:%S %Z", localtime(&now)) != 0)
+  	{
+ 		PQExpBuffer qry = createPQExpBuffer();
+ 	
+ 		appendPQExpBuffer(qry, "-- ");
+ 		appendPQExpBuffer(qry, msg);
+ 		appendPQExpBuffer(qry, " ");
+ 		appendPQExpBuffer(qry, buf);
+ 		appendPQExpBuffer(qry, "\n");
+ 
+ 		ArchiveEntry(AH, nilCatalogId, createDumpId(),
+ 					 "DUMP TIMESTAMP", NULL, "",
+ 					 false, "DUMP TIMESTAMP", qry->data, "", NULL,
+ 					 NULL, 0,
+ 					 NULL, NULL);
+ 		destroyPQExpBuffer(qry);
+ 	}
  }

Re: [HACKERS] pg_dump --comment?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bruce Momjian  writes:
> The following patch adds start/stop times for pg_dump and pg_dumpall

What happens in a pg_dump -Fc / pg_restore scenario?

			regards, tom lane

Re: [HACKERS] pg_dump --comment?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Bruce Momjian  writes:
> Tom Lane wrote:
>> What happens in a pg_dump -Fc / pg_restore scenario?

> So, in non-ascii format, I need to dump a comment record and read it
> back on restore?  Yuck.  Anyone want to tackle that?

Yes.  Hacking the output routines alone is almost never the right way
to do things in pg_dump.

My feeling is that if anyone cares to tackle this, it would be
appropriate to add a record type that carries a user-supplied
comment, and then people could do something like
	pg_dump --comment "generated on `date`"
if they want to have a timestamp.  I don't want a timestamp put
into the file by default, because that creates problems for
comparing dump files.

			regards, tom lane
FAQ