On Sat, May 26, 2018 at 1:27 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 05/26/2018 06:23 AM, Olivier Gautherot wrote:On Sat, May 26, 2018 at 12:11 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.[snip]com >> wrote:
On 05/25/2018 06:35 PM, Olivier Gautherot wrote:
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 ) to create a new $DATA directory for a replica instance.ckup.html
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.klaver@xxxxxxxxxxx
Olivier Gautherot
http://www.linkedin.com/in/ogautherot
http://www.linkedin.com/in/
