Bug 2231473

Tested also with pod offline:


$ starttime=$(date +%F_%H-%M-%S)

$ noobaa=noobaa-db

$ oc get sts noobaa-db-pg -o yaml > ${noobaa-db}.${starttime}.yaml


$ oc patch sts noobaa-db-pg -n openshift-storage -p '{"spec": {"template": {"spec": {"containers": [{"name": "db", "command": ["sleep"], "args": ["infinity"]}]}}}}'

Wait for new pod noobaa-db-pg-0 to deploy

$ oc get pods -w

  containers:
  - args:
    - infinity
    command:
    - sleep

$ oc rsh noobaa-db-pg-0

$ pg_ctl status -D /var/lib/pgsql/data/userdata
pg_ctl: server is running (PID: 1)
/usr/bin/postgres

$ pg_ctl stop -D /var/lib/pgsql/data/userdata
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down
sh-4.4$ pg_ctl restart -D /var/lib/pgsql/data/userdata
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down

$ ls -ld /var/lib/pgsql/data/userdata
drwsrws---. 20 10001 1000690000 4096 Aug  8 00:00 /var/lib/pgsql/data/userdata

sh-4.4$ chmod 4700 /var/lib/pgsql/data/userdata
sh-4.4$ ls -ld /var/lib/pgsql/data/userdata
drws--S---. 20 10001 1000690000 4096 Aug  8 00:00 /var/lib/pgsql/data/userdata


$ pg_ctl stop -D /var/lib/pgsql/data/userdata
waiting for server to shut down............................................................... failed
pg_ctl: server does not shut down

Hi Amit, 

Thank you for the update.

I will collect the needed data.

Hi Amit,

Customer updated:

these are log entries in the recent PG log

2023-08-16 07:33:21.667 UTC [38] ERROR:  tuple already updated by self
2023-08-16 07:33:21.667 UTC [38] STATEMENT:  ALTER USER "noobaa" WITH ENCRYPTED PASSWORD 't/HQKQTq7zuoFQ==';
2023-08-16 07:38:23.345 UTC [24] LOG:  database system was interrupted; last known up at 2023-08-16 07:33:21 UTC
2023-08-16 07:38:23.411 UTC [24] LOG:  database system was not properly shut down; automatic recovery in progress
2023-08-16 07:38:23.423 UTC [24] LOG:  invalid record length at 8/42A493F0: wanted 24, got 0
2023-08-16 07:38:23.423 UTC [24] LOG:  redo is not required
2023-08-16 07:38:23.491 UTC [22] LOG:  database system is ready to accept connections
2023-08-16 07:38:23.553 UTC [38] ERROR:  tuple already updated by self
2023-08-16 07:38:23.553 UTC [38] STATEMENT:  ALTER USER "noobaa" WITH ENCRYPTED PASSWORD 't/HQKQTq7zuoFQ==';
2023-08-16 07:43:32.412 UTC [24] LOG:  database system was interrupted; last known up at 2023-08-16 07:38:23 UTC
2023-08-16 07:43:32.440 UTC [24] LOG:  database system was not properly shut down; automatic recovery in progress
2023-08-16 07:43:32.451 UTC [24] LOG:  invalid record length at 8/42A49468: wanted 24, got 0
2023-08-16 07:43:32.451 UTC [24] LOG:  redo is not required
2023-08-16 07:43:32.518 UTC [22] LOG:  database system is ready to accept connections
2023-08-16 07:43:32.574 UTC [38] ERROR:  tuple already updated by self
2023-08-16 07:43:32.574 UTC [38] STATEMENT:  ALTER USER "noobaa" WITH ENCRYPTED PASSWORD 't/HQKQTq7zuoFQ==';
2023-08-16 07:48:42.408 UTC [24] LOG:  database system was interrupted; last known up at 2023-08-16 07:43:32 UTC
2023-08-16 07:48:42.433 UTC [24] LOG:  database system was not properly shut down; automatic recovery in progress
2023-08-16 07:48:42.444 UTC [24] LOG:  invalid record length at 8/42A494E0: wanted 24, got 0
2023-08-16 07:48:42.444 UTC [24] LOG:  redo is not required
2023-08-16 07:48:42.518 UTC [22] LOG:  database system is ready to accept connections
2023-08-16 07:48:42.566 UTC [38] ERROR:  tuple already updated by self
2023-08-16 07:48:42.566 UTC [38] STATEMENT:  ALTER USER "noobaa" WITH ENCRYPTED PASSWORD 't/HQKQTq7zuoFQ==';
2023-08-16 07:53:57.337 UTC [24] LOG:  database system was interrupted; last known up at 2023-08-16 07:48:42 UTC
2023-08-16 07:53:57.398 UTC [24] LOG:  database system was not properly shut down; automatic recovery in progress
2023-08-16 07:53:57.411 UTC [24] LOG:  invalid record length at 8/42A49558: wanted 24, got 0
2023-08-16 07:53:57.411 UTC [24] LOG:  redo is not required
2023-08-16 07:53:57.475 UTC [22] LOG:  database system is ready to accept connections
2023-08-16 07:53:57.548 UTC [38] ERROR:  tuple already updated by self
2023-08-16 07:53:57.548 UTC [38] STATEMENT:  ALTER USER "noobaa" WITH ENCRYPTED PASSWORD 't/HQKQTq7zuoFQ==';
2023-08-16 07:59:04.400 UTC [24] LOG:  database system was interrupted; last known up at 2023-08-16 07:53:57 UTC
2023-08-16 07:59:04.427 UTC [24] LOG:  database system was not properly shut down; automatic recovery in progress
2023-08-16 07:59:04.440 UTC [24] LOG:  invalid record length at 8/42A495D0: wanted 24, got 0
2023-08-16 07:59:04.440 UTC [24] LOG:  redo is not required
2023-08-16 07:59:04.504 UTC [22] LOG:  database system is ready to accept connections
2023-08-16 07:59:04.560 UTC [38] ERROR:  tuple already updated by self


