Re: Migrate 2 DB's - v8.3

Thank you for your time Alan.

I'd like to confirm my understanding of your statement, and ask a question.

To move the DB, you are suggesting something like this:
pg_dump -h dbms11 -U postgres -C mls11 | psql -h localhost -d mls11 -U
postgres

I'm not familiar with removing/adding indexes (I'm not a DBA, just trying
to pretend to be one for this project). Can you elaborate on what might I
need to do there?

Kind Regards,
Jeff

On Fri, May 27, 2016 at 6:05 PM Alan Hodgson <ahodgson(at)lists(dot)simkin(dot)ca>
wrote:

> On Friday, May 27, 2016 05:32:08 PM Melvin Davidson wrote:
> > Well, Slony certainly will do the trick.
> > Keep in mind you will need to do schema only first to the slave.
> > You set up replication from the old server with the db on the new server
> as
> > the slave. Then you initiate replication. It will probably take a long
> time
> > to
> > replicate, but then you have the option to promote the slave at your time
> > preference (IE: your 2 hr window). It should only take a few minutes for
> > Slony to do the switchover, but the best thing to do is a dry run first.
> > IOW, you'll have to do the whole thing twice to get an accurate switch
> time,
> > but you won't need to change your network until you are ready to go live.
>
> Slony doesn't do BLOBs, afaik, unless he's using BYTEA fields.
>
> Otherwise I believe dump/reload is OP's only choice. He should be able to
> do
> 90GB in 2 hours on fast enough hardware; just pipe it over the network to
> do
> the restore simultaneous with the dump.
>
> Also remove as many indexes as possible beforehand and use create
> concurrently
> manually afterwards to add them back in.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>