Backing up PostgreSQL Database in Linux

Question

I have to create a postgresql database back up(only the schema) in Linux and restore it on a window machine. I backed up the database with option -E, but I was not able to restore it on the window machine.

Here is the command that I used to backup the database

pg_dump -s -E SQL_ASCII books > books.backup

below is the error message that I received when I tried to restore it.

C:/Program Files/PostgreSQL/9.3/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "books" --role "sa" --no-password  --list "C:\progress_db\test1"
pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

Am I supposed to use a different command or am I missing something? Your help is greatly appreciated.

Did you read the error message? "input file appears to be a text format dump. Please use psql" — leonbloy
– leonbloy, Commented May 21, 2014 at 20:16
I did read the error message. It was the main reason why I used the option -E SQL_ASCII, so that it does not create a text format file. — Josiane Ferice
– Josiane Ferice, Commented May 21, 2014 at 20:21
-E sets the character encoding, it has nothing to do with the format. Look for -F. postgresql.org/docs/9.3/interactive/app-pgdump.html Anyway, you 'd normally prefer to do the text dump. — leonbloy
– leonbloy, Commented May 21, 2014 at 20:22
Thanks, I think it's my understanding of the option E that is creating the confusion for me. — Josiane Ferice
– Josiane Ferice, Commented May 21, 2014 at 20:31

leonbloy · Accepted Answer · 2014-05-27 14:35:33Z

2

The first lines in the official docs about restoring say:

The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is

psql dbname < infile

where infile is the file output by the pg_dump command

Or simply read the error message.

The option -E SQL_ASCII only sets the character encoding, it has nothing to do with the format of the dump. By default, pg_dump generates a text file that contains the SQL statements to regenerate the database; in this case, to restore the database you only need to execute the sql commands in the file, as in a psql terminal - that's why a simple psql dbname < mydump is the way to go.

pg_restore is to be used with the alternative "binary" -postgresql specific- format of pg_dump.

edited May 27, 2014 at 14:35

answered May 21, 2014 at 20:20

leonbloy

76.5k22 gold badges149 silver badges198 bronze badges

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

Collectives™ on Stack Overflow

Backing up PostgreSQL Database in Linux

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related