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 <g...@turnstep.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 g...@turnstep.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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