$ oc rsh noobaa-db-pg-0
Defaulted container "db" out of: db, init (init)
error: unable to upgrade connection: container not found ("db")

$ oc debug pod/noobaa-db-pg-0
Warning: would violate PodSecurity "restricted:latest": allowPrivilegeEscalation != false (containers "init", "db" must set securityContext.allowPrivilegeEscalation=false), unrestricted capabilities (containers "init", "db" must set securityContext.capabilities.drop=["ALL"]), seccompProfile (pod or containers "init", "db" must set securityContext.seccompProfile.type to "RuntimeDefault" or "Localhost")
Starting pod/noobaa-db-pg-0-debug ...
Pod IP: 10.141.15.243
If you don't see a command prompt, try pressing enter.
sh-4.4$


sh-4.4$ pg_ctl start -D /var/lib/pgsql/data/userdata
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2023-08-16 08:03:49.077 UTC [18] LOG:  starting PostgreSQL 12.12 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
2023-08-16 08:03:49.078 UTC [18] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-08-16 08:03:49.078 UTC [18] LOG:  listening on IPv6 address "::", port 5432
2023-08-16 08:03:49.090 UTC [18] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-08-16 08:03:49.100 UTC [18] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-16 08:03:49.152 UTC [18] LOG:  redirecting log output to logging collector process
2023-08-16 08:03:49.152 UTC [18] HINT:  Future log output will appear in directory "log".
 done
server started
sh-4.4$

sh-4.4$ psql
psql (12.12)
Type "help" for help.

