Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [pg-dump]

Use this tag for questions concerning pg_dump, the PostgreSQL tool for logical backup (also known as database export or database dump). Please also use the [postgresql] tag.

Filter by
Sorted by
Tagged with
2 votes
1 answer
2k views

PG_DUMP on Replica DB server Error

I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it. I need to run ...
0 votes
1 answer
4k 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
2 answers
34 views

Using pgdump_all and cron

I have a daily cron job to dump my database. Part of the crontab includes the following: 01 01 * * * root /etc/cron.d/backupDaily.sh and part of the backup script contains the following: ...
1 vote
2 answers
777 views

Why isn't pg_restore --create working?

I backed up a database called app_data, on a 9.3 server, with this basic command: pg_dump -n public -F custom app_data > app_data.pg.dump Then I try to restore it on another server (running 9.4) ...
0 votes
1 answer
858 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 ...
0 votes
0 answers
18 views

Postgresql 16.2: ERROR: could not find hash function for hash operator 607

I've problem with my database running on postgresql 16.2. My application is running fine, it can query data, but I'm not able to create a backup using pg_dump or inspect database via psql. Running ...
1 vote
2 answers
3k views

Converting PostgreSQL dump file into CSV files

I have a PostgreSQL database running in a docker container. I used the below command to get a dump file of the Postgres database - "docker exec -t your-db-container pg_dumpall -c -U postgres > ...
0 votes
1 answer
1k 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
1 answer
324 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
2 answers
1k views

How can I use pg_dump to export a single table to CSV that can then be imported by Oracle SQL Loader?

Thanks in advance for any help on this. Problem Summary: I'm looking for the most efficient way possible to export a single table from Postgres/Greenplum for a large number of records (100M+) so that ...
1 vote
1 answer
569 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 ...
6 votes
1 answer
1k views

Postgresql: How to avoid encoding issues when copying a schema from one server to another?

I'm using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server: $ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema....
0 votes
1 answer
1k views

pg_dump can not create directory after increase directory space

I recently run pg_dump to backup database using this command using user postgres : time pg_dump -j 4 -Fd -Z 3 db_profile -f /backup-nfs/ Previously, the size of backup-nfs was 100 G. The pg_dump ...
3 votes
2 answers
544 views

Getting Postgres CREATE TABLE statements

I created some tables (9, to be exact) with a Django (1.9.6) migration and now I'm trying to get simple CREATE TABLE statements for them. I tried this answer, but using pg_dump in this way gives me ...
1 vote
1 answer
3k views

pg_dump issue while dumping single schema

pg_dump does refuse to dump a given schema in my DB. pg_dump -d <dbname> -s -n dm_geo -f <filename> Result -- -- PostgreSQL database dump -- -- Dumped from database version 9.5.4 -- ...
0 votes
1 answer
2k views

Error message from server: ERROR: invalid memory alloc request size

I am running with the below issue while taking pg_dump(PG14) of a table with bytea column. pg_dump: error: Error message from server: ERROR: invalid memory alloc request size 1460154641 The command ...
4 votes
2 answers
8k views

How to dump multiple databases of the same cluster in single execution?

I have 100 databases in my cluster. I need to backup 30 databases (as single file or multiple file, anything is fine). I could do it for tables using pg_dump with -t. Is there any way to do ...
1 vote
1 answer
958 views

"Unsupported version" error restoring a schema backed up with the same version it was dumped

I am trying to backup and restore a schema from a remote server to a local system. Even though pg_dump and pg_restore report the same version, the later fails with the familiar "Unsupported ...
1 vote
0 answers
137 views

Why is the data in a varchar field exceeding the size limit?

I have an application that is copying data from one PostgreSQL database to another. For the most part, that application works exactly as expected. However, there is a comments column in one of the ...
1 vote
2 answers
139 views

Migrating from PostgreSQL to MySQL (data with \r\n)

I'm trying to migrate a database from PostgreSQL to MySQL on an Ubuntu 23.04 system (latest version of both). So far, it looks like things are fine, except for one problem. In one of the character ...
0 votes
1 answer
208 views

PostgreSQL 16, pg_dumpall Restore

I'm trying to move a DB from a server running PostgreSQL 9.6 (CentOS) to another running PostgreSQL 16.1 (Ubuntu), I've used pg_dumpall --clean (from the 16.1 install) to output the DB's to a file ...
0 votes
1 answer
168 views

TablePlus: backup and restore DB with circular FK contraints/references

After a backup on TablePlus (documentation) with only the --format=custom option, I got the following: Backup database 'postgres' Dumping... warning: there are circular foreign-key constraints on this ...
1 vote
1 answer
32 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
1k 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 ...
0 votes
0 answers
51 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 ...
0 votes
0 answers
29 views

Enable syncing DB dump between tenants in a multi-tenant Postgres database

