1

I'm trying to generate a SQL script that will recreate a Database I have on my installation of PostgreSQL but I have not been successful yet.

I'm able to create a backup file of my database using the following:

"C:\Program Files\PostgreSQL\14\bin\pg_dump" -U postgres -p 5432 -W -F p -h localhost TEST_DB > C:\Postgres_DB_Backups\TEST_DB.sql

When I open this file, which is a backup of my database, and try to run that file in pgAdmin, I get a "PostgreSQL restore error: "syntax error at or near "x"".

Not sure what's causing that error though because all I did was backup the database and make no other changes. I searched on stack overflow for similar issues and all I saw was something about it could be an encoding issue but it did not mention any way to address the issue if that is indeed the problem.

What exactly did I do wrong? What is the best way to backup a database in PostgreSQL?

2 Answers 2

2

Don't use shell redirection on Windows with pg_dump. The shell will helpfully "correct" encoding issues and corrupt your dump. Instead, specify the output filename with the -f option:

"C:\Program Files\PostgreSQL\14\bin\pg_dump" -U postgres -p 5432 -W -F p -h localhost -f C:\Postgres_DB_Backups\TEST_DB.sql TEST_DB

To restore using psql, which also supports -f:

psql -U postgres -f C:\Postgres_DB_Backups\TEST_DB.sql TEST_DB_DB 

A similar problem was seen in this question: issues with encoding and pg_dump/restore between windows and linux

2
  • Should I change my restore commands as well? Or is what I have below ok? C:\Program Files\PostgreSQL\14\bin> psql -U postgres TEST_DB_DB < C:\Postgres_DB_Backups\TEST_DB.sql
    – JTD2021
    Oct 9, 2022 at 0:22
  • I added an example of psql usage to the answer.
    – dwhitemv
    Oct 9, 2022 at 1:41
1

A dump generated by pg_dump cannot be run as an SQL script in pgAdmin because of the way it mixes COPY statements and data. If you use the --inserts option of pg_dump, you might have more luck.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.