In PostgreSQL how do I generate a script that will recreate the database and all of its contents?

Question

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?

Answer 1

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

Answer 2

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.