pg_dump - how to force to show timestamps in client log

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_dump - how to force to show timestamps in client log

durumdara
Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have timestamps.

PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.  


Thank you for any information you can provide!

dd


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Joshua Drake-2
Howdy,

pg_dump is just issuing SQL commands, you could turn on timestamps in your postgresql log.

JD

On Fri, Nov 20, 2020 at 10:02 AM Durumdara <[hidden email]> wrote:
Hello!

We need to log the pg_dump's state.
What objects are in copy, and what are the starting and ending times.

But when I try to redirect the output, the result doesn't have timestamps.

PG 11, on Windows.

As I see the -v option isn't enough to see the starting times.

For example:

2020-11-19 12:00:01.084 Dump table content table1
2020-11-19 12:03:12.932 Dump table content table2
...
etc.  


Thank you for any information you can provide!

dd


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Adrian Klaver-4
In reply to this post by durumdara
On 11/20/20 10:01 AM, Durumdara wrote:

> Hello!
>
> We need to log the pg_dump's state.
> What objects are in copy, and what are the starting and ending times.
>
> But when I try to redirect the output, the result doesn't have timestamps.
>
> PG 11, on Windows.
>
> As I see the -v option isn't enough to see the starting times.
>
> For example:
>
> 2020-11-19 12:00:01.084 Dump table content table1
> 2020-11-19 12:03:12.932 Dump table content table2
> ...
> etc.


If you are redirecting to a file it have the creation time that you can
use. Internally times don't really matter for the objects as the dump is
based on a snapshot. Said snapshot is based on visible transactions not
time. So for practical purposes they all occur at the same 'time'.

>
>
> Thank you for any information you can provide!
>
> dd
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Ron-2
On 11/20/20 2:56 PM, Adrian Klaver wrote:

> On 11/20/20 10:01 AM, Durumdara wrote:
>> Hello!
>>
>> We need to log the pg_dump's state.
>> What objects are in copy, and what are the starting and ending times.
>>
>> But when I try to redirect the output, the result doesn't have timestamps.
>>
>> PG 11, on Windows.
>>
>> As I see the -v option isn't enough to see the starting times.
>>
>> For example:
>>
>> 2020-11-19 12:00:01.084 Dump table content table1
>> 2020-11-19 12:03:12.932 Dump table content table2
>> ...
>> etc.
>
>
> If you are redirecting to a file it have the creation time that you can
> use. Internally times don't really matter for the objects as the dump is
> based on a snapshot. Said snapshot is based on visible transactions not
> time. So for practical purposes they all occur at the same 'time'.

It makes all the difference when monitoring the progress of a backup.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Adrian Klaver-4
On 11/20/20 1:00 PM, Ron wrote:

> On 11/20/20 2:56 PM, Adrian Klaver wrote:
>> On 11/20/20 10:01 AM, Durumdara wrote:
>>> Hello!
>>>
>>> We need to log the pg_dump's state.
>>> What objects are in copy, and what are the starting and ending times.
>>>
>>> But when I try to redirect the output, the result doesn't have
>>> timestamps.
>>>
>>> PG 11, on Windows.
>>>
>>> As I see the -v option isn't enough to see the starting times.
>>>
>>> For example:
>>>
>>> 2020-11-19 12:00:01.084 Dump table content table1
>>> 2020-11-19 12:03:12.932 Dump table content table2
>>> ...
>>> etc.
>>
>>
>> If you are redirecting to a file it have the creation time that you
>> can use. Internally times don't really matter for the objects as the
>> dump is based on a snapshot. Said snapshot is based on visible
>> transactions not time. So for practical purposes they all occur at the
>> same 'time'.
>
> It makes all the difference when monitoring the progress of a backup.
>

With -v you will get running list of objects dumped, just not the time.
The time is only of value relative to the following. Progress will only
be measurable by determining what is left to run and the time for each
object. Not sure that is feasible as you would have to pre-run the dump
to get information about the number of objects and an estimate of the
data quantity involved and the effect of each on the other. I could see
that estimate getting worse the bigger the data set(and hence the more
you cared) got. Because at some point the load on the machine would
affect the output speed of the dump.


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Ron-2
On 11/20/20 3:39 PM, Adrian Klaver wrote:

