Stack Overflow
Stack Overflow

Questions tagged [pg-dump]

pg_dump is a utility for backing up a PostgreSQL database.

656 questions
273
votes
8 answers

How to create a backup of a single table in a postgres database?

Is there a way to create a backup of a single table within a database using postgres? And how? Does this also work with the pg_dump command?
Elitmiar
  • 35,072
  • 73
  • 180
  • 229
206
votes
32 answers

Postgresql 9.2 pg_dump version mismatch

I am trying to dump a Postgresql database using the pg_dump tool. $ pg_dump books > books.out How ever i am getting this error. pg_dump: server version: 9.2.1; pg_dump version: 9.1.6 pg_dump: aborting because of server version mismatch The…
Chris Colla
  • 2,163
  • 2
  • 13
  • 5
124
votes
2 answers

How can I get pg_dump to authenticate properly

I have tried using host variable PGPASSWORD and .pgpass and neither of these two will allow me to authenticate to the database. I have chmod'd .pgpass to appropriate permissions and also tried: export PGPASSWORD=mypass and PGPASSWORD=mypass The…
Kosmonaut
  • 2,154
  • 2
  • 18
  • 19
102
votes
5 answers

pg_dump postgres database from remote server when port 5432 is blocked

I'm trying to pg_dump a SQL database on a remote server in our DMZ. There are 2 problems. there is not a lot of space left on the remote server so the normal command run to locally backup the database pg_dump -C database > sqldatabase.sql.bak …
Anthony McGovern
  • 1,023
  • 2
  • 8
  • 5
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
69
votes
12 answers

postgresql where does the output of pg_dump go

I am trying to backup a db of postgresql and I want to use pg_dump command. I tried : psql -U postgres postgres-# pg_dump test > backup.sql But I don't know where the output file goes. Any help will be appreciated
Dariush Jafari
  • 5,223
  • 7
  • 42
  • 71
64
votes
7 answers

Easy way to view postgresql dump files?

I have a ton of postgresql dump files I need to peruse through for data. Do I have to install Postgresql and "recover" each one of them into new databases one by one? Or I'm hoping there's a postgresql client that can simply open them up and I can…
at.
  • 50,922
  • 104
  • 292
  • 461
48
votes
9 answers

pg_dump: too many command line arguments

what is wrong with this command: pg_dump -U postgres -W admin --disable-triggers -a -t employees -f D:\ddd.txt postgres This is giving error of too many command-line arguments
Bhargav Gor
  • 521
  • 2
  • 6
  • 7
47
votes
4 answers

How do I do a schema only backup and restore in PostgreSQL?

How do I take a schema level backup in PostgreSQL database and restore on the another database? Is there any single command available for this? For example, can I pg_dump and restore in single line?
user1671630
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
37
votes
7 answers

How to fix pg_dump version mismatch errors?

When trying to get local data to Heroku, I am encountering a version mismatch between two different versions of pg_dump. Specifically, I am getting this message: pg_dump: server version: 9.2.2; pg_dump version: 9.1.4 pg_dump: aborting because of…
user1147171
  • 1,213
  • 3
  • 14
  • 22
36
votes
2 answers

Postgresql: backup all table structures but only a few data table

I have a database with some tables for the application settings, lists like users, departments, cities. I want the structure and the data for those tables. So if i get a new user the backup will save it. But also have some data for historic and…
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
1
2 3
…
43 44