Questions tagged [pg-dump]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 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 ...
user avatar
-1 votes
1 answer
206 views

pg_dump - aborting because of server version mismatch

El error se genero tratando de generar el DUMP: pg_dump -h 192.168.0.56 --no-owner -U user name_db > /var/tmp/dump.sql Output: pg_dump: server version: 12.3; pg_dump version: 9.2.24 pg_dump: ...
user avatar
0 votes
1 answer
49 views

Postgresql migrate schema with large objects included, but only for this schema

I have a super large database in postgresql 13, the size if 1 TB and I need to migrate only one schema to another database, the problem is that this schema has blobs. So if I migrate with pg_dump and ...
user avatar
0 votes
0 answers
16 views

Errors messages when pusing data with psql on existing database

So I have a remote postgres database hosted on aws's rds, for a django application. This is still in dev (though obviously would like to start trying to do things right already). I need to upload ...
user avatar
0 votes
1 answer
51 views

Backing up a DB on remote server

I need to backup a postgres DB which is located on a remote server. This server also hosts a lot of other stuff, and that's why I don't have a general access to the server itself. Therefore I believe ...
user avatar
0 votes
1 answer
43 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 ...
user avatar
  • 103
1 vote
0 answers
89 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). ...
user avatar
  • 157
0 votes
1 answer
168 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 ...
user avatar
  • 103
2 votes
1 answer
408 views

pg_dump is not the same version as psql

