Thread:
Hi,
How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use this is for 9.1 database.
My database size is 820 GB and it’s taking 7 hours to complete.
Postgres Version: 9.1.2
PogtGIS: 1.5
Regards,
Sachin Srivastava |
On 05/15/2015 02:46 PM, Sachin Srivastava wrote: > > Hi, > > How can I fast my daily pg_dump backup. Can I use parallel > option(Which is introduced in Postgres 9.3) with Postgres 9.1. There > is any way I can use this is for 9.1 database. > IMHO, if has been introduced in 9.3, it is not in 9.1, unless you find some official backport. > My database size is 820 GB and it’s taking 7 hours to complete. > You need to provide some storage information and spécification. With (most) bare filesystem operation such as "dd": how much IO rate do you get? Cheers.
On Fri, May 15, 2015 at 8:54 PM, Mihamina Rakotomandimby <mihamina.rakotomandimby@rktmb.org> wrote: > On 05/15/2015 02:46 PM, Sachin Srivastava wrote: >> How can I fast my daily pg_dump backup. Can I use parallel option(Which is >> introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use >> this is for 9.1 database. >> > > IMHO, if has been introduced in 9.3, it is not in 9.1, unless you find some > official backport. To be more precise, pg_dump supports dump from servers down to 7.0, so you can do it: http://www.postgresql.org/docs/9.3/static/app-pgdump.html Now the output may not be compatible with a 9.1 server and may need manual editing. Also, be careful that 9.1 servers do not support synchronized snapshots for parallel jobs, hence you may finish with an inconsistent dump if your server has write activity during the dump. Btw, if you are running on 9.1.2, update to 9.1.15. You are missing 3 years worth of many bug fixes, some of them being critical. -- Michael
Sachin Srivastava wrote: > How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) > with Postgres 9.1. There is any way I can use this is for 9.1 database. You cannot do that. Switch to file system backup, that is much faster. Yours, Laurenz Albe
Hello Sachin,
I hate to respond by suggesting an alternative but it may be good to try using pg_basebackup (Doc: http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html) to back-up your database. It takes a copy of the file system files rather than querying the data as an ordinary connection which is much faster. Straight file system backup is fast too but it may be more difficult to restore the database to a consistent state. At this point pg_dump is very good for exporting specific tables or schemas or for loading data into another dbms but it is slow and has a lot of overhead because of MVCC. pg_basebackup is faster, but does require you take a backup of the entire cluster.
Will J. Dunn
On Fri, May 15, 2015 at 8:09 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
Sachin Srivastava wrote:
> How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3)
> with Postgres 9.1. There is any way I can use this is for 9.1 database.
You cannot do that.
Switch to file system backup, that is much faster.
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Michael,
So, as per your suggestion I'll update my database from 9.1.2 to 9.1.15.
Kindly confirm, which year this 9.1.2 was released and when 9.1.15 was released.
And easily I can upgrade this and what are the steps to upgrade, kindly confirm?
Regards,
Sachin
On Fri, May 15, 2015 at 5:16 PM, Sachin Srivastava <ssr.teleatlas@gmail.com> wrote:
Hi,
How can I fast my daily pg_dump backup. Can I use parallel option(Which is introduced in Postgres 9.3) with Postgres 9.1. There is any way I can use this is for 9.1 database.
My database size is 820 GB and it’s taking 7 hours to complete.
Postgres Version: 9.1.2
PogtGIS: 1.5
Regards,
Sachin Srivastava
Assistant Technical Lead(Oracle/PostgreSQL) | TSG
Cyient | www.cyient.com
Sachin Srivastava schrieb am 18.05.2015 um 12:04: > Kindly confirm, which year this 9.1.2 was released and when 9.1.15 was released. That information is part of the release notes: http://www.postgresql.org/docs/9.1/static/release-9-1-2.html http://www.postgresql.org/docs/9.1/static/release-9-1-15.html Thomas
On Mon, May 18, 2015 at 7:04 PM, Sachin Srivastava <ssr.teleatlas@gmail.com> wrote:
-- Hi Michael,So, as per your suggestion I'll update my database from 9.1.2 to 9.1.15.Kindly confirm, which year this 9.1.2 was released and when 9.1.15 was released.And easily I can upgrade this and what are the steps to upgrade, kindly confirm?
On disk format for the same major version is compatible, so simply install the new binaries and restart your server. The installation of the new binaries depends on your OS and/or the way things have been installed.
Michael
On 5/18/2015 4:48 AM, Michael Paquier wrote:
And easily I can upgrade this and what are the steps to upgrade, kindly confirm?On disk format for the same major version is compatible, so simply install the new binaries and restart your server. The installation of the new binaries depends on your OS and/or the way things have been installed.
you need to read the release notes to see if there's any special steps due to bug fixes. some incremental updates may require a reindex of certain index types, for instance.
-- john r pierce, recycling bits in santa cruz