Re: Fast logical replication jump start with PG 10

On Sat, May 26, 2018 at 1:27 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/26/2018 06:23 AM, Olivier Gautherot wrote:
>
>> On Sat, May 26, 2018 at 12:11 AM, Adrian Klaver <
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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-pgbaseba
> ckup.html) to create a new $DATA directory for a replica instance.
>

Good tip, I'll give it a try.

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;
>

This was what I was planning to do, so great.

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.
>

This was the bit I missed! Excellent point!

5) Then I started entering data in the master and it was replicated.
>
> Caveats:
>
> 1) This was a small database.
>

I don't think the size is relevant in this specific case.

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

Same comment: different ports mean basically different instances.

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

This is also my plan for Production, so it's fine.

Thanks!!!

[snip]
>

> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

Olivier Gautherot
http://www.linkedin.com/in/ogautherot