CODEWITHSUNDEEP
postgresqlpostgresql-9.1
Josiane Ferice
Josiane Ferice

Reputation: 941

Backing up PostgreSQL Database in Linux

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.

Upvotes: 2

Views: 1977

Answers (1)

leonbloy
leonbloy

Reputation: 75896

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.

Upvotes: 2

Related Questions