> On 11/20/20 1:00 PM, Ron wrote:
>> On 11/20/20 2:56 PM, Adrian Klaver wrote:
>>> On 11/20/20 10:01 AM, Durumdara wrote:
>>>> Hello!
>>>>
>>>> We need to log the pg_dump's state.
>>>> What objects are in copy, and what are the starting and ending times.
>>>>
>>>> But when I try to redirect the output, the result doesn't have timestamps.
>>>>
>>>> PG 11, on Windows.
>>>>
>>>> As I see the -v option isn't enough to see the starting times.
>>>>
>>>> For example:
>>>>
>>>> 2020-11-19 12:00:01.084 Dump table content table1
>>>> 2020-11-19 12:03:12.932 Dump table content table2
>>>> ...
>>>> etc.
>>>
>>>
>>> If you are redirecting to a file it have the creation time that you can
>>> use. Internally times don't really matter for the objects as the dump is
>>> based on a snapshot. Said snapshot is based on visible transactions not
>>> time. So for practical purposes they all occur at the same 'time'.
>>
>> It makes all the difference when monitoring the progress of a backup.
>>
>
> With -v you will get running list of objects dumped, just not the time.
> The time is only of value relative to the following. Progress will only be
> measurable by determining what is left to run and the time for each
> object. Not sure that is feasible as you would have to pre-run the dump to
> get information about the number of objects and an estimate of the data
> quantity involved and the effect of each on the other. I could see that
> estimate getting worse the bigger the data set(and hence the more you
> cared) got. Because at some point the load on the machine would affect the
> output speed of the dump.

By knowing the sizes of the tables, and how long it takes to takes the first
"some" tables, then one can forecast how long it takes to backup the whole
database.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Mark Johnson-2
This all sounds like a previous discussion on pg hackers about a progress meter for pg_dump.  Search the archives for that discussion.  Also, search the web for something like "pg_dump progress meter" and you'll get a few suggestions like pipe to pv, although that does not appear to work with all of the file formats supported by pg_dump.

