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.
246 questions
0
votes
1
answer
9
views
Negative lookahead in table-matching pattern for psql or pg_dump
I'm trying to do a schema-only pg_dump of my database, however with data from one or two specific tables included. This means I can't use the --schema-only or --data-only options of pg_dump, and I can'...
- 604
0
votes
0
answers
41
views
Automatically transfer all data from postgres server to another remote server
I need to synchronize a test environment with production data (n8n worflows, user info, etc), both have postgres databases and reside on the same intranet
(e.g: server A: 10.0.0.10, server B: 10.0.0....
0
votes
1
answer
89
views
PostgresSQL - Slow restore performance on physical & much more powerful hardware
We've been tasked to restore one large database on daily basis for analytics purpose.
Here is something that we cannot change:
Vendor provided database dump file on daily basis on S3
We cannot ...
- 101
0
votes
2
answers
54
views
How to correctly dumpall a PostgreSQL cluster when in the newer version one extension is separated into 2 extensions
I have a PostgreSQL version 10 and a PostGIS extension. In this old version of PostgreSQL, the PostGIS has also support for raster. But in the newer versions of PostGIS, the raster support in a ...
- 101
0
votes
1
answer
103
views
Is pg_dump as acceptable as pg_dumpall for upgrading a database across major PostgreSQL versions?
I recently migrated a Postgres database from one server running Ubuntu 20.04 and Postgres 12 to another server running Ubuntu 24.04 and Postgres 16. To move the database from one server to the other, ...
- 151
4
votes
2
answers
326
views
Restore a full PostgreSQL server including passwords
I'm preparing a migration from one server to another. Actually, the same hardware but a full reinstall of a newer OS version. So everything including PostgreSQL needs to be reinstalled. But the user ...
- 303
0
votes
0
answers
2k
views
pg_restore: error: unsupported version (1.16) in file header
baran@heaven:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main....
- 133
0
votes
0
answers
73
views
PostgreSQL Dump with replace statement
have two PostgreSQL servers, A and B. Some tables from Server A need to be replicated or synced to Server B. If the data in these tables on Server A is modified, I want those updates to be reflected ...
- 133
2
votes
1
answer
538
views
PostgreSQL: pg_dump fails to correctly pg_restore due to missing or erroneous custom function, but present in dump
I am running PostgreSQL 13.15 in a local docker container, with matching pg_dump/pg_restore versions. When dumping and restoring the database, one of the custom functions that was previously present ...
- 78
0
votes
0
answers
141
views
pg_dump 16 hangs on "saving search_path" while using the option "--table-and-children"
I have this table which contains about 250 partitions, and each partition contains 4 sub partitions (so 1,000 partitions in total), and I'm trying to get the DDLs for those partitions.
Pg_dump version:...
- 145
0
votes
2
answers
170
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:
...
- 3,207
1
vote
1
answer
9k
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 ...
- 674
1
vote
0
answers
668
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 ...
- 455
0
votes
1
answer
427
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
538
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 ...
- 339