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

[Mai Peng <maily.peng@xxxxxxxxxxxxxxxxx>]

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://logical_user@xxxxxxxxxxx/data'
  PUBLICATION cms;

Thank you



> Le 29 juin 2020 à 19:10, Peter Eisentraut <peter.eisentraut@xxxxxxxxxxxxxxx> 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