PostgreSQL 15 Restore Failing Due to Incorrect UUID Handling in Dump

Question

I'm facing an issue while restoring a PostgreSQL 15 database dump. During the restore process, I encounter the following errors:

ERROR: trailing junk after numeric literal at or near "..."
ERROR: invalid command \N
ERROR: invalid command \N
.
.
.
ERROR: out of memory

The error occurs after processing the UUIDs in the dump file. It seems that the UUIDs are being interpreted incorrectly, possibly as numeric literals, which causes subsequent issues with invalid commands like \N, leading to an out of memory error.

Here is the command I'm using to create the dump:

pg_dump -h <host> -p 5432 -U <username> <dbname> > new.dump

And the restore command:

psql postgresql://<user>:<password>@<host>/<dbname> < new.dump

Could anyone advise on the best way to create a dump file that correctly handles UUIDs during the restore process to avoid these issues? Specifically, I want to ensure UUIDs are treated as UUIDs and not interpreted as numeric literals, which leads to the invalid commands and out of memory error.

Any help or best practices would be greatly appreciated!

Thanks!

Additional info:

I am restoring to a PostgreSQL 15 instance.

OS and version: Debian 12

pg_dump -V returns :

pg_dump (Postgresql) 16.2

• Laurenz Albe: I doubt that that is the original error. Try to identify the first error message you are getting during the restore. What you see may be a consequence.
• Adrian Klaver: 1) Are you restoring to Postgres 15 instance? 2) What does pg_dump -V return? 3) What OS and version are you doing this on? Add information as text update to question text
• Dunes: \N is the default representation of NULL in pg_dump output. In and of itself it should be valid. You'll often see it reported as an error in an INSERT, when the appropriate table was not created properly. To help you debug, add these to your psql command psql -v ON_ERROR_STOP=1 -b. This will make the script stop as soon as it hits an error and print out the erroneous command. There is a chance the real error may have happened before that, but it's a good starting point. You may wish to update your question with the output when using the new psql command options.
• mustaccio: Sounds to me like an unmatched quote somewhere.
• Luna: I was able to fix the issue. it turned out that a column was missing in the database. After reviewing the error output carefully, I noticed that the column mentioned in the error didn't exist. To fix it, I manually created the missing column, and after that, the import worked successfully.

Answer 1

I figured it out! After looking closely at the error message, I realized that a column was missing in the database. Once I manually added the missing column, everything worked fine.