Questions tagged [pg-dump]
The pg-dump tag has no usage guidance.
151
questions
0
votes
1answer
18 views
Using pg_dump to create 144 backups per day
Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is).
Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In ...
0
votes
0answers
15 views
Postgresql strange backup message with docker
I'm trying to backing up my remote PostgreSql database into the local one.
The backup command is the following:
docker run --rm -e PGUSER=user -e PGPASSWORD=password -e PGDATABASE=db -it postgres:11....
0
votes
2answers
27 views
How to use pg_restore in database with different postgis installation (postgis schema location)?
I have database1 where postgis is installed in the public schema.
And database2 where postgis is located in a schema called postgis.
When I dump database1.schema1, schema1.table1 references its geom ...
1
vote
2answers
158 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:
...
0
votes
1answer
32 views
How to dump only create table statements (or table definitions only) with pg_dump in Postgresql
I want only the table definitions, not wiews nor functions.
I have a schema named "cvp". I try:
pg_dump --schema-only -n cvp -t 'cvp.*' --format plain
And I get:
pg_dump: No matching tables ...
0
votes
1answer
145 views
pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible
I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL:
heroku pg:backups:...
1
vote
1answer
91 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 ...
0
votes
0answers
78 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 ...
-1
votes
1answer
836 views
pg_restore error while initializing
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
91 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 ...
2
votes
1answer
33 views
pg_restore “CREATE CAST” not executed if using public or pg_catalog schemas
While moving database to another instance I'm using pg_dump and pg_restore with intermediate file.
Dump file was created like this:
pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$...
1
vote
0answers
49 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
1answer
229 views
Moving a large table from Localhost to remote server
Postgres 11, Windows 10, pgAdmin 4
I have a large post-gis enabled table (29million rows, 20GB + a 5GB spatial index) stored on my local machine (localhost).
I want to move this table to an AWS ...
0
votes
1answer
34 views
PSQL incomplete backup : how to debug
I've been doing backups of my database " screen ".
after connecting to PSQL and typing \l+
I'm getting (among other things):
Name | Owner | Encoding | Collate | Ctype | ...
0
votes
0answers
186 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
128 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
50 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
43 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
1k 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
57 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
362 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
418 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
359 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
57 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
367 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
1answer
156 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
141 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
402 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
44 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
150 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
94 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
531 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 ...
2
votes
0answers
622 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
181 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/...
6
votes
2answers
4k 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
1answer
891 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
298 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
1answer
216 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
397 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
369 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
288 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
234 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
3k 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
1answer
152 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
26 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
40 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
662 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
35 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
...
3
votes
1answer
7k 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
33 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)
