Questions tagged [pg-dump]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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: ...
2
votes
2answers
332 views

Merging multple pg_dumps with columns using auto key

I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables ...
2
votes
1answer
788 views

pg_dump of table and its transitive dependencies

Sometimes I'd like to export just a single table and its dependencies, so I can do some experiments with it on a test database. Is there a way to do that, other than dumping the full db schema with ...
2
votes
1answer
360 views

Idempotent PostgreSQL schema dump that can be applied to an existing DB

I'm looking for a way to replicate a PostgreSQL schema to a database which may already have some of it defined. I wonder if there's a simple way to produce DDL that is idempotent (i.e. that can be ...
2
votes
1answer
6k views

pg_restore: [custom archiver] could not read from input file: end of file

Not going into details how that happened, all started with a corrupt DB. It was OK on the file-system level, but trying to: select count(*) from mytable resulted in the following: ERROR: could ...
2
votes
2answers
2k views

pg_restore does not restore blobs

I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command: pg_dump --format=c --...
2
votes
1answer
1k views

Postgres Restore Not Restoring Bytea Data

This is a very urgent situation. We have a postgres 9.4 database installed on a CentOs machine. We are trying to take a backup from a Heroku Postgres database and restore it to the database on ...
2
votes
0answers
578 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 ...
2
votes
1answer
340 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
468 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
2answers
105 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
738 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
87 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
1answer
142 views

Replace content of only one table with new version (should disable foreign keys?)

I have 2 tables. 2nd has foreign keys to reference 1st. I need to replace the whole content of the 1st table with the new version from another database. The new version satisfies all constraints and ...
2
votes
0answers
446 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
517 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
456 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
2answers
3k 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 ...
1
vote
3answers
2k views

postgreSQL hot_standby pg_dump on slave

I have master-slave (hot_standby) pgsql configuration and I want to make backups from one of my slaves. I don't want PITR, just good old database dump. I switched to postgres from MySQL. With MySQL ...
1
vote
1answer
2k 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 ...
1
vote
2answers
110 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 ...
1
vote
1answer
576 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
1
vote
1answer
154 views

How do I get the GRANT statements for a specified schema?

Is there a way to generate the GRANT statements that have been run on a schema in PostgreSQL, for example if myUser has been granted usage on mySchema the script would generate GRANT USAGE ON SCHEMA ...
1
vote
1answer
77 views

How to avoid modifications during pg_dumpall [duplicate]

I want to do a final pg_dumpall on a server, then copy the output to a different server and restore the db there. How can I avoid modification to the database during pg_dumpall?
1
vote
1answer
826 views

script for restore of postgres database localy from dump given on stdin - ambiguous redirect

Runing this command: sshpass -p "pass" ssh x@1.2.3.4 "pg_dump -U foo some_db" runs perfectly fine. It returns on stdandard output the dump of db. Then I would like to restore new_db based on dump ...
1
vote
1answer
42 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 ...
1
vote
1answer
168 views

pg_dump and pg_dumpall tools backup create zero size backup file from linux putty terminal

I have installed postgres database version 9.6, 10 on OS Centos 7 64 bit. I'm trying to take a backup with this command: -bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/...
1
vote
1answer
209 views

Change pg_dump directory output to table name instead of object number

pg_dump -F d will result in multiple <number>.dat.gz files which represent the object number in the database. I wish pg_dump create those files in the format <table name>.dat.gz instead so ...
1
vote
1answer
1k 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 ...
1
vote
2answers
2k views

Data missing in PostgreSQL dump

I'm trying to verify that I am actually getting data in my pg_dump from a PostgreSQL 9.3 server. I'm specifically running the following to get a dump of my Foreman database: pg_dump -Fc foreman -U ...
1
vote
2answers
1k views

pg_dump: Show GRANT Statements only

I executed this manually: GRANT SELECT ON TABLE my_table TO some_user; How can I dump this statement again? I looked at pg_dumpd but only found a way to do a data-only dump or a schema-only dump. ...
1
vote
1answer
2k views

how can i lock or disable connections to postgres DB while performing maintenance? [duplicate]

I never have this issue with MySQL but PostgreSQL is ALWAYS a pain when trying to do any kind of maintenance. So I am trying to truncate and load new data into databases on PostgreSQL server but ...
1
vote
1answer
7k views

How to convert postgresql dump file into mysql dump file

I having Postgresql dump file like data.sql. I need to convert postgresql file into mysql dump file It is possible to convert without install postgresql database . Thanks in advance
1
vote
1answer
530 views

Why can't I restore the DB from an SQL dump in PostgreSQL 9.4 (problem with dblink)?

I have created a dump of a DB using: pg_dump db_name > db_dump.sql and proceed to restore it with: psql --single-transaction db_name < db_dump.sql Prior to attempting to restore the DB, I ...
1
vote
1answer
1k views

PostgreSQL - partial dump of large objects (pg_largeobject table)

Context : Our web-app is like a collaboration platform where you can share files, appointments, survey and so on. An instance of this web-app is divided in communities, each one is independent to each ...
1
vote
1answer
73 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: ...
1
vote
0answers
41 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 ...
1
vote
0answers
44 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
116 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
85 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
251 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
380 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
213 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
34 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
31 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
1answer
773 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
1k 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
479 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
838 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
1answer
939 views

pg_dump fails to create a backup when running from System User in Windows Task Scheduler

I am trying to run a pg_dump through a scheduled task in windows. I have two batch files. One creates the task. The other one runs the pg_dump. Even though the pg_dump works with the current user with ...