Questions tagged [pg-dump]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
5
votes
2answers
2k 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?
0
votes
1answer
14 views

pg_dump - table dependencies when using --table

The documentation says this when using -t table: Note When -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, ...
0
votes
0answers
14 views

multiple pg_dump

My Postgres 9.3 cluster has 9 databases. I want to take backup of these databases using pg_dump utility of PostgreSQL 10. Since individual dbs are too large in size (~350GB), I want to run pg_dump ...
0
votes
1answer
675 views

pg_restore: [archiver] could not find entry for ID 12

I'm trying to restore a dump (-Fd --data-only) from PostgreSQL v9.4.9, but I've come across the following error: pg_restore: [archiver] could not find entry for ID 12 Exactly the same happens if ...
0
votes
2answers
33 views

Missing data for one schema in pg_dump

I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like pg_dump -h localhost -d db -U postgres -Fc -f file.dump When I restored a ...
5
votes
1answer
3k 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 ...
0
votes
1answer
20 views

Using pg_dump to create 144 backups per day

Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is). Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In ...
2
votes
2answers
122 views

Getting Postgres CREATE TABLE statements

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
1
vote
1answer
860 views

pg_dump issue while dumping single schema

pg_dump does refuse to dump a given schema in my DB. pg_dump -d <dbname> -s -n dm_geo -f <filename> Result -- -- PostgreSQL database dump -- -- Dumped from database version 9.5.4 -- ...
0
votes
1answer
1k views

PostgreSQL pg_dump custom file input not dumping data

When I'm using custom format (-Fc) in pg_dump, the file is smaller (42kb), and when I restore it, it only restores the tables and columns, not the data. But when I try to use plain text (-Fp), the ...
5
votes
3answers
775 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 ...
1
vote
2answers
198 views

postgresql pg_dump out of memory

is it possible to limit somehow usage of resources for pg_dump? The problem is, whole DBase is 775GB and i have two very large tables: pg_largeobject table is – 390GB pg_largeobject statistics: ...
0
votes
1answer
154 views

pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible

I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL: heroku pg:backups:...
4
votes
1answer
584 views

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

I am 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....
1
vote
1answer
165 views

how to automatically create the associated schema when I `pg_restore` a table?

I backed a table into a file using something like pg_dump --table=ex.tab. But when I restore the dump file into a newly created database, it complains that the schema ex does not exist. Looking into ...
4
votes
1answer
2k 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 ...
0
votes
1answer
141 views

Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database

We have one master table and multiple child tables (dependent tables) associated with it. While taking the backup of the master table , I would like to take the backup of all its child (dependent) ...
1
vote
2answers
222 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
6
votes
2answers
4k 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....
0
votes
0answers
17 views

Postgresql strange backup message with docker

I'm trying to backing up my remote PostgreSql database into the local one. The backup command is the following: docker run --rm -e PGUSER=user -e PGPASSWORD=password -e PGDATABASE=db -it postgres:11....
0
votes
2answers
27 views

How to use pg_restore in database with different postgis installation (postgis schema location)?

I have database1 where postgis is installed in the public schema. And database2 where postgis is located in a schema called postgis. When I dump database1.schema1, schema1.table1 references its geom ...
-1
votes
1answer
866 views

pg_restore error while initializing

I am getting the error of: pg_restore: [archiver (db)] Error while INITIALIZING: pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "...
2
votes
1answer
373 views

PG_DUMP on Replica DB server Error

I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it. I need to run ...
1
vote
1answer
163 views

How can I share pg_dump output with my team so they can restore without errors?

I am on a team that is building a Drupal website. All of the settings, including the migration scripts from the old site, will be in code, so in theory all of our work can be replicated just from a ...
0
votes
1answer
757 views

What is the key to pg_restore?

We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore. We are ...
5
votes
2answers
1k 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 ...
5
votes
1answer
2k 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 ...
0
votes
1answer
33 views

How to dump only create table statements (or table definitions only) with pg_dump in Postgresql

