Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
61 votes
1 answer
107k views

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 ...
Ali Razeghi - AWS's user avatar
35 votes
4 answers
50k views

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's user avatar
  • 453
34 votes
4 answers
74k views

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 ...
Andy K's user avatar
  • 667
31 votes
4 answers
59k views

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 ...
maja's user avatar
  • 413
29 votes
2 answers
59k views

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 ...
Marcus Junius Brutus's user avatar
26 votes
6 answers
144k views

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 ...
uberrebu's user avatar
  • 451
23 votes
4 answers
26k views

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 ...
Jacek Prucia's user avatar
15 votes
1 answer
22k views

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 ...
Nyxynyx's user avatar
  • 1,121
15 votes
4 answers
41k views

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&...
Jerome's user avatar
  • 279
14 votes
1 answer
14k views

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 ...
Lben's user avatar
  • 173
12 votes
1 answer
8k views

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 ...
Ophir Yoktan's user avatar
12 votes
4 answers
37k views

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 ...
Nux's user avatar
  • 275
11 votes
1 answer
31k views

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....
Ian23's user avatar
  • 113
11 votes
1 answer
5k views

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 <path> <dbname> ...
Bertuz's user avatar
  • 245
10 votes
4 answers
5k views

How do I prevent changes to my PostgreSQL database?

My steps: run the final pg_dumpall on a server prod-server-old Shut down prod-server-old for ever. copy the output to a different server (prod-server-new) Restore the db there. All traffic goes to ...
guettli's user avatar
  • 1,521
9 votes
2 answers
11k views

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....
devondre's user avatar
  • 205
9 votes
1 answer
16k views

Postgres Dump To TXT File

Is there a way to convert a Postgres dump file to a text file? I'm experiencing issues getting the dump to the proper file location of I would just restore from the dump file and then export to text. ...
Drew's user avatar
  • 308
9 votes
2 answers
15k views

Differences between .dump and .sql in pg_dump PostgreSQL

I'm trying to take a backup of schema using pg_dump like, $ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump It create the backup, But I can't restore it using pg_restore. It give ...
Ganapathy's user avatar
  • 513
9 votes
1 answer
12k views

PostgreSQL 9.3: Dump a database excluding the data of some tables

Consider a production database with a few dozens of normal-size tables, and a handful of huge tables. I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, ...
Adam Matan's user avatar
  • 11.7k
9 votes
2 answers
7k views

How to exclude tables when using pg_dumpall with postgresql?

If use pg_dump, it has a --exclude-table-data option to ignore special table. But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
cloud_cloud's user avatar
9 votes
1 answer
10k views

pg_dump with ssl certificates

I would like to run the pg_dump command from a remote host off my database server that only allows fully verified certificate based connections. How should I go about doing this? The man pages for 9.1 ...
Ben's user avatar
  • 91
8 votes
1 answer
11k views

In Postgresql, after pg_dump the access privileges on the database itself are not restored

Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee gee=> \l List of databases Name | Owner | ...
ericj's user avatar
  • 183
7 votes
2 answers
23k views

pg_dump/pg_restore: different PostgreSQL version, another database name, another tablespace

I am going to move a database from the old server to a new one. the old one runs PostgreSQL 9.1, the new one has 9.3. I want to change the database name. I want the new database to be in a dedicated ...
saabeilin's user avatar
  • 251
7 votes
1 answer
8k views

PostgreSQL dump and restore

I can't seem to dump/restore on Windows with my database. Lines used and error messages: pg_dump -h localhost -U postgres --format=c -O -d ue > latest.dump pg_restore -h localhost -U postgres -d ...
Josh's user avatar
  • 273
7 votes
4 answers
5k views

How to run pg_dump so that it doesn't slow down other connections?

I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size: pg_dump -Fc dbname > file The problem I am having is that while pg_dump runs, which takes several hours, all ...
zabojnik's user avatar
7 votes
1 answer
8k views

postgres backup for specific table with blob oid

I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 ...
RBB's user avatar
  • 785
6 votes
1 answer
6k views

Local Backup from Amazon RDS PostgreSQL database

I have a PostgreSQL database on an Amazon RDS instance. I want to have a local dump of this instance. So far, I've tried with this: PGPASSWORD="Password" pg_dump -h mydb.xxxxxx.us-east-1.rds....
NeoVe's user avatar
  • 181
6 votes
1 answer
5k views

pg_dump: No matching schemas were found

I am trying to dump a schema from Postgres 9.4, running on Cents 6.5, but I get an error: pg_dump: No matching schemas were found Here is the command I am running: pg_dump -U postgres -n "OLD-...
Nathan Foley's user avatar
6 votes
2 answers
14k views

pg_dump permission denied for sequence

I have a strange issue with dumping post-data section. Dump command is: pg_dump -Fc --verbose --section=post-data --no-owner --no-acl -h localhost -p 9999 -U root db_name -W > constraints.dump ...
retgoat's user avatar
  • 163
