Issues with running pg_dump with docker exec?

[rosenfeld]

Hi, today I noticed a weird behavior when trying to restore a dump from my back-ups to my local machine. pg_restore returned successful but without restoring anything from a 600MB dump. With --verbose it reported data-only was implied but I didn't ask for data-only. I was creating the back-up with docker exec container-name pg_dump -Fc dbname > /tmp/db.dump. The dump file was created in the Docker host machine as expected but for some reason I was not able to restore from that dump. If I use an external postgresql-client package in the host, then the dump file works as expected. Is docker exec appending anything else to the output that could be causing such bad dump file? Maybe the documentation could add a warning if this is the case. If I knew that beforehand I'd probably added some volume mapping to local back-ups (-v /tmp/db-dumps:/db-dumps). Anyway, I'm curious. Has someone else experienced such weird behavior? If so, do you know why it doesn't work as expected?

[rosenfeld]

The message I got with --verbose included this: "pg_restore: implied data-only restore"

[MFProduction]

+1

[tianon]

Sorry for the delay -- I'm not able to reproduce the issue. When I run docker exec --user postgres test pg_dump -Fc postgres > test.dump and then inspect the resulting file, there's nothing extra in the file (it's simply the binary data generated by pg_dump itself):

$ docker exec -i --user postgres test pg_restore < test.dump
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.1
-- Dumped by pg_dump version 10.1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE postgres IS 'default administrative connection database';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- PostgreSQL database dump complete
--

[kingkuong]

I'm not sure if you want to close this issue, ran into the same problem today.

If I'm sh into the container and run pg_dump and pg_restore inside the container, it works normally.

Running docker exec returns the same error pg_restore: implied data-only restore

[tianon]

@cuongtranx can you please provide a complete set of minimal example commands to reproduce the issue?

[kingkuong]

Sure:

docker exec db-container pg_dump -Fc -U username -d dbname > ./dump
docker exec db-container pg_restore -Fc -v -U username -d dbname /tmp/dump

verbose returns the following message: error pg_restore: implied data-only restore

I've tried to create dump as text (without -Fc flag) and it seems to run normally. It's the custom format that's having issues.

[tianon]

That's not a complete example -- how does /tmp/dump get into the container for the second command? Are you doing a bind mount? Are those two separate containers with separate /var/lib/postgresql/data volumes?

[eburgueno]

(Apologies. Please ignore previous, deleted comments)

I traced this back to using docker exec -t instead of just docker exec. The TTY adds EOL characters that corrupt the binary output.

Here's a reproducer: sql.txt

[RafalSkolasinski]

Sorry for commenting in and old issue but this is the first thread that actually helped me solve a similar problem (I was invoking pg_dump using docker-compose exec).
In my case I had to add -T flag to disable the TTY. Just posting it here in case someone will have similar issues.

[elitan]

Don't know what's going on here but I had to NOT use -t in docker exec to get this to work. I had the following error: pg_restore: error: could not read from input file: end of file.

This is the command that worker for me:

docker exec postgres_docker pg_dump -Fc -U postgres > db.dump

This did not work:

docker exec -t postgres_docker pg_dump -Fc -U postgres > db.dump

[estan]

Sorry for necroposting, but @elitan I suspect that @RafalSkolasinski above was actually talking about docker-compose exec, not docker, since -T is the option for docker-compose exec to disable allocation of a pseudo-TTY, while in docker exec it is the absense of -t.