Questions tagged [pg-dump]
The pg-dump tag has no usage guidance.
0
votes
2answers
22 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
16 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 ...
3
votes
1answer
97 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. ...
0
votes
2answers
16 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
19 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:
...
1
vote
0answers
20 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 ...
0
votes
1answer
21 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
21 views
How to know or recover my pg_dump password? [closed]
I would like to backup my Postgre db using pg_dump. However, the first question asked by the prompt is the password.
But I have no idea what it is. Indeed, it is different from my admin account ...
0
votes
1answer
19 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 ...
1
vote
2answers
35 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
24 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
27 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
156 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
1answer
68 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/...
3
votes
2answers
2k 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
357 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
316 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
38 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
78 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
236 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
104 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
108 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
136 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
874 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
32 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
95 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
20 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
36 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
295 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
31 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
29 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
250 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
104 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
440 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
169 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
691 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
396 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
109 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
344 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
58 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
128 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
544 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
943 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 ...
4
votes
1answer
685 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
511 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
256 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 ...
