Upgrading SAS® Viya® 3.5 might result in PostgreSQL backup error "SSL SYSCALL error: EOF detected"


After upgrading SAS Viya 3.5 and running the playbook, you might encounter an error with the SAS® Infrastructure Data Server when the pg_basebackup runs. The error might look similar to the following:

LOG: f_do_pg_basebackup: /opt/sas/viya/home/postgresql11/bin/pg_basebackup -U sas -D /opt/sas/viya/config/data/sasdatasvrc/postgres/node2 -Xs -P -R -v
ERROR: f_do_pg_basebackup: failed '/opt/sas/viya/home/postgresql11/bin/pg_basebackup -U sas -D /opt/sas/viya/config/data/sasdatasvrc/postgres/node2 -Xs -P -R -v'. rC=1
ERROR: f_config_pg_standby: failed 'f_do_pg_basebackup'. rC=1
ERROR: f_config_pg_node: failed 'f_config_pg_standby'. rC=1
ERROR: f_setup_node_main: failed 'su -l sas -c "/bin/bash -c 'export CurrentLogFile=/opt/sas/viya/config/var/log/sasdatasvrc/postgres/node2/sds_setup_node.sh_20211222_193220_949.log; export PlaybookDeploymentFlg=1; . /opt/sas/viya/home/libexec/sasdatasvrc/script/sds_set_env_variable.sh /opt/sas/viya/config/etc/sasdatasvrc/postgres/node2/sds_env_var.sh; f_config_pg_node; exit $?'"'. rC=1
level=warn app=sas-crypto-management timestamp=2021-12-22T10:32:23.24703288Z function=certificate.GenerateVaultCertificateWithCSR msg="passphrase not provided, will not encrypt private key"
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: B05/90000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_25121"
0/8288693 kB (0%), 0/1 tablespace (...svrc/postgres/node2/backup_label)
59633/8288693 kB (0%), 0/1 tablespace (...c/postgres/node2/base/13286/1255)
173792/8288693 kB (2%), 0/1 tablespace (...ostgres/node2/base/16401/1754195)
283857/8288693 kB (3%), 0/1 tablespace (...tgres/node2/base/16401/1759397.1)
398001/8288693 kB (4%), 0/1 tablespace (...tgres/node2/base/16401/1759397.1)
495921/8288693 kB (5%), 0/1 tablespace (...tgres/node2/base/16401/1759397.1)
610097/8288693 kB (7%), 0/1 tablespace (...tgres/node2/base/16401/1759397.1)
724305/8288693 kB (8%), 0/1 tablespace (...tgres/node2/base/16401/1759397.1)
838385/8288693 kB (10%), 0/1 tablespace (...tgres/node2/base/16401/1759397.1)
952600/8288693 kB (11%), 0/1 tablespace (...ostgres/node2/base/16401/1758096)
1066859/8288693 kB (12%), 0/1 tablespace (...ostgres/node2/base/16401/1759428)
1181068/8288693 kB (14%), 0/1 tablespace (...ostgres/node2/base/16401/1754196)
1292270/8288693 kB (15%), 0/1 tablespace (...ostgres/node2/base/16401/1754315)
1392541/8288693 kB (16%), 0/1 tablespace (...ostgres/node2/base/16401/1754192)
pg_basebackup: could not read COPY data: SSL SYSCALL error: EOF detected
pg_basebackup: removing contents of data directory "/opt/sas/viya/config/data/sasdatasvrc/postgres/node2"
ERROR: f_do_pg_basebackup: failed '/opt/sas/viya/home/postgresql11/bin/pg_basebackup -U sas -D /opt/sas/viya/config/data/sasdatasvrc/postgres/node2 -Xs -P -R -v'. rC=1
ERROR: f_config_pg_standby: failed 'f_do_pg_basebackup'. rC=1
ERROR: f_config_pg_node: failed 'f_config_pg_standby'. rC=1
ERROR: f_setup_node_main: failed 'su -l sas -c "/bin/bash -c 'export CurrentLogFile=/opt/sas/viya/config/var/log/sasdatasvrc/postgres/node2/sds_setup_node.sh_20211222_193220_949.log; export PlaybookDeploymentFlg=1; . /opt/sas/viya/home/libexec/sasdatasvrc/script/sds_set_env_variable.sh /opt/sas/viya/config/etc/sasdatasvrc/postgres/node2/sds_env_var.sh; f_config_pg_node; exit $?'"'. rC=1
ERROR: main: failed 'f_setup_node_main'. rC=1

This issue might occur if the SAS Infrastructure Data Server (PostgreSQL) is very large.

To get past the current issue and help prevent the issue in the future, you can extend the time that the playbook allows for the backup and replication by editing the async, retries, and delay values in the pgpool and sasdatasvrc start-up scripts.

  1. Take a back-up and plan for a production-down situation.

  2. As the SAS user, check the cluster status:
    • cd /opt/sas/viya/config/data/sasdatasvrc/postgres/pgpool0
    • ./statusall​​​​​​

  3. If the status of the cluster shows failed nodes, then you need to delete the contents of those nodes so that they can be re-created by the primary.
    • Log on to the Standby node as root. Note: Make sure that you are in the STANDBY node. Accidentally deleting the primary node is not recoverable without a full restore of the SAS Viya system.
    • rm -rf /opt/sas/viya/config/data/sasdatasvrc/postgres/node#/*
      • where node# is the failed Standby node number
  4. As the ROOT user, re-create the node manually:
    • time bash -c '/opt/sas/viya/home/libexec/sasdatasvrc/script/sds_setup_node.sh -config_path /opt/sas/viya/config/etc/sasdatasvrc/postgres/node#/sds_env_var.sh
      • where node# is the failed Standby node number

  5. After successful completion of step 4, make a note of the "time" that it took.

  6. Back up both playbook files:
    • <sasinstall>/sas_viya_playbook/roles/sasdatasvrc-x64_redhat_linux_6-yum/tasks/start.yml
    • <sasinstall>/sas_viya_playbook/roles/pgpoolc-x64_redhat_linux_6-yum/tasks/start.yml

  7. Convert the "real" time given into seconds. This value plus 300 multiplied by 2 should be your new async time.
    • For example, if the time was 25:19, the total seconds (1511 + 300) x 2 = 3622. Round this number to the closest 10th (3620) and this would be your new async time in both files from step 6.

  8. Adjust the async, retries, and delay values in both files from step 6 to match your new async time by dividing async by 10 (the delay). Here is an example:
    • async = 3620
    • delay = 10
    • retries = 362

These should be your new values going forward. Remember that if you update the playbooks at any time in the future, you must readjust these values for your specific system needs.