Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
2
votes
1answer
167 views
Temporarily disable indexes before updating whole table
For testing purposes I periodically restore production backup into a new database (AWS RDS PostgreSQL instance) with pg_restore. In the new database I run SQL queries that anonymize sensitive data. ...
0
votes
1answer
106 views
replacing a database with pg_restore
I am trying to replace an existing database with pg_restore and am running into issues. I apologize if this issue is elementary.
Specifically my Postgres install is set up like this:
database ...
1
vote
1answer
85 views
Why might a parallel pg_restore command take hours longer to complete its non-parallel equivalent?
I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs:
pg_restore -d analytics -U postgres -j 4 -v "D:\Program Files\PostgreSQL\10\backups\...
0
votes
1answer
83 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
0answers
49 views
pg_restore with tablespace location
Is it possible to pg_restore a dump file, but also specify the location of the tablespace like was done in this sql:
CREATE TABLESPACE <tablespace> OWNER <ROLE> LOCATION '<PATH>';
1
vote
0answers
123 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
2answers
1k views
pg_restore into a new database
I'm using Heroku's Postgres option and have a backup I downloaded from it. I'd like to restore it into a fresh database so I can look at it. I've been trying different commands to little avail. My ...
2
votes
1answer
546 views
How to restore function using PG_RESTORE
I did the backup of my database:
-bash-4.2$ pg_dump -d mydb --format=custom -f /tmp/dump
-bash-4.2$
Below checked and I saw my functions there:
-bash-4.2$ pg_restore -l /tmp/dump | grep -i ...
2
votes
0answers
536 views
Trouble when using pg_restore
I have a database named mydbname that I created manually.
I also created a user myuser for which I granted all privileges on mydbname.
I dumped my database using pg_dump this way:
$> pg_dump --...
0
votes
1answer
263 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
1answer
215 views
Empty table after pg_restore
I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it.
Steps:
pg_dump --format custom --verbose --file "potential_users.backup" --table "public....
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
0answers
539 views
Postgresql Restore - duplicate key value violates unique constraint
I have a PostgreSQL 9.2 server and want to restore this to PostgreSQL 9.6.
Here is the table.
create table mytable (
ID int,
EID int,
Name varchar(100),
Address varchar(300),
primary key (ID, EID)
)...
0
votes
1answer
348 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
0answers
266 views
PostgreSQL: Pg_restore to AWS RDS Failed
I am trying to restore my local database using pg_restore command. My command line is-
pg_restore -v -h Amazonhostname -U dbsetup -C -d catalog_db < c:\Catalog_backup.tar
But I am getting the ...
2
votes
0answers
330 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 ...
2
votes
2answers
405 views
Why is pg_restore unable to create database with --create option before restoring from dump?
Why pg_restore is unable to create database with --create option before restoring from dump?
pg_restore --username=myuser --create --dbname=mydb --verbose mybackup
Error,
pg_restore: [archiver (db)...
2
votes
0answers
54 views
may i reduce the restore time with pgsql 9.2?
I need your help about how to reduce time to restore process for a big dump (400GB)
We have a database that takes weeks to restore due to the single threaded nature of index generation on PostgreSQL. ...
2
votes
2answers
116 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
472 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
1answer
767 views
What file formats can be restored using pg_restore on PostgreSQL?
first question here, fairly new to DB's and Data itself.
My question is fairly simple, after playing around with Postgres, PGAdmin3, I realised I would like to access some public data, but I ...
3
votes
1answer
3k views
What does this error mean when doing a pg_restore on a Postgres 9.6.2 database?
So I have a rails 5.0.x application running on Heroku with Postgres 9.6.1. I pull down and do a pg_restore --exit-on-error --verbose --clean --no-acl --no-owner -h localhost -d database_development ~/...
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
1answer
120 views
postgres restore configuration?
I have a database dump that's about 140Gb. (When loaded, the database is about 350Gb.) I occasionally load copies of my dumped data into development and testing databases, which takes a while.
Any ...
1
vote
2answers
118 views
reducing migration time of a large size postgresql database
We have a postgresql 9.4 DB server that handles 2 DBs, each ~800GB. No replication is implemented yet.
We reduced pg_dump time with using -j & -Fd options to about 2 hours.
The problem is, we ...
0
votes
0answers
3k views
Dump and Restore Only Data in Postgresql
I have two pgsql database A1 in server A and B1 in server B. I want to dump only data from A1 and restore in B1 without overwriting the existing data. May i know how to proceed?
2
votes
1answer
4k views
pg_restore: [custom archiver] could not read from input file: end of file
Not going into details how that happened, all started with a corrupt DB. It was OK on the file-system level, but trying to:
select count(*) from mytable
resulted in the following:
ERROR: could ...
1
vote
0answers
265 views
Restore DB backup: schema node not found for object TABLE DATA
I made a backup from my db in postgresql via pgadmin. now I want to restore it in my pesonal laptop. but I get this error for each table in my DB:
schema node not found for object TABLE DATA
0
votes
1answer
476 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 ...
1
vote
1answer
1k views
how can i lock or disable connections to postgres DB while performing maintenance? [duplicate]
I never have this issue with MySQL but PostgreSQL is ALWAYS a pain when trying to do any kind of maintenance.
So I am trying to truncate and load new data into databases on PostgreSQL server but ...
4
votes
1answer
2k views
ERROR: invalid input syntax for type money
While importing I get this error:
pg_restore: [archiver (db)] COPY failed for table "transaction_details":
ERROR: invalid input syntax for type money: "$0.00"
restore completed, but the ...
1
vote
0answers
282 views
How do I generate a Postgres dump with the column names in my INSERT statement?
I’m trying to export data from my Postgres 9.5 instance on my Mac to my Postgres 9.5 db on an Ubuntu Linux box. I’m using this command to export
pg_dump -U username --format plain --section data --...
1
vote
1answer
528 views
pg_restore restore FK constraints with explicit --table argument
I started using the -t argument of pg_restore to only restore some tables, which has worked well for read-only workflows.
The problem is that the -t seems to not restore the FK constraints nor ...
0
votes
1answer
3k views
Why is my pg_restore command not working?
Yesterday, I created backups of my various PostgreSQL databases using the following command:
pg_dump -Fc -d junk -h localhost -p 5434 -U postgres -W > z:\pg_dump\96_junk.dump
I created an ...
0
votes
2answers
1k views
Differences between .dump and .sql in pg_dump PostgreSQL
I'm trying to take a backup of schema using pg_dump like,
$ pg_dump -U ganapathy -n hotel_management > ganapathy_schema.dump
It create the backup, But I can't restore it using pg_restore. It give ...
0
votes
1answer
4k views
Error loading example PostgreSQL database: “Could not find header for file …” [closed]
I am learning o use PostgreSQL so I am trying to insert a whole practice database "dvdrental", which I downloaded from PostgreSQL learning blog. I am on a Mac OSX and I am using PostgreSQL through my ...
3
votes
1answer
395 views
Postgresql: How to avoid encoding issues when copying a schema from one server to another?
I am using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server:
$ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema....
1
vote
1answer
345 views
Postgres very slow on server
Restoring a single table on the server is very slow
From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016
Server
2016-07-22 13:55:11 CEST [88719-1] tester@test LOG: duration: 22988.122 ...
3
votes
1answer
4k views
Restore PostgreSQL database with same locale settings as source database
I have a PostreSQL 9.x database on an Ubuntu 14.04 LTS production machine.
My development machine is Windows 7-based, providing PostreSQL 9.y.
I want to restore the Ubuntu PostreSQL database on my ...
2
votes
0answers
415 views
parallel pg_restore with materialized views
I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS.
To troubleshoot the restore process, I have split the restore ...
1
vote
0answers
137 views
pg_restore slow after adding a check function
I have a postgres database with 10 schemas, and recently I had to find a way for some tables between schemas to have common records.
The way I did this was to remove the foreign key on some tables in ...
2
votes
1answer
5k views
PostgreSQL: Restoring 1 file from a directory format dump
I've got a large'ish (> 700GB) PostgreSQL database. I'm working on importing it into AWS RDS. I realize there are modules that will cause problems, we're dealing with that separately, so for right ...
1
vote
1answer
118 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) ...
2
votes
2answers
1k views
pg_restore does not restore blobs
I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command:
pg_dump --format=c --...
0
votes
0answers
115 views
Proper handling of errors on pg_restore
Following the execution of
pg_dump -Fc
the follow-up pg_restore is generating errors.
Under
pg_restore -c -v -1 -d
the following error is generated Error while PROCESSING TOC: The motive is:
...