I updated postgres to version 14 with sudo apt upgrade. psql --version psql (PostgreSQL) 14.1 (Ubuntu 14.1-2.pgdg20.04+1) But pg_dump did not upgrade with everything else: pg_dump --version pg_dump (...
user avatar
1 vote
0 answers
32 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 ...
user avatar
0 votes
1 answer
223 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: ...
user avatar
0 votes
0 answers
114 views

pg_dump with proper table dependency analysis?

Is it possible to dump then recreate with proper table analysis, so for example if table A depends on table B, then table B will be dumped first, then the table A something like CREATE TABLE B(...); ...
user avatar
  • 1,199
0 votes
0 answers
90 views

PostgreSQL - Running out of memory when restoring database from large dump

I have a Postgres database dump in text format, which is roughly 20GB. I am trying to restore the database using the command $ psql DatabaseName < path-to-dump But after I while I get the message ...
user avatar
  • 1
0 votes
0 answers
40 views

Clone Postgres DB to other machine

I would like to set up a complete clone of my Postgres DB on a different machine. I created a dump with pg_dumpall, but trying to restore it I get multiple errors: psql:dump_04-10-2021_15_20_38.sql:24:...
user avatar
  • 133
0 votes
0 answers
178 views

How can I exclude Materialised Views from a pg_dump

I'm doing a pg_dump of an AWS RDS database so I can use it for local development, but there are some large tables I want to exclude. I've successfully removed them using the --exclude-table=blah* flag ...
user avatar
0 votes
0 answers
144 views

pg_dump is NOT including extensions

I'm trying to do a backup / restore of a postgresql 11 database from an RDS instance using pd_dump. The documentation states: When pg_dump is used, the CREATE EXTENSION command will be included in ...
user avatar
0 votes
1 answer
191 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 ...
user avatar
0 votes
1 answer
141 views

Postgresql: avoid referencing schema in trigger function

I have written function that I registered in a trigger that ensure referential consistency in non-normalized data (like we store agency_id + branch_id and I ensure that a referenced branch is inside a ...
user avatar
  • 429
1 vote
3 answers
905 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 ...
user avatar
  • 111
0 votes
0 answers
255 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: ...
user avatar
  • 216
0 votes
1 answer
107 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 ...
user avatar
  • 216
1 vote
1 answer
98 views

pg_dump while inserting data

I need to dump some tables of my postgres database and I am scared of getting inconsistent data.The database is verly large and the dump lasts some minutes. On my research I found a command to dump ...
user avatar
  • 13
2 votes
1 answer
1k 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: ...
user avatar
0 votes
0 answers
78 views

pg_dump fills up memory but does not write to disk

What could cause pg_dump to fill up memory and not write to disk? Strace just hangs at a certain point with no errors and database log does not show any errors. Once memory is filled up and swap is ...
user avatar
  • 868
0 votes
1 answer
263 views

No schema, tables after sucessful restore of PostgreSQL database

I backed up the stack exchange archive database hosted on an RDS instance of PostgreSQL like so: pg_dump stackexchange -h <host> -U postgres -password -o > stack.dmp ...A couple of hours ...
user avatar
1 vote
1 answer
197 views

Can I make postgres' pg_dump condense the alter table statements in the create table section?

If I use pg_dump to get the schema from my database, I get separate sections for things like keys, constraints etc., like this: CREATE TABLE public.account ( id uuid NOT NULL, db_version ...
user avatar
0 votes
0 answers
115 views

Exclude triggers in pg_dump?

Is it possible to exclude triggers when doing a pg_dump? Our triggers are named *_trigger. Is there a way I can use that as a pattern to exclude them? I tried using -N *_trigger but that did not work. ...
user avatar
  • 333
0 votes
1 answer
1k views

psql: warning: extra command-line argument "postgres.dolphin.com" ignored

when I using this command to copy table video from database dolphin to database acientbay in Fedora 32, shows error like this: /usr/share/postgresql-13/bin/pg_dump -v -t video -h postgres.dolphin.com -...
user avatar
  • 477
1 vote
0 answers
116 views

Possible to include the create schema command in pg_dump?

I'm using pg_dump to export only the DDL from a database. It's working fine, however the create schema command is not included in the output file. Here is what I'm using: pg_dump -s -c -t myschema....
user avatar
  • 333
0 votes
1 answer
348 views

pg_dump. Some COPY processes stuck on 'idle in transaction' after pg_dump completion

We do a dump of huge database (~ 2 TB) via pg_dump command: pg_dump -h 192.168.0.132 -d vodka_factory -U vodka_user -F d -j 10 -v -f /backups/vodka_factory_2021-03-26-23-56.dump/ pg_dump process ...
user avatar
0 votes
1 answer
312 views

pg_dump - how to split it into directories and files?

I am looking for a way to dump PostgreSQL12 database model into a directory/file structure, instead of a single file - for versioning purposes. I found this old thread, which mentions this exact case ...
user avatar
0 votes
1 answer
89 views

pg_dump -> import traject using "upsert" paradigm?

Well say I have two physically separate database and I wish to replace database B with data from A. Except that I wish to keep the data in B which does not exist in A? So basically I'd go over each ...
user avatar
  • 391
4 votes
1 answer
884 views

What is the correct way to make an exact copy of a database with pg_dump?

Basically, I'm trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc. Is this doable with pg_dump or does psql ...
user avatar
  • 303
0 votes
1 answer
80 views

Does daily pg_dump mess up postgres cache?

I migrated my geospatial Postgres 12.5 database to another cloud provider. I use postgis and I have around 35GB of data and 8GB of memory. Performances are way worse than on my previous provider, and ...
user avatar
  • 101
0 votes
1 answer
54 views

Detecting running pg_dump conflicting with schema changes

I use Heroku PostgreSQL and their PGBackups for my production environment. I have found, for reasons that I'm not quite clear on, that at least some schema changes do not run correctly during the time ...
user avatar
  • 101
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. ...
user avatar
  • 157
0 votes
1 answer
529 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 ...
user avatar
  • 57
0 votes
1 answer
673 views

PostgreSQL pg_dump -E encoding option not working

I have a UTF8 database qdb and I want to back it up to a plain file using the same UTF8 encoding. I am using pg_dump as I don't have pgAdmin working now. I however can't get pg_dump to output a UTF8-...
user avatar
  • 103
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 ...
user avatar
  • 175
0 votes
1 answer
200 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/ ...
user avatar
2 votes
1 answer
119 views

Is there a way to convert a custom format Pg dump file to a directory format?

We create a "custom" format (-Fc) dump file of our production DB each night. One of the things this is used for is restoring into a test DB. We then run a number of update queries to ...
user avatar
0 votes
2 answers
152 views

Where I run mysqldump or pg_dump, Both services (mysql or postgres) freeze?

I need to create a database without stopping the services and I don't know this answer. I need my web pages to continue working properly in that time. Can someone answer me?
user avatar
0 votes
1 answer
1k views

Schema pg_dump failed due to a Lock on a table

I'm running backup restore on a schema every day and get this every now and then: pg_dump: Error message from server: ERROR: relation not found (OID 86157003) DETAIL: This can be validly caused by ...
user avatar
  • 131
2 votes
1 answer
214 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 ...
user avatar
  • 157
0 votes
0 answers
356 views

How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?

We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place ...
user avatar
0 votes
1 answer
262 views

Differences between .out and .sql in pg_dump PostgreSQL

I create a backup file of postgeSQL database: >pg_dump --dbname=db_test --host=localhost --username=user -F c -f dump_test.out In documentation for pg_dump is used extension .sql and for ...
user avatar
  • 103
0 votes
1 answer
226 views

postgres database with multiple database object owners and pg_dump

with a postgres 11.2 database with multiple database object owners and trying to use the following command as the database owner (not the postgres superuser): pg_dump -h hostname -p 5432 -U ...
user avatar
  • 3
0 votes
0 answers
84 views

How Can I Move Column Definitions From One Table to Another (psql PostgreSQL)?

I have added many new columns to a table in my database in my local environment with ADD COLUMN statements, over 30. My application is split into multiple environments (UAT, STAG, PROD) and we are ...
user avatar
0 votes
2 answers
38 views

How should I be backing up Postgres for this scenario?

So the way I have been backing up Postgres is with: pg_dumpall -U eox-dev -h localhost --clean --file=/home/backups/postgres.sql I want to make sure I have a production ready backup that has ...
user avatar
  • 303
2 votes
1 answer
693 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 ...
user avatar
  • 1,001

1
2 3 4 5