Questions tagged [pg-dump]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
2answers
209 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
1
vote
0answers
390 views

Having problems trying to back up a Postgres database from AWS RDS

I am trying to backup a Postgres database from RDS and I am getting this error message. pg_dump: [archiver (db)] query failed: SSL error: unexpected message pg_dump: [archiver (db)] query was: ...
1
vote
1answer
162 views

How can I share pg_dump output with my team so they can restore without errors?

I am on a team that is building a Drupal website. All of the settings, including the migration scripts from the old site, will be in code, so in theory all of our work can be replicated just from a ...
1
vote
0answers
125 views

Importing PostgreSQL backup into slightly different database

I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to ...
1
vote
1answer
150 views

What determines whether `pg_dump` includes a varchar length?

I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns ...
0
votes
1answer
371 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 ...
0
votes
1answer
262 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....
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
2k views

AWS Postgres RDS dumping data into sql format

My team has hosted a django project in AWS with a Postgres RDS instance. What is the proper way to get export the database into SQL or pg_dump?
0
votes
1answer
684 views

error recovering pg_dump compressed file

I produced this backup: pg_dump -v -Fc -Z5 -f thebackup.dmp.gz thedb I can't open the dump with gzip, 7zip... I could recover the same files in the past without using -Fc parameter. Is it possible ...
0
votes
2answers
3k views

Using pg_dump and psql to transfer table with timestamp into another Db

I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another. pg_dump -U postgres -t public.table1 database1 | psql -U ...
0
votes
1answer
28 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
166 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
2answers
326 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
425 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 ...
0
votes
1answer
148 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
1answer
267 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
887 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
722 views

Unable to backup postgresql database. pg_dump memory alloc error

After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished. The very first thing I attempt to do is to ...
0
votes
1answer
1k views

“Unrecognized configuration parameter: qreplace_function” trying to backup a database

I am new to PostgreSQL and I am trying to backup a database with the pg_dump utility. I am using this command: /opt/PostgresPlus/9.4AS/bin/pg_dump -U (username) -f /opt/PostgresPlus/9.4AS//pgbackup/...
0
votes
1answer
211 views

Postgres Data Dump problem

I am trying to dump this data into Postgres: COPY address_types (game_id, device_type) FROM stdin; 1 Ps3 2 Xbox 3 Other \. But I always get this error: [Err] ERROR: syntax error at or ...
0
votes
1answer
3k views

How do I load a data-only dump created with pg_dump to another database?

I have two PostgreSQL databases on my development server, one I use for development and another for production where I test my web applications. When I was using an earlier version of PostgreSQL I was ...
0
votes
1answer
636 views

pg_get_viewdef error while running pg_dump in PostgreSQL

Occasionally, I get the following error while doing a simple pg_dump ( pg_dump -U postgres -Fc -f mybackup.dmp mydb): pg_dump: [archiver (db)] query failed: ERROR: could not open relation with OID ...
0
votes
1answer
1k views

How can I pg_dump my database while excluding extensions?

I am trying to migrate my PostgreSQL 9.3 database to heroku. My database uses the UUID-ossp contrib extension I am not able to load my db onto Heroku because ERROR: permission denied for language ...
0
votes
1answer
120 views

pg_dump inserting C source code in dump file

Am backing up daily my DB with pg_dump. The strange issue is that when I try to restore it with psql, errors are thrown. After opening the dump file with vim and analyzing the line that is causing the ...
0
votes
1answer
13 views

Odoo 10 is not backing up DB in PostgreSQL 9.5. Shows “SQL state: 22008. Timestamp out of range on account_bank_statement_line.” [closed]

At our company, we had a DB crash a few days ago due to hardware reasons. We recovered from that but since then we're having this following error every time we try to back up our DB. pg_dump: ERROR: ...
0
votes
0answers
9 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
21 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 ...
0
votes
1answer
74 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:...
0
votes
0answers
58 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 ...
0
votes
0answers
75 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 ...
0
votes
1answer
31 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
150 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
102 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 ...
0
votes
0answers
40 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
940 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
53 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
52 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
336 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: ...
0
votes
1answer
129 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
334 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
43 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 ...
0
votes
1answer
826 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 ...
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
631 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 ...
0
votes
1answer
712 views

What is the key to pg_restore?

We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore. We are ...
0
votes
1answer
68 views

change MySQL commands to PostgreSQL in a bash script

I have a bash script to backup MySQL databases. I have pasted the relevant part below (ignore the variables). The 2 lines I think I need to change to make this work in PostgreSQL are; DBS="$(mysql --...
0
votes
1answer
800 views

Cannot import database exported by phpPgAdmin

I have exported database with phpPgAdmin tool - format sql, structure and data. When I try to import this sql using: psql command \i filename.sql After some time (file is 2GB) I get following ...
0
votes
1answer
94 views

Postgresql database backup (and restore) from two servers to third storage place

I have two server machines with PostgreSQL: (A) is version 9.0.15 and (B) version 9.2. I wish to make backups of the databases stored in (A) and (B) - and be able to do restore if needed too! - to a '...