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.

If pg_dump had a --comment flag that allowed me to pass a string that 
would be stored inside the dumpfile, that I could then retrieve in some 
way (without doing a full restore of the dump), that would meet my 
needs. In discussing this idea with other people, it sounded like a 
general-use feature that mankind as a whole could benefit from. :)

Here's what I'm envisioning:

    pg_dump --comment 'This is a comment' <more pg_dump args>

That would store the comment ('This is a comment') in the dump file 
somehow. The definition of "somehow" would vary depending on the output 
format (text, tar, or custom). Initially, since I only use the custom 
format, I would only focus on getting it to work with that. But for the 
text format, there could be a SQL comment at the top of the file with

-- COMMENT: This is a comment

or something. In the tar format, there could be a "comment" file in the 
archive that contains the text "This is a comment".

For the custom format...I haven't looked at the format specification, so 
I don't know exactly where the comment would go. It could go at the very 
top of the file, and have a special delimiter after it. pg_restore would 
just skim over the file until the delimiter is reached, and then go on 
about its business. The benefit of this scheme is that any program could 
read the comment -- just open a file and read the bytes until the delimiter.

There could also be a pg_dump or pg_restore option that prints out the 
comment stored in a given dump file, or another binary (pg_comment?) 
that does that.

Is this a desirable feature? Should I work it up like described and 
submit a patch? Any comments/suggestions?

Thanks!

- Chris
Thu, 27 May 2004 22:10:14 -0400
Chris Campbell <chris@bignerdranch.com>
pg_dump --comment?
> 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...

Chris


Fri, 28 May 2004 10:38:13 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: pg_dump --comment?
In article <40B69F86.2020002@bignerdranch.com>,
Chris Campbell <chris@bignerdranch.com> writes:

> 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.

> If pg_dump had a --comment flag that allowed me to pass a string that
> would be stored inside the dumpfile, that I could then retrieve in
> some way (without doing a full restore of the dump), that would meet
> my needs. In discussing this idea with other people, it sounded like a
> general-use feature that mankind as a whole could benefit from. :)

Why don't you just do

  ( echo "-- This is my comment"
    pg_dump whatever
  ) > dumpfile

?


28 May 2004 12:55:59 +0200
Harald Fuchs <hf517@protecting.net>
Re: pg_dump --comment?
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.

Jon

Fri, 28 May 2004 14:15:38 +0000 (UTC)
Jon Jensen <jon@endpoint.com>
Re: pg_dump --comment?
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.
>
>  
>

That makes sense.

A better answer to this particular problem might be incremental dumps, 
though :-)

cheers

andrew

Fri, 28 May 2004 10:48:56 -0400
Andrew Dunstan <andrew@dunslane.net>
Re: pg_dump --comment?

On May 28, 2004, at 10:48 AM, Andrew Dunstan wrote:

> A better answer to this particular problem might be incremental dumps, 
> though :-)
>

Oh would incremental dumps be ever so hungrily accepted by ever so many 
shops. I had imagined that PITR transaction log archiving would allow 
one to perform an equivalent to "repay initial full data dump", then 
foreach transaction log, replay. If no writes have occurred, then the 
transaction log would be small / nongrowing, right?

For example, we perform a full dump hourly, scp to a backup db box who 
then imports it in its entirety, giving us, on average, a half-hour's 
worth of potential data lossage at any given moment in time if the 
primary box goes horribly bad. With current database size / machine 
speed, etc., this process takes ~8 minutes, so we're not sweating it. 
But we know that the vast majority of the data did not change in the 
past hour, so the majority of that work was not truly necessary.

With PITR log archiving, could we kick off this transfer + replay 
activity on the second box to ultimately just ship deltas? I suspect 
that we could not get such fixed-time guarantees anymore, since any 
individual transaction log file would not become full until, well, 
enough writes have occurred to push it over the edge, so our 'data 
hanging in the wind not yet on the backup box' becomes a function of 
'most recent N Kbytes of changed data'. By lowering transaction segment 
size (or whatever controls the size of individual transaction log 
files) we could force archiving to occur more / less frequently 
according to write traffic, right? Poor man's (very) async replication.

If all of this is correct, kudos to the forward-thinkers involved with 
PITR and the abstract archive transaction log protocol. If my proposed 
usage is flawed, then negative kudos to my puny mind.

