Using pg_dump as logical backup rather than pg_dumpall

• Which image of the operator are you using?
  -- registry.opensource.zalan.do/acid/postgres-operator:v1.9.0
• Where do you run it - cloud or metal? Kubernetes or OpenShift?
  -- Bare Metal K8s
• Are you running Postgres Operator in production?
  -- Yes
• Type of issue?
  -- Feature request

Presently, the PostgreSQL Operator allows for logical backups using the pg_dumpall utility. However, during the restoration process, it restores all databases rather than the specific one we desire. This becomes problematic, especially on a fresh Postgres cluster, as it includes the restoration of the postgres database, resulting in the overwriting of all passwords. Consequently, when other pods access the updated secret, authentication issues arise due to the continued usage of outdated passwords.

I suggest enhancing the PostgreSQL operator by transitioning to the use of pg_dump for logical backups instead of pg_dumpall . This modification would yield a more efficient and user-friendly operator, enabling selective restoration of individual databases while avoiding the unintended modification of critical system databases.

Start by tracing the operator's logical backup and restoration flow, comparing the current pg_dumpall usage with the requested pg_dump behavior. Verify how a selected database is restored and whether the postgres database or its passwords are changed. Done means individual databases can be restored without overwriting unrelated system database credentials.