0

I have a .bak file (74GB) containing all my database webapp from Postgres 12.1 generated via the following .bash cronjob:

#!/bin/bash
backupfolder=”/home/backups/database/postgresql"
day=$(date+%Y%m%d)
su postgres −c ”pg_dump webapp > $backupfolder/$day.bak”

The version of pg_dump is 10.19. Today when I tried to restore the database to the same version of postgres (12.1)

psql webbackup < /home/backups/database/postgresql/20220111.bak

with another name for testing purposes, it was all going well until this message shows up:

...
COPY 20997
COPY 3217
ERROR:  missing data for column "fecha"
CONTEXT:  COPY valores, line 106982440: "17779705"

As you can see, the copy of the other tables went okay, but then it comes to this error with the table "valores" and the process just shut down.

if I check the versions I get:

$ pg_dump -V
pg_dump (PostgreSQL) 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
$ pg_restore -V
pg_restore (PostgreSQL) 10.19 (Ubuntu 10.19-0ubuntu0.18.04.1)
$ psql -V
psql (PostgreSQL) 12.1 (Ubuntu 12.1-1.pgdg18.04+1)

Why is this happening? Is there any way to read the backup file to find the error?

CarlosCarraPer
  • 1
  • 2
  • 1
    This needs more information: 1) How was the backup done? In other words show the complete command used to produce it. 2) What version of Postgres was being backed up? What version being restored to? 3) How was the backup restored? Again the complete command 4) What versions of the commands where used? Add answers as update to your question. – Adrian Klaver Jan 12 '22 at 16:44
  • Edited. Hope it clarifies the problem. – CarlosCarraPer Jan 13 '22 at 12:26
  • Not really. You have not included which version of Postgres was being dumped from and which version was being restored to? – Adrian Klaver Jan 13 '22 at 16:20
  • There needs to a complete accounting of what you did, for instance: I dumped database name `some_name` from a X.x version of Postgres using this command and restored to a X.x version of Postgres using this command, etc. Also this: `psql webapp < ...` does not match up with 'Today when I tried to restore the database with another name...'. The command would restore with the same name. – Adrian Klaver Jan 13 '22 at 16:28
  • I did the edits. – CarlosCarraPer Jan 17 '22 at 08:42
  • You want to use the most recent version of `pg_dump` (12.1 in this case) to take the dump of the database. – Adrian Klaver Jan 17 '22 at 16:09
  • Should have added Postgres version 12 is at 12.9 and it would be a good idea to upgrade to that. – Adrian Klaver Jan 17 '22 at 16:21

0 Answers0