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
0 votes
1 answer
21 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 ...
AStaUK's user avatar
  • 11
0 votes
1 answer
39 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 ...
CPHPython's user avatar
  • 329
1 vote
1 answer
48 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 ...
Ray's user avatar
  • 113
1 vote
1 answer
22 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 $...
Vasileios Giannakidis's user avatar
0 votes
1 answer
232 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 ...
toraritte's user avatar
  • 111
0 votes
1 answer
237 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?
datascinalyst's user avatar
0 votes
0 answers
28 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 ...
Roberto's user avatar
  • 142
0 votes
0 answers
22 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 ...
deGee's user avatar
  • 101
1 vote
1 answer
58 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&...
YIF99's user avatar
  • 13
0 votes
0 answers
51 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?
user2233709's user avatar
0 votes
1 answer
756 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 ...
Sajith P Shetty's user avatar
0 votes
1 answer
167 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 ...
emmaakachukwu's user avatar
1 vote
3 answers
231 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 ...
Leon's user avatar
  • 315
0 votes
1 answer
51 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 ...
Michał Lipok's user avatar
0 votes
0 answers
84 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 ...
Michał Lipok's user avatar
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 ...
sebeid's user avatar
  • 1,381
0 votes
1 answer
2k 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 ...
showkey's user avatar
  • 284
1 vote
1 answer
2k 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&...
Philippe Hebert's user avatar
0 votes
0 answers
183 views

pg_dump error: connexion to database « dbdev » failed : fe_sendauth: no password supplied

I am trying to use pg_dump command in a container without entering the password. I firstly checked pg_dump with password entered in promp, when I do pg_dump -Fp --host my_hostname --port 32045 --...
jos97's user avatar
  • 101
1 vote
0 answers
54 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 ...
Dor Cohen's user avatar
0 votes
1 answer
758 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. ...
user avatar
0 votes
0 answers
332 views

Copy a partition from one database to another in postgres

I have a range (day wise) partitioned table managed using pg_partman extension and replica of the same is created in a different database. I want to copy a partition specific of that day into another ...
Akil Vohra's user avatar
0 votes
0 answers
350 views

Refresh materialized views upon database restore

Our database (PG 12) uses a few materialized views, some depending on others. We use pg_dump for backups and psql to restore. And, we do this restore every once in a while, for example when upgrading ...
vektor's user avatar
  • 371
0 votes
0 answers
46 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 ...
Mukul Gupta's user avatar
0 votes
0 answers
153 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', ...
user63764's user avatar
1 vote
0 answers
223 views

Cannot access pg_largeobject table although I am the lomowner

I am looking for the best way on how to export data from the pg_largeobject table. I don't really understand what's the best way. I don't have access to the superuser, thus a pg_dump doesn't seem to ...
Chris's user avatar
  • 11
1 vote
1 answer
2k views

postgres pg_dump + .pgpass file : no password supplied

per PSQL docs I created a .pgpass file on macOS with 0600 mask. a.b.com:32109:mydb:user1:password1 when I list the file: $ ls -la .pgpass -rw------- 1 user staff 212 Dec 21 12:56 .pgpass export ...
taitelman's user avatar
  • 121
0 votes
1 answer
1k views

pg_dump output formats

I'm trying to dump a database and restore it. Running on windows 10, PostgreSQL 15. I cannot wrap my head around the following: This command creates a file that I cannot restore through pgadmin (...
Mixel's user avatar
  • 3
-1 votes
1 answer
429 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]....
padjee's user avatar
  • 167
0 votes
0 answers
3k 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 "...
padjee's user avatar
  • 167
0 votes
1 answer
291 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 ...
RonJohn's user avatar
  • 605
1 vote
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 ...
zigmoo's user avatar
  • 11
0 votes
0 answers
259 views

pg_dump taking lot of time for tables with TOAST entries

We are using Azure Postgresql Single Server(Postgresql on Windows). Some particular tables use TOAST as it stores an XML blob. We are seeing pg_dump take a lot of time (~8 hours) to export few ...
Mukul Gupta's user avatar
0 votes
1 answer
907 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 ...
padjee's user avatar
  • 167
0 votes
1 answer
421 views

pg_dump dumping files to ROOT

I run pg_dump last nite to backup some 111Gb database : time pg_dump -j 5 -Fd -Z 1 db_profans -f /backup-nfs/ I monitor the size growth But, suddenly this morning, the database is down. I check the ...
padjee's user avatar
  • 167
0 votes
2 answers
735 views

postgresql error during pg_dump invalid value for parameter "default_tablespace": "tbs_db1_index"

Can someone help me please? :) Ive 2 servers, want to split databases in exclusive instances, so im migrating my databases 1 by 1 to second server Postgres version 11.16 both servers so here's my ...
Jan's user avatar
  • 5
0 votes
0 answers
1k views

pg_dump permission denied for relation error for owner user

I am attempting to dump out a PostgreSQL database with pg_dump: /usr/bin/pg_dump -f /home/crmpicco/rfc_754622.sql -F c -x -E UTF8 -b --disable-triggers -U root -h tillman.ff1lvb.us-west-2.rds....
crmpicco's user avatar
  • 145
-1 votes
1 answer
1k 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 ...
sheckwes's user avatar
-1 votes
1 answer
233 views

how to back-up all databases in postgresql iteratively?

I have a large number of databases in postgresql 9.5 which I want to back-up using pg_dump tool. Each back-up should get its name from the corresponding database. when I run SELECT datname FROM ...
Brian's user avatar
  • 3
0 votes
0 answers
324 views

pg_dump Isn't Reading ~/.pgpass

I'm trying to bash script a daily backup of my data using pg_dump. I have a single entry in my ~/.pgpass file, which is properly structured, proper host, port, database, user and password. pg_dump -O ...
Rich_F's user avatar
  • 131
0 votes
2 answers
2k 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 > ...
sreejan patel's user avatar
1 vote
0 answers
665 views

backup a single user , password and privileges from postgresql

I am moving a database user form one postgresql server . I need to take backup of the user name , password and privileges and restore into another pgsql server. I looked into the documentation and ...
sherin's user avatar
  • 111
1 vote
1 answer
482 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 ...
Nicole Staline's user avatar
-2 votes
1 answer
3k 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: ...
Miguel Guzmán 's user avatar
1 vote
1 answer
980 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 ...
Francisco Villegas's user avatar
0 votes
1 answer
688 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 ...
Sanyifejű's user avatar
0 votes
1 answer
721 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 ...
pbm's user avatar
  • 103
2 votes
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). ...
Leon's user avatar
  • 315
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 ...
scrollout's user avatar
  • 103
3 votes
1 answer
4k 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 (...
Leonardo Petrucci's user avatar

1
2 3 4 5