Questions tagged [pg-dump]
The pg-dump tag has no usage guidance.
0
votes
0answers
10 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
11 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
10 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
64 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
1answer
14 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
63 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
36 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
122 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
22 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
2answers
61 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
42 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
48 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
55 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
23 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
38 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
43 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
29 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
251 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
91 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
475 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
434 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
60 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
116 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
272 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
152 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
149 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
158 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
1k 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
37 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
109 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
21 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
37 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
351 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
4k 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
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)
2
votes
1answer
111 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
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:
...
0
votes
1answer
517 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
194 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
824 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
478 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
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
1answer
117 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 ...
3
votes
0answers
372 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
65 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
168 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
659 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/...
