Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
86
questions
0
votes
1answer
25 views
Made a plain-text backup (SQL script file format), how do I restore just the data (assuming the schema is valid)?
I use to backup my DB with pg_dump -cF p my_db > snapshot.sql.
How can I restore only the data from one of those files (assuming the schema is valid)?
Do I have to issue just and all the COPY ...
1
vote
1answer
11 views
Restore Postgres backup from pgdump.dir
I have been seeing *.pgdump directory mostly to get the backup and restore. But for my new project the backups are stored under dbname<date>.pgdump.dir
Under this directory, the backups are ...
0
votes
1answer
43 views
pg_restore: error: could not execute query: ERROR: materialized view "ws1_all_formate" has not been populated
I had this error after the execution of pg_restore:
pg_restore: finished item 19181 MATERIALIZED VIEW DATA avenant_f
pg_restore: from TOC entry 19171; 0 2565914 MATERIALIZED VIEW DATA ...
1
vote
3answers
40 views
How can I restore pg_dump file on vm
I created a dump with pg_dump using,
sudo -u postgres pg_dump dbname > dbexport.psql
I tried to restore that dump with,
sudo -u postgres pg_restore -d dbname < dbexport.psql
I got an error in ...
0
votes
0answers
65 views
Restoring a dump of a schema (made with the -n option of pg_dump) of a postgis database; schema "public" already exists
While trying to restore a PostgreSQL dump that has been made using the -n public option of pg_dump (see:
Postgis extension doesn't seem to be taken into account when restoring a PostGIS database: ...
0
votes
1answer
48 views
Postgis extension doesn't seem to be taken into account when restoring a PostGIS database: ERROR: type "public.geometry" does not exist
I am struggling trying to restore a PostgreSQL dump that has been made using pg_dump (v. 12+) out of a production database having PostGIS installed, the very first issue is this one when restoring the ...
2
votes
1answer
445 views
pg_restore: error: could not execute query: ERROR: option "locale" not recognized
I'm getting the following error when I'm trying to restore my dump file to another (local) PostgreSQL server (running on Debian GNU/Linux 10.10):
pg_restore: error: could not execute query: ERROR: ...
0
votes
1answer
27 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 ...
1
vote
1answer
1k 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
144 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
333 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
291 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
666 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
5k 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
124 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
528 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
80 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
239 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
84 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
79 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
93 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
1answer
911 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
409 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
3k 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
168 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
51 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
29 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
150 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
151 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
38 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
260 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
3k 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
484 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
5k 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
5k 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
112 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=$...
3
votes
1answer
5k 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
2k 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
264 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
151 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
2k 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
2k 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. ...
1
vote
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
3k 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
755 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
448 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 ...
14
votes
3answers
22k 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 ...
3
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 --...
