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

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

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

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

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

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

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

psql db_development < postgres_db.dump

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

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?