Thread: Streaming replication: PANIC on tertiary when secondary promoted

What is your recovery_target_timeline set to on replicas ?

I just did a primary -> replica -> cascading replica setup. and then
promoted replica as new primary.
cascading replica was working fine, no restarts required.

for me recovery_target_timeline was set to 'latest'

i have pg14beta installed btw.

initdb -D primary
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

postgres@db:~/playground$ pg_ctl -D primary -l logfile start
waiting for server to start.... done
server started
postgres@db:~/playground$ psql
psql (14beta1)
Type "help" for help.

postgres=# select pg_create_physical_replication_slot('replica');
 pg_create_physical_replication_slot
-------------------------------------
 (replica,)
(1 row)

postgres=# create table t(id int primary key);
CREATE TABLE
postgres=# insert into t select x from generate_series(1, 100) x; checkpoint;
INSERT 0 100
postgres=# \q

-- create a replica
postgres@db:~/playground$ pg_basebackup -D replica -R -X stream -S
replica -v -d "dbname=postgres port=5432" -U postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@db:~/playground$ vim replica/postgresql.conf

--start the replica (port 5433)
postgres@db:~/playground$  pg_ctl -D replica -l replicalog start
waiting for server to start.... done
server started
postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
-------
   100
(1 row)

postgres@db:~/playground$ psql -p 5433
psql (14beta1)
Type "help" for help.


-- create a replica slot for cascading streaming replication
postgres=# select pg_create_physical_replication_slot('cascading_replica');
 pg_create_physical_replication_slot
-------------------------------------
 (cascading_replica,)
(1 row)

postgres=# \q

-- create a cascading replica off replica

postgres@db:~/playground$ pg_basebackup -D cascading_replica -R -X
stream -S cascading_replica -v -d "dbname=postgres port=5433" -U
postgres
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/30000D8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
postgres@db:~/playground$ vim cascading_replica/postgresql.conf
postgres@db:~/playground$ pg_ctl -D cascading_replica -l creplica start
waiting for server to start.... done
server started

-- validate receiving data fine.
postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
-------
   100
(1 row)


-- stop primary
postgres@db:~/playground$ pg_ctl -D primary -l logfile stop
waiting for server to shut down.... done
server stopped


-- promote replica to new primary
postgres@db:~/playground$ psql -p 5433
psql (14beta1)
Type "help" for help.

postgres=# select pg_promote();
 pg_promote
------------
 t
(1 row)

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

postgres=# \q

--do some dml, validate changes replayed to new replica.
postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
-------
   100
(1 row)

postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
-------
   100
(1 row)

postgres@db:~/playground$ psql -p 5433 -c 'delete from t where id < 50;'
DELETE 49
postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
 count
-------
    51
(1 row)

postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
 count
-------
    51
(1 row)


in all my cases. recovery_timeline was set to 'latest'.
i did not rx any panic messages in logs.

I had it "latest" as well.
I'll try to reproduce it again tomorrow.

