Stack Overflow
Stack Overflow

Questions tagged [pg-dump]

pg_dump is a utility for backing up a PostgreSQL database.

656 questions
7
votes
2 answers

How to check if postgresql backup was successful?

We have a postgresql database that is backed up nightly from a cron job with a following command: su postgres -c "pg_dump our_database | gzip > /home/smb/shared/database_backup.bak.gz" recently we had a disk failure that started with a few bad…
Dragan Matic
  • 329
  • 1
  • 3
  • 10
7
votes
3 answers

How to pg_restore one table and its schema from a Postgres dump?

I am having some difficulties with restoring the schema of a table. I dumped my Heroku Postgres db and I used pg_restore to restore one table from it into my local db (it has more than 20 tables). It was successfully restored, but I was having…
coredumped0x
  • 768
  • 1
  • 12
  • 28
7
votes
3 answers

pg_restore fails when trying to create function referencing table that does not exist yet

I've used pg_dump --no-privileges --format custom --compress=0 some_database > my-dump.pgdump to dump a database, but I'm running into issues when I try to restore it. Specifically, it appears to be loading function definitions before table…
David Wolever
  • 148,955
  • 89
  • 346
  • 502
7
votes
1 answer

Why does my db/structure.sql file contain a CREATE SCHEMA statement after running db:structure:dump?

After migrating my development database, I'm unable to run rails db:test:prepare. My application uses the db/structure.sql file to update the test database: # config/application.rb config.active_record.schema_format = :sql When I run rails…
ulysses_rex
  • 330
  • 3
  • 16
7
votes
1 answer

postgres dump with inserts but without copy actions

I am trying to dump my database. For reasons I can't go into detail here, I am unable to use the "Copy" function. This means that the insert statements have to be "pure" insert. However, I am not sure how to reflect this in my dump. Currently, this…
Marc Rasmussen
  • 19,771
  • 79
  • 203
  • 364
7
votes
3 answers

Excluding sequences from pg_dump

I'm creating an export of a postgres database (10.1) with some tables excluded. I followed the instructions in Is there a way to get pg_dump to exclude a specific sequence?. However, sequences for the excluded tables are still included. Is there a…
Matt Hampel
  • 5,088
  • 12
  • 52
  • 78
7
votes
3 answers

pg_dump serial datatype issues

Could someone explain to me why a PostgreSQL table created with the following scripts: CREATE TABLE users ( "id" serial NOT NULL, "name" character varying(150) NOT NULL, "surname" character varying (250) NOT NULL, "dept_id" integer NOT…
Peter
  • 181
  • 2
  • 12
7
votes
3 answers

psql and pg_dump version mismatch

omnia@ubuntu:~$ psql --version psql (PostgreSQL) 9.3.4 omnia@ubuntu:~$ pg_dump --version pg_dump (PostgreSQL) 9.2.8 omnia@ubuntu:~$ dpkg -l | grep pg ii gnupg 1.4.11-3ubuntu2.5 GNU privacy guard - a free…
Pablo Fernandez
  • 103,170
  • 56
  • 192
  • 232
7
votes
3 answers

PostgreSQL 9.0.13 doing a pg_restore but no evidence that disk space is being used

I'm trying to restore a pg_dump taken with this command from another server. sudo -u postgres pg_dump --verbose --format=custom --file=pg-backup.sql -U postgres salesDB After I copied over the pg-backup.sql file I'm trying to restore with this…
Wade Jernigan
  • 121
  • 1
  • 4
6
votes
2 answers

pg_restore error: function raise_err(unknown) does not exist

I run a daily backup of my database using pg_dump and pg_restore that recently stopped working after I pushed an update. I have a function validate_id that's a Case/When statement just as a quick check for some the data that has integrity issues.…
HelpMeExitVim
  • 105
  • 6
6
votes
1 answer

Pgadmin 4 pg_restore: [archiver] input file appears to be a text format dump. Please use psql

Hi I am using Postgres 11 and pgadmin 4.1. I have a SQL file i am trying to import in my newly created database in pgadmin 4. I know its a generalized error but I tried my best to resolve it, but not working for me. Here is the error: Here are a…
Maha Dev
  • 3,915
  • 2
  • 31
  • 50
6
votes
1 answer

How to use pg_dump/pg_restore to update database?

Say I have db0 on the local machine and db1 on a remote server. I just want to keep db1 up-to-date with db0. Say I added few (thousands) rows to my_table in db0 and now I want to insert it to my_table in db1. All the recipes and docs about…
Alexander Gorg
  • 1,049
  • 16
  • 32
6
votes
2 answers

pg_dump server and pg_dump version mismatch in docker

When I run the command psql --version within the railsApp container, I get 9.4.12 and when I run the same within the postgres container, I get 9.6.2. How can I get the versions to match? I am getting the following error when I try to do a migration…
Suthan Bala
  • 3,209
  • 5
  • 34
  • 59
6
votes
2 answers

Create PostgreSQL dump with ONE INSERT statement instead of INSERT per row

I try to do a table data dump using pg_dump, something like this: pg96\bin\pg_dump ... --format plain --section data --column-inserts --file obj.account.backup --table obj.account database_xyz Instead of getting INSERT INTO obj.account(name,…
Rainer
  • 1,067
  • 2
  • 14
  • 29
6
votes
2 answers

Using pg_restore to create or overwrite tables

I know this is a weird request, but for some hacky reasons I can't avoid, I'd like to be able to consistently sync a few tables from one database to another. I know I could write out the functionality myself in a script, but I figure pg_dump and…
m0meni
  • 16,006
  • 16
  • 82
  • 141
1 2 3
…
43 44