postgresqldockerbackupdatabase-restore

Incorrect characters after restore with Docker Postgres database

I make a backup with the command

docker exec -t arcane-aio pg_dump arcane-aio -c -U admin > arcane_aio_db.sql

I restore the backup with the command

cat arcane_aio_db.sql | docker exec -i arcane-aio psql -U admin -d arcane-aio

All is good, but all Russian symbols are replaced by "?".

The string value before the restore is Привет, hi!. The string value after the restore of the backup is ??????, hi!.

I checked the encoding of the backup, the database before the backup, the database after the restore, and they are the same (en_US.utf8). Could it be that this encoding don't support the Russian language?

We are using Windows.

After a change of the system encoding from Cyrillic to UTF-8, the values in the data dump become correct.

But after the restore, we still see "?" instead of Russian symbols in the database.

Solution

  • the cat command uses your shell character encoding.

    Did you try running simply the first part:

    cat arcane_aio_db.sql

    I bet it also shows the ???.

    You need to set the charset to the same encoding on both sides. You probably have UTF-8 on one side and some russian language on the other.

    The pipe, that writes to file is binary and doesn't care about the encoding, but cat does.

    You can check your encoding with

    echo $LANG

    make sure it is UTF-8 on both sides and that should fix your issue.

    ** EDIT

    a work-around is to do the backup and restore within the container:

    #get into the container
docker exec -it arcane-aio /bin/bash
# in the container run:
pg_dump arcane-aio -c -U admin > arcane_aio_db.sql
# try restore:
cat arcane_aio_db.sql | psql -U admin -d arcane-aio

    if that works, then it's an encoding issue between your docker container and local machine. You can do the dump / restore within the container and copy the file in/out with docker cp

    On another thought, the SQL you 'cat' may contain quotes or $ or # or other characters that are problematic sent directly into a TTY.

    So you may want to try this instead, to make sure the whole thing is quoted:

    eval "echo \"$(cat arcane_aio_db.sql)\"" | docker exec -t arcane-aio psql -U admin -d arcane-aio