On 16/06/2021 17:20, Vijaykumar Jain wrote:
> What is your recovery_target_timeline set to on replicas ?
>
> I just did a primary -> replica -> cascading replica setup. and then
> promoted replica as new primary.
> cascading replica was working fine, no restarts required.
>
> for me recovery_target_timeline was set to 'latest'
>
> i have pg14beta installed btw.
>
> initdb -D primary
> The files belonging to this database system will be owned by user "postgres".
> This user must also own the server process.
>
> postgres@db:~/playground$ pg_ctl -D primary -l logfile start
> waiting for server to start.... done
> server started
> postgres@db:~/playground$ psql
> psql (14beta1)
> Type "help" for help.
>
> postgres=# select pg_create_physical_replication_slot('replica');
>   pg_create_physical_replication_slot
> -------------------------------------
>   (replica,)
> (1 row)
>
> postgres=# create table t(id int primary key);
> CREATE TABLE
> postgres=# insert into t select x from generate_series(1, 100) x; checkpoint;
> INSERT 0 100
> postgres=# \q
>
> -- create a replica
> postgres@db:~/playground$ pg_basebackup -D replica -R -X stream -S
> replica -v -d "dbname=postgres port=5432" -U postgres
> pg_basebackup: initiating base backup, waiting for checkpoint to complete
> pg_basebackup: checkpoint completed
> pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
> pg_basebackup: starting background WAL receiver
> pg_basebackup: write-ahead log end point: 0/2000138
> pg_basebackup: waiting for background process to finish streaming ...
> pg_basebackup: syncing data to disk ...
> pg_basebackup: renaming backup_manifest.tmp to backup_manifest
> pg_basebackup: base backup completed
> postgres@db:~/playground$ vim replica/postgresql.conf
>
> --start the replica (port 5433)
> postgres@db:~/playground$  pg_ctl -D replica -l replicalog start
> waiting for server to start.... done
> server started
> postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
>   count
> -------
>     100
> (1 row)
>
> postgres@db:~/playground$ psql -p 5433
> psql (14beta1)
> Type "help" for help.
>
>
> -- create a replica slot for cascading streaming replication
> postgres=# select pg_create_physical_replication_slot('cascading_replica');
>   pg_create_physical_replication_slot
> -------------------------------------
>   (cascading_replica,)
> (1 row)
>
> postgres=# \q
>
> -- create a cascading replica off replica
>
> postgres@db:~/playground$ pg_basebackup -D cascading_replica -R -X
> stream -S cascading_replica -v -d "dbname=postgres port=5433" -U
> postgres
> pg_basebackup: initiating base backup, waiting for checkpoint to complete
> pg_basebackup: checkpoint completed
> pg_basebackup: write-ahead log start point: 0/3000028 on timeline 1
> pg_basebackup: starting background WAL receiver
> pg_basebackup: write-ahead log end point: 0/30000D8
> pg_basebackup: waiting for background process to finish streaming ...
> pg_basebackup: syncing data to disk ...
> pg_basebackup: renaming backup_manifest.tmp to backup_manifest
> pg_basebackup: base backup completed
> postgres@db:~/playground$ vim cascading_replica/postgresql.conf
> postgres@db:~/playground$ pg_ctl -D cascading_replica -l creplica start
> waiting for server to start.... done
> server started
>
> -- validate receiving data fine.
> postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
>   count
> -------
>     100
> (1 row)
>
>
> -- stop primary
> postgres@db:~/playground$ pg_ctl -D primary -l logfile stop
> waiting for server to shut down.... done
> server stopped
>
>
> -- promote replica to new primary
> postgres@db:~/playground$ psql -p 5433
> psql (14beta1)
> Type "help" for help.
>
> postgres=# select pg_promote();
>   pg_promote
> ------------
>   t
> (1 row)
>
> postgres=# select pg_is_in_recovery();
>   pg_is_in_recovery
> -------------------
>   f
> (1 row)
>
> postgres=# \q
>
> --do some dml, validate changes replayed to new replica.
> postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
>   count
> -------
>     100
> (1 row)
>
> postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
>   count
> -------
>     100
> (1 row)
>
> postgres@db:~/playground$ psql -p 5433 -c 'delete from t where id < 50;'
> DELETE 49
> postgres@db:~/playground$ psql -p 5433 -c 'select count(1) from t;'
>   count
> -------
>      51
> (1 row)
>
> postgres@db:~/playground$ psql -p 5434 -c 'select count(1) from t;'
>   count
> -------
>      51
> (1 row)
>
>
> in all my cases. recovery_timeline was set to 'latest'.
> i did not rx any panic messages in logs.

On 16/06/2021 20:31, Alexey Bashtanov wrote:
> I had it "latest" as well.
> I'll try to reproduce it again tomorrow. replica -v -d 
> "dbname=postgres port=5432" -U postgres

I cannot quite reproduce it artificially.

One more piece of detail: in the chain 
serverA->serverB->serverC->serverD when serverB was promoted and serverC 
crashed I had wal_compression enabled on serverA only.

Best regards, Alex