Re: [GENERAL] How to reduce pg_dump backup time

[Scott Mead]

On Tue, Oct 6, 2015 at 8:54 AM, Adrian Klaver 
wrote:

> On 10/05/2015 09:52 PM, Sachin Srivastava wrote:
>
>> Dear Team,
>>
>> I am using PostgreSQL 9.1 on Redhat Linux on my Production environment.
>> My database size is 680 GB and it take 7 hour for completion the pg_dump
>> backup.
>>
>> I want that my pg_dump backup should be fast and take less time.
>>
>> In PostgresQL 9.3 there is “ -j /njobs” /option is available for fast
>> pg_dump backup.
>>
>> There is any way, that I will use *“ **-j /njobs”/*/option /in
>> “PostgreSQL 9.1 “ so that my backup is completed in less time or any
>> other way in ”PostgreSQL 9.1” to take the backup fast. Kindly suggest?
>>
>
> In addition to Scott's suggestions have you looked at replication?:
>
> http://www.postgresql.org/docs/9.1/interactive/high-availability.html
>
> Basically, continuous backup.
>

+1 - It doesn't actually lower the base backup time, but, there's little
overhead for archiving (other than storage space) and restoration is just
starting an instance and replaying a few logs.  *Much* faster.  You can use
a tool like WAL-E or barman to build out a nice strategy.

   At the end of the day, you'll still want to do a periodic, logical
backup (make sure your phyiscal backups are sane), but, using physical
backups will open you up to many more options.

--
Scott Mead
Sr. Architect
*OpenSCG*
http://www.openscg.com 



>> *[root]# lsb_release –a*
>>
>> **
>>
>> LSB
>>
>> Version::base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
>>
>> Distributor ID: RedHatEnterpriseServer
>>
>> Description:Red Hat Enterprise Linux Server release 6.7 (Santiago)
>>
>> Release:6.7
>>
>> Codename:Santiago
>>
>> *[root@CPPMOMA_DB01 even]# psql*
>>
>> Password:
>>
>> psql.bin (9.1.2)
>>
>> Type "help" for help.
>>
>> postgres=# select version();
>>
>> version
>>
>>
>> ---
>>
>> PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
>> 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit
>>
>> (1 row)
>>
>> postgres=#
>>
>> *Regards,*
>>
>> Sachin Srivastava
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] How to reduce pg_dump backup time

[Adrian Klaver]

On 10/05/2015 09:52 PM, Sachin Srivastava wrote:

Dear Team,

I am using PostgreSQL 9.1 on Redhat Linux on my Production environment.
My database size is 680 GB and it take 7 hour for completion the pg_dump
backup.

I want that my pg_dump backup should be fast and take less time.

In PostgresQL 9.3 there is “ -j /njobs” /option is available for fast
pg_dump backup.

There is any way, that I will use *“ **-j /njobs”/*/option /in
“PostgreSQL 9.1 “ so that my backup is completed in less time or any
other way in ”PostgreSQL 9.1” to take the backup fast. Kindly suggest?


In addition to Scott's suggestions have you looked at replication?:

http://www.postgresql.org/docs/9.1/interactive/high-availability.html

Basically, continuous backup.



*[root]# lsb_release –a*

**

LSB
Version::base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: RedHatEnterpriseServer

Description:Red Hat Enterprise Linux Server release 6.7 (Santiago)

Release:6.7

Codename:Santiago

*[root@CPPMOMA_DB01 even]# psql*

Password:

psql.bin (9.1.2)

Type "help" for help.

postgres=# select version();

version

---

PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit

(1 row)

postgres=#

*Regards,*

Sachin Srivastava



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How to reduce pg_dump backup time

[Scott Mead]

> On Oct 6, 2015, at 00:52, Sachin Srivastava  wrote:
> 
> Dear Team,
> 
>  
> 
> I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My 
> database size is 680 GB and it take 7 hour for completion the pg_dump backup.
> 
>  
> 
> I want that my pg_dump backup should be fast and take less time.
> 
>  
> 
> In PostgresQL 9.3 there is “ -j njobs” option is available for fast pg_dump 
> backup.
> 
>  
> 
> There is any way, that I will use “ -j njobs” option in “PostgreSQL 9.1 “ so 
> that my backup is completed in less time or any other way in ”PostgreSQL 9.1” 
> to take the backup fast. Kindly suggest?
> 
>  
> 
If you're using compression, disable it. It'll require more space, but would be 
faster. Honestly, you should upgrade to a newer version, 9.1 is EOL in slightly 
less than 1 year. 

  For the upgrade, you can use a newer version of pg_dump. I'm not sure if the 
parallel option would be available against a 9.1 server (but it's worth a try). 
I wouldn't try restoring that dump to a 9.1 server, just use it to upgrade to 
9.4. 


>  
> 
> [root]# lsb_release –a
> 
>  
> 
> LSB Version:
> :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
> 
> Distributor ID: RedHatEnterpriseServer
> 
> Description:Red Hat Enterprise Linux Server release 6.7 (Santiago)
> 
> Release:6.7
> 
> Codename:   Santiago
> 
>  
> 
> [root@CPPMOMA_DB01 even]# psql
> 
> Password:
> 
> psql.bin (9.1.2)
> 
> Type "help" for help.
> 
>  
> 
> postgres=# select version();
> 
> version
> 
> ---
> 
>  PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 
> 20080704 (Red Hat 4.1.2-46), 64-bit
> 
> (1 row)
> 
>  
> 
> postgres=#
> 
>  
> 
>  
> 
>  
> 
>  
> 
> Regards,
> 
> Sachin Srivastava

[GENERAL] How to reduce pg_dump backup time

[Sachin Srivastava]

Dear Team,



I am using PostgreSQL 9.1 on Redhat Linux on my Production environment. My
database size is 680 GB and it take 7 hour for completion the pg_dump
backup.



I want that my pg_dump backup should be fast and take less time.



In PostgresQL 9.3 there is “ -j *njobs” *option is available for fast
pg_dump backup.



There is any way, that I will use *“ **-j njobs”** option *in “PostgreSQL
9.1 “ so that my backup is completed in less time or any other way in
”PostgreSQL 9.1” to take the backup fast. Kindly suggest?





*[root]# lsb_release –a*



LSB Version:
:base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch

Distributor ID: RedHatEnterpriseServer

Description:Red Hat Enterprise Linux Server release 6.7 (Santiago)

Release:6.7

Codename:   Santiago



*[root@CPPMOMA_DB01 even]# psql*

Password:

psql.bin (9.1.2)

Type "help" for help.



postgres=# select version();

version

---

 PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-46), 64-bit

(1 row)



postgres=#









*Regards,*
Sachin Srivastava