Our Java application stores data pertaining to multiple tenants in one single DB instance on AWS RDS. This setup is replicated across regions in prod. There are multiple schemas and each table within ...
1 vote
1 answer
1k 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 ...
4 votes
1 answer
1k views

Output from `pg_dump --schema-only` has inconsistent order

I'm trying to compare schemas between 2 (identical) databases using pg_dump --schema-only in PostgreSQL 9.3.9, but the diff of the output shows the ordering is inconsistent for two triggers: 2896c2896 ...
1 vote
1 answer
147 views

Move several tables from remote database to another

I have to move several tables from one remote database to another in PostgreSQL. From my understanding the only way of doing it is via pg_dump. pg_dump -h <source_db_host> -U <source_db_user&...
1 vote
3 answers
539 views

Why is NOT there "pg_get_tabledef" that is equivelant of "pg_get_indexdef" for tables in PostgreSQL?

I have searched the docs of PostgreSQL and Google, but I haven't found the function pg_get_tabledef, while pg_get_indexdef exists. There's no pg_get_tabledef? Maybe it's too difficult to be ...
0 votes
0 answers
83 views

pg_dump custom format

I wish I could experiment around pg_dump’s “custom” format. Is there any documentation available for this format? Or any library to help read/write such files?
0 votes
1 answer
472 views

Export functions together with tables from a postgres schema

I have a postgres schema which I would like to export a few tables from. To do this, I obviously used the --table flag in the pg_dump command. This works fine except for the part where I need to ...
0 votes
1 answer
86 views

pg_dump.exe compared to MS SQL backup

One of my clients recently migrated a CRM product from MS SQL to PostgreSQL. I have a question about how data backup works using pg_dump.exe As so far in MS SQL, when the backup started, the database ...
0 votes
0 answers
99 views

Why pg_dump.exe starts to generate so high utilization on C:\pagefile.sys?

We have some strange situation on one client machine. We have PostgreSQL 13 on Windows 10 (yeah do not blame me). Machine have 16GB RAM. When pg_dump.exe performs backup postgres.exe process uses disc ...
0 votes
0 answers
24 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 ...
4 votes
2 answers
14k views

What to look for in bad pg_dump log

We want to programmatically detect errors in cron scheduled pg_dumps. Apart from cheking whether or not the log file ends in "pg_dump: saving database definition": What other tell-tale string can I ...
0 votes
1 answer
989 views

I want to know how to resolve the version error when backing up postgresql

pg_dump: error: server version: 15.1; pg_dump version: 14.2 pg_dump: error: aborting because of server version mismatch The error occurs. I want to know how to adjust the dump version to 15.1. ...
0 votes
1 answer
1k views

Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database

We have one master table and multiple child tables (dependent tables) associated with it. While taking the backup of the master table , I would like to take the backup of all its child (dependent) ...
9 votes
2 answers
7k views

How to exclude tables when using pg_dumpall with postgresql?

If use pg_dump, it has a --exclude-table-data option to ignore special table. But pg_dumpall command doesn't has that option. So how to exclude several tables when dump all databases?
15 votes
4 answers
41k views

Restoring from pg_dump with foreign key constraints

In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example: ERROR: insert or update on table "channelproducts&...
35 votes
4 answers
50k views

how to track progress of a large postgres dump

Is there a way to see the progress of a pg_dump operation on a big db (ie > 1GB)? adding the -v option just dumps text on the screen, but doesn't give me much meaningful tracking information.
-1 votes
2 answers
2k views

What is the key to pg_restore?

We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore. We are ...
1 vote
1 answer
4k views

How can make PG_DUMP work without asking password?

I have set pgpassword in .bashrc: vim .bashrc export PGPASSWORD=pass_for_postgresql It is no need to type password for command psql -U postgres. When i dump a table from db1 into db2 with : pg_dump ...
0 votes
1 answer
1k 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 ...
1 vote
1 answer
4k views

pg_dump: error: no matching tables were found

Given the following Postgres container: docker run -i -p 54350:5432 --name test_postgres -e POSTGRES_PASSWORD=password -e POSTGRES_DB=test -e POSTGRES_SCHEMA=dba postgres:11 "-clog_statement=all&...
1 vote
0 answers
59 views

Postgres: copy from 2 different DBs for identical tables

I have two identical DBs, under the same port and host. (they both postgres DBs and on AWS) I want to create a script\write any query (even though it is not possible to create a query that use data ...
31 votes
4 answers
59k views

pg_restore: [archiver] did not find magic string in file header

I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump: sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name This command generates a ...
4 votes
1 answer
4k views

Why does pg_dump output not include 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 ...
1 vote
1 answer
1k 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 votes
1 answer
636 views

Copy between database using pg_dump and SSH

I want to copy a tabel from one server to another using pg_dump and SSH. Here is the script that I run from the source database : pg_dump -h 127.0.0.1 -Fc --dbname=postgresql://postgres:[email protected]....

1
2 3 4 5