Questions tagged [pg-dump]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
1answer
12 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 ...
1
vote
3answers
24 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 ...
0
votes
0answers
44 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: ...
0
votes
1answer
43 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 ...
1
vote
1answer
13 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 ...
2
votes
1answer
136 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: ...
0
votes
0answers
21 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 ...
0
votes
1answer
22 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 ...
1
vote
1answer
26 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 ...
0
votes
0answers
31 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. ...
0
votes
1answer
211 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 -...
0
votes
0answers
25 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....
0
votes
1answer
62 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 ...
0
votes
1answer
89 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 ...
0
votes
1answer
36 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 ...
0
votes
0answers
11 views

Dump for databse with 10% free memmory

I would like to do a dump of 10TB db. I have about 10% of free memmory. Each month I'm generating about 100GB. Reason for do a dump: I need to clean data e.g. parse type of some columns and add ...
0
votes
0answers
22 views

LOCK TABLE public.schema_migrations IN ACCESS SHARE MODE when executing pg_dump

I have a cron job that execute a backup on all databases on the sever. The pg_dump gets stuck in one database/table. Tracking the issue on pg_status_activity table I got: Message: LOCK TABLE public....
4
votes
1answer
238 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 ...
0
votes
1answer
39 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 ...
0
votes
1answer
25 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 ...
0
votes
1answer
815 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. ...
0
votes
1answer
190 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 ...
0
votes
1answer
203 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-...
0
votes
1answer
325 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 ...
0
votes
1answer
54 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/ ...
2
votes
1answer
58 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 ...
0
votes
2answers
81 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?
0
votes
1answer
371 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 ...
2
votes
1answer
65 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 ...
0
votes
0answers
229 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 ...
0
votes
1answer
137 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 ...
0
votes
1answer
110 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 ...
0
votes
0answers
33 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 ...
0
votes
2answers
35 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 ...
1
vote
1answer
318 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 ...
9
votes
1answer
10k views

How to Install postgresql-client-11 on Ubuntu 18.04

I need to use the pg_basebackup/pg_dump program on an Ubuntu 18.04 system to connect to a remote PostgreSQL 11.6 server. However, the current system only contains pg_basebackup 10.10 and the remote ...
0
votes
2answers
6k views

pg_restore: error: could not read from input file: end of file

I do try to create a dump of a table, that is stored inside of my database. The table is called webapp_product In order to create the dump, I do use following commands: docker-compose exec db1 ...
0
votes
1answer
842 views

Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is ...
0
votes
2answers
2k 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 ...
0
votes
1answer
125 views

pg_dump - table dependencies when using --table

The documentation says this when using -t table: Note When -t is specified, pg_dump makes no attempt to dump any other database objects that the selected table(s) might depend upon. Therefore, ...
0
votes
0answers
75 views

multiple pg_dump

My Postgres 9.3 cluster has 9 databases. I want to take backup of these databases using pg_dump utility of PostgreSQL 10. Since individual dbs are too large in size (~350GB), I want to run pg_dump ...
0
votes
3answers
1k views

Missing data for one schema in pg_dump

I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like pg_dump -h localhost -d db -U postgres -Fc -f file.dump When I restored a ...
0
votes
1answer
38 views

Using pg_dump to create 144 backups per day

Imagine the following scenario (this may not be a viable or sensible idea but please imagine that it is). Using Ubuntu, a cron job calls pg_dump and backs up a Postgres database every 10 minutes. In ...
0
votes
2answers
136 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 ...
1
vote
2answers
2k views

postgresql pg_dump out of memory

is it possible to limit somehow usage of resources for pg_dump? The problem is, whole DBase is 775GB and i have two very large tables: pg_largeobject table is – 390GB pg_largeobject statistics: ...
0
votes
1answer
275 views

How to dump only create table statements (or table definitions only) with pg_dump in Postgresql

I want only the table definitions, not wiews nor functions. I have a schema named "cvp". I try: pg_dump --schema-only -n cvp -t 'cvp.*' --format plain And I get: pg_dump: No matching tables ...
1
vote
1answer
486 views

pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible

I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL: heroku pg:backups:...
1
vote
1answer
814 views

Postgresql cannot restore partial pg_dump “relation ”public.my_table_seq“ does not exist”

In my database I dumped some tables via the following psql command: pg_dump -t my_table1 -t mytable2 > my_dump.sql And I try to import it into a postgresql docker image as a standalone database ...
0
votes
0answers
325 views

pg_dump not recognized, even though I've confirmed the binary is present

I'm trying to do a dump of a table in PostgreSQL via pgAdmin3. When I try to run the the following command: pg_dump -t schema.table_name database_name > chosen_save_name.sql it errors out and ...
-1
votes
1answer
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 "...