TCA 2.1 unable to perform postgres restore using CN Backup file

Products

• VMware
• VMware Telco Cloud Automation

Issue/Introduction

How to manually restore the backup with Network Slicing enabled

Symptoms:
When performing postgres restore does not work as expected for CN backed-up copy with Network Slicing enabled

Log Snippet:

Log Snippet:

1. {"level":"info","msg":"Restoring postgres file location /tmp/backup/TCA-BACKUP-BUNDLE-2022-06-28T03_38_10Z/postgres/tca-mgr/pg_data.out","time":"2022-06-28T03:45:16Z"}
{"level":"info","msg":"pgDir /tmp/backup/TCA-BACKUP-BUNDLE-2022-06-28T03_38_10Z/postgres/tca-mgr/pg_data.out ","time":"2022-06-28T03:45:16Z"}
{"level":"error","msg":"exit status 2, psql: error: connection to server at \"postgres.tca-mgr\" (100.67.173.248), port 5432 failed: FATAL:  password authentication failed for user \"postgres\"\n,","time":"2022-06-28T03:45:16Z"}
{"level":"error","msg":"Couldn't complete postgres operation Get database names  exit status 2","time":"2022-06-28T03:45:16Z"}

2. the following roles kills the pgautofailover_monitor, pgautofailover_replicator restore process due to already existing roles

Note: The preceding log excerpts are only examples. Date, time, and environmental variables may vary depending on your environment.

Environment

VMware Telco Cloud Automation 2.0

Resolution

VMware is aware of this issue as reported in TCA 2.1, current workaround is available as mentioned below to address the issue.

Workaround:
Manual restore of postgres using the following steps:


1. Untar backup bundle

 1.1 Delete the following folders:

  <bundlename>/secrets/tca-mgr/tca-admin-cred-secret
  <bundlename>/secrets/tca-mgr/postgres-db-secret
  <bundlename>/secrets/tca-mgr/postgres-app-user-db-secret
  <bundlename>/secrets/tca-mgr/network-slicing-cred-secret
  <bundlename>/secrets/tca-mgr/keycloak-user-cred-secret

 1.2 Copy the files to another location

<bundlename>/postgres/tca-mgr/pg_data.out
<bundlename>/postgres/tca-system/pg_data.out

 1.3 Tar the file back for restore purposes

2. Kubectl edit statefulset postgres -n tca-mgr

   Change the following attribute: replicas to 0

3. Perform restore with tar file created from step 1.3 and wait for it complete

4. Once the restore is over, perform the following:

Kubectl edit statefulset postgres -n tca-mgr

Change the following attribute: replicas to 2

5. Remove the following role content from tca-mgr/pg_data.out

--
-- Roles
--

CREATE ROLE audit;
ALTER ROLE audit WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE audit_service_db_user;
ALTER ROLE audit_service_db_user WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:6zwfey5zVfLrSoHFKzxubA==$V1e3FGVpYS+Fq2nCppGrvGu43DHZhv9uadhPjOA5Fzs=:a1g8/YKkrPvEhAQV4DzCi/0fHHsqnKkS2CtjShWf27M=';
CREATE ROLE keycloak;
ALTER ROLE keycloak WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE keycloak_user;
ALTER ROLE keycloak_user WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:oh/vS42tJ7UmCOW0G+6h+g==$jkhCCFpoiG8tuaxsVHcLg4eHKZ5VPm1pF5ozBgMmnrM=:2lTYipsJS/ntYpoDsz2nGpZEuOFYDqgRQJ3oaUCidPI=';
CREATE ROLE network_slicing;
ALTER ROLE network_slicing WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:7DxRwwuN55SxiG5Lidv5IA==$LDAFKmMjd2+OjA3VatcVGqOw0OJCrgYP0Dhu+OlPjbc=:yKG6vNjd+sZW9tSMo9Z9BYxYF8Zxo5kH4bfTCi3r5Qk=';
CREATE ROLE nsmf;
ALTER ROLE nsmf WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE nssmf;
ALTER ROLE nssmf WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE nssmf_ran;
ALTER ROLE nssmf_ran WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE pgautofailover_monitor;
ALTER ROLE pgautofailover_monitor WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 1 PASSWORD 'SCRAM-SHA-256$4096:TXFQAOw85k1H3h/mDoboxA==$X+Wb3XaQDfTCHZkmrpshgdjN62wtKm9y6c8QNuWzpzo=:rsuZRr97qOvO74cO8H1ZEWkIlAxr4FksyqPQiRkLPWQ=';
CREATE ROLE pgautofailover_replicator;
ALTER ROLE pgautofailover_replicator WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:uBn1gCxBN5CdYZixwQhR2g==$3GFfWOcEhgM39DQE6wD56J5Qh+JHLHERETbDes+7Qhs=:88I+y+XkYli993rf+4CbIQxlqtTwjHbmdJmZoBjj8a0=';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:j+oR4+gs11889HVb6ng47A==$FS3bhH23uqV/Ah1v15dPKOTzmhky2/cEDYFswiU9lY4=:vXiVUFbymZRBfNMfOQ8MPhxS2E1o/UP2HzugcMV3y/M=';
CREATE ROLE postgres_app_user;
ALTER ROLE postgres_app_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:Hpd9jfFCCk1Ya53rJPNQaw==$cYLeBIYnXmE34tV6T8ETongDQUGoTFitobP/SSMSdtM=:LDzwXoy/3ShT5ZcDOh4sckIbl/dvTOWUM+eiTDA/91Q=';
CREATE ROLE postgres_exporter;
ALTER ROLE postgres_exporter WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:o0tA9EzqnBohZHD4//zevA==$Ma/ZGgePQXnlsgo43Yne6kquRWjJnNvj36GZ6qYTUEQ=:zCH5YKrDsd8/z+ImOZCEeDWgM8yMHAi5O9fMctPK9NI=';
CREATE ROLE sms;
ALTER ROLE sms WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE tca;
ALTER ROLE tca WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE tca_admin;
ALTER ROLE tca_admin WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:5DImlXq25V9ENkodSLfMsw==$sKRAHQ0jKI8WiAgC+QiC5IgKcQ/3ZBWxQ3zkNhEVkAY=:6Ns+/FazBqaemZdCRhXFJnU7BQXNBj6TynWNBX2u4TQ=';
--
-- User Configurations
--

