On Sat, May 26, 2018 at 1:27 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> On 05/26/2018 06:23 AM, Olivier Gautherot wrote: > >> On Sat, May 26, 2018 at 12:11 AM, Adrian Klaver < >> adrian.kla...@aklaver.com <mailto:adrian.kla...@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-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.kla...@aklaver.com > Olivier Gautherot http://www.linkedin.com/in/ogautherot