I want only the table definitions, not wiews nor functions. I have a schema named "cvp". I try: pg_dump --schema-only -n cvp -t 'cvp.*' --format plain And I get: pg_dump: No matching tables ...
1
vote
1answer
110 views

Postgresql cannot restore partial pg_dump “relation ”public.my_table_seq“ does not exist”

In my database I dumped some tables via the following psql command: pg_dump -t my_table1 -t mytable2 > my_dump.sql And I try to import it into a postgresql docker image as a standalone database ...
12
votes
4answers
45k 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 ...
2
votes
3answers
3k views

pg_restore terminates with “out of memory” error

Trying to restore a 45mb pg_dump file but it keeps failing with an "out of memory" error. Content of my postgresql-10-main.log: 2018-01-16 06:14:50.692 WAT [32405] postgres@app_development LOG: ...
8
votes
2answers
14k 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" ...
0
votes
0answers
84 views

pg_dump not recognized, even though I've confirmed the binary is present

I'm trying to do a dump of a table in PostgreSQL via pgAdmin3. When I try to run the the following command: pg_dump -t schema.table_name database_name > chosen_save_name.sql it errors out and ...
0
votes
0answers
99 views

Can I get pg_dump to simplify the ACL commands?

When using pg_dump with a database with non-default schema foo like so: pg_dump --schema-only --schema='foo' -U myuser -d mydb -h myhost -W > mydb.sql The SQL dump file is full of these seemingly ...
2
votes
1answer
33 views

pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas

While moving database to another instance I'm using pg_dump and pg_restore with intermediate file. Dump file was created like this: pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
1
vote
0answers
49 views

pg_dump does not return schema for repmgr objects

While trying to browse repmgr.monitoring_history, I noticed it has many tens of millions of records. Wanting to get a peak at the indexes that (may or may not) already exist on the table, I try a ...
0
votes
1answer
249 views

Moving a large table from Localhost to remote server

Postgres 11, Windows 10, pgAdmin 4 I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost). I want to move this table to an AWS ...
6
votes
1answer
1k 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> ...
0
votes
1answer
37 views

PSQL incomplete backup : how to debug

I've been doing backups of my database " screen ". after connecting to PSQL and typing \l+ I'm getting (among other things): Name | Owner | Encoding | Collate | Ctype | ...
0
votes
0answers
194 views

pg_dump can't write to drive C | Windows 10

Problem is just as the title says. pg_dump literally can't write to anywhere on drive C: - the drive windows is installed on. More details: created Temp folder on C: drive modified access - added ...
3
votes
0answers
488 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 ...
19
votes
2answers
36k 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 ...
0
votes
0answers
138 views

Is there a way to restore contents of pg_largeobject for BLOB data using pg_restore?

Product: PostgreSQL 10.3 We have a need to take a copy of our production database and restore that into a separate environment for testing and training purposes. The problem is that the database is ...
1
vote
0answers
51 views

Dumping Postgres schema from Google cloud SQL

Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump ...
0
votes
0answers
45 views

DROP DEFAULT on psql restore fails because column not existing anymore

A dump creates a command to remove default values like: ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT; When on restore the column with the default value do not exist anymore, ...
13
votes
2answers
10k views

how to track progress of a large postrgresql 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.
0
votes
1answer
57 views

Veryfing pg_restore database with pg_dump database

I just did a migration from Postgres 9.4 to 10 database. How to verify if everything is okay? I tried to look into huge tables, the rows are different (somehow in Postgres 10 then are more rows than ...
0
votes
2answers
373 views

pg_dump: Write failed: Connection reset by peer

we are trying to move all database data to other location. we chose to perform pg_dump and we encountered few problems: first was out of memory problem: during pg_dump initialization we get an error: ...
0
votes
2answers
373 views

Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor

I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux: "C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --...
0
votes
1answer
426 views

Migrate complete postgres database cluster to another machine using pg_dump

I am referring to How to get a working and complete PostgreSQL DB backup and test In this example/solution, which part of the restore process does actually the "CREATE DATABASE sandbox"? I have been ...