No title

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

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.

What is size of table?

I m having two Database example

01. Cricket 320G
02.badminton 250G

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

On Tue, 26 Jul, 2022, 20:34 Adrian Klaver, <adrian.klaver@aklaver.com>
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?

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

śr., 27 lip 2022 o 08:08 hubert depesz lubaczewski <depesz@depesz.com>
napisał(a):

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

I believe it's worth to look at this project:
https://github.com/dimitri/pgcopydb since it is trying to solve exactly
this problem

pozdrawiam,
best regards,
mit freundlichen Grüßen,
Alicja Kucharczyk
*Warsaw PostgreSQL User Group*
<https://www.meetup.com/pl-PL/Warsaw-PostgreSQL-Users-Group/&gt;