Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
82
questions
0
votes
0answers
20 views
what are the files present in C:\Program Files\PostgreSQL\12\pgAdmin 4\runtime folder for postgresql 12?
I am trying to restore dvdrental.tar using pgadmin 4 it popped up an error saying that there "doesn't exist C:\Program Files\PostgreSQL\12\pgAdmin 4\runtime\pg_restore.exe". Hence i have ...
0
votes
1answer
15 views
pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column
Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images.
A dump of the database was taken from the first ...
0
votes
1answer
241 views
How to prevent pg_dump from generating “SET default_table_access_method=heap;”?
I have a big dump file that is generated by pg_dump that comes with PostgreSQL v12 client, and it is in customized format instead of plain sql text. The version of the server is PostgreSQL v12 too.
...
0
votes
1answer
37 views
Does pg_restore continue after internet connection got lost
I know a query runs until it returns data when connection to the client is lost. However, what if you use pg_restore. Does it upload the restore file and then does the restore on the server? Or does ...
0
votes
1answer
92 views
pg_restore importing single table to a file
I have a backup in directory format taken with pg_dump.
When I try to export single table from this dump with the command:
pg_restore -Fd mydirectory -t sometable > table.sql
I get the error :
...
0
votes
1answer
86 views
How to get lsn(xid) numbers of transactions from WAL file?
I'm looking for a way of getting transactions from WAL files.
Basically I need their LSN numbers.
(ideally both transactions LSN numbers and WAL files first and last LSN numbers)
I have a PostgreSQL ...
1
vote
0answers
270 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
2k 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 ...
0
votes
0answers
64 views
How to backup a postgresql db to export on AWS lightsail
I want to take a backup from my postgresql db which I use for my Django project.
I'm using ubuntu 18.04 and want to import it in AWS lightsail.
I read pgadmin docs on how to take a backup with a ...
0
votes
1answer
132 views
pg_restore showing errors when specifying schema when backing up with pg_dump
I have created two different dump files one without specifying the schema, another specifying the public schema
without specifying the public schema'
pg_dump -h IP_ADDRESS -p 5432 -U my_user -Fc my_db ...
0
votes
1answer
33 views
Error using pg_restore
I am using the following guide to migrate a database from my Windows 10 machine to my Managed Database on Digital Ocean.
https://www.digitalocean.com/docs/databases/postgresql/how-to/import-databases/
...
0
votes
0answers
115 views
PostgreSQL pg_restore: How do I save script report to a txt file?
I need to run pg_restore on my PC with Windows CMD to restore a dump to a remote DB. I need to save the running report of the pg_restore into a file, to make sure the restoration ran as expected. ...
0
votes
0answers
63 views
PITR Restore on Postgresql Windows
I have Postgresql 11 running on Windows server and master/slave replication working perfectly.
I have enable the wal_archiving and also i have enabled automatic backups using pg_dump
I need to ask if ...
1
vote
1answer
43 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 ...
2
votes
1answer
36 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 ...
0
votes
0answers
54 views
PostgreSQL - Details/counts in pg_dump file
I'm involved with converting a system from Oracle to PostgreSQL.
We currently use database dumps quite heavily to move data between systems.
For Postgresql, I'm using pdump -Fc and restoring using ...
0
votes
1answer
508 views
Stop pg_restore from returning false errors?
How do you stop pg_restore from returning an error code if it encounters a meaningless non-error like “schema "public" already exists”?
I'm trying to automate a database transfer, and ...
1
vote
1answer
207 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
...
0
votes
2answers
2k views
How do I dump a Postgres table and restore under another user/role?
I am getting an error when I try to restore a Postgres database table to a different user/role than from which it was dumped.
I am doing the pg_dump as OS user: 'postgres', and doing the restore as ...
0
votes
1answer
110 views
How to transfer postgres databases from one server to other
VPS server has old Debian 6 Squeeze with Postgres 9.1
It has 24 databases.
Every night backup copies are created using pg_dump to /root/backups directory for every database.
This directory has 24 ....
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:...
0
votes
0answers
37 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
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,...
0
votes
0answers
69 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
100 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
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 ...
0
votes
2answers
214 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
2k views
pg_restore error while initializing. (Closed)
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
356 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
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 ...
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
79 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
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 ...
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 ...
0
votes
0answers
179 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
122 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 ...
2
votes
1answer
878 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 ...
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. ...
0
votes
1answer
1k 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 ...
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\...
0
votes
1answer
619 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
421 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 ...
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 ...
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
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
1k 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
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
...
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....
0
votes
2answers
825 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
5k 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:
...