----
James Robinson
Socialserve.com


Fri, 28 May 2004 11:15:28 -0400
James Robinson <jlrobins@socialserve.com>
Re: pg_dump --comment?
At 12:15 AM 29/05/2004, Jon Jensen wrote:
>However, I would like to see an option to include the timestamp if someone
>wants it.

The timestamp is already in the header for custom dumps. Have a look at the 
first few lines from a 'pg_restore -l'.

In terms of general comments, there is already a 'WARNING' type TOC entry 
for custom dumps. This emits a warning message when pg_restore is called.

A similar mechanism could be used to dump SQL comments. eg. new TOC type of 
'SQL_COMMENT' with a defn starting with '--' would work with old & new 
pg_dump versions. It might be a good idea to investigate the likely uses 
for these comments, since the TOC entries may be suppressed depending on 
whether they are deemed to be part of the schema or the data. Once the 
modes of use are defined, a better mechanism may become apparent.





----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp.mit.edu:11371       |/ 


Sat, 29 May 2004 01:58:46 +1000
Philip Warner <pjw@rhyme.com.au>
Re: pg_dump --comment?
This can be accomplished with 10-20 lines of shell scripting. I don't 
think it's worth the trouble bloating pg_dump with it at all.


Jan

On 5/27/2004 10:10 PM, Chris Campbell 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.
> 
> If pg_dump had a --comment flag that allowed me to pass a string that 
> would be stored inside the dumpfile, that I could then retrieve in some 
> way (without doing a full restore of the dump), that would meet my 
> needs. In discussing this idea with other people, it sounded like a 
> general-use feature that mankind as a whole could benefit from. :)
> 
> Here's what I'm envisioning:
> 
>     pg_dump --comment 'This is a comment' <more pg_dump args>
> 
> That would store the comment ('This is a comment') in the dump file 
> somehow. The definition of "somehow" would vary depending on the output 
> format (text, tar, or custom). Initially, since I only use the custom 
> format, I would only focus on getting it to work with that. But for the 
> text format, there could be a SQL comment at the top of the file with
> 
> -- COMMENT: This is a comment
> 
> or something. In the tar format, there could be a "comment" file in the 
> archive that contains the text "This is a comment".
> 
> For the custom format...I haven't looked at the format specification, so 
> I don't know exactly where the comment would go. It could go at the very 
> top of the file, and have a special delimiter after it. pg_restore would 
> just skim over the file until the delimiter is reached, and then go on 
> about its business. The benefit of this scheme is that any program could 
> read the comment -- just open a file and read the bytes until the delimiter.
> 
> There could also be a pg_dump or pg_restore option that prints out the 
> comment stored in a given dump file, or another binary (pg_comment?) 
> that does that.
> 
> Is this a desirable feature? Should I work it up like described and 
> submit a patch? Any comments/suggestions?
> 
> Thanks!
> 
> - Chris


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Mon, 31 May 2004 09:01:39 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: pg_dump --comment?
Jan Wieck wrote:
> This can be accomplished with 10-20 lines of shell scripting. I don't 
> think it's worth the trouble bloating pg_dump with it at all.

Considering how much bloat -v adds, adding a datestamp to it is trivial.
 -v outputs things like:
	
	--
	-- TOC entry 1449 (class 0 OID 0)
	-- Dependencies: 4
	-- Name: public; Type: ACL; Schema: -; Owner: postgres
	--

so adding a timestamp at the top is probably a good extension.

Actually, the pg_dump -v docs are incorrect because they only mention
additional stderr output, not the additional comments.  I updated the
text to:

        Specifies verbose mode.  This will cause
        <application>pg_dump</application> to output detailed object
        comments in the dump file, and progress messages to standard error.

This -v functionality controlling comments is new in 7.5.

-- 
  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

Mon, 31 May 2004 09:37:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: pg_dump --comment?
Harald Fuchs wrote:

> Why don't you just do
> 
>   ( echo "-- This is my comment"
>     pg_dump whatever
>   ) > dumpfile
> 
> ?

How could I dump using the custom format, and then use "dumpfile" with 
pg_restore to restore the dump? If I just prepend the comment to the 
file, then pg_restore will choke, since the file won't be in the proper 
custom format. I would have to remove the comment before sending the 
file to pg_restore. Is there an easy way to do that? That can be easily 
automated, and not take a huge amount of time given a 4 gig dump file 
that must be modified?

