Seeded Replication

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Seeded Replication

Lou Tseng
Hi, 

We are working on a project to set up replication from on premises Postgresql 10.4 to Azure Postgresql services. However, our database is about 200G and it will take a long time to complete the initial data copy.  We would like to manually seed the subscriber database with data dump and then turn on the subscription like depesz showed in this post https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ .  It works for the small testing database but when I applied same steps with entire database, I am getting "ERROR:  duplicate key value violates unique constraint" errors. Basically postgresql does not recognize the primary key.

Any thought / suggestion is helpful!

Lou Tseng

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Reply | Threaded
Open this post in threaded view
|

Re: Seeded Replication

pavan95
Hi Lou,

Did you restore data on the subscriber during the first time. If yes this error is likely to occur. 

As a remedy, you need to restore only structural dump during initial building of subscription.

Earlier the same issue was faced by me later it got resolved following the above said approach. 

Kindly revert back if any queries. 

Regards, 
Pavan Teja, 
9841380956 

On Wed, 3 Apr, 2019, 5:15 PM Lou Tseng, <[hidden email]> wrote:
Hi, 

We are working on a project to set up replication from on premises Postgresql 10.4 to Azure Postgresql services. However, our database is about 200G and it will take a long time to complete the initial data copy.  We would like to manually seed the subscriber database with data dump and then turn on the subscription like depesz showed in this post https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ .  It works for the small testing database but when I applied same steps with entire database, I am getting "ERROR:  duplicate key value violates unique constraint" errors. Basically postgresql does not recognize the primary key.

Any thought / suggestion is helpful!

Lou Tseng

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Reply | Threaded
Open this post in threaded view
|

Re: Seeded Replication

Lou Tseng
Pavan,

This is the steps I took.  I think I did it right:

[Master]

pg_dump -s -f schema.sql
pg_dump -a -f data.sql
CREATE PUBLICATION

[Client/Slave]

psql \i schema.sql
CREATE SUBSCRIPTION ... WITH (enabled = false)
SET session_replication_role = replica;  -- turn off trigger
psql \i data.sql
ALTER SUBSCRIPTION ... ENABLE

--> errors occur here

Thoughts?

Lou Tseng

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092


From: Pavan Teja <[hidden email]>
Sent: Wednesday, April 3, 2019 6:48 AM
To: Lou Tseng
Cc: [hidden email] >> PG-General Mailing List
Subject: Re: Seeded Replication
 
Hi Lou,

Did you restore data on the subscriber during the first time. If yes this error is likely to occur. 

As a remedy, you need to restore only structural dump during initial building of subscription.

Earlier the same issue was faced by me later it got resolved following the above said approach. 

Kindly revert back if any queries. 

Regards, 
Pavan Teja, 
9841380956 

On Wed, 3 Apr, 2019, 5:15 PM Lou Tseng, <[hidden email]> wrote:
Hi, 

We are working on a project to set up replication from on premises Postgresql 10.4 to Azure Postgresql services. However, our database is about 200G and it will take a long time to complete the initial data copy.  We would like to manually seed the subscriber database with data dump and then turn on the subscription like depesz showed in this post https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ .  It works for the small testing database but when I applied same steps with entire database, I am getting "ERROR:  duplicate key value violates unique constraint" errors. Basically postgresql does not recognize the primary key.

Any thought / suggestion is helpful!

Lou Tseng

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

Reply | Threaded
Open this post in threaded view
|

Re: Seeded Replication

Adrian Klaver-4
In reply to this post by Lou Tseng
On 4/3/19 4:44 AM, Lou Tseng wrote:

> Hi,
>
> We are working on a project to set up replication from on premises
> Postgresql 10.4 to Azure Postgresql services. However, our database is
> about 200G and it will take a long time to complete the initial data
> copy.  We would like to manually seed the subscriber database with data
> dump and then turn on the subscription like depesz showed in this post
> https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ .
> It works for the small testing database but when I applied same steps
> with entire database, I am getting "ERROR:  duplicate key value violates
> unique constraint" errors. Basically postgresql does not recognize the
> primary key.

Actually it does which why you get the error, which by the way is the
same error depesz got in his demo:)

 From your subsequent post:

CREATE SUBSCRIPTION ... WITH (enabled = false)

Did the above also have?:

https://www.postgresql.org/docs/11/sql-createsubscription.html

copy_data = false

If not then you would copying over the data again and that would trigger
the duplicate key error.


>
> Any thought / suggestion is helpful!
>
> Lou Tseng
>
> [hidden email] <mailto:[hidden email]>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Seeded Replication

Lou Tseng
Thank you Adrian.  copy_data = false did the trick!

Lou Tseng

[hidden email]
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092


From: Adrian Klaver <[hidden email]>
Sent: Wednesday, April 3, 2019 7:10 PM
To: Lou Tseng; [hidden email]
Subject: Re: Seeded Replication
 
On 4/3/19 4:44 AM, Lou Tseng wrote:
> Hi,
>
> We are working on a project to set up replication from on premises
> Postgresql 10.4 to Azure Postgresql services. However, our database is
> about 200G and it will take a long time to complete the initial data
> copy.  We would like to manually seed the subscriber database with data
> dump and then turn on the subscription like depesz showed in this post
> https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ .
> It works for the small testing database but when I applied same steps
> with entire database, I am getting "ERROR:  duplicate key value violates
> unique constraint" errors. Basically postgresql does not recognize the
> primary key.

Actually it does which why you get the error, which by the way is the
same error depesz got in his demo:)

 From your subsequent post:

CREATE SUBSCRIPTION ... WITH (enabled = false)

Did the above also have?:

https://www.postgresql.org/docs/11/sql-createsubscription.html

copy_data = false

If not then you would copying over the data again and that would trigger
the duplicate key error.


>
> Any thought / suggestion is helpful!
>
> Lou Tseng
>
> [hidden email] <[hidden email]>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Seeded Replication

Peter Eisentraut-6
In reply to this post by Lou Tseng
On 2019-04-03 13:44, Lou Tseng wrote:
> However, our database is about 200G and it will take a long time to
> complete the initial data copy.  We would like to manually seed the
> subscriber database with data dump and then turn on the subscription
> like depesz showed in this
> post https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-logical-replication/ .

I doubt you will get a huge speedup, since the dump and the initial data
copy use the same COPY command internally.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services