Thread:

On 7/26/22 06:27, Rama Krishnan wrote:
> Hi all,
> 
> How to take a table backup using directory format?

pg_dump -d <some_db> -U <some_user>  -t <the_table> -Fd -f <new_dir>

> I am having huge size of table when I am using a pg_dump it tooks more 
> time kindly suggest me

Not sure what the above means, so:

What is size of table?

What sort of time interval are you seeing?

What problem is it causing?

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/26/22 08:15, Rama Krishnan wrote:
> Hi Adrian
> 
> 
> Thanks for your reply,
> 
> My actual db size was 320G while I am taking custom format and moving 
> into directly S3 it took more than one day so I am trying to use 
> directory format because  parllel option (-j option) supports ik 
> directory format.

Is the database in AWS also or is it locally hosted?

In either case what is the network distance that the data has to cross?

What is the network speed of the slowest link?

> 
> What is size of table?
> 
> I m having two Database example
> 
> 01. Cricket 320G
> 02.badminton 250G

So you are talking about an entire database not a single table, correct?

> 
> What sort of time interval are you seeing?
> 
> I am doing purge data to keep 1 year data n db more than year data I am 
> going yo take dump backup for future reports purpose.
> 
> What problem is it causing?
> 
> The normal custom format backup took more than day
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/26/22 10:22, Adrian Klaver wrote:
> On 7/26/22 08:15, Rama Krishnan wrote:
>> Hi Adrian
>>
>>
>> Thanks for your reply,
>>
>> My actual db size was 320G while I am taking custom format and moving 
>> into directly S3 it took more than one day so I am trying to use 
>> directory format because  parllel option (-j option) supports ik 
>> directory format.
>
> Is the database in AWS also or is it locally hosted?
>
> In either case what is the network distance that the data has to cross?
>
> What is the network speed of the slowest link?
>
>>
>> What is size of table?
>>
>> I m having two Database example
>>
>> 01. Cricket 320G
>> 02.badminton 250G
>
> So you are talking about an entire database not a single table, correct?

In a private email, he said that this is what he's trying:
Pg_dump -h endpoint -U postgres Fd - d cricket | aws cp - s3://dump/cricket.dump

It failed for obvious reasons.


-- 
Angular momentum makes the world go 'round.

On 7/26/22 9:29 AM, Ron wrote:
> On 7/26/22 10:22, Adrian Klaver wrote:
>> On 7/26/22 08:15, Rama Krishnan wrote:
>>> Hi Adrian
>>>
>>>

>>> What is size of table?
>>>
>>> I m having two Database example
>>>
>>> 01. Cricket 320G
>>> 02.badminton 250G
>>
>> So you are talking about an entire database not a single table, correct?
> 
> In a private email, he said that this is what he's trying:
> Pg_dump -h endpoint -U postgres Fd - d cricket | aws cp - 
> s3://dump/cricket.dump
> 
> It failed for obvious reasons.


 From what I gather it did not fail, it just took a long time. Not sure 
adding -j to the above will improve things, pretty sure the choke point 
is still going to be aws cp.

Rama if you have the space would it not be better to dump locally using 
-Fc to get a compressed format and the upload that to s3 as a separate 
process?


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Tue, Jul 26, 2022 at 10:48:47AM -0700, Adrian Klaver wrote:
> On 7/26/22 9:29 AM, Ron wrote:
> > On 7/26/22 10:22, Adrian Klaver wrote:
> > > On 7/26/22 08:15, Rama Krishnan wrote:
> > > > Hi Adrian
> > > > 
> > > > 
> 
> > > > What is size of table?
> > > > 
> > > > I m having two Database example
> > > > 
> > > > 01. Cricket 320G
> > > > 02.badminton 250G
> > > 
> > > So you are talking about an entire database not a single table, correct?
> > 
> > In a private email, he said that this is what he's trying:
> > Pg_dump -h endpoint -U postgres Fd - d cricket | aws cp -
> > s3://dump/cricket.dump
> > 
> > It failed for obvious reasons.
> From what I gather it did not fail, it just took a long time. Not sure
> adding -j to the above will improve things, pretty sure the choke point is
> still going to be aws cp.

It's really hard to say what is happening, because the command, as shown
wouldn't even work.

Starting from Pg_dump vs. pg_dump, space between `-` and `d`, "Fd" as
argument, or even the idea that you *can* make -Fd dumps to stdout and
pass it to aws cp.

depesz