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

Question

I take backup using   pg dump db production  gt  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

User · Answer

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

User · Answer

From the pg dump documentation   Examples  To dump a database called mydb into a SQL-script file     pg dump mydb  gt  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  gt  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  gt  db dump   To drop the database and recreate it from the dump     dropdb mydb   pg restore -C -d postgres db dump

User · Answer

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

User · Answer

The answer above didn t work for me  this worked   psql db development  lt  postgres db dump

User · Answer

For me  It s working like this one  C  Program Files PostgreSQL 12 bin gt  psql -U postgres -p 5432  -d dummy -f C  Users Downloads d2cm test sql

User · Answer

For me when i try to restore from remote host i used psql -U username -p 5432 -h 10 10 10 1 -d database  lt  db dump  worked fine  And if not remote just following command worked  psql -d database  lt  db dump

User · Answer

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

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

Examples related to postgresql

Examples related to backup

Examples related to pg-restore