Postgres Pro
Downloads
Home   >   mailing lists

Thread: 20220221-Clarification regarding PostgeSQL DB backup

20220221-Clarification regarding PostgeSQL DB backup

From
"Techsupport"
Date:

Hi Team,                

 

We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has  Half Billion records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup

 

Thanks,

Karthick Ramu

Re: 20220221-Clarification regarding PostgeSQL DB backup

From
Daevor The Devoted
Date:
Hi Karthick

I'm a little rusty on PG (it's been about a decade since I last worked intensely with it), but I seem to remember that restoring with indexes can be very slow. The faster approach is to restore without indexes, and then create the indexes once the data restore is complete.

My knowledge may be outdated, so best to check with others that have more recent knowledge. However, it's probably worth investigating in the meantime.

Okay, I just had a quick look at the documentation, and it seems there is an option to run multiple concurrent jobs for the time-consuming parts of a restore (which includes index creation):

-j number-of-jobs

see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check the docs for whatever PG version you have)

Good luck!

~~Na-iem Dollie

On Mon, Feb 21, 2022 at 6:59 AM Techsupport <techsupport@sardonyx.in> wrote:

Hi Team,                

 

We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has  Half Billion records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup

 

Thanks,

Karthick Ramu

Re: 20220221-Clarification regarding PostgeSQL DB backup

From
Ron
Date:
On 2/20/22 10:59 PM, Techsupport wrote:
@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}@font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0in; margin-bottom:.0001pt; font-size:11.0pt; font-family:"Calibri","sans-serif";}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}span.EmailStyle17 {mso-style-type:personal-compose; font-family:"Calibri","sans-serif"; color:windowtext;}.MsoChpDefault {mso-style-type:export-only; font-family:"Calibri","sans-serif";}div.WordSection1 {page:WordSection1;}

Hi Team,                

 

We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has  Half Billion records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup


Please show us the full pg_dump command, including all parameters.

--
Angular momentum makes the world go 'round.

RE: 20220221-Clarification regarding PostgeSQL DB backup

From
"Techsupport"
Date:

Hi ~~Na-iem Dollie,

 

Thanks  for your reply,

 

In SQL Server there is an option to take Full backup and Differential Backup for a particular database.

 

Like that, we need to take the differential Backup in PostgreSQL. This is my primary need.

 

Thanks,

Karthick Ramu

 

 

From: Daevor The Devoted [mailto:dollien@gmail.com]
Sent: Monday, February 21, 2022 11:05 AM
To: Techsupport
Cc: pgsql-generallists.postgresql.org
Subject: Re: 20220221-Clarification regarding PostgeSQL DB backup

 

Hi Karthick

 

I'm a little rusty on PG (it's been about a decade since I last worked intensely with it), but I seem to remember that restoring with indexes can be very slow. The faster approach is to restore without indexes, and then create the indexes once the data restore is complete.

 

My knowledge may be outdated, so best to check with others that have more recent knowledge. However, it's probably worth investigating in the meantime.

 

Okay, I just had a quick look at the documentation, and it seems there is an option to run multiple concurrent jobs for the time-consuming parts of a restore (which includes index creation):

 

-j number-of-jobs

 

see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check the docs for whatever PG version you have)

 

Good luck!

 

~~Na-iem Dollie

 

On Mon, Feb 21, 2022 at 6:59 AM Techsupport <techsupport@sardonyx.in> wrote:

Hi Team,                

 

We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has  Half Billion records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup

 

Thanks,

Karthick Ramu

Re: 20220221-Clarification regarding PostgeSQL DB backup

From
Daevor The Devoted
Date:
Hi Karthick

In that case, your are options are:

This is "like" using diff backups, but not quite the same.

OR

2. Use a 3rd party app, for example Barman or pgBackRest

Good luck!

~~Na-iem Dollie

On Mon, Feb 21, 2022 at 8:25 AM Techsupport <techsupport@sardonyx.in> wrote:

Hi ~~Na-iem Dollie,

 

Thanks  for your reply,

 

In SQL Server there is an option to take Full backup and Differential Backup for a particular database.

 

Like that, we need to take the differential Backup in PostgreSQL. This is my primary need.

 

Thanks,

Karthick Ramu

 

 

From: Daevor The Devoted [mailto:dollien@gmail.com]
Sent: Monday, February 21, 2022 11:05 AM
To: Techsupport
Cc: pgsql-generallists.postgresql.org
Subject: Re: 20220221-Clarification regarding PostgeSQL DB backup

 

Hi Karthick

 

I'm a little rusty on PG (it's been about a decade since I last worked intensely with it), but I seem to remember that restoring with indexes can be very slow. The faster approach is to restore without indexes, and then create the indexes once the data restore is complete.

 

My knowledge may be outdated, so best to check with others that have more recent knowledge. However, it's probably worth investigating in the meantime.

 

Okay, I just had a quick look at the documentation, and it seems there is an option to run multiple concurrent jobs for the time-consuming parts of a restore (which includes index creation):

 

-j number-of-jobs

 

see: https://www.postgresql.org/docs/current/app-pgrestore.html (or check the docs for whatever PG version you have)

 

Good luck!

 

~~Na-iem Dollie

 

On Mon, Feb 21, 2022 at 6:59 AM Techsupport <techsupport@sardonyx.in> wrote:

Hi Team,                

 

We have used PG_DUMP to take backup of particular database from the PostgreSQL Server. It takes too long to restore the databases , which has  Half Billion records (almost 8 Hour)

 

My primary need is to make the Differential and Incremental backup on the Windows Server. When I search there is a tool Barman and PG_BackRest is available. But that will be supported only for Linux based servers only

 

Please suggest me a tool to make differential backup

 

Thanks,

Karthick Ramu