1

I get my PostgreSQL database backup using the following command:

pg_dump --encoding utf8 "host=localhost port=5432 
    dbname=employee user=postgres" > C:\backup\employee.sql

Note: I tried both with and without --encoding parameter. Also tried to use many different file format for backup as: dump, tar, etc.


Then I try to restore database using the following command:

psql -e -h 127.0.0.1 -p 5432 -U postgres -d employee -f C:\backup\employee.sql

There are lots of lines on the console during restore process, but at the end of this log, the following error is shown:

psql:C:\backup\employee.sql:1: ERROR: syntax error at or near "ÿs" LINE 1: ÿs-ebSG:9loios3"s"des9",e3s{0LrT}0se"me"D13,mispRio""""aild{...

How can I fix it? I might convert backup file encoding via conv, but as I use Windows, I cannot used it.

Update:

  1. Postgres version I am dumping from: PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by Debian clang version 10.0.1 , 64-bit

  2. Postgres version I am restoring to: PostgreSQL 11.12 (Debian 11.12-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

  3. pg_dump version I am using: I have a look at the container to inspec pg_dump version, but cannot get the pg_dump folder.

Improve this question
5
  • 1
    Update your question to include following: 1) What is the encoding for database employee as shown by \l in both the source and target databases? 2) What is line output just before and after the error? If it where me I would drop the -e on the restore. That just generates lines that overshadow what you are really after, the errors. Commented Jun 2, 2021 at 20:01
  • 1) Source Database: Encoding: UTF8, Collation / Character Type: en_US.UTF8. The source database is created before restore process. 2) Some errors e.g. "pg_restore: error: could not set default_table_access_method" Commented Jun 2, 2021 at 20:32
  • Please read the entire comment. I asked that you add this information to your question along with the encoding for the target database. Also add to question 1) Postgres version your are dumping from? 2) Postgres version you are restoring to? 3) pg_dump version you are using? Commented Jun 2, 2021 at 20:43
  • @AdrianKlaver Please see my update. Thanks. Commented Jun 4, 2021 at 12:24
  • To get the pg_dump version do pg_dump -V. Also your original commands show you working in a Windows file system, yet the Postgres versions show them being on Linux. Please explain the discrepancy. Commented Jun 4, 2021 at 14:36

1 Answer 1

Reset to default
2

This is a classical error due to the fact that PostgerSQL is unable to execute a binary backup, but only a dump which is a copie of the values of the rows of the table. So literrals are extracts as it, and number or dates are convert into litterals. Then SQL commands are added to recreate the structure and the INSERTs.

The result is a texte file that can be interpreted as a SQL script to reconstitute the database.

But this method as a major disadvantage... The dump file must be encoded in a proper coding way to avoid some combination of bytes that can drive the file to be restored in a situation that the reader process interprets thoses bytes as non printed commands like carriage return, line feed...

In other databases like SQL Server there is no dump, but only binary backups and the binary pages are only replaced from the backup file to the target data file which avoid all those troubles.

So be carefull when doing a dump, use the recommanded encoding even when extracting the data or import in to re create the db.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot, voted up. Would you suggest a proper backup and restore script with necessary flags e.g. encoding? Actually I also tried to set encoding while backup, but it dopes not seem to fix the problem during restore procedures. Also, do you suggest to use dump for backup? I am really confused as there are multiple ways for that and generally I am between pg_restore and psql especially for restoring :(
This answer contains erroneous information. Postgres can take a binary backup, see Pg basebackup.
PG_basebackup take a copy of file, note pages. This is not what is a binary backup like Oracle, SQL Server, IBM DB2 or Sybase ASE do...
No solution as there are still unanswered questions out, namely what version of pg_dump is being used and is the OP working in Windows or Linux or across OS'es? Without those answers anything is a guess at best, see your answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.