Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [pg-dump]

The tag has no usage guidance.

0
votes
1answer
14 views

Retreiving 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 ...
-1
votes
0answers
18 views

Postgresql 11 pg_dump [on hold]

I am am trying to backup a Postgresql 11.1 database through pg_dump (part of PgAdmin 4 on Windows). I get a version mismatch (pg_dump is version 10 and database version is 11 although I use the ...
1
vote
2answers
32 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
0answers
19 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/...
0
votes
1answer
19 views

importing-a-sql-db-dump-postgres11 pgadmin4

I have a SQL dump file: C:\Users\Rayyan\Desktop\dump_2.sql. I have been trying to import this for so long. I am using pgadmin 4, and since I could find nothing in that GUI to help, I tried using ...
1
vote
0answers
53 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
23 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/...
0
votes
0answers
15 views

postgres 9.6 restoring a server in an other one

I made a pg_dumpall of a 9.6 postgres server in order to restore it (for the first time) completely on another computer. On this second computer, postgres 9.6 is also installed with old versions of ...
3
votes
2answers
1k 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
26 views

PG10: Not able to take backup

I have only one database PG10 is installed via EDB GUI installer on Ubuntu 16.04.3 LTS, Now I'm trying to take backup of my database and it is giving me an error, which I can't understand. Command: ...
0
votes
0answers
229 views

Unable to access LOB stream after importing a data-only dump from some tables

We've been working on an application for a long time and after all the tests and adding necessary features, we decided to change the phase from testbed to production. There were tables we needed the ...
0
votes
1answer
230 views

How to disable foreign key constraints in Postgres dump file?

I used pg_dump -U postgres -p 65432 --table=tableName --data-only --column-inserts DBName>fileName.sql to dump one particular table's rows. Now I need to insert them to another database but I get ...
1
vote
0answers
25 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> -...
0
votes
1answer
54 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
0answers
198 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 ...
2
votes
1answer
70 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 ...
0
votes
1answer
64 views

How do I copy a DB dump file to a remote computer? [closed]

I want to create a copy of my database from one server to another (my laptop to a server for example). Ive been looking online about how to do it and found this article:http://www.postgresqltutorial....
1
vote
0answers
113 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 ...
0
votes
1answer
602 views

out of shared memory error when doing pg_dump

I have a postgres database with 3 schemas and around 1200 tables. When I try to take a schema only pg_dump of it using the command, pg_dump -h localhost -p 5432 -U postgres -d my_test_db -s, I ...
0
votes
0answers
25 views

Postgresql: Restored dump into another database with another user, results in permission denied

I'm using postgresql on two machines, and I regularly create a dump on the production server and restore it on my development computer. Usernames are identical. The dump / restore porcess works in ...
0
votes
1answer
85 views

Schema Dump Without User Mapping Passwords

Is it possible to do a pg_dump --schema-only of the database schema but not include any username/password combinations that are in user mappings? For example, I would like items such as the following ...
0
votes
0answers
19 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
33 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
235 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 ...
1
vote
0answers
30 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 ...
2
votes
1answer
3k 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....
1
vote
0answers
27 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)
0
votes
0answers
212 views

Backup size is larger than the database size

I am taking a PostgreSQL database backup by using pg_dump method. My database size is 25 GB, but after taking the backup, the backup size is showing as 52 GB. I am totally confused why this is ...
2
votes
1answer
88 views

how to create ambari database backup without enter password

We configure the file .pgpass as follows in order to create database backups without entering a password: -bash-4.2$ more .pgpass localhost:5432:postgres:ambari:bigdata -bash-4.2$ pwd /var/lib/pgsql -...
1
vote
2answers
1k 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: ...
0
votes
1answer
366 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
143 views

how to prevent multiple pg_dump to execute at the same time

I am exposing a REST API to backup a Postgres DB. I have few questions related to that. I have seen most of these questions floating around google but could not find any suitable answer Is there any ...
0
votes
1answer
556 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 ...
0
votes
1answer
315 views

Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS

I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1. The restore seems successful, but the output gave me 27 ...
0
votes
1answer
1k 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
1answer
102 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 ...
2
votes
0answers
322 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 ...
7
votes
4answers
1k views

How do I prevent changes to my 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 ...
1
vote
1answer
50 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?
2
votes
2answers
109 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 ...
1
vote
0answers
427 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
2answers
794 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 ...
3
votes
1answer
567 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> ...
1
vote
2answers
389 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. ...
3
votes
2answers
201 views

Postgres dumping specific non-table objects

I find myself needing to migrate a specific application from one host to another. That application touches a subset of tables within a certain PostgreSQL database; in order to migrate properly, I need ...
2
votes
1answer
478 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 ...
1
vote
1answer
2k 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 ...
2
votes
0answers
231 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 ...
3
votes
1answer
60 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 ...
8
votes
1answer
5k 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 ...