Re: Migrate 2 DB`s - v8.3

Thanks Greg,

Sounds like I've unknowingly stumbled onto a good path, the one you
suggested.

I actually installed v9.5 on the target server. I have it running on a
different port (5444) and using a different data directory than the v8.3
install.

I'm doing the dump, and forwarding it to the remote. It's been running
for a while... but I'm actually not seeing anything show up on the target
side. Does it dump locally first and then pipe over? Here is details of
the happenings: http://pastebin.com/fEm3uJqy

pg_dump -v -C mls | psql -h db-blob04 -d mls -p 5444 -U postgres

Perhaps I will kill this eventually and try the timings you suggest with
just the data.

Thoughts/comments are always welcome....

On Fri, May 27, 2016 at 8:43 PM Greg Sabino Mullane <greg(at)turnstep(dot)com>
wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> > To move the DB, you are suggesting something like this:
> > pg_dump -h dbms11 -U postgres -C mls11 | psql -h localhost -d mls11
>
> Basically yes.
>
> > 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?
>
> It basically means doing a DROP INDEX foobar; for each index on the new
> database, copying the data over, and then doing CREATE INDEX CONCURRENTLY
> foobar ...
>
> You mentioned that a pg_dump and psql restore takes longer than your 2 hour
> window, but a lot of that time may simply be the index creations. You
> should
> test out how long your biggest table takes by doing this:
>
> * Copy the schema only to the new server:
>
> pg_dump mls11 -h dbms11 --schema-only -C | psql
>
> * Pick your largest table on the new server, and drop all indexes,
> triggers, and constraints on it. Then time copying the data:
>
> time pg_dump mls11 -h dbms11 --data-only -t foobar | psql mls11 -h newhost
>
> This should give you a better indication of the bare minimum time needed
> for that table. If you can find a newer version of pg_dump, you can do
> all of the above a lot easier like so:
>
> pg_dump mls11 -h dbms11 --section=pre-data -C | psql
> time pg_dump mls11 -h dbms11 --section=data | psql mls11
>
> This copies all the tables, and prevents the indexes and foreign keys from
> being created. If that comes under your 2 hour window, you can at least
> have
> a usable production database, and then start adding the indexed and
> foreign keys
> back in. There are some further tricks one can do to speed up the transfer
> time,
> but this will get you in the basic ballpark.
>
> (It should be noted that Postgres 8.3 is extremely old and completely
> unsupported. The inability to easily migrate to a new server is unlikely
> to be your last problem because of this. You may even want to push for
> a migration to 9.5 if you can, as that will also incur the same migration
> timings as moving to a new 8.3 server, but at the end of the day you will
> have a shiny 9.5 database.)
>
> If that transfer is still over the 2 hour window, you will have to look
> into
> a trigger based solution that can handle such an old version (which
> basically
> means Slony or Bucardo). Even if it cannot copy all of the tables, it may
> be
> able to do some of them, and then you can use pg_dump | psql for the rest.
>
>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201605272040
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
> -----BEGIN PGP SIGNATURE-----
>
> iEYEAREDAAYFAldI6WkACgkQvJuQZxSWSsgkHACg2KjWStQF9qhIL6fNFhFB74Za
> utAAoMa2WqCEfURl57g+hZc+LCEAnhT/
> =WXCu
> -----END PGP SIGNATURE-----
>
>
>
>
> --
> 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
>