6 votes
1 answer
2k views

When pg_restore honours --dbname option

I am trying to restore a binary pg_dump backup under a different database name that was in the original dump. Despite giving --dbname command line argument, pg_restore tries to restore everything ...
Mikko Ohtamaa's user avatar
6 votes
1 answer
5k views

dumping database with "compressed data is corrupt" errors

I'm using pg_dump to dump a large (396 GB) PostgreSQL database: pg_dump --clean --create mydatabase After running for a day, a statement fails with ERROR: compressed data is corrupt and pg_dump ...
kontextify's user avatar
6 votes
2 answers
11k views

pg_restore not restoring sequences [closed]

I'm transferring all of the databases on my old postgres server to a new server. To do this I'm using pg_dumpall and then pg_restore. However, it appears that no sequences have been dumped. This is ...
spencerrecneps's user avatar
6 votes
1 answer
8k views

How do I limit the resources that pg_dump uses?

Is it possible to limit the system resources used up by pg_dump? I have a fairly resource intensive server that uses a PostgreSQL database. and it is running on FreeBSD 10.2. The server has 8 GB of ...
quickblueblur's user avatar
6 votes
2 answers
4k views

How to speedup pg_dump big database

I've got big database (about 900GB), and when I migrate it to new server dump speed is not more than 2MB/s (very slow). Link between servers is 10Gb/s. The database contain 20 tables with different ...
gorek's user avatar
  • 61
6 votes
1 answer
1k views

Postgresql: How to avoid encoding issues when copying a schema from one server to another?

I'm using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server: $ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema....
David Tinker's user avatar
5 votes
1 answer
13k views

Can I use pg_restore to restore data to subset of tables?

I am trying to restore data in to select tables of a database (9.3.5). This is a data refresh from production to test, while maintaining some table data in the test database (like application users ...
GrnMtnBuckeye's user avatar
5 votes
1 answer
3k views

PostgreSQL 9.3: pg_dump fails due to tiger.direction_lookup

I am trying to dump the schema from one of my PostgreSQL 9.3+PostGIS databases: /usr/lib/postgresql/9.3/bin/pg_dump --schema-only -h stg-db.....rds.amazonaws.com ...
Adam Matan's user avatar
  • 11.7k
5 votes
1 answer
6k views

pg_restore: error: could not execute query: ERROR: option "locale" not recognized

I'm getting the following error when I'm trying to restore my dump file to another (local) PostgreSQL server (running on Debian GNU/Linux 10.10): pg_restore: error: could not execute query: ERROR: ...
Emre Sevinç's user avatar
5 votes
2 answers
6k views

How to anonymize pg_dump output before it leaves server?

For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the ...
François Beausoleil's user avatar
5 votes
1 answer
2k views

How to set search_path during pg_restore?

pg_dump and pg_restore will set search_path to an empty string, which will fail if there are functions that call functions in the public schema without explicitly specifying the schema name (ie: ...
Bhoomtawath Plinsut's user avatar
5 votes
2 answers
5k views

Update staging data with production data in Postgresql

I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a ...
django09's user avatar
5 votes
0 answers
1k views

pg_restore error: Extensions installed out of sequence

I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command: $ pg_dump -c -U postgres -Fc -f test.dump testdb When I ...
devteam's user avatar
  • 51
4 votes
1 answer
6k views

How to prevent pg_dump from generating "SET default_table_access_method=heap;"?

I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too. ...
Leon's user avatar
  • 347
4 votes
1 answer
814 views

pg_dump custom archive and create backup date

I have archive file with custom format created by pg_dump. Does it contain the backup creation date? I can't create backups by myself. I receive the archived files over specific network channel. ...
Yura Shinkarev's user avatar
4 votes
2 answers
12k views

Postgres 9.3 changes the standard_conforming_strings value

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3 In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default ...
Diego Puertas's user avatar
4 votes
2 answers
14k views

What to look for in bad pg_dump log

We want to programmatically detect errors in cron scheduled pg_dumps. Apart from cheking whether or not the log file ends in "pg_dump: saving database definition": What other tell-tale string can I ...
Tulains Córdova's user avatar
4 votes
1 answer
3k views

What is the correct way to make an exact copy of a database with pg_dump?

Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc. Is this doable with pg_dump or does psql ...
strumpy_strudel's user avatar
4 votes
2 answers
8k views

How to dump multiple databases of the same cluster in single execution?

I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t. Is there any way to do ...
RBB's user avatar
  • 785
4 votes
1 answer
2k views

pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4). well, here's the command: pg_dump -f file.sql -F plain --inserts -n schema database Ok. My ...
George Silva's user avatar
4 votes
1 answer
4k views

Why does pg_dump output not include extensions?

I'm trying to do a backup / restore of a PostgreSQL 11 database from an RDS instance using pd_dump. The documentation states: When pg_dump is used, the CREATE EXTENSION command will be included in ...
FisherKing's user avatar

1
2 3 4 5