Search Postgresql Archives

Re: Fast logical replication jump start with PG 10

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

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  


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux