Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
63
questions
0
votes
0answers
38 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:...
0
votes
0answers
24 views
pg_restore idling during second half of restore
We're migrating from PostgreSQL hosted on our VM to Azure Database for PostgreSQL managed database. One of the first problems we hit has been time to restore database dump. It took 20-30 min on our VM ...
1
vote
1answer
16 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,...
0
votes
0answers
17 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
0answers
29 views
advice : restore a table with PostgreSQL in a database
(dba beginner)
As an exercise, I test different methods to restore all or part of a database.
I have 2 dumps of a table: the first one in sql format, the second one in custom format. So I use psql in ...
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 ...
2
votes
1answer
33 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 ...
0
votes
2answers
73 views
pg_restore fails with drop default on identity column
Trying to restore a Postgres 10.6 database via pg_restore, and it seems to want to do a DROP DEFAULT when it should be doing a DROP IDENTITY during the clean phase. See below:
pg_restore --verbose -...
-1
votes
1answer
865 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
2answers
50 views
Migrate large postgresql database (windows -> linux) [closed]
I have a "large" PostgreSQL (9.6 with timescale plugin) database (about 1Tb). And would like to migrate it to a Linux machine. The naive approach would be pg_dump and pg_restore.
(Not sure how long ...
1
vote
1answer
1k 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
1answer
787 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
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
1answer
261 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 ...
3
votes
0answers
407 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 ...
0
votes
0answers
137 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
45 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
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 ...
1
vote
1answer
163 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
1answer
740 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
676 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
536 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
300 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
237 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 ...
2
votes
3answers
8k 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
1k 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
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 --...
0
votes
1answer
675 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....
0
votes
2answers
432 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....
2
votes
3answers
3k 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
1k 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
920 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 ...
3
votes
0answers
488 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
752 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
90 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
354 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
1k 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
1k 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 ...
5
votes
1answer
7k 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 ~/...
3
votes
1answer
5k 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
191 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
187 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 ...
2
votes
1answer
7k 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
338 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
757 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
2k 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 ...
3
votes
1answer
3k 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
511 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 --...