--
-- User Config "postgres_exporter"
--

ALTER ROLE postgres_exporter SET search_path TO 'postgres_exporter', 'pg_catalog';


--
-- Role memberships
--

GRANT audit TO audit_service_db_user GRANTED BY postgres;
GRANT keycloak TO keycloak_user GRANTED BY postgres;
GRANT nsmf TO network_slicing GRANTED BY postgres;
GRANT nssmf TO network_slicing GRANTED BY postgres;
GRANT nssmf_ran TO network_slicing GRANTED BY postgres;
GRANT postgres_exporter TO postgres GRANTED BY postgres;
GRANT sms TO network_slicing GRANTED BY postgres;
GRANT tca TO tca_admin GRANTED BY postgres;

5.2 Remove the following role content from tca-system/pg_data.out

--
-- Roles
--

CREATE ROLE pgautofailover_monitor;
ALTER ROLE pgautofailover_monitor WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 1 PASSWORD 'SCRAM-SHA-256$4096:d62aNsbxXpxCTbbEhP0ihA==$KfDxWaum+gqV+LQjWKiBYy4BwOOuJE6hSx7qV70JP3Q=:mqkwzppiVZw0/OkNs3XY8BP6u8FBLYq2f8/7OvMgCCE=';
CREATE ROLE pgautofailover_replicator;
ALTER ROLE pgautofailover_replicator WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN REPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:iN2JKfe1VMD7D1VYbQqqdA==$rtFeDCiorEgTtSsMeb7IVT6rk2VNQrYZgfUeL1FqsN4=:dn5LlO2LYkfEglJTLdtbTaXxYnEikslg5qxE+L/IFPI=';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:QUb2d0erVt8WK3QrlHn0rw==$u2kVhNg4c/4ABpwRFkDoBwFM0Nir6P3TVLutot1KqwE=:WKN/2OWBgNIPGMWJ2B252y5ZiR/3OiglXoEV/4I1CDA=';
CREATE ROLE postgres_app_user;
ALTER ROLE postgres_app_user WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:0nhz3wtZICVT4Es6zsX3oQ==$oYZh5488OeZFSrtowKn2MLq7p20L0HbDHfk3jygTZIE=:Um9IaP2WALL1CO+hQsim/QyAgTRWTxP0f4xUNfr97NE=';
CREATE ROLE postgres_exporter;
ALTER ROLE postgres_exporter WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:Rg8buFXzRAvfbmaunp0cig==$IcPku00CE7J5K1SvLTbk9sssr3//djWLR2JV46JPOUc=:lep5WuIwM2dOV/AJxWkvKBbzrZYCc93OHacCZTmjmuM=';
CREATE ROLE tca;
ALTER ROLE tca WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
CREATE ROLE tca_admin;
ALTER ROLE tca_admin WITH NOSUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256$4096:ceXkZNv8s8efNsPyImvfpQ==$QnY4WUcCgdND0woncUBY7O0z+MnOB3msL6hTerSDQlk=:ClVpCYFJmEMmFp+vupDUXZ6T6f2KLWLBNX3SK2T3RRY=';
--
-- User Configurations
--

--
-- User Config "postgres_exporter"
--

ALTER ROLE postgres_exporter SET search_path TO 'postgres_exporter', 'pg_catalog';


--
-- Role memberships
--

GRANT postgres_exporter TO postgres GRANTED BY postgres;
GRANT tca TO tca_admin GRANTED BY postgres;

6. Copy the tca-mgr/pg_data.out to postgres pod using the following command:

   Kubectl cp pg_data.out tca-mgr/<primary postgres node>:/tmp/pg_data.out

6.1. Copy the tca-system/pg_data.out to postgres pod using the following command:

   Kubectl cp pg_data.out tca-system/<primary postgres node>:/tmp/pg_data.out

7. Perform restore manually:

 kubectl exec -ti postgres-0 -n tca-mgr bash
​​​​​​​
 psql -U postgres -f tmp/pg_data.out

 kubectl exec -ti postgres-0 -n tca-system bash

 psql -U postgres -f tmp/pg_data.out

8. Restore the replicas:

 Kubectl edit statefulset postgres -n tca-mgr