postgresql_db module zcat error restoring sql.gz files

[alanbchristie]

SUMMARY

When I use the dump option of the postgresql_db module to create a .sql.gz file, the restore mechanism generates an error claiming the file does not exist. It looks like a problem with the use of the underlying zcat utility because it does work if the target does not use gz compression, e.g. if the target is /tmp/dump.sql.

ISSUE TYPE

• Bug Report

COMPONENT NAME

module: community.postgresql.postgresql_db
state: restore

ANSIBLE VERSION

ansible [core 2.14.5]
  config file = /Users/alan/Code/InformaticsMatters/ansible-gizmos/ansible.cfg
  configured module search path = ['/Users/alan/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Users/alan/Library/Caches/pypoetry/virtualenvs/ansible-gizmos-s2R2cgEh-py3.10/lib/python3.10/site-packages/ansible
  ansible collection location = /Users/alan/.ansible/collections:/usr/share/ansible/collections
  executable location = /Users/alan/Library/Caches/pypoetry/virtualenvs/ansible-gizmos-s2R2cgEh-py3.10/bin/ansible
  python version = 3.10.8 (main, Oct 13 2022, 10:18:28) [Clang 13.0.0 (clang-1300.0.29.30)] (/Users/alan/Library/Caches/pypoetry/virtualenvs/ansible-gizmos-s2R2cgEh-py3.10/bin/python)
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

N/A

CONFIGURATION

ANSIBLE_NOCOWS(/Users/alan/Code/InformaticsMatters/ansible-gizmos/ansible.cfg) = True
CONFIG_FILE() = /Users/alan/Code/InformaticsMatters/ansible-gizmos/ansible.cfg

OS / ENVIRONMENT

Running on the control machine: -

OS: macOS Big Sur Version 11.7.6

STEPS TO REPRODUCE

Dump a PostgreSQL DB to a sql.gz file: -

- name: Dumping database
  community.postgresql.postgresql_db:
    name: "{{ kdd_db_database }}"
    state: dump
    target: /tmp/dump.sql.gz
    login_host: "127.0.0.1"

And then try and restore with: -

- name: Restoring database
  community.postgresql.postgresql_db:
    name: "{{ kdr_db_database }}"
    state: restore
    target: /tmp/dump.sql.gz
    login_host: "127.0.0.1"

EXPECTED RESULTS

restore should work

ACTUAL RESULTS

restore fails claiming that there is no such file or directory.

TASK [k8s_database_restore : Restoring database ************************************************
fatal: [localhost]: FAILED! => {"changed": false, "cmd": "cmd: ****", "msg": "zcat: can't stat: /tmp/datatier.sql.gz (/tmp/datatier.sql.gz.Z): No such file or directory\n", "rc": 1, "stdout": "", "stdout_lines": []}

[Andersson007]

@alanbchristie hello, thanks for reporting the issue!

In the error message, we see that zcat appends .Z to the filename.
As described on that page

The names of compressed input files are expected to end in .Z, .gz, or .bz2. If a specified input file name does not end in this suffix, zcat searches for a file named file.Z, file.gz, or file.bz2. For example, if the command line specifies file abc, zcat looks for abc.Z, abc.gz, or abc.bz2. If none of these are found, zcat uses abc with no extension.

After googling a bit, found out that we are not alone, see this stack overflow post. It's zcat MacOS specific behavior.

Not sure, what we can do with this. Any ideas would be appreciated

[alanbchristie]

Wow - that is frustrating.

My opinion? There are a number of options I guess: -

• A: It's the Ansible module's responsibility to unpack it using whatever means necessary. After all it was "happy enough" to create the file in the first place, and it knows I'm on macOS. On macOS, if it has to use zcat then it could temporarily rename the file to keep zcat happy?
• B: Use a different compression tool to pack and unpack? Is there a reason zcat is used? Is gzip a universal tool?
• C: Declare clearly in the documentation that .gz compression does not work on macOS. But that sounds lame (see A or B)

I think it's A or B?

[Andersson007]

@alanbchristie thanks for the options!

I'm personally inclining to A. Not sure about renaming though. If something goes wrong during the execution, the file can stay renamed, which might be not what our users expect.

We could, say:

• add a note that if you're on MacOS, your compressed dump files must have .Z suffix
• add try-except and then parse the error message:
  • If it matches the error we see here (i.e. no file with Z at the end) and it's MacOS, fail with message "Rename the file to make it end with .Z"

@alanbchristie @hunleyd @betanummeric @aleksvagachev what do you folks think?

[betanummeric]

The particular issue can probably solved quite easily by giving the compressed file on stdin to the uncompressor, instead of a command line argument. This should be tested carefully with all supported compressors though.

Further, I think we should offer a module argument to specify a custom (de)compression command that overrides the file-suffix-based guesswork.
This is a postgres module, not a compression module - others can do compression better and should have the power to do so. Nonetheless, we can continue to support common formats with reasonable effort.