postgres=# ANALYZE VERBOSE;
INFO:  analyzing "pg_catalog.pg_type"
INFO:  "pg_type": scanned 10 of 10 pages, containing 406 live rows and 0 dead rows; 406 rows in sample, 406 estimated total rows
INFO:  analyzing "pg_catalog.pg_foreign_server"
INFO:  "pg_foreign_server": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_authid"
INFO:  "pg_authid": scanned 1 of 1 pages, containing 10 live rows and 17 dead rows; 10 rows in sample, 10 estimated total rows
INFO:  analyzing "pg_catalog.pg_statistic_ext_data"
INFO:  "pg_statistic_ext_data": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_user_mapping"
INFO:  "pg_user_mapping": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_subscription"
INFO:  "pg_subscription": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_attribute"
INFO:  "pg_attribute": scanned 54 of 54 pages, containing 2913 live rows and 0 dead rows; 2913 rows in sample, 2913 estimated total rows
INFO:  analyzing "pg_catalog.pg_proc"
INFO:  "pg_proc": scanned 79 of 79 pages, containing 2960 live rows and 0 dead rows; 2960 rows in sample, 2960 estimated total rows
INFO:  analyzing "pg_catalog.pg_class"
INFO:  "pg_class": scanned 13 of 13 pages, containing 395 live rows and 0 dead rows; 395 rows in sample, 395 estimated total rows
INFO:  analyzing "pg_catalog.pg_attrdef"
INFO:  "pg_attrdef": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_constraint"
INFO:  "pg_constraint": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  analyzing "pg_catalog.pg_inherits"
INFO:  "pg_inherits": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_index"
INFO:  "pg_index": scanned 4 of 4 pages, containing 159 live rows and 0 dead rows; 159 rows in sample, 159 estimated total rows
INFO:  analyzing "pg_catalog.pg_operator"
INFO:  "pg_operator": scanned 14 of 14 pages, containing 770 live rows and 0 dead rows; 770 rows in sample, 770 estimated total rows
INFO:  analyzing "pg_catalog.pg_opfamily"
INFO:  "pg_opfamily": scanned 2 of 2 pages, containing 107 live rows and 0 dead rows; 107 rows in sample, 107 estimated total rows
INFO:  analyzing "pg_catalog.pg_opclass"
INFO:  "pg_opclass": scanned 2 of 2 pages, containing 128 live rows and 0 dead rows; 128 rows in sample, 128 estimated total rows
INFO:  analyzing "pg_catalog.pg_am"
INFO:  "pg_am": scanned 1 of 1 pages, containing 7 live rows and 0 dead rows; 7 rows in sample, 7 estimated total rows
INFO:  analyzing "pg_catalog.pg_amop"
INFO:  "pg_amop": scanned 6 of 6 pages, containing 715 live rows and 0 dead rows; 715 rows in sample, 715 estimated total rows
INFO:  analyzing "pg_catalog.pg_amproc"
INFO:  "pg_amproc": scanned 3 of 3 pages, containing 447 live rows and 0 dead rows; 447 rows in sample, 447 estimated total rows
INFO:  analyzing "pg_catalog.pg_language"
INFO:  "pg_language": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
INFO:  analyzing "pg_catalog.pg_largeobject_metadata"
INFO:  "pg_largeobject_metadata": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_aggregate"
INFO:  "pg_aggregate": scanned 2 of 2 pages, containing 136 live rows and 0 dead rows; 136 rows in sample, 136 estimated total rows
INFO:  analyzing "pg_catalog.pg_largeobject"
INFO:  "pg_largeobject": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_statistic_ext"
INFO:  "pg_statistic_ext": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_rewrite"
INFO:  "pg_rewrite": scanned 12 of 12 pages, containing 126 live rows and 0 dead rows; 126 rows in sample, 126 estimated total rows
INFO:  analyzing "pg_catalog.pg_trigger"
INFO:  "pg_trigger": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_event_trigger"
INFO:  "pg_event_trigger": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_description"
INFO:  "pg_description": scanned 41 of 41 pages, containing 4714 live rows and 0 dead rows; 4714 rows in sample, 4714 estimated total rows
INFO:  analyzing "pg_catalog.pg_cast"
INFO:  "pg_cast": scanned 2 of 2 pages, containing 216 live rows and 0 dead rows; 216 rows in sample, 216 estimated total rows
INFO:  analyzing "pg_catalog.pg_enum"
INFO:  "pg_enum": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_namespace"
INFO:  "pg_namespace": scanned 1 of 1 pages, containing 6 live rows and 0 dead rows; 6 rows in sample, 6 estimated total rows
INFO:  analyzing "pg_catalog.pg_conversion"
INFO:  "pg_conversion": scanned 3 of 3 pages, containing 132 live rows and 0 dead rows; 132 rows in sample, 132 estimated total rows
INFO:  analyzing "pg_catalog.pg_depend"
INFO:  "pg_depend": scanned 56 of 56 pages, containing 7601 live rows and 0 dead rows; 7601 rows in sample, 7601 estimated total rows
INFO:  analyzing "pg_catalog.pg_database"
INFO:  "pg_database": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
INFO:  analyzing "pg_catalog.pg_db_role_setting"
INFO:  "pg_db_role_setting": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_tablespace"
INFO:  "pg_tablespace": scanned 1 of 1 pages, containing 2 live rows and 0 dead rows; 2 rows in sample, 2 estimated total rows
INFO:  analyzing "pg_catalog.pg_pltemplate"
INFO:  "pg_pltemplate": scanned 1 of 1 pages, containing 8 live rows and 0 dead rows; 8 rows in sample, 8 estimated total rows
INFO:  analyzing "pg_catalog.pg_auth_members"
INFO:  "pg_auth_members": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
INFO:  analyzing "pg_catalog.pg_shdepend"
INFO:  "pg_shdepend": scanned 1 of 1 pages, containing 46 live rows and 0 dead rows; 46 rows in sample, 46 estimated total rows
INFO:  analyzing "pg_catalog.pg_shdescription"
INFO:  "pg_shdescription": scanned 1 of 1 pages, containing 3 live rows and 0 dead rows; 3 rows in sample, 3 estimated total rows
INFO:  analyzing "pg_catalog.pg_ts_config"
INFO:  "pg_ts_config": scanned 1 of 1 pages, containing 22 live rows and 0 dead rows; 22 rows in sample, 22 estimated total rows
INFO:  analyzing "pg_catalog.pg_ts_config_map"
INFO:  "pg_ts_config_map": scanned 3 of 3 pages, containing 418 live rows and 0 dead rows; 418 rows in sample, 418 estimated total rows
INFO:  analyzing "pg_catalog.pg_ts_dict"
INFO:  "pg_ts_dict": scanned 1 of 1 pages, containing 22 live rows and 0 dead rows; 22 rows in sample, 22 estimated total rows
INFO:  analyzing "pg_catalog.pg_ts_parser"
INFO:  "pg_ts_parser": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "pg_catalog.pg_ts_template"
INFO:  "pg_ts_template": scanned 1 of 1 pages, containing 5 live rows and 0 dead rows; 5 rows in sample, 5 estimated total rows
INFO:  analyzing "pg_catalog.pg_extension"
INFO:  "pg_extension": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
INFO:  analyzing "pg_catalog.pg_foreign_data_wrapper"
INFO:  "pg_foreign_data_wrapper": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_foreign_table"
INFO:  "pg_foreign_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_policy"
INFO:  "pg_policy": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_replication_origin"
INFO:  "pg_replication_origin": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_default_acl"
INFO:  "pg_default_acl": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_init_privs"
INFO:  "pg_init_privs": scanned 2 of 2 pages, containing 171 live rows and 0 dead rows; 171 rows in sample, 171 estimated total rows
INFO:  analyzing "pg_catalog.pg_seclabel"
INFO:  "pg_seclabel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_shseclabel"
INFO:  "pg_shseclabel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_collation"
INFO:  "pg_collation": scanned 25 of 25 pages, containing 779 live rows and 0 dead rows; 779 rows in sample, 779 estimated total rows
INFO:  analyzing "pg_catalog.pg_partitioned_table"
INFO:  "pg_partitioned_table": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_range"
INFO:  "pg_range": scanned 1 of 1 pages, containing 6 live rows and 0 dead rows; 6 rows in sample, 6 estimated total rows
INFO:  analyzing "pg_catalog.pg_transform"
INFO:  "pg_transform": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_sequence"
INFO:  "pg_sequence": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_publication"
INFO:  "pg_publication": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_publication_rel"
INFO:  "pg_publication_rel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "pg_catalog.pg_subscription_rel"
INFO:  "pg_subscription_rel": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
INFO:  analyzing "information_schema.sql_packages"
INFO:  "sql_packages": scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
INFO:  analyzing "information_schema.sql_features"
INFO:  "sql_features": scanned 8 of 8 pages, containing 716 live rows and 0 dead rows; 716 rows in sample, 716 estimated total rows
INFO:  analyzing "information_schema.sql_implementation_info"
INFO:  "sql_implementation_info": scanned 1 of 1 pages, containing 12 live rows and 0 dead rows; 12 rows in sample, 12 estimated total rows
INFO:  analyzing "information_schema.sql_parts"
INFO:  "sql_parts": scanned 1 of 1 pages, containing 9 live rows and 0 dead rows; 9 rows in sample, 9 estimated total rows
INFO:  analyzing "information_schema.sql_languages"
INFO:  "sql_languages": scanned 1 of 1 pages, containing 4 live rows and 0 dead rows; 4 rows in sample, 4 estimated total rows
INFO:  analyzing "information_schema.sql_sizing"
INFO:  "sql_sizing": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows
INFO:  analyzing "information_schema.sql_sizing_profiles"
INFO:  "sql_sizing_profiles": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
ANALYZE
postgres=#
postgres=# VACUUM FULL ANALYZE pg_statistic;
VACUUM
postgres=#

Hi Amit,

Thank you for the update, ill reach out to cu with ask.