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

score 198 · Answer 1 · edited Apr 01 '20 at 19:07

198

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

edited Apr 01 '20 at 19:07

Ronan Boiteau

9,608
6
34
56

answered Nov 16 '16 at 12:34

Roman Kiselenko

43,210
9
91
103

3

when i do pg_restore I get pg_restore: [archiver] input file appears to be a text format dump. Please use psql. – Haseeb Ahmad Nov 16 '16 at 12:37
Which one I use to restore db ? – Haseeb Ahmad Nov 16 '16 at 12:40
pg_restore -d newdb db.dump ?? It gives me same error – Haseeb Ahmad Nov 16 '16 at 12:41
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`. – Roman Kiselenko Nov 16 '16 at 12:42
214

this does not answer the question – dopatraman Dec 08 '17 at 00:27
2

for a way to restore from a text file checkout https://serverfault.com/questions/260607/pg-dump-and-pg-restore-input-file-does-not-appear-to-be-a-valid-archive – chrs Mar 19 '18 at 13:56
24

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. – Matt May 03 '19 at 16:23
1

The answer *does* answer the question, though the comment of @Matt 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](https://stackoverflow.com/a/59307721/11154841) 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. – questionto42 Aug 29 '21 at 18:04

Uziel Valdez · Answer 2 · 2018-11-14T17:39:29.850

129

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

psql db_development < postgres_db.dump

edited Nov 14 '18 at 17:39

answered Aug 24 '18 at 17:41

Uziel Valdez

2,040
1
15
20

3

Short and sweet. Worked for me also. – Pupil Jul 03 '19 at 12:08
3

Worked fine. I would just add the -U, like psql -Umyuser db_development < postgres_db.dump – RicHincapie Feb 25 '21 at 16:05
Nice, It also works for me. – Abdul Basit Oct 04 '21 at 09:43
My data file is a plain sql file so it prompted me to use this way. Thanks. – Danniel Little May 03 '22 at 18:27

score 72 · Answer 3 · answered Dec 12 '19 at 15:16

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

This answer best addresses the actual question. – wgj Oct 20 '21 at 16:12 — wgj, Oct 20 '21 at 16:12

score 37 · Answer 4 · edited Jul 27 '20 at 00:09

37

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

edited Jul 27 '20 at 00:09

Viraj Singh

1,951
1
17
27

answered Nov 11 '19 at 05:22

Tserenjamts

579
6
15

1

This command helps me. Thanks a lot. – Veleirian Nov 29 '21 at 09:25

score 12 · Answer 5 · edited Jan 19 '21 at 21:06

12

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

edited Jan 19 '21 at 21:06

luckydonald

5,976
4
38
58

answered Mar 31 '20 at 13:16

RAJNISH YADAV

141
1
6

score 7 · Answer 6 · answered Dec 27 '21 at 08:44

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

score 7 · Answer 7 · answered Mar 20 '22 at 11:38

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.

answered Mar 20 '22 at 11:38

Fatih Ceylan

91
1
2
5

score 4 · Answer 8 · answered Nov 22 '19 at 13:27

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?

score 4 · Answer 9 · answered May 25 '22 at 04:15

4

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

answered May 25 '22 at 04:15

Supun Sandeeptha

127
1
2

score 3 · Answer 10 · answered Apr 21 '22 at 07:12

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

score 2 · Answer 11 · answered Sep 05 '18 at 13:56

2

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

answered Sep 05 '18 at 13:56

M2E67

937
7
23

This should be the accepted answer. `pg_restore` is not the right command if you have a text dump. – c0mr4t Dec 06 '22 at 00:38

score 1 · Answer 12 · answered Nov 24 '22 at 14:46

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

score 0 · Answer 13 · answered Nov 22 '21 at 08:09

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

score 0 · Answer 14 · answered Nov 29 '21 at 09:30

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)

score -2 · Answer 15 · answered Jun 29 '22 at 18:30

-2

here is the solution,

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

answered Jun 29 '22 at 18:30

Vaibhav Jain

305
3
8

Does this work for you? Because this is exactly the command which leads to he error mentioned in the question. – Matěj Račinský Feb 05 '23 at 16:33

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

15 Answers15

Linked

Related