Questions tagged [pg-restore]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
15 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 $...
Vasileios Giannakidis's user avatar
0 votes
1 answer
18 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 ...
toraritte's user avatar
  • 111
0 votes
1 answer
17 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?
datascinalyst's user avatar
0 votes
0 answers
8 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 ...
Roberto's user avatar
  • 142
0 votes
0 answers
22 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 ...
sebeid's user avatar
  • 1,383
2 votes
1 answer
926 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 ...
Geuis's user avatar
  • 143
2 votes
1 answer
1k 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....
Dor Cohen's user avatar
0 votes
1 answer
55 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 ...
David S's user avatar
  • 282
0 votes
0 answers
40 views

Cross-cloud backup and restore postgresql database with minimal downtime

I have a huge database running in Azure Postgresql (Windows Server) and I want to migrate the database to a different cloud provider, say, AWS RDS. Usually, I use pg_dump, rsync and pg_restore. This ...
Mukul Gupta's user avatar
0 votes
0 answers
126 views

Restore only specific columns with pg_restore

I have a database dump with the table 'articles', amongst other tables. The table 'articles' contains columns 'a', 'b' and 'c'. I have another, development database, with a table named 'articles', ...
user63764's user avatar
0 votes
0 answers
2k 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 "...
padjee's user avatar
  • 157
0 votes
1 answer
251 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 ...
RonJohn's user avatar
  • 606
1 vote
1 answer
159 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 ...
DaleSwanson's user avatar
0 votes
1 answer
644 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/...
s.k's user avatar
  • 342
1 vote
1 answer
2k 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 ...
Sajith P Shetty's user avatar
0 votes
1 answer
378 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 ...
Sajith P Shetty's user avatar
-1 votes
1 answer
983 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 ...
sheckwes's user avatar
1 vote
1 answer
437 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 ...
Nicole Staline's user avatar
1 vote
0 answers
444 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 ...
s.k's user avatar
  • 342
5 votes
1 answer
6k 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 ...
flwd's user avatar
  • 153
0 votes
1 answer
602 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 ...
pbm's user avatar
  • 103
1 vote
0 answers
2k 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). ...
Leon's user avatar
  • 285
0 votes
1 answer
2k 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 ...
scrollout's user avatar
  • 103
1 vote
0 answers
389 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 ...
Dr Rob Lang's user avatar
3 votes
1 answer
1k 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: ...
Bhoomtawath Plinsut's user avatar
0 votes
0 answers
73 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 ...
astaga's user avatar
  • 1
0 votes
1 answer
682 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 ...
Claudio Floreani's user avatar
1 vote
1 answer
164 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 ...
kaounKaoun's user avatar
1 vote
1 answer
908 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 ...
Abdallah Mehdoini's user avatar
1 vote
3 answers
4k 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 ...
JC23's user avatar
  • 111
0 votes
1 answer
753 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 ...
s.k's user avatar
  • 342
5 votes
1 answer
4k 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: ...
Emre Sevinç's user avatar
0 votes
1 answer
283 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 ...
Nyxynyx's user avatar
  • 1,111
4 votes
1 answer
5k 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. ...
Leon's user avatar
  • 285
0 votes
1 answer
922 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 ...
botenvouwer's user avatar
0 votes
1 answer
1k 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 : ...
Burak Yurdakul's user avatar
0 votes
1 answer
1k 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 ...
isxaker's user avatar
  • 67
8 votes
0 answers
2k 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 ...
Gabriel's user avatar
  • 347
1 vote
1 answer
9k 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 ...
rcx935's user avatar
  • 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 ...
david's user avatar
  • 205
0 votes
1 answer
565 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/ ...
Milambardo's user avatar
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. ...
Rino's user avatar
  • 199
1 vote
1 answer
154 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 ...
sqwale's user avatar
  • 211
2 votes
1 answer
1k 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 ...
Leon's user avatar
  • 285
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 ...
Cerin's user avatar
  • 1,294
2 votes
1 answer
2k 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 ...
Nyxynyx's user avatar
  • 1,111
0 votes
2 answers
6k 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 ...
user10664542's user avatar
1 vote
1 answer
317 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 ....
Andrus's user avatar
  • 123
10 votes
3 answers
4k 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:...
Richie Thomas's user avatar
1 vote
0 answers
179 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 ...
mario's user avatar
  • 11