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

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

psql db_development < 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

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

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

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?

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.

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

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

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

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

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)