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

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@10.xx.xx.xx/data'
  PUBLICATION cms;

Thank you



> Le 29 juin 2020 à 19:10, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> 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
subscriptionon 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
accumulatedWAL 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
nota specific enough technical term. 
>
> --
> Peter Eisentraut              http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services