input file appears to be a text format dump. Please use psql

Question

I take backup using

pg_dump db_production > postgres_db.dump

and then I copy it to localhost using scp.

Now when I import on my local db it gives an error

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

by using commad line

pg_restore -d db_development postgres_db.dump

Piderit

Answer 1

From the pg_dump documentation:

Examples

To dump a database called mydb into a SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql

To dump a database into a custom-format archive file:

$ pg_dump -Fc mydb > db.dump

To dump a database into a directory-format archive:

$ pg_dump -Fd mydb -f dumpdir

To reload an archive file into a (freshly created) database named newdb:

$ pg_restore -d newdb db.dump

From the pg_restore documentation:

Examples

Assume we have dumped a database called mydb into a custom-format dump file:

$ pg_dump -Fc mydb > db.dump

To drop the database and recreate it from the dump:

$ dropdb mydb
$ pg_restore -C -d postgres db.dump

Araujo

when i do pg_restore I get pg_restore: [archiver] input file appears to be a text format dump. Please use psql. – Piderit

Which one I use to restore db ? – Piderit

pg_restore -d newdb db.dump ?? It gives me same error – Piderit

Looks like you didn't read the answer. To dump pg_dump mydb > db.sql, to load (in a fresh db) psql -d newdb -f db.sql. – Araujo

this does not answer the question – Breannebrear

for a way to restore from a text file checkout serverfault.com/questions/260607/… – Salty

IMO this post has a little too much detail which obfuscates the answer. What the author failed to point out is that the pg_dump documentation for the '-F' parameter does not say the default format ('p'/'plain') is suitable for pg_restore. pg_restore requires that pg_dump be used with the 'c', 'd', or 't' formats. – Niblick

The answer does answer the question, though the comment of @Niblick is right that this is not made clear enough: the answer should explain in more detail that the custom format is needed, as the 2019 answer does, but the comments and their votes are misleading. They are from those who do not know the importance of -F and just go on to the next answer. – Macleod

Answer 2

The answer above didn't work for me, this worked:

psql db_development < postgres_db.dump

Bridges

Short and sweet. Worked for me also. – Camire

Worked fine. I would just add the -U, like psql -Umyuser db_development < postgres_db.dump – Homestretch

Nice, It also works for me. – Chumash

My data file is a plain sql file so it prompted me to use this way. Thanks. – Marxist

Answer 3

In order to create a backup using pg_dump that is compatible with pg_restore you must use the --format=custom / -Fc when creating your dump.

From the docs:

Output a custom-format archive suitable for input into pg_restore.

So your pg_dump command might look like:

pg_dump --file /tmp/db.dump --format=custom --host localhost --dbname my-source-database --username my-username --password

And your pg_restore command:

pg_restore --verbose --clean --no-acl --no-owner --host localhost --dbname my-destination-database /tmp/db.dump

Freddie

This answer best addresses the actual question. – Gausman

Answer 4

For me when i try to restore from remote host i used

psql -U username -p 5432 -h 10.10.10.1 -d database < db.dump

worked fine. And if not remote just following command worked.

psql -d database < db.dump

Flintlock

This command helps me. Thanks a lot. – Lowpressure

Answer 5

For me, It's working like this one.

C:\Program Files\PostgreSQL\12\bin> psql -U postgres -p 5432  -d dummy -f C:\Users\Downloads\d2cm_test.sql

Spurious

Answer 6

If you restore .SQL file. Create a new database in pgAdmin. Go to the terminal and navigate the folder/directory where your .sql file is located. And then write the following command in terminal.

Syntax: supername user postgres psql newDatabasename < inputfile.sql

Examaple:

sudo -u postgres psql newDb < restoreDb.sql

Bungalow

Answer 7

I've got same error when tried to backup db with DBeaver. If anyone uses DBeaver interface instead of command line on Windows, make sure your selected format as tar during backup and restore settings.

Randolphrandom

Answer 8

psql -U <username> -d <database-name> -h <host-name> -f <backup.sql>

Orlan

Answer 9

If you have a full DB dump:

PGPASSWORD="your_pass" psql -h "your_host" -U "your_user" -d "your_database" -f backup.sql

If you have schemas kept separately, however, that won't work. Then you'll need to disable triggers for data insertion, akin to pg_restore --disable-triggers. You can then use this:

cat database_data_only.gzip | gunzip | PGPASSWORD="your_pass" psql -h "your_host" -U root "your_database" -c 'SET session_replication_role = replica;' -f /dev/stdin

On a side note, it is a very unfortunate downside of postgres, I think. The default way of creating a dump in pg_dump is incompatible with pg_restore. With some additional keys, however, it is. WTF?

Latrena

Answer 10

Providing a simple one line answer which worked for me and will work for you too for most cases

psql -U username -d database_name < dump_file.sql

If above gives role related errors then replace username with postgres.

psql -U postgres -d database_name < dump_file.sql

Poss

Answer 11

if you use pg_dump with -Fp to backup in plain text format, use following command:

cat db.txt | psql dbname

to copy all data to your database with name dbname

Septal

This should be the accepted answer. pg_restore is not the right command if you have a text dump. – Preconceive

Answer 12

The error is Caused due to the sql is dumped without using -Fc format arguments.

The dump can be taken from -

pg_dump -h localhost -U postgres -Fc database_name > ~/psql/database_name.dump

the same can now be restored as with pg_restore-

pg_restore -h localhost -U postgres -d database_name ~/psql/database_name.dump

Reason :-

1. The Default dump from pg_dump can only be read using psql command restore. Like -

   psql -h localhost -U postgres database_name < database_name.dump

2. It is compulsory to provide -Fc in pg_dump, if you consider to restore from pg_restore

Amenable

Answer 13

I've been struggling with this as well. This is the combination of dump & restore commands that worked for me:

pg_dump -Ft -C -h database_host -U username database > DATA.dump

To restore

pg_restore -x --no-owner -d database DATA.dump

Remove the -x flag if you want to keep the same access privileges (ACLs) in your DB. You must have the same roles and users in the database for this.

https://www.postgresql.org/docs/15/app-pgdump.html

https://www.postgresql.org/docs/15/app-pgrestore.html

Alded

Answer 14

Probably when you create a backup you want to restore it in another network or create a remote restoration.

We need to create a backup file using the --format=custom [-Fc] to restore it using pg_restore. We can use a connection string postgresql://<user>:<pass>@localhost:5432/<dbname> and replace <user>, <pass>, and <dbname> with your information.

pg_dump -v -Fc \
postgresql://<user>:<pass>@localhost:5432/<dbname> \
> db-20211122-163508.sql

To restore we will call it using --clean [-c] and --create [-C] to drop the database before restoring. Replace <user>, <host>, <port>, and <dbname> with your information.

pg_restore -vcC \
-U <user> \
-h <host> \
-p <port> \
-d <dbname> \
< db-20211122-163508.sql

Natalienatalina

Answer 15

If you backup with this way, I think this will be more easy to import database.

pg_dump -h (remote db address) -a --column-inserts -U postgres (database name) > (file name).sql

For import,

psql
-f (file name).sql
--host (remote db address)
--port 5432
--username postgres
--password (your password)
--dbname (database you want to import)

Lowpressure

Answer 16

here is the solution,

pg_restore -U username -p 5432 -h 10.10.10.1 -d database_name < dump_file

Salsala

Does this work for you? Because this is exactly the command which leads to he error mentioned in the question. – Duane