Stack Overflow
Stack Overflow

Questions tagged [pg-restore]

288 questions
223
votes
15 answers

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…
Haseeb Ahmad
  • 7,914
  • 12
  • 55
  • 133
172
votes
14 answers

Getting "[archiver] unsupported version (1.13) in file header" when running pg_restore

I just upgraded to postgres 10.2 on mac os which matches 10.2 on heroku. I'm trying to download a copy of the database and restore it locally. Before the upgrade the restore would work fine. I run pg_restore --verbose --clean --no-acl --no-owner…
Mark Swardstrom
  • 17,217
  • 6
  • 62
  • 70
88
votes
8 answers

I want to restore the database with a different schema

I have taken a dump of a database named temp1, by using the follwing command $ pg_dump -i -h localhost -U postgres -F c -b -v -f pub.backup temp1 Now I want to restore the dump in a different database called "db_temp" , but in that I just want…
abubacker
  • 4,638
  • 6
  • 32
  • 35
82
votes
2 answers

Why is pg_restore returning successfully but not actually restoring my database?

I have a Postgres 8.4 database on a linux server that I have dumped using the following command: pg_dump --format=c --exclude-table=log --file=/path/to/output my_db I then ftp the created file to my local Windows 7 machine and attempt to restore…
Mike Deck
  • 18,045
  • 16
  • 68
  • 92
75
votes
3 answers

How do I convert a binary pgdump (compressed) to a plain SQL file?

I do want to search for some data inside a database dump but these dumps are using the binary-compressed format (PGDMP header). How can I convert these to SQL without restoring them?
sorin
  • 161,544
  • 178
  • 535
  • 806
70
votes
6 answers

pg_restore: [archiver] unsupported version (1.14) in file header

I have a live server and development box, call them live and dev respectively both running postgresql. I can see both and manage both with pgadmin4 without trouble, and both are fully functional the one being a live website and the other where I run…
Bernd Wechner
  • 1,854
  • 1
  • 15
  • 32
41
votes
4 answers

Exclude Table during pg_restore

UPDATE: Was able to exclude the data in the table durning the pg_dump command. Makes it even faster than trying to not load the data because you don't have to wait for that data to be dumped. --exclude-table-data=event_logs (PostgreSQL) 9.4.4 Anyone…
covard
  • 1,667
  • 2
  • 18
  • 33
39
votes
2 answers

Is there a "pg_restore --quiet" option like "psql --quiet"?

psql has a -q / --quiet option (environment variable QUIET). pg_restore does not have a quiet option. Is there any way to make pg_restore not verbosely show the SQL commands that it's executing? # e.g., here's the verbose output that I don't want…
Rob Bednark
  • 25,981
  • 23
  • 80
  • 125
38
votes
1 answer

will pg_restore overwrite the existing tables?

Say I have two host servers s1 and s2. In both the servers i have a schema named n1, now i have made some changes to some of the tables present in schema n1 of s1. I want the same change to be done to schema n1 of server s2. what i am planning to do…
Karthik
  • 629
  • 2
  • 8
  • 12
32
votes
3 answers

pg_restore toc error

i was using the following syntax for pg_dump and restore pg_dump eval --inserts -b -c --encoding UTF8 -Fc -f eval.sql.tar.gz -x -U postgres createdb -T template0 test -U postgres pg_restore -d test eval.sql.tar.gz -e -U postgres the dump was…
MAHI
  • 9,263
  • 11
  • 36
  • 47
29
votes
4 answers

pg_restore with -C option does not create the database

I am using pg_dump and pg_restore for backup and restore of postgres database. Here is some information from documentation that will be relevant for this question For Pg_restore, -C option is described as follows -C --create Create the database…
Swapnil17
  • 666
  • 1
  • 10
  • 22
28
votes
4 answers

Postgres errors on ARM-based M1 Mac w/ Big Sur

Ever since I got a new ARM-based M1 MacBook Pro, I've been experiencing severe and consistent PostgreSQL issues (psql 13.1). Whether I use a Rails server or Foreman, I receive errors in both my browser and terminal like PG::InternalError: ERROR:…
Carl
  • 401
  • 1
  • 8
  • 12
25
votes
5 answers

Restore dump on the remote machine

I've got my own machine with postgres dmp file, which I want to restore on the remote virtual machine (e.g. ip is 192.168.0.190 and postgres port is 5432) in my network. Is it possible to restore this dump using pg_restore without copying dump to…
Danila Zharenkov
  • 1,720
  • 1
  • 15
  • 27
24
votes
5 answers

postgresql pg_dump without schema name

pg_dump version 10.3 started adding the schema name explicitly to the table name. So what used to be INSERT INTO table (id) VALUES (1); Is now INSERT INTO public.table (id) VALUES (1); How can you switch it off? My problem is, that I dump this…
raumi75
  • 341
  • 1
  • 2
  • 4
23
votes
6 answers

Postgres Tutorial: pg_restore: [archiver] input file does not appear to be a valid archive

I'm working through the Postgres DVD tutorial and am running into issues importing their sample database. Running pg_restore -U postgres -d dvdrental ~[filepath]/dvd-database.tar.gz gives me pg_restore: [archiver] input file does not appear to be a…
Charlie Weems
  • 1,682
  • 1
  • 16
  • 28
1
2 3
…
19 20