We have a postgresql 9.4 DB server that handles 2 DBs, each ~800GB. No replication is implemented yet.
We reduced pg_dump time with using -j & -Fd options to about 2 hours.
The problem is, we need to separate DBs, so we setup a fresh new server with Postgresql 9.6, but pg_restore takes a very long time (~2 days). I tried parallel pg_restore using -j (4, 8, 16, although VM has a 4 core CPU and 16GB of RAM) and tuned some parameters in posotgresql.conf like shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_buffers and so forth, but they didn't have any significant effect on reducing time of restore.
Even, I tried to restore data and schema separately, but same results. I think the problem is we have many tables with 15M records that all have index and indices will increase restore time but I'm not sure of that
We thought about replication, so we do not have any significant downtime (after sync is complete, we can stop old/master server and change replica/slave to acts as a master server), but as version of postgresql servers is not same, it didn't work out! Updating main server postgresql version to 9.6 and then start replication will take such a time!
Any advice and suggestions will be greatly appreciated!