Given those requirements, building a commenting mechanism into the 
custom format would work out very nicely, I think.

Thanks!

- Chris
Mon, 31 May 2004 19:14:08 -0400
Chris Campbell <chris@bignerdranch.com>
Re: pg_dump --comment?
In article <40BBBC40.3040804@bignerdranch.com>,
Chris Campbell <chris@bignerdranch.com> writes:

> Harald Fuchs wrote:
>> Why don't you just do
>> ( echo "-- This is my comment"
>> pg_dump whatever
>> ) > dumpfile
>> ?

> How could I dump using the custom format, and then use "dumpfile" with
> pg_restore to restore the dump? If I just prepend the comment to the
> file, then pg_restore will choke, since the file won't be in the
> proper custom format. I would have to remove the comment before
> sending the file to pg_restore. Is there an easy way to do that? That
> can be easily automated, and not take a huge amount of time given a 4
> gig dump file that must be modified?

Since pg_restore is able to read from standard input, that should not
be a problem: instead of

  pg_restore options dumpfile

just do

  sed 1d dumpfile | pg_restore options


01 Jun 2004 10:10:38 +0200
Harald Fuchs <hf517@protecting.net>
Re: pg_dump --comment?
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 ****
         <para>
  	Specifies verbose mode.  This will cause
  	<application>pg_dump</application> to output detailed object
!         comments in the dump file, and progress messages to standard error.
         </para>
        </listitem>
       </varlistentry>
--- 403,410 ----
         <para>
  	Specifies verbose mode.  This will cause
  	<application>pg_dump</application> to output detailed object
!         comments in the dump file, start and stop times, and progress 
!         messages to standard error.
         </para>
        </listitem>
       </varlistentry>
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 ****
        <listitem>
         <para>
  	Specifies verbose mode.  This will cause
! 	<application>pg_dumpall</application> to print progress
! 	messages to standard error.
         </para>
        </listitem>
       </varlistentry>
--- 192,200 ----
        <listitem>
         <para>
  	Specifies verbose mode.  This will cause
! 	<application>pg_dumpall</application> to output start and stop
!         times in the dump file, and progress messages to standard error.
!         It will also enable verbose output in <application>pg_dump</>.
         </para>
        </listitem>
       </varlistentry>
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 <ctype.h>
  #include <errno.h>
+ #include <time.h>
  #include <unistd.h>
  
  #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 <errno.h>
+ #include <time.h>
  
  #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);
Fri, 4 Jun 2004 01:03:20 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] pg_dump --comment?
Bruce Momjian <pgman@candle.pha.pa.us> 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

Fri, 04 Jun 2004 01:10:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pg_dump --comment? 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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

Fri, 4 Jun 2004 09:24:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] pg_dump --comment?
Bruce Momjian <pgman@candle.pha.pa.us> 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

Fri, 04 Jun 2004 09:43:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] pg_dump --comment? 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> 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 ****
         <para>
  	Specifies verbose mode.  This will cause
  	<application>pg_dump</application> to output detailed object
!         comments in the dump file, and progress messages to standard error.
         </para>
        </listitem>
       </varlistentry>
--- 403,410 ----
         <para>
  	Specifies verbose mode.  This will cause
  	<application>pg_dump</application> to output detailed object
!         comments in the dump file, start and stop times, and progress 
!         messages to standard error.
         </para>
        </listitem>
       </varlistentry>
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 ****
        <listitem>
         <para>
  	Specifies verbose mode.  This will cause
! 	<application>pg_dumpall</application> to print progress
! 	messages to standard error.
         </para>
        </listitem>
       </varlistentry>
--- 192,200 ----
        <listitem>
         <para>
  	Specifies verbose mode.  This will cause
! 	<application>pg_dumpall</application> to output start and stop
!         times in the dump file, and progress messages to standard error.
!         It will also enable verbose output in <application>pg_dump</>.
         </para>
        </listitem>
       </varlistentry>
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 <termios.h>
  #endif
+ #include <time.h>
  
  #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);
+ 	}
  }
Sat, 5 Jun 2004 00:57:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] pg_dump --comment?