Infrastructure at your Service

Traditionally everything which is related to recovery in PostgreSQL goes to recovery.conf. This is not only true for recovery settings but also for turning an instance into a replica which follows a master. Some days ago this commit landed in the PostgreSQL git repository. What that effectively means is, that there will be no more recovery.conf starting with PostgreSQL 12. How does that work then? Lets do some tests.

Obviously you need the latest development version of PostgreSQL (if you are not sure on how to do that check here and here):

[email protected]:/home/postgres/ [PGDEV] psql -X postgres
psql (12devel)
Type "help" for help.

postgres=# select version();
 PostgreSQL 12devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

Lets look at the replica case first. When you do a pg_basebackup you can tell it to write a recovery.conf file (at least you could tell that up to PostgreSQL 11). So what changed here:

[email protected]:/home/postgres/ [PGDEV] pg_basebackup --help | grep -A 1 recovery
  -R, --write-recovery-conf
                         write configuration for replication

When you compare that to a version before 12 you’ll notice the difference in wording:

[email protected]:/home/postgres/ [PGDEV] /u01/app/postgres/product/10/db_4/bin/pg_basebackup --help | grep -A 1 recovery
  -R, --write-recovery-conf
                         write recovery.conf for replication

The word “recovery.conf” is gone and it is a more general statement about replication configuration now. What does pg_baebackup do now in PostgreSQL 12 when we ask to write the configuration for recovery:

[email protected]:/home/postgres/ [PGDEV] pg_basebackup -R -D /var/tmp/pg12s/

We do not have a recovery.conf file:

[email protected]:/home/postgres/ [PGDEV] ls -la /var/tmp/pg12s/
total 64
drwxr-xr-x. 20 postgres postgres  4096 Nov 27 20:19 .
drwxrwxrwt.  6 root     root       256 Nov 27 20:19 ..
-rw-------.  1 postgres postgres   224 Nov 27 20:19 backup_label
drwx------.  5 postgres postgres    41 Nov 27 20:19 base
-rw-------.  1 postgres postgres    33 Nov 27 20:19 current_logfiles
drwx------.  2 postgres postgres  4096 Nov 27 20:19 global
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_commit_ts
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_dynshmem
-rw-------.  1 postgres postgres  4513 Nov 27 20:19 pg_hba.conf
-rw-------.  1 postgres postgres  1636 Nov 27 20:19 pg_ident.conf
drwxr-xr-x.  2 postgres postgres    32 Nov 27 20:19 pg_log
drwx------.  4 postgres postgres    68 Nov 27 20:19 pg_logical
drwx------.  4 postgres postgres    36 Nov 27 20:19 pg_multixact
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_notify
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_replslot
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_serial
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_snapshots
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_stat
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_stat_tmp
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_subtrans
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_tblspc
drwx------.  2 postgres postgres     6 Nov 27 20:19 pg_twophase
-rw-------.  1 postgres postgres     3 Nov 27 20:19 PG_VERSION
drwx------.  3 postgres postgres    60 Nov 27 20:19 pg_wal
drwx------.  2 postgres postgres    18 Nov 27 20:19 pg_xact
-rw-------.  1 postgres postgres   390 Nov 27 20:19
-rw-------.  1 postgres postgres 26000 Nov 27 20:19 postgresql.conf
-rw-------.  1 postgres postgres     0 Nov 27 20:19 standby.signal

Replica related configuration is appended to

[email protected]:/home/postgres/ [PGDEV] cat /var/tmp/pg12s/ 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
logging_collector = 'on'
log_truncate_on_rotation = 'on'
log_filename = 'postgresql-%a.log'
log_line_prefix = '%m - %l - %p - %h - %[email protected]%d '
log_directory = 'pg_log'
primary_conninfo = 'user=postgres passfile=''/home/postgres/.pgpass'' port=5433 sslmode=prefer sslcompression=0 target_session_attrs=any'

But what about timeline and all the other settings? All these have been merged into the normal postgresql.[auto.]conf file as well:

postgres=# select name,setting from pg_settings where name like '%recovery%';
           name            | setting 
 recovery_end_command      | 
 recovery_min_apply_delay  | 0
 recovery_target           | 
 recovery_target_action    | pause
 recovery_target_inclusive | on
 recovery_target_lsn       | 
 recovery_target_name      | 
 recovery_target_time      | 
 recovery_target_timeline  | 
 recovery_target_xid       | 
 trace_recovery_messages   | log
(11 rows)

So all the settings can now be set in one file. The remaining question is: How does the instance then know when it needs to go into recovery? Before PostgreSQL 12 the presence of the recovery.conf file told the instance to go into recovery. Now, that the file is gone there must be a new mechanism and that is the “standby.signal” file in case of a replica:

[email protected]:/home/postgres/ [PGDEV] cat /var/tmp/pg12s/standby.signal 
[email protected]:/home/postgres/ [PGDEV] 

That file is empty and just tells PostgreSQL to go into recovery and then process the recovery related parameters which are now in postgresql.[auto.]conf. The same is true when a recovery is requested: The signal file in that case is “recovery.signal”.

All in all that means there is one configuration file less to take care of and that is good. The question will be on how fast all the third party tools will catch up with that change.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure