The sequence on the replicated machine was (pseudo-code to simplify the syntax): - pg_dump --section=pre-data -h master_machine master_database | psql -h replication_machine replication_database # This took seconds, "pre-data" discards the indexes
- psql -h replication_machine -c "CREATE SUBSCRIPTION mysub CONNECTION "..." PUBLICATION mypub;" replication_database # This took about 1 hour for the initial sync
- pg_dump --section=post-data -h master_machine master_database | psql -h replication_machine replication_database # This took 2h20m to load the various indexes
This sequence follows the recommendation of section 14.4.3 in https://www.postgresql.org/docs/10/static/populate.html . If I stick to streaming as we do today (e.g. pg_upgrade and then rsync to the replication server), I can be ready in about 1 hour (more acceptable for the customer).
I am still learning what logical replication is capable of so take the following with that in mind.
2) I configured the master and the replica for logical replication. Also changed the copied over conf files to work for the new instance e.g. changed the port number.