Questions tagged [pg-dump]

The tag has no usage guidance.

52 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
1answer
1k 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?
4
votes
1answer
630 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 ...
4
votes
1answer
492 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....
3
votes
0answers
419 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 ...
3
votes
1answer
2k views

postgres backup for specific table with blob oid

I have a table which having an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table is having only 10 entries (which is approximately ...
3
votes
0answers
555 views

Output from `pg_dump --schema-only` has inconsistent order

I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers: ...
3
votes
0answers
251 views

Restoring Postgres fixtures into a different database with different premissions

I've got an application called BigSense that uses two database users/rolls. One has full permissions and is used by my application to create/adjust table definitions. Then the application starts with ...
3
votes
1answer
2k views

Use pg_dump with postgis extensions?

I'm using Postgres 9.4. I'm trying to dump a database (both the schema and the actual data) with the PostGIS extensions (set up using CREATE EXTENSION): $ pg_dump prescribing -U prescribing -h ...
2
votes
1answer
229 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 ...
2
votes
0answers
385 views

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Version 9.3) Asking this after searching around without any solution: I create a simple materialized view (in practice, I have a few of them). I call pg_dump to backup the database. While pg_dump ...
2
votes
1answer
88 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 ...
2
votes
0answers
692 views

How to transfer table from one database to another in postgresql

pg_dump -h host_ip -U postgres -d source_db -t aa_nqrgs.act_26022017 | psql -h destination_host_ip -U postgres -d destination_db I have tried this command to transfer a table from one server to ...
2
votes
0answers
74 views

Can I calculate the number of locks required by pg_dump ahead of time?

I've got a multi-tenant app on postgres 9.5 DB with some 200+ schemas each with about 170 tables on. pg_dump operations have become a frustrating hit and hope exercise where I fiddle with ...
2
votes
0answers
984 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 ...
2
votes
0answers
428 views

pgAdmin export line overflow on large field

Background: I have a database of significant geographical locations downloaded from GeoNames open database. The create script for the table is as follows CREATE TABLE populargeonameswiki ( geonameid ...
2
votes
0answers
492 views

How to do a pgdump with strings encoded?

When I perform a dump with pg_dump tool, with a "flat" sql format, pg_dump create a regular sql file but the restoration with pg_restore fails because the strings are not escaped. For example, single ...
2
votes
0answers
454 views

How do you stop Postgres from performing writes inside base/ directory during pg_dump?

We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec ...
1
vote
0answers
27 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 ...
1
vote
1answer
86 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 ...
1
vote
0answers
68 views

Can pg_dump be broken into batches for large DBs?

I have a fairly huge DB and pg_dump croaks on me when I try to export it to a single file. Is there a way to do it in batches, whereby each batch would cover a segment of tables and export the schema/...
1
vote
0answers
397 views

PostgreSQL 10.3 pg_dumpall fails: server closed the connection unexpectedly

System: Windows 10 Pro 64bit, Version 10.0.17134 Build 17134. with Postgres 10.3 db server. I backup my postgres db using the following statements: "C:\PostgreSQL\pg10\bin\pg_dumpall.exe" -U ...
1
vote
0answers
118 views

Dump limit in PostgreSQL

I am trying to dump a database which has a size of just over 100 GiB. I am using the following command (with pv to give a progress bar to observe where this problem occurs): pg_dump -h <host> -...
1
vote
0answers
331 views

Postgres pg_dump copy table to another database isn't copying columns

Goal: copy a single table including all columns from one database to another using psql on the command line, avoiding an issue with XML from QGIS layer styles. I am running the following on the ...
1
vote
0answers
184 views

Postgres pg_dump restore performance. Is using pg_dump to back up large databases an acceptable solution

Folks, I am considering moving from SQL Server to Postgres. The per core licensing of SQL Server is too expensive for my needs. My one concern is restoring Postgres from a dump. I believe that the ...
1
vote
0answers
32 views

Replace oldSchema with newSchema in functions

I exported a database called oldDatabase using pg_dump in directory format. This database contains a schema, tables and functions owned by oldRole. These functions contain SQL statements like ...
1
vote
0answers
30 views

Minimum permissions for postgres server dump

Sql request to create a user with the minimum rights necessary for the full backup of the server postgres (without data)
1
vote
2answers
2k 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: ...
1
vote
1answer
651 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 -- ...
1
vote
0answers
863 views

pg_restore on OS X

I'm running this command to export a database: sudo PGUSER="postgres" PGPASSWORD="D" /Library/PostgreSQL/9.5/pgAdmin3.app/Contents/SharedSupport/pg_dump --format=c okay_db_3 > '/Volumes/1TB/...
1
vote
0answers
400 views

How do I generate a Postgres dump with the column names in my INSERT statement?

I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box. I’m using this command to export pg_dump -U username --format plain --section data --...
1
vote
0answers
795 views

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology crashes server

I am moving to a new Windows server and upgrading PostgreSQL to 9.6 and PostGIS to 2.3 while I am at it (old versions were 9.2.4 and 2.0). I used pg_dumpall to dump the entire server. Came to 318GB. ...
1
vote
2answers
167 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) ...
1
vote
0answers
383 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query was: ...
1
vote
1answer
153 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 ...
1
vote
0answers
125 views

Importing PostgreSQL backup into slightly different database

I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to ...
1
vote
1answer
143 views

What determines whether `pg_dump` includes a varchar length?

I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns ...
0
votes
0answers
14 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
1answer
49 views

Error while restoring the data in postgres using pg_restore

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 "...
0
votes
0answers
20 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 ...
0
votes
0answers
18 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
0answers
28 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 ...
0
votes
0answers
26 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 ...
0
votes
0answers
27 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, ...
0
votes
1answer
399 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 ...
0
votes
2answers
32 views

Does pg_dump flag -j give performance improvement up to -j 16 or -j 32 or even -j 64?

I am backing up and restoring a large DB (7+TB) ... I am using pg_dump -j 8 -Fd (8 parallel dump threads with directory formatting). I just wondered if anybody has tested with a higher -j value ...
0
votes
1answer
87 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) ...
0
votes
1answer
35 views

Retrieving database dump after copying from remote server

I used command pg_dump -C -h remotehost -U remoteuser db_name | psql localhost -U localuser to copy database on remote server to my local machine. Can I somehow retreive the dump obtained after ...
0
votes
0answers
22 views

Rename a schema that contains an extension used by another schema

How can you rename a schema containing an extension without breaking tables in other schemas that rely on this extension (PostGIS)? I'm trying to temporarily rename a schema "dest" to "public" in ...
0
votes
1answer
39 views

Small databases, large backup files

We have PostgreSQL 9.6 databases and a dailytask running a pg_dump for all databases, though these backups are getting "large" at this point. My database was 900MB, then I tried clearing it by ...
0
votes
1answer
507 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 ...