Questions tagged [pg-restore]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
16
votes
3answers
21k views

postgres - pg_dump and pg_restore without roles

I'm trying to restore a dump without having the appropriate roles on the receiving database. As mentioned here but also here, you need to have the --no-owner as an option, either in pg_dump or ...
12
votes
3answers
18k 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 ...
9
votes
3answers
1k views

`pg_restore` with `jobs` flag results in `pg_restore: error: a worker process died unexpectedly`

I have a script which runs the following: pg_restore tmp/latest.backup --verbose --clean --no-acl --no-owner --dbname hub_development --jobs=12 This frequently fails with the following error: error:...
7
votes
1answer
11k 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 ~/...
5
votes
1answer
13k 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....
5
votes
1answer
6k 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 ...
5
votes
1answer
9k 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 ...
5
votes
1answer
933 views

Postgresql: How to avoid encoding issues when copying a schema from one server to another?

I'm 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....
4
votes
1answer
4k 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 ...
4
votes
2answers
6k 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 ...
4
votes
0answers
1k views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
4
votes
0answers
603 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 ...
3
votes
1answer
10k 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 ...
3
votes
1answer
1k 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. ...
3
votes
2answers
556 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 ...
2
votes
1answer
3k views

PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?

I worked with many different databases (SQL Server, Oracle, IBM db2) and for all of them if we have a backup it is just enough to execute RESTORE command in order to restore the data. I am beginner ...
2
votes
2answers
2k 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\...
2
votes
1answer
39 views

Does dumping/restoring do the same as “vacuum full”?

I have a large database running with PostgreSQL v11, and the disk space is very limited, so I have to VACUUM (FULL) the database frequently (e.g. every day). But vacuuming is a very time-consuming ...
2
votes
1answer
2k 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
2answers
1k 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
1answer
37 views

Automatically reload DB nightly

Nightly we dump our Production DB and load it into our Staging and Dev environments. A bash script is taking care of that and the steps to achieve this are the following: download the dump from the ...
2
votes
1answer
4k views

How to restore a postgres database created via an older version of postgres?

I have a file db.backup, that I would like to restore to PostgreSQL version 11. When I tried to install via Azure Data Studio I got an error: Restore: pg_restore: [archiver] unsupported version (1....
2
votes
1answer
82 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=$...
2
votes
1answer
962 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 ...
2
votes
3answers
6k 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: ...
2
votes
1answer
301 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 ...
2
votes
1answer
9k 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 ...
2
votes
2answers
2k 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 --...
2
votes
0answers
1k 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 --...
2
votes
1answer
109 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
1answer
792 views

Postgres very slow on server [closed]

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 ...
2
votes
0answers
657 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
1answer
4k views

OpenProject pg_restore - input file does not appear to be a valid archive

I'm trying to restore my OpenProject's database created with the command sudo openproject run backup this command created the file postgresql-dump-20190722131834.pgdump which I'm trying to restore ...
1
vote
2answers
301 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 ...
1
vote
2answers
13k 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 ...
1
vote
1answer
228 views

Database Size is Different after pg_restore (389 GB vs 229 GB)

I created a backup of my database in a PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_dump: PGPASSWORD=mypassword pg_dump -h 127.22.0.4 -p 5432 -U postgres -Z0 -Fc database_development ...
1
vote
1answer
1k 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 ...
1
vote
1answer
3k 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 ...
1
vote
1answer
1k 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 ...
1
vote
0answers
321 views

pg_restore --clean not working because cascade drop

I'm working with a copy (DB-B) of a database (DB-A) I keep up to date by running daily: pg_restore -U postgres -d someDB --clean -j 2 -L WhatToImportList admin.dump But I started noticing duplicate ...
1
vote
1answer
3k views

Restoring a backup gives “Failed exit code 1”

I'm migrating from Heroku's Postgres to AWS RDS. I've generated a pg_backup.dump file from Heroku, installed Postgres on my local machine, launched PGAdmin, configure remote AWS server, and used the ...
1
vote
1answer
44 views

Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB

We are migrating from Google-Cloud PGSQL managed database to a managed PGSQL server on Digital ocean. The dilemma we are facing is that both Google and Digital ocean are managed so we have no access ...
1
vote
1answer
25 views

Can I do a backup without extension functions?

I use the PostGIS extension on my database. The thing is when I do a backup and then a restore, all the PostGIS functions give a warning during the restore process, because the functions already exist,...
1
vote
0answers
428 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 ...
1
vote
0answers
38 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 ...
1
vote
0answers
2k 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
0answers
373 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
1
vote
0answers
708 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
0answers
209 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 ...
1
vote
2answers
365 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) ...