Re: Fast logical replication jump start with PG 10 - Mailing list pgsql-general

On 05/26/2018 06:23 AM, Olivier Gautherot wrote:

On Sat, May 26, 2018 at 12:11 AM, Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
    On 05/25/2018 06:35 PM, Olivier Gautherot wrote:

[snip]

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.

1) I used pg_basebackup(www.postgresql.org/docs/10/static/app-pgbasebackup.html) to create a new $DATA directory for a replica instance.

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.

3) I set up the PUBLICATION:

CREATE PUBLICATION everything FOR ALL TABLES;

4) I set up the SUBSCRIPTION:

CREATE SUBSCRIPTION test_sub CONNECTION 'dbname=production user=postgres port=5432' PUBLICATION everything WITH(copy_data=false);

*NOTE* the copy_data=false.

5) Then I started entering data in the master and it was replicated.

Caveats:

1) This was a small database.

2) The master and replica where on the same machine.

3) There was no activity on the master between the pg_basebackup and the CREATE PUBLICATION/CREATE SUBSCRIPTION commands.

[snip]

--
Adrian Klaver
adrian.klaver@aklaver.com