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.
245 questions
0 votes
1 answer
18 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
1 answer
54 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....
1 vote
1 answer
105 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 ...
- 111
0 votes
2 answers
57 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
118 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
351 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
2 votes
1 answer
547 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
144 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
171 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
676 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
433 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
543 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
1 vote
2 answers
342 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