Postgres Pro
Downloads
Home   >   mailing lists

Thread: Upgrade data

Upgrade data

From
josue
Date:
Hello list,

I need to upgrade my dbs from 743 to 801, current data size is around
5GB, I've tried this way:

  ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981

but is too slow, any idea or suggestion to properly upgrade my dbs, I
also have blobs stored there.

Thanks in advance,


--
Sinceramente,
Josué Maldonado.

... "La amistad, como todo verdadero encuentro, es dar y recibir." P.
Guisar.

Re: Upgrade data

From
Richard Huxton
Date:
josue wrote:
> Hello list,
>
> I need to upgrade my dbs from 743 to 801, current data size is around
> 5GB, I've tried this way:
>
>  ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981
>
> but is too slow, any idea or suggestion to properly upgrade my dbs, I
> also have blobs stored there.

Try pg_dump followed by pg_restore - both using version 8.0.1. If that's
still too slow, the only other option is to dump/restore and then setup
replication between the old and new databases to bring your new system
up to date. Slony can be used for this.

--
   Richard Huxton
   Archonet Ltd

Re: Upgrade data

From
Scott Marlowe
Date:
On Wed, 2005-03-30 at 01:33, Richard Huxton wrote:
> josue wrote:
> > Hello list,
> >
> > I need to upgrade my dbs from 743 to 801, current data size is around
> > 5GB, I've tried this way:
> >
> >  ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981
> >
> > but is too slow, any idea or suggestion to properly upgrade my dbs, I
> > also have blobs stored there.
>
> Try pg_dump followed by pg_restore - both using version 8.0.1. If that's
> still too slow, the only other option is to dump/restore and then setup
> replication between the old and new databases to bring your new system
> up to date. Slony can be used for this.

Actually, if you're gonna use slony, the only thing you need to dump and
restore to the target database is the schema, slony will move all the
data for you.

Re: Upgrade data

From
josue
Date:
Scott Marlowe wrote:
> On Wed, 2005-03-30 at 01:33, Richard Huxton wrote:
>
>>josue wrote:
>>
>>>Hello list,
>>>
>>>I need to upgrade my dbs from 743 to 801, current data size is around
>>>5GB, I've tried this way:
>>>
>>> ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981
>>>
>>>but is too slow, any idea or suggestion to properly upgrade my dbs, I
>>>also have blobs stored there.
>>
>>Try pg_dump followed by pg_restore - both using version 8.0.1. If that's
>>still too slow, the only other option is to dump/restore and then setup
>>replication between the old and new databases to bring your new system
>>up to date. Slony can be used for this.
>
>
> Actually, if you're gonna use slony, the only thing you need to dump and
> restore to the target database is the schema, slony will move all the
> data for you.

Thanks for the anwser, currently I'm using pg_dump to create a tar file
and I'll restore it with pg_restore


--
Sinceramente,
Josué Maldonado.

... "Cuando era joven leía casi siempre para aprender; hoy, a veces, leo
para olvidar."

Re: Upgrade data

From
josue
Date:
I did a pg_dumpall and this sentence to restore it back

./psql template1 -U postgres -p 9981 < /home2/tmp/dbtest.tar

and got this error

cannot allocate memory for output buffer

dbtest.tar is around 1.7 Gb, could anyone please bring some help to
update my 7.4 db to 8.0


Thanks in advance,

--
Sinceramente,
Josué Maldonado.

... "Toda la justicia social descansa en estos dos axiomas: El robo es
punible y el producto del robo es sagrado." Anatole France. (
Jacques-Anatole Thibault ) Escritor francés.

Re: Upgrade data

From
Doug McNaught
Date:
josue <josue@lamundial.hn> writes:

> I did a pg_dumpall and this sentence to restore it back
>
> ./psql template1 -U postgres -p 9981 < /home2/tmp/dbtest.tar

You need to use 'pg_restore' for tar format dumps.

-Doug

Re: Upgrade data

From
George Essig
Date:
On Tue, 29 Mar 2005 15:39:28 -0600, josue <josue@lamundial.hn> wrote:
> Hello list,
>
> I need to upgrade my dbs from 743 to 801, current data size is around
> 5GB, I've tried this way:
>
>   ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981
>
> but is too slow, any idea or suggestion to properly upgrade my dbs, I
> also have blobs stored there.

Drop '-d' from the pg_dump command.  '-d' creates a dump file with
insert statements instead of copy statements.  From the pg_dump man
page:

-d
--inserts
              Dump  data as INSERT commands (rather than COPY). This will make
              restoration very slow, but it makes the archives  more  portable
              to other SQL database packages.

George Essig