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(at)10(dot)xx(dot)xx(dot)xx/data'
PUBLICATION cms;

Thank you

> Le 29 juin 2020 à 19:10, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)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 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