Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
110
questions
0
votes
1
answer
28
views
PostgreSQL: pg_dump fails to correctly pg_restore due to missing or erroneous custom function, but present in dump
I am running PostgreSQL 13.15 in a local docker container, with equivalent pg_dump/pg_restore versions. When dumping and restoring the database, one of the custom functions that was previously ...
- 38
0
votes
0
answers
79
views
Using `pg_restore` after `pg_dump`
I'm trying to move from SQL Server to Postgres and am trying to check that I can restore a backup.
The first boobytrap is that the pg_dump command for backup must be done with the -F custom option in ...
0
votes
0
answers
20
views
pg_restore does not give back shell
I got a dump file.I'm trying to restore it but after it finished it does not give back shell and stucks.Is there any solution ?
- 1
0
votes
1
answer
75
views
Command pg_restore does not import / restore any data, printing "depends on"
A database origin was dumped in postgres "custom" format using: pg_dump -d origin --data-only -Fc > file.dump.
The dump was then used to restore all data in a different database with the ...
- 101
1
vote
1
answer
3k
views
"Unsupported version" error restoring a schema backed up with the same version it was dumped
I am trying to backup and restore a schema from a remote server to a local system. Even though pg_dump and pg_restore report the same version, the later fails with the familiar "Unsupported ...
- 674
0
votes
1
answer
86
views
How to pg_restore some tables with automatic index creation?
How to restore some table from pg_dump custom format, and automatically create it's index ?
I just restore some tables from database dump (custom format).
pg_restore -d database --table=table1 --table=...
- 11
1
vote
1
answer
56
views
pg_restore drops extension plpgsql
We face a situation where pg_restore drops extension plpgsql and then recreates it. This is done by a CI job we have but i could not reproduce it. These are the commands we use:
pg_dump -d $...
0
votes
1
answer
2k
views
`pg_restore` unable to read `toc.dat` created by `pg_dump -Fd`: `pg_restore: error: expected format (1) differs from format found in file (3)`
The pg_dump -Fd mydb -f mydb.dump command creates a mydb.dump directory with a toc.dat file. The pg_dump man page mentions that pg_restore is able to read this file, but after skimming its man page ...
- 121
0
votes
1
answer
2k
views
Trying to restore data from new Postgres version to old Postgres version
I took a backup of the Postgres database version 15, and now I want to restore that data to Postgres version 13.1.2? is it possible?
0
votes
0
answers
58
views
How to create a postgres backup of all databases with pg_dumpall compatible with pg_restore
I want to implement a backup/restore strategy for a postgres instance, and I'm having some struggle when choosing the most appropriate and flexible way to do it.
On one side, pg_dumpall allows to dump ...
- 142
0
votes
0
answers
25
views
Rstore postgres Database
I am trying to restore a PostgreSQL database that consists of two different schemas. I am using pg_restore for the restore process. However, I am encountering errors due to dependencies between the ...
- 1,401
3
votes
1
answer
2k
views
pg_restore complains both if the database exists or not when importing a backup
Postgresql version: 15
Backup was made from version 12.
(I don't know if --schema-only is part of the problem.)
I have a single backup tar file of a database that I'm trying to restore, but I keep ...
- 153
2
votes
1
answer
4k
views
How can I exclude some tables when using pg_restore?
I created a backup file from one database and I try to copy it to a different database, under the same schema, while excluding two large tables.
I tried this command :
pg_restore --host=violacredit....
- 31
0
votes
1
answer
104
views
Refreshing development environment from backup that contains FDW in Postgres
Background:
We recently added a foreign data wrapper from one of our Postgres (v14) databases to another Postgres (v14) database in a different system. It works fine in our production environment, but ...
- 284
0
votes
0
answers
5k
views
best way to backup and restore partitioned table
I have a table with 600 partitions and 68 millions of rows.
I successfully run backup using
pg_dump -h 127.0.0.1 -Fc --dbname=postgresql://postgres:[email protected]:5432/postgres -t "...
- 317
0
votes
1
answer
371
views
AWS RDS database uses more space after pg_dump/pg_restore
I've got an RDS Postgresql 12 instance that allocated 11,000,000 MB of io1 disk space, and there's 7,300,000 MB of Free Storage Space.
That means the database is only using 11TB - 7.3TB = 3.7TB of ...
- 646
1
vote
1
answer
316
views
How can I load a custom format dump from pg_dump into an RDS while ignoring constraint checks during the import?
I have a number of constraints:
I have binary custom format dump from Heroku that I need to load via pg_restore.
I need to load this onto an AWS RDS instance, where I don't have full superuser (only ...
- 113
0
votes
1
answer
1k
views
pg_restore error when using the --single-transaction: options c/--clean and -a/--data-only cannot be used together (even so the -a flag was not used)
I met a strange PostgreSQL (v.13.8) on a Windows server 2019 machine today when trying to restore a custom dump file.
The following command worked:
pg_restore -d postgres://postgres@localhost:5432/...
- 384
1
vote
1
answer
5k
views
pg_restore ERROR: Schema "public" already exists (inter version restore)
This is a specific scenario where we are restoring the custom dump extracted from Postgres 10 to Postgres 14. We are getting the below error:
ERROR: Schema "public" already exists
I ...
- 154
0
votes
1
answer
1k
views
pg_restore: "-n" option is not importing large objects
I am trying to import a database "demodb" from a dump using -n option. The intention is to import only specific schema called "demoschema". I create demodb as blank db before ...
- 154
-1
votes
1
answer
2k
views
pg_restore relation error
I am trying to run pg_restore on a file so I am able to use it in postgres. The file is a psql file. I try the command: pg_restore --no-privileges --no-owner -d test backupfile.psql -c -U postgres ...
- 1
1
vote
1
answer
618
views
pg_dump on heavily written production db causing queries to hang
Trying to migrate our existing DB. There are no followers or existing backups that are available to us (they are on our cloud solution, but no available for download). So our only option is to run ...
- 121
1
vote
0
answers
542
views
pg_restore into PG 13: error: could not execute query: ERROR: must be owner of index
I'm running this pg_restore (13.5) command:
pg_restore -d <connection_uri> \
--single-transaction \
--clean \
--if-exists \
--no-owner \
--role=www \
--schema my_schema \
--verbose ...
- 384
7
votes
1
answer
9k
views
pg_restore hangs in terminal indefinitely with zero output in verbose mode
I'm trying to restore a PostgreSQL backup that was created on version 11.7 on a fresh install of version 14.1.
When running the pg_restore command, it hangs on the terminal indefinitely with zero ...
- 173
0
votes
1
answer
1k
views
Make a copy of database with new collation
I have a small hobby project in Django backed by Cloud SQL Postgres instance. Recently I realised that data sorting is not correct and identified it as issue with collation setting.
Indeed collation ...
- 103
2
votes
0
answers
3k
views
What's the difference between "--section=data" and "--data-only" of pg_dump/pg_restore?
In the man page of pg_dump/pg_restore, that is mentioned about --data-only option(but I didn't find --section=data) as following:
--data-only
Dump only the data, not the schema (data definitions). ...
- 357
0
votes
1
answer
3k
views
Copying a PostgreSQL schema and its data from one database to another
I am trying to copy a PostgreSQL schema and it's data from one database to another without impacting the availability of the current schema (old_schema). I would also like to do this for a specific ...
- 103
1
vote
0
answers
532
views
FK constraint ON DELETE RESTRICT not working on some database instances
My database instances appear to treat FK constraint ON DELETE RESTRICT differently and I don't know why. On production database instance (and a clone, where I do my testing), I believe the RESTRICT is ...
- 111
5
votes
1
answer
2k
views
How to set search_path during pg_restore?
pg_dump and pg_restore will set search_path to an empty string, which will fail if there are functions that call functions in the public schema without explicitly specifying the schema name (ie: ...
0
votes
0
answers
83
views
postgresql database didn't restored in ubuntu and got blank
i try to restore databse form my pc(windows) that i send it from pscp
i use this script :
sudo -u postgres pg_restore -U postgres -f /home/abura/first_backup.sql
and got blank, so i randomly click to ...
- 1
0
votes
1
answer
1k
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
1
answer
354
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 ...
- 115
1
vote
1
answer
2k
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
3
answers
6k
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 ...
- 111
0
votes
1
answer
1k
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 ...
- 384
5
votes
1
answer
6k
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: ...
- 189
0
votes
1
answer
399
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,141
4
votes
1
answer
7k
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.
...
- 357
1
vote
1
answer
1k
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 ...
- 113
0
votes
1
answer
2k
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 :
...
- 101
1
vote
1
answer
2k
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 ...
- 77
10
votes
0
answers
3k
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 ...
- 377
1
vote
1
answer
10k
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 ...
- 113
0
votes
1
answer
4k
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 ...
- 215
0
votes
1
answer
745
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/
...
- 183
0
votes
0
answers
2k
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. ...
- 219
1
vote
1
answer
191
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 ...
- 221
3
votes
1
answer
2k
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 ...
- 357
0
votes
1
answer
2k
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,385
2
votes
1
answer
3k
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,141