Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
106
questions
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 $...
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 ...
- 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?
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 ...
- 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 ...
- 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 ...
- 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....
- 31
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 ...
- 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 ...
- 125
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', ...
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 "...
- 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 ...
- 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 ...
- 113
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/...
- 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 ...
- 144
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 ...
- 144
-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 ...
- 1
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 ...
- 121
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 ...
- 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 ...
- 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 ...
- 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). ...
- 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 ...
- 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 ...
- 111
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: ...
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 ...
- 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 ...
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 ...
- 115
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 ...
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 ...
- 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 ...
- 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: ...
- 189
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 ...
- 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.
...
- 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 ...
- 103
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 :
...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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/
...
- 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. ...
- 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 ...
- 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 ...
- 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 ...
- 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
...
- 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 ...
- 101
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 ....
- 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:...
- 203
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 ...
- 11