Postgresql: restore database from dump sql file error [closed]

Question

There is a database that runs in the docker container . I need to make a local copy of this database. To do this I use the command

docker exec -t 5a5c84157ffe pg_dumpall -c -U postgres | gzip > /home/jekson/dump_$(date +"%Y-%m-%d_%H_%M_%S").sql`

As a result I get dump_2021-09-03_11_44_36.sql file. Next create an empty database and a user for it locally. And try to restore data

psql -h localhost -d marketing -U marketing -f dump_2021-09-03_11_44_36.sql

But got an error

psql:dump_2021-09-03_11_44_36.sql:2: ERROR:  invalid byte sequence for encoding "UTF8": 0x8b
psql:dump_2021-09-03_11_44_36.sql:12: error: invalid command \T�F5��
                                                                    �a��M
psql:dump_2021-09-03_11_44_36.sql:14: error: invalid command \��p���ϦM)=�K�"�y�tÂ�"9���Ӕ�����1%�2j��F~��]0w��2C�ɦ
psql:dump_2021-09-03_11_44_36.sql:16: error: invalid command \�!O�C�ө�l?�m��G'�����qf#"z[�`�}-�ʷ��?S��u>�F1-w[�
psql:dump_2021-09-03_11_44_36.sql:17: error: invalid command \ݫ|�����e��)y��^t��-�<��'����a3��'�Ns�
psql:dump_2021-09-03_11_44_36.sql:18: error: invalid command \<R4�.^�@���9�
psql:dump_2021-09-03_11_44_36.sql:19: error: invalid command \�V8ro_�
psql:dump_2021-09-03_11_44_36.sql:30: error: invalid command \�Ԁ�<o��$��G&C
psql:dump_2021-09-03_11_44_36.sql:32: error: invalid command \0�&���
psql:dump_2021-09-03_11_44_36.sql:34: error: invalid command \��w���%���4:"��v�sj�����)Ѽ4je��xl�;������$��j�����j۞J����!�QT�0�/�%�6�O��j��ȫb�GP��S
psql:dump_2021-09-03_11_44_36.sql:35: error: invalid command \~S��H��1�������d$�{�ujx�B]�1�GC��B�ᕜ�NjgwA���j10�I�)����?Ѭ]���]P�&���
psql:dump_2021-09-03_11_44_36.sql:39: error: invalid command \"��X�=��?ʹ=���g
psql:dump_2021-09-03_11_44_36.sql:42: error: invalid command \�����ߵ��,�
                                                                        �
                                                                         ;����Ӷw
psql:dump_2021-09-03_11_44_36.sql:48: error: invalid command \zz:<o�Z��I(F��*����am�y�`��(�2Ŕd�7�u�3���p����.��k�M��M6
psql:dump_2021-09-03_11_44_36.sql:53: error: invalid command \l�����sP���}<�J�<?���v����?�l$MT2C)�<��
psql:dump_2021-09-03_11_44_36.sql:55: ERROR:  invalid byte sequence for encoding "UTF8": 0x8f
psql:dump_2021-09-03_11_44_36.sql:55: ERROR:  invalid byte sequence for encoding "UTF8": 0x97
psql:dump_2021-09-03_11_44_36.sql:55: ERROR:  invalid byte sequence for encoding "UTF8": 0xf6 0x43 0xed 0x93
psql:dump_2021-09-03_11_44_36.sql:56: ERROR:  invalid byte sequence for encoding "UTF8": 0xf9
psql:dump_2021-09-03_11_44_36.sql:56: error: invalid command \oU���G�Moc�
psql:dump_2021-09-03_11_44_36.sql:58: ERROR:  invalid byte sequence for encoding "UTF8": 0xd2 0xef
psql:dump_2021-09-03_11_44_36.sql:59: ERROR:  invalid byte sequence for encoding "UTF8": 0xac
psql:dump_2021-09-03_11_44_36.sql:66: ERROR:  invalid byte sequence for encoding "UTF8": 0xa9
psql:dump_2021-09-03_11_44_36.sql:87: error: invalid command \<$�
psql:dump_2021-09-03_11_44_36.sql:89: error: invalid command \�q���P�aya��<2
(��]�ޅ��͏���v���L#��2��O5��2B�S
psql:dump_2021-09-03_11_44_36.sql:95: error: invalid command \A
psql:dump_2021-09-03_11_44_36.sql:96: error: invalid command \&AT�x��p��Vp+���L��Ǩ8�(��UǝhU+�9�E�}�g����ɟ"
psql:dump_2021-09-03_11_44_36.sql:99: error: invalid command \��)���K|J8���ک"
psql:dump_2021-09-03_11_44_36.sql:108: error: invalid command \!���L#��K�pWJ�(0��
psql:dump_2021-09-03_11_44_36.sql:111: error: invalid command \���p_
psql:dump_2021-09-03_11_44_36.sql:132: error: invalid command \�yq�j�(����EQ&/Cny0���Nz��EFY���x�����2AE��7$��z�v/�t��P�����3ya#$yоԎ�b��?C���H��8C_�BŁH!
psql:dump_2021-09-03_11_44_36.sql:137: error: invalid command \�9s�X!�zM߶3=7Q�*T�Bo
psql:dump_2021-09-03_11_44_36.sql:141: error: invalid command \�L{�PA��m�NZnt)�y�_��
                                                                                    ٬���'�����˯^hvUi������>�5��B���r�
psql:dump_2021-09-03_11_44_36.sql:145: error: invalid command \l񡎷�?�_�po�����A����/c(h�������@��P���߈w�o^�����w��&|�:�ߟ9J���gx�3�������#���'�i���B>�I�b���/5e�'�O�
psql:dump_2021-09-03_11_44_36.sql:152: error: invalid command \[�gI?��6���W�$
psql:dump_2021-09-03_11_44_36.sql:155: error: invalid command \&Tʅ�sZ�7abw�w̘)KoRǥD���eAs�L,E�d�a�VD��h�E���u���b�v��_��֗Z�:��C-
psql:dump_2021-09-03_11_44_36.sql:160: error: invalid command \��a����
                                                                      +�]�Ґ�R���Ȋ���e��W
psql:dump_2021-09-03_11_44_36.sql:970815: ERROR:  invalid byte sequence for encoding "UTF8": 0xc0 0xa9

check encoding

postgres=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 UTF8
(1 row)

psql (PostgreSQL) 13.4 (Ubuntu 13.4-0ubuntu0.21.04.1)

Answer 1

As a result I get dump_2021-09-03_11_44_36.sql file ...

Yes, but what's in that file?
Let's pull that command of yours apart:

docker exec -t 5a5c84157ffe pg_dumpall -c -U postgres 

Use docker to run pg_dumpall as the user postgres and creating a character-based dump file.
So far so good.

| gzip 

Ah.
You're taking that plain text SQL files and piping it through the gzip utility, which outputs a compressed ZIP file ...

> /home/jekson/dump_$(date +"%Y-%m-%d_%H_%M_%S").sql 

... and writing that to your [supposedly, but not] sql file.

When you attempt to restore this, you're feeding the ZIP file into psql, not the plain text SQL that it expects - hence all the complaints.

You either need to unzip the file before feeding it into mysql ...

gunzip /home/jekson/dump_2021-09-03_11_44_36.sql \
   | psql -h localhost -d marketing -U marketing  

... or, probably simpler, don't bother using gzip at all.
YMMV, depending on just how big the dump file is.