2

Environment: Postgresql 13.x (dockerized)

I was trying to test the DR setup for PostgreSQL nodes. pg_basebackup and wal_files archive was taken from the standby mode.

Done restoration on a new node by copying pg_basebackup and configured postgresql.conf to use restore_command pointing to walfiles archive.

#----------------------- RECOVERY CONFIGS -----------------------
restore_command = 'cp /db-restore/mydb/walfiles/%f "%p"'
recovery_target_timeline = 'latest'
recovery_target_action = promote
  • recovery seems to be fine. Some random select queries returning correct results.
  • But logfile is throwing below error frequently.
2022-04-19 10:19:53 UTC [291] rep_usr@[unknown] ERROR:  replication slot "slot_name" does not exist
2022-04-19 10:19:58 UTC [296] rep_usr@[unknown] ERROR:  replication slot "slot_name" does not exist

  • As I have taken backup from standby, is this restoration making new node as a standby and looking for the replication_slot it used in the previous generation?

  • How can I make new node as a Master (remove replication_slot info)

  • What are the proper steps to recover if the backup was taken from standby.

  • I have 1 master and 2 standby nodes. And planning to take a backup from a standby. So is there any specific changes required for archive_mode and archive_command when using this on a standby node? Current commands:

archive_mode = always
archive_level = logical
archive_command = 'test ! -f /db-archives/walfiles/%f && cp %p /db-archives/walfiles/%f'"

Could someone help with this? Any pointers?

I am sure, db-backup will have info about replication_slot and connection_info as the pg_basebackup itself is a clone of entire DB. To revert configs, I am manually removing postgresql.auto.conf in main directory which contains above parameters.

  • So how can I remove any other references of replication_slot if there are any in the DB backup?
Anto
  • 3,128
  • 1
  • 20
  • 20

2 Answers2

1

These error messages don't seem to be thrown by recovery, but by some other tool that connects as database user rep_usr.

Create the replication slot if your application needs it!

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • I haven't connected any application to this new DB yet. And this `replication_slot` was created by me in Master as part of the replication setup. I will revisit the entire configs, and restoration again. Would like to know if `archive_mode=always` is the correct one to use on standby nodes if the backup is taken from there? Are any specific changes required to enable backup from standby? Backup and restore from Master didn't face any such issues. Could you please direct me to the correct docs if I need to implement something specifically for standby nodes? – Anto Apr 19 '22 at 13:35
  • I am sure, db-backup will have info about `replication_slot` and `connection_info` as the pg_basebackup is a clone of DB. To revert configs, I am manually removing `postgresql.auto.conf` which contains above parameters. So how can I remove any other references of replication slot if there is any in the Database backup? – Anto Apr 19 '22 at 13:38
  • Then who is that `rep_usr` and what is it doing? – Laurenz Albe Apr 19 '22 at 14:27
  • @Anto "I haven't connected any application to this new DB yet." Well, then someone else has. Expanding your log_line_prefix with things like appname and IP address could help figure that out. – jjanes Apr 19 '22 at 15:45
  • I will set up a new restoration after blocking all connections. Will update this thread. Thanks @jjanes Laurenz for your inputs. – Anto Apr 20 '22 at 07:59
  • Update. I removed all configs and started with fresh. And removed `main/postgresql.auto.conf` which was present in the backup. `main/postgresql.auto.conf` contains the configs used for `pg_basebackup` in standby nodes. (slot_name, and connect_info) Thanks guys ! – Anto Apr 25 '22 at 10:25
1

I removed all configs and started with fresh.

  • removed main/postgresql.auto.conf which was present in the backup.
  • main/postgresql.auto.conf is present in standby nodes when we take pg_basebackup. contains the configs used for pg_basebackup in standby nodes. (slot_name, and connect_info).

As I was restoring backup from standby to a Master, I don't need that postgresql.auto.conf.

Anto
  • 3,128
  • 1
  • 20
  • 20