Questions tagged [pg-restore]
The pg-restore tag has no usage guidance.
94
questions
0
votes
0
answers
15
views
Restore patroni managed postgres cluster from tape backups
I have a patroni managed Postgres cluster. I've set up wal-g for creating backups in Google Cloud Storage. Everything works fine and I'm able to restore the cluster or bootstrap a new one using GCS ...
0
votes
1
answer
43
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 ...
0
votes
0
answers
105
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 ...
- 216
0
votes
1
answer
572
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 ...
- 103
0
votes
1
answer
55
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
130
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). ...
- 157
0
votes
1
answer
248
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
36
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
0
votes
1
answer
293
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
31
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
101
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
26
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
0
votes
1
answer
213
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
1k
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
0
answers
296
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: ...
- 216
0
votes
1
answer
145
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 ...
- 216
2
votes
1
answer
2k
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: ...
- 149
0
votes
1
answer
96
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,001
1
vote
1
answer
2k
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.
...
- 157
0
votes
1
answer
342
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
546
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
576
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 ...
- 57
4
votes
0
answers
1k
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 ...
- 307
1
vote
1
answer
7k
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
1k
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 ...
- 175
0
votes
1
answer
226
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
594
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. ...
- 157
0
votes
0
answers
118
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 ...
- 11
1
vote
1
answer
106
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 ...
- 181
2
votes
1
answer
254
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 ...
- 157
0
votes
1
answer
1k
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,038
2
votes
1
answer
747
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,001
0
votes
2
answers
4k
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
243
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
9
votes
3
answers
2k
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:...
- 193
0
votes
0
answers
77
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
1
vote
1
answer
40
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
0
answers
240
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 ...
- 167
0
votes
2
answers
195
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 ...
- 31
2
votes
1
answer
43
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 ...
- 369
0
votes
2
answers
359
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
1
answer
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
2
answers
651
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 ...
- 105
1
vote
1
answer
7k
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 ...
- 141
2
votes
1
answer
6k
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,222
2
votes
1
answer
145
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=$...
- 123
3
votes
1
answer
6k
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 ...
- 625
4
votes
0
answers
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 ...
- 41
0
votes
0
answers
348
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, ...
- 131
0
votes
1
answer
206
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 ...
- 59