After renaming a database the subscription ( logical replication) stay sticky to the old database (master)

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

After renaming a database the subscription ( logical replication) stay sticky to the old database (master)

Mai Peng
Hello,
I restored the production database to staging environment, then dropped the publication on old database, dropped the subscription on the slave.
I have to rename the old database in order to keep an old version of data. When I re-add the subscription logical replication, pointing to the new database, the slave still want to synchronize to the old database ( renamed) and the accumulated WAL files are crashing the filesystem.
How can I handle the problem ?
Thank you

Reply | Threaded
Open this post in threaded view
|

Re: After renaming a database the subscription ( logical replication) stay sticky to the old database (master)

Peter Eisentraut-6
On 2020-06-29 14:58, Mai Peng wrote:
> I restored the production database to staging environment, then dropped the publication on old database, dropped the subscription on the slave.
> I have to rename the old database in order to keep an old version of data. When I re-add the subscription logical replication, pointing to the new database, the slave still want to synchronize to the old database ( renamed) and the accumulated WAL files are crashing the filesystem.
> How can I handle the problem ?

You need to be more specific what exactly you are doing, including
specific commands.  For example, "pointing to" is not a specific enough
technical term.

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


Reply | Threaded
Open this post in threaded view
|

Re: After renaming a database the subscription ( logical replication) stay sticky to the old database (master)

Mai Peng
Hello Peter,
Here the script:

## Drop data_old is exists
psql -h ${PG_HOST} -U ${PG_USER} -d postgres -c "DROP DATABASE IF EXISTS data_old;"

## Drop data_new is exists
psql -h ${PG_HOST} -U ${PG_USER} -d postgres -c "DROP DATABASE IF EXISTS data_new;"

## create database data_new
createdb -h ${PG_HOST} -U ${PG_USER} data_new

## create extension data_new
psql -h ${PG_HOST} -U ${PG_USER} -d dbz_new -f extensions.sql

## restore database
pg_restore -h ${PG_HOST} -Fc -U ${PG_USER} -d data_new -j4 --disable-triggers ${TEMP}/data.sqlc > $LOGFILE


## add acl
psql -h ${PG_HOST} -U ${PG_USER} -d data_new -f acl.sql


## drop subscription on master social and drop publication on data_new
psql -h ${PG_SOCIAL_HOST} -U ${PG_SOCIAL_USER} -d data -f clean_slave_social.sql

psql -h ${PG_HOST} -U ${PG_USER} -d data_new -c "DROP PUBLICATION cms;"


## stop all session on dbz and rename database
psql -h ${PG_HOST} -U ${PG_USER} -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <>pg_backend_pid() AND datname=‘data';\
ALTER database data rename to data_old; ALTER database data_new rename to data;"

## recreate publication on master
psql -h ${PG_HOST} -U ${PG_USER} -d data -f publication_data_social.sql


## recreate subscription on slave
psql -h ${PG_SOCIAL_HOST} -U ${PG_SOCIAL_USER}  -d data -f subscription_data_social.sql
=>CREATE SUBSCRIPTION subcms
  CONNECTION 'postgresql://[hidden email]/data'
  PUBLICATION cms;

Thank you



> Le 29 juin 2020 à 19:10, Peter Eisentraut <[hidden email]> a écrit :
>
> On 2020-06-29 14:58, Mai Peng wrote:
>> I restored the production database to staging environment, then dropped the publication on old database, dropped the subscription on the slave.
>> I have to rename the old database in order to keep an old version of data. When I re-add the subscription logical replication, pointing to the new database, the slave still want to synchronize to the old database ( renamed) and the accumulated WAL files are crashing the filesystem.
>> How can I handle the problem ?
>
> You need to be more specific what exactly you are doing, including specific commands.  For example, "pointing to" is not a specific enough technical term.
>
> --
> Peter Eisentraut              http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Reply | Threaded
Open this post in threaded view
|

Re: After renaming a database the subscription ( logical replication) stay sticky to the old database (master)

Peter Eisentraut-6
On 2020-07-01 11:01, Mai Peng wrote:
> Hello Peter,
> Here the script:

But you didn't describe what the problem is.  I don't see anything wrong
at first glance.

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