What do you see in pg_stat_activity?  It's been a while since I tried to monitor a running pg_dump.  

 Also, if you redirect the output to a file then doesn't the file's timestamp get updated each time something happens.  (That's what I used to do with Oracle before they added timestamps).  

On Fri, Nov 20, 2020 at 5:00 PM Ron <[hidden email]> wrote:
On 11/20/20 3:39 PM, Adrian Klaver wrote:
> On 11/20/20 1:00 PM, Ron wrote:
>> On 11/20/20 2:56 PM, Adrian Klaver wrote:
>>> On 11/20/20 10:01 AM, Durumdara wrote:
>>>> Hello!
>>>>
>>>> We need to log the pg_dump's state.
>>>> What objects are in copy, and what are the starting and ending times.
>>>>
>>>> But when I try to redirect the output, the result doesn't have timestamps.
>>>>
>>>> PG 11, on Windows.
>>>>
>>>> As I see the -v option isn't enough to see the starting times.
>>>>
>>>> For example:
>>>>
>>>> 2020-11-19 12:00:01.084 Dump table content table1
>>>> 2020-11-19 12:03:12.932 Dump table content table2
>>>> ...
>>>> etc.
>>>
>>>
>>> If you are redirecting to a file it have the creation time that you can
>>> use. Internally times don't really matter for the objects as the dump is
>>> based on a snapshot. Said snapshot is based on visible transactions not
>>> time. So for practical purposes they all occur at the same 'time'.
>>
>> It makes all the difference when monitoring the progress of a backup.
>>
>
> With -v you will get running list of objects dumped, just not the time.
> The time is only of value relative to the following. Progress will only be
> measurable by determining what is left to run and the time for each
> object. Not sure that is feasible as you would have to pre-run the dump to
> get information about the number of objects and an estimate of the data
> quantity involved and the effect of each on the other. I could see that
> estimate getting worse the bigger the data set(and hence the more you
> cared) got. Because at some point the load on the machine would affect the
> output speed of the dump.

By knowing the sizes of the tables, and how long it takes to takes the first
"some" tables, then one can forecast how long it takes to backup the whole
database.

--
Angular momentum makes the world go 'round.


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Ron-2
TBH, it's also been a while since I needed to monitor pg_dump, since we now use pgbackrest (which just so happens to have a progress meter...).

On 11/20/20 4:32 PM, Mark Johnson wrote:
This all sounds like a previous discussion on pg hackers about a progress meter for pg_dump.  Search the archives for that discussion.  Also, search the web for something like "pg_dump progress meter" and you'll get a few suggestions like pipe to pv, although that does not appear to work with all of the file formats supported by pg_dump.

What do you see in pg_stat_activity?  It's been a while since I tried to monitor a running pg_dump.  

 Also, if you redirect the output to a file then doesn't the file's timestamp get updated each time something happens.  (That's what I used to do with Oracle before they added timestamps).  

On Fri, Nov 20, 2020 at 5:00 PM Ron <[hidden email]> wrote:
On 11/20/20 3:39 PM, Adrian Klaver wrote:
> On 11/20/20 1:00 PM, Ron wrote:
>> On 11/20/20 2:56 PM, Adrian Klaver wrote:
>>> On 11/20/20 10:01 AM, Durumdara wrote:
>>>> Hello!
>>>>
>>>> We need to log the pg_dump's state.
>>>> What objects are in copy, and what are the starting and ending times.
>>>>
>>>> But when I try to redirect the output, the result doesn't have timestamps.
>>>>
>>>> PG 11, on Windows.
>>>>
>>>> As I see the -v option isn't enough to see the starting times.
>>>>
>>>> For example:
>>>>
>>>> 2020-11-19 12:00:01.084 Dump table content table1
>>>> 2020-11-19 12:03:12.932 Dump table content table2
>>>> ...
>>>> etc.
>>>
>>>
>>> If you are redirecting to a file it have the creation time that you can
>>> use. Internally times don't really matter for the objects as the dump is
>>> based on a snapshot. Said snapshot is based on visible transactions not
>>> time. So for practical purposes they all occur at the same 'time'.
>>
>> It makes all the difference when monitoring the progress of a backup.
>>
>
> With -v you will get running list of objects dumped, just not the time.
> The time is only of value relative to the following. Progress will only be
> measurable by determining what is left to run and the time for each
> object. Not sure that is feasible as you would have to pre-run the dump to
> get information about the number of objects and an estimate of the data
> quantity involved and the effect of each on the other. I could see that
> estimate getting worse the bigger the data set(and hence the more you
> cared) got. Because at some point the load on the machine would affect the
> output speed of the dump.

By knowing the sizes of the tables, and how long it takes to takes the first
"some" tables, then one can forecast how long it takes to backup the whole
database.

--
Angular momentum makes the world go 'round.



--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump - how to force to show timestamps in client log

Peter J. Holzer
In reply to this post by durumdara
On 2020-11-20 19:01:47 +0100, Durumdara wrote:

> We need to log the pg_dump's state.
> What objects are in copy, and what are the starting and ending times.
>
> But when I try to redirect the output, the result doesn't have timestamps.
>
> PG 11, on Windows.
>
> As I see the -v option isn't enough to see the starting times.
>
> For example:
>
> 2020-11-19 12:00:01.084 Dump table content table1
> 2020-11-19 12:03:12.932 Dump table content table2
You can pipe the output through a timestamping tool like this:

pg_dump --compress=5 -v -Fd -f "$name.$$" $db 2>&1 | ts > log/"$name".$(isodate).log

ts is available here: https://github.com/hjp/simple/tree/master/ts

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [hidden email]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

signature.asc (849 bytes) Download Attachment