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
> 
> ltseng@advancedpricing.com <mailto:ltseng@advancedpricing.com>
> <http://www.advancedpricing.com/>
> Advanced Medical Pricing Solutions <http://advancedpricing.com/>
> 35 Technology Parkway South, Suite. 100
> Peachtree Corners, GA 30092
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com