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.
242
questions
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 ...
- 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 ...
- 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 ...
- 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 $...
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 ...
- 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?
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 ...
- 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 ...
- 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&...
- 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?
- 193
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 ...
- 154
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 ...
- 103
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 ...
- 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 ...
- 119
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 ...
- 119
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 ...
- 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 ...
- 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&...
- 113
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 --...
- 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 ...
- 31
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.
...
cyanogen
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 ...
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 ...
- 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 ...
- 125
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', ...
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 ...
- 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 ...
- 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 (...
- 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]....
- 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 "...
- 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 ...
- 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 ...
- 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 ...
- 125
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 ...
- 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 ...
- 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 ...
- 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....
- 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 ...
- 1
-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 ...
- 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 ...
- 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 > ...
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 ...
- 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 ...
- 121
-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: ...
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 ...
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 ...
- 101
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 ...
- 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). ...
- 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 ...
- 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 (...
