Обсуждение: BUG #16288: application_name from client's primary_conninfo not set in pg_stat_replication on master
BUG #16288: application_name from client's primary_conninfo not set in pg_stat_replication on master
От
PG Bug reporting form
Дата:
The following bug has been logged on the website: Bug reference: 16288 Logged by: Andreas Ntaflos Email address: daff@ptmx.at PostgreSQL version: 12.2 Operating system: Ubuntu 18.04 Description: In a two-node PostgreSQL 12.2 replication scenario, where one node is master and the other a streaming client, the application_name field in the client's primary_conninfo setting does not appear to be set correctly in the pg_stat_replication view on the master. Instead of the application_name value defined on the client side, the pg_stat_replication view shows the cluster name in the application_name column. In our case the replication scenario is managed by Corosync/Pacemaker and the pgsql resource agent (latest from https://github.com/ClusterLabs/resource-agents/blob/master/heartbeat/pgsql which supports the replication/recovery changes introduced in PostgreSQL 12). This currently doesn't work correctly since the resource agent relies on the information queried from pg_stat_replication to match the Pacemaker cluster configuration settings, where application_name is supposed to be the streaming sync client's hostname (i.e. node name). Recovery settings on the client (db01.example.com) look like this (managed by the pgsql resource agent): primary_conninfo = 'host=10.1.33.187 port=5432 user=replication application_name=db01 keepalives_idle=60 keepalives_interval=5 keepalives_count=5' restore_command = '' recovery_target_timeline = 'latest' primary_slot_name = 'repl_slot_db01' The pg_stat_replication view on the master (db02.example.com) looks like this: postgres=# select usename, application_name, client_addr, client_hostname, client_port, state, sync_state from pg_stat_replication; usename | application_name | client_addr | client_hostname | client_port | state | sync_state ------------------+--------------------+-------------+------------------+-------------+-----------+------------ replication | 12/main | 10.1.33.70 | db01.example.com | 58024 | streaming | async As you can see, application_name should be "db01" as defined in primary_conninfo but is "12/main" in pg_stat_replication. Is this a bug in PostgreSQL 12 or is application_name in primary_conninfo no longer the proper way to set the application name? For reference, this replication scenario (also managed by Pacemaker) used to work, and still works, on PostgreSQL 9.5 through 11. We only encountered this problem on PostgreSQL 12.2. Please let me know if there is any more relevant information I can provide.
Hello Works for me. Please share results of such queries: select * from pg_stat_wal_receiver; select * from pg_file_settings where name = 'primary_conninfo'; Cluster name will be used as fallback application name when no application_name was provided. I think you have several primary_conninfo,one of which is in postgresql.auto.conf that was created by pg_basebackup -R regards, Sergei
On 09.03.20 15:31, Sergei Kornilov wrote: > Hello > > Works for me. Thank you very much for the quick reply and pointing me to the solution! > Please share results of such queries: > > select * from pg_stat_wal_receiver; > select * from pg_file_settings where name = 'primary_conninfo'; > > Cluster name will be used as fallback application name when no application_name was provided. I think you have severalprimary_conninfo, one of which is in postgresql.auto.conf that was created by pg_basebackup -R You are absolutely right, there was a postgresql.auto.conf created by pg_basebackup -R that did not contain the application_name field in the primary_conninfo setting. Once I deleted that file and reloaded the PostgreSQL streaming client everything started working as expected. In the past the Pacemaker resource agent would overwrite any recovery.conf file that might have been left by pg_basebackup -R so this discrepancy in primary_conninfo fields would never be a problem. But now, in accordance with PostgreSQL 12, the Pacemaker resource agent manages the file /var/lib/pgsql/tmp/recovery.conf which contains the primary_conninfo setting. That file is included from postgresql.conf: include '/var/lib/pgsql/tmp/recovery.conf' # added by pgsql RA So the file postgresql.auto.conf is no longer deleted or overwritten, but remains in place, containing a "wrong" primary_conninfo setting. This trips up the Pacemaker resource agent. Thank you again for the most valuable pointer! I did not really believe this could be a proper bug in PostgreSQL itself but I was thoroughly out of ideas because I did not know or realize that postgresql.auto.conf even existed. Much appreciated! Best regards, Andreas