Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_upgrade vs. pg_dump

pg_upgrade vs. pg_dump

From
"Perry, Hemy"
Date:

Hello,

 

I am planning  a migration from 9.1.7 to 9.4.0.

Based on my understanding, the recommendation is to use pg_upgrade over ‘dump and restore’.

Is that right?

 

If I want to use pg_upgrade, I need to provide the -b bindir (--old-bindir=bindir) and so my second question is what if I’ll provide 9.4 (the new PostgreSQL executable directory) also as the ‘old-bindir’, can it work that way? Always?

[I am trying to solve a problem that I might not have the old-bindir available on the machine and only the new-bindir will be available (as well as the old & new datadir of course J]

 

Thanks

Hemy

 

 

Re: pg_upgrade vs. pg_dump

From
"Stéphane Wirtel"
Date:
On 20 Jan 2015, at 20:22, Perry, Hemy wrote:

> Hello,
>
> I am planning  a migration from 9.1.7 to 9.4.0.
> Based on my understanding, the recommendation is to use pg_upgrade
> over 'dump and restore'.
> Is that right?
You can compile the twice versions of PostgreSQL on the same computer
and just execute the upgrade with pg_upgrade and the right parameters.


>
> If I want to use pg_upgrade, I need to provide the -b bindir
> (--old-bindir=bindir) and so my second question is what if I'll
> provide 9.4 (the new PostgreSQL executable directory) also as the
> 'old-bindir', can it work that way? Always?
> [I am trying to solve a problem that I might not have the old-bindir
> available on the machine and only the new-bindir will be available (as
> well as the old & new datadir of course :)]
>
> Thanks
> Hemy


--
Stéphane Wirtel - http://wirtel.be - @matrixise

Re: pg_upgrade vs. pg_dump

From
"Perry, Hemy"
Date:
Thank you Stéphane.
 
What about the second question?
 
If I want to use pg_upgrade, I need to provide the -b bindir (--old-bindir=bindir) and so my second question is what if I’ll provide 9.4 (the new PostgreSQL executable directory) also as the ‘old-bindir’, can it work that way? Always?
[I am trying to solve a problem that I might not have the old-bindir available on the machine and only the new-bindir will be available (as well as the old & new datadir of course J]
 
 
Hemy
 
-----Original Message-----
From: Stéphane Wirtel [mailto:stephane@wirtel.be]
Sent: 20 January 2015 21:24
To: Perry, Hemy
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] pg_upgrade vs. pg_dump
 
On 20 Jan 2015, at 20:22, Perry, Hemy wrote:
 
> Hello,
>
> I am planning  a migration from 9.1.7 to 9.4.0.
> Based on my understanding, the recommendation is to use pg_upgrade
> over 'dump and restore'.
> Is that right?
You can compile the twice versions of PostgreSQL on the same computer and just execute the upgrade with pg_upgrade and the right parameters.
 
 
>
> If I want to use pg_upgrade, I need to provide the -b bindir
> (--old-bindir=bindir) and so my second question is what if I'll
> provide 9.4 (the new PostgreSQL executable directory) also as the
> 'old-bindir', can it work that way? Always?
> [I am trying to solve a problem that I might not have the old-bindir
> available on the machine and only the new-bindir will be available (as
> well as the old & new datadir of course :)]
>
> Thanks
> Hemy
 
 
--
Stéphane Wirtel - http://wirtel.be - @matrixise
 

Re: pg_upgrade vs. pg_dump

From
Matheus de Oliveira
Date:

On Tue, Jan 20, 2015 at 5:22 PM, Perry, Hemy <hemy_perry@mentor.com> wrote:

Based on my understanding, the recommendation is to use pg_upgrade over ‘dump and restore’.

Is that right?

 


pg_dump + pg_restore still works very well, but the process is generally slower. But if you have a small database and the time it takes is not big, then you are fine to use this method if you want to.
 

If I want to use pg_upgrade, I need to provide the -b bindir (--old-bindir=bindir) and so my second question is what if I’ll provide 9.4 (the new PostgreSQL executable directory) also as the ‘old-bindir’, can it work that way? Always?

[I am trying to solve a problem that I might not have the old-bindir available on the machine and only the new-bindir will be available (as well as the old & new datadir of course J]


You need both binaries in the server for pg_upgrade, there is no way around it.

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

Re: pg_upgrade vs. pg_dump

From
Harshad Adalkonda
Date:

On Wed, Jan 21, 2015 at 12:52 AM, Perry, Hemy <hemy_perry@mentor.com> wrote:

Hello,

 

I am planning  a migration from 9.1.7 to 9.4.0.

Based on my understanding, the recommendation is to use pg_upgrade over ‘dump and restore’.

Is that right?

 

If I want to use pg_upgrade, I need to provide the -b bindir (--old-bindir=bindir) and so my second question is what if I’ll provide 9.4 (the new PostgreSQL executable directory) also as the ‘old-bindir’, can it work that way? Always?

[I am trying to solve a problem that I might not have the old-bindir available on the machine and only the new-bindir will be available (as well as the old & new datadir of course J]

 

Thanks

Hemy

 

 


Hi,

First Answer:
you can use dump & restore if your database size is small, but if your database size is huge then you should go for pg_upgrade.
pg_upgrade is fast & time consuming than dump & restore for huge databases.

Second Answer:
pg_upgrade requires old bin & old data directory because pg_upgrade upgrades headers of old data directory by using old & new binaries.
It converts the old data headers to new one.

If you put new binaries as old bin directory it will not work because the pg_ctl of 9.4 will look for postgresql.auto.conf file in your old data directory which is required for Postgresql 9.4 to start the old cluster.

I hope you got the pg_upgrade concept.

--
Thanks & Regards,
Harshad Adalkonda
Database Administrator
www.shreeyansh.com