Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [pg-dump]

Use this tag for questions concerning pg_dump, the PostgreSQL tool for logical backup (also known as database export or database dump). Please also use the [postgresql] tag.

243 questions
63
votes
1 answer

How to get a working and complete PostgreSQL DB backup and test

I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users. From what I've read, and it could be wrong, finding a good PostgreSQL blog has…
Ali Razeghi - AWS
  • 7,566
  • 1
  • 26
  • 38
40
votes
4 answers

how to track progress of a large postgres dump

Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
abbood
  • 503
  • 1
  • 4
  • 7
38
votes
4 answers

postgres - pg_dump and pg_restore without roles

I'm trying to restore a dump without having the appropriate roles on the receiving database. As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or pg_restore or both. I've used the following command line…
Andy K
  • 707
  • 1
  • 10
  • 19
34
votes
4 answers

pg_restore: [archiver] did not find magic string in file header

I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a valid sql file that starts with droping any existing…
maja
  • 443
  • 1
  • 5
  • 7
29
votes
2 answers

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f dumpfile My problem is that the database contains…
Marcus Junius Brutus
  • 3,409
  • 7
  • 30
  • 44
27
votes
6 answers

Delete all data in Postgres database

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a staging server. pg_dump -h localhost -U adminuser…
uberrebu
  • 481
  • 1
  • 7
  • 12
23
votes
4 answers

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I make sure it also has plpgsql extension. Then when…
Jacek Prucia
  • 341
  • 1
  • 2
  • 4
17
votes
4 answers

Restoring from pg_dump with foreign key constraints

In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example: ERROR: insert or update on table "channelproducts" violates foreign key constraint…
Jerome
  • 299
  • 1
  • 2
  • 12
16
votes
1 answer

How to Install postgresql-client-11 on Ubuntu 18.04

I need to use the pg_basebackup/pg_dump program on an Ubuntu 18.04 system to connect to a remote PostgreSQL 11.6 server. However, the current system only contains pg_basebackup 10.10 and the remote database is using PostgreSQL 11.6. Running…
Nyxynyx
  • 1,131
  • 6
  • 18
  • 29
15
votes
4 answers

How to restore postgreSQL schema dump to schema with different name

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema. The dump type is "custom". So effectively I want something like: pg_dump -U postgres --schema…
Nux
  • 305
  • 1
  • 2
  • 10
14
votes
1 answer

Why postgresql pg_dump exports a view as a table?

I am using PostgreSQL 9.3 pg_dump tool for extracting only the public schema definition using: pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql but when I check schema.sql one of our views appears in a CREATE TABLE statement…
Lben
  • 173
  • 1
  • 1
  • 7
12
votes
1 answer

Is it possible to backup and restore a materialized view with it's data?

I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing. Is it possible in PostgreSQL 9.4? (the alternative is to create actual tables, but they are harder…
Ophir Yoktan
  • 223
  • 2
  • 6
12
votes
1 answer

how to dump a case-sensitive table within a specific schema

I'm trying to dump a case-sensitive table named app_auth.User by executing the following command pg_dump --schema=app_auth -t '"User"' -U em3local -h 127.0.0.1 -Fc -a -f but what I'm returned is: pg_dump: No matching tables were…
Bertuz
  • 255
  • 2
  • 7
11
votes
2 answers

Importing large SQL dump with millions of INSERT statements

I need to import a big .sql file (8.1GB when unpacked) into PostgreSQL. I tried to use \i /path/to/file.sql but it is way too slow. How does one speed up the import? I need to import this data weekly. The first 2000 lines can be found here, while…
devondre
  • 225
  • 1
  • 2
  • 5
11
votes
1 answer

pg_restore ERROR: "Relation does not exist" and creating new database

I have made a backup of my specific tables that I want to restore into a new database using: call pg_dump -Fc -h server -d database -U user -p password -v -f dump.sql -t public.table1 -t public.table2 And I have no problems. I then want to restore…
Ian23
  • 113
  • 1
  • 1
  • 4
1
2 3
…
16 17