Re: How to transfer databases form one server to other

On 1/26/20 10:44 PM, Andreas Joseph Krogh wrote:
> På mandag 27. januar 2020 kl. 03:26:59, skrev Ron <ronljohnsonjr(at)gmail(dot)com
> <mailto:ronljohnsonjr(at)gmail(dot)com>>:
>
> [..]
> I ran *uncompressed* pg_dump on multiple TB+ sized databases from v8.4
> servers across the LAN using 9.6 binaries on the remote server.  It
> was quite fast.  Threading was key.
>
> According to the manual: https://www.postgresql.org/docs/12/app-pgdump.html
> the "directory format" is the only format which supports parallel dumps,
> if I'm not reading it wrong.
> How did threading solve "between database" dump/restore for you? Did you
> dump to "directory format" first, then restore?

Yes.

> If so, then that requires quite a bit of temp-space...

Correct. The databases are mostly compressed TIFF and PDF images in bytea
fields, so having Postgres try and compress them again was *slow* and used a
*lot* of CPU.  Thus, I did uncompressed backups, and that took a *lot* of
scratch disk space.

(We were not only upgrading Postgres 8.4 to 9.6, but also RHEL 5.10 to 6.10,
and moving to a geographically distant data center.  Thus, I deemed
pg_upgrade to be impractical.)

We spun up some VMs with 10 total TB in the same DC as the source (physical)
servers, and I installed Pg 9.6 on these "intermediate servers", and did
remote pg_dumps of the 8.4 servers.  Then I installed 9.6 on the VMs in the
new DC, and NFS mounted the intermediate servers' volumes and ran
multi-threaded pg_restore on the new servers.  They pulled the data across
the WAN.

--
Angular momentum makes the world go 'round.