Converting yes or no to one letter strings.
Hi everyone, Is it possible to convert a boolean yes or no field to hold a one letter string? For example, the strings: 's' 'f' 'p' 'e' To start off, I just need to convert true to 's'. false will have to be manually changed to 'f' or 'p' or 'e' as appropriate. Lou
Seeded Replication
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 lts...@advancedpricing.com<mailto:lts...@advancedpricing.com> [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]<http://www.advancedpricing.com/> Advanced Medical Pricing Solutions<http://advancedpricing.com/> 35 Technology Parkway South, Suite. 100 Peachtree Corners, GA 30092
Re: Seeded Replication
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 lts...@advancedpricing.com<mailto:lts...@advancedpricing.com> [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]<http://www.advancedpricing.com/> Advanced Medical Pricing Solutions<http://advancedpricing.com/> 35 Technology Parkway South, Suite. 100 Peachtree Corners, GA 30092 From: Pavan Teja Sent: Wednesday, April 3, 2019 6:48 AM To: Lou Tseng Cc: pgsql-gene...@postgresql.org >> 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, mailto:lts...@advancedpricing.com>> 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 lts...@advancedpricing.com<mailto:lts...@advancedpricing.com> [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]<http://www.advancedpricing.com/> Advanced Medical Pricing Solutions<http://advancedpricing.com/> 35 Technology Parkway South, Suite. 100 Peachtree Corners, GA 30092
Lingering replication slots
Hi, A newbie question: how do I delete lingering replication slots? When creating Subscription for logical replication, the CREATE SUBSCRIPTION stuck and didn't return. After ctrl-c and aborted the command, the master database have lingering replication slots that I can't delete because the active is true. Do I just kill the pid? Thanks! SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid |database| temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn --+--+---+--++---+++--+--+--+- support_sub2 | pgoutput | logical | 28384483 | dragon_support | f | t | 29566 | | 96562907 | 5108/29C1610 | support_sub | pgoutput | logical | 28384483 | dragon_support | f | t | 27253 | | 96562907 | 5108/2858880 |
Re: Lingering replication slots
Thank you. Wasn't able to drop replication slot because it's still active. Arjun suggested pg_terminate_backend() and it did the trick. Thanks a lot! Lou Tseng lts...@advancedpricing.com<mailto:lts...@advancedpricing.com> [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]<http://www.advancedpricing.com/> Advanced Medical Pricing Solutions<http://advancedpricing.com/> 35 Technology Parkway South, Suite. 100 Peachtree Corners, GA 30092 From: Adrian Klaver Sent: Wednesday, April 3, 2019 11:27 AM To: Lou Tseng; pgsql-gene...@postgresql.org Subject: Re: Lingering replication slots On 4/3/19 9:01 AM, Lou Tseng wrote: > Hi, > > A newbie question: how do I delete lingering replication slots? When > creating Subscription for logical replication, the CREATE SUBSCRIPTION > stuck and didn't return. After ctrl-c and aborted the command, the > master database have lingering replication slots that I can't delete > because the active is true. Do I just kill the pid? > > Thanks! > > > SELECT * FROM pg_replication_slots ; > > slot_name |plugin| slot_type |datoid|database| temporary | active | > active_pid | xmin | catalog_xmin | restart_lsn| confirmed_flush_lsn > > --+--+---+--++---+++--+--+--+- > > support_sub2 | pgoutput | logical | 28384483 | dragon_support | f | > t|29566 || 96562907 | 5108/29C1610 | > > support_sub| pgoutput | logical | 28384483 | dragon_support | f | > t|27253 || 96562907 | 5108/2858880 | > See below(read full description at link): https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-REPLICATION pg_drop_replication_slot(slot_name name) -- Adrian Klaver adrian.kla...@aklaver.com
Re: Seeded Replication
Thank you Adrian. copy_data = false did the trick! Lou Tseng lts...@advancedpricing.com<mailto:lts...@advancedpricing.com> [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]<http://www.advancedpricing.com/> Advanced Medical Pricing Solutions<http://advancedpricing.com/> 35 Technology Parkway South, Suite. 100 Peachtree Corners, GA 30092 From: Adrian Klaver Sent: Wednesday, April 3, 2019 7:10 PM To: Lou Tseng; pgsql-gene...@postgresql.org 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 > > lts...@advancedpricing.com <mailto:lts...@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.kla...@aklaver.com
Logical replication failed recovery
Hi folks, Is there a good tutorial to recover from logical replication out of sync? We ran into this error stating WAL has been removed and now replica is out of sync. Also, is there a way to increase the number of WAL kept on the master? Thanks! 2019-04-07 12:28:37.180 UTC [22292] ERROR: could not receive data from WAL stream: ERROR: requested WAL segment 0001520800EB has already been removed 2019-04-07 12:28:37.182 UTC [114186] LOG: worker process: logical replication worker for subscription 8907992 (PID 22292) exited with exit code 1 Lou Tseng lts...@advancedpricing.com<mailto:lts...@advancedpricing.com> [http://advancedpricing.com/wp-content/uploads/logo_AMPS_email.png]<http://www.advancedpricing.com/> Advanced Medical Pricing Solutions<http://advancedpricing.com/> 35 Technology Parkway South, Suite. 100 Peachtree Corners, GA 30092
