Thread: BUG #17863: Unable to restore dump 12.12 -> 15.2
The following bug has been logged on the website:
Bug reference: 17863
Logged by: Andrey Lizenko
Email address: lizenko79@gmail.com
PostgreSQL version: 15.2
Operating system: Ubuntu 20.04.5 LTS
Description:
Please close, if already reported or fixed. But its too many release notes,
to check everything and, on the other hand, I suppose, that dumps might be
compatible.
12.12
pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name>
15.2
pg_restore -U <user_name> -h 127.0.0.1 -v -d ./<db_name>_20230322.dmp
In short:
pg_restore: processing data for table "<db_name>.consensus_estimate"
pg_restore: error: could not uncompress data: (null)
No other errors in logs.
Here is the table:
<db_name>=# \d+ <db_name>.consensus_estimate
Table
"<db_name>.consensus_estimate"
Column | Type | Collation | Nullable |
Default | Storage | Stats target |
Description
---------------------+------------------+-----------+----------+--------------------------------------------------------+----------+--------------+-------------
event_time | bigint | | |
| plain | |
<field_name> | text | | not null |
| extended | |
source | text | | |
| extended | |
disabled | boolean | | |
| plain | |
ext | jsonb | | |
| extended | |
sid | text | | |
| extended | |
period | text | | |
| extended | |
currency_code | text | | |
| extended | |
ymd | integer | | |
| plain | |
reference_period | text | | |
| extended | |
adjusted_eps_mean | double precision | | |
| plain | |
adjusted_eps_median | double precision | | |
| plain | |
reported_eps_mean | double precision | | |
| plain | |
reported_eps_median | double precision | | |
| plain | |
id | integer | | not null |
nextval('<db_name>.consensus_estimate_id_seq'::regclass) | plain |
|
ext_hash | uuid | | |
| plain | |
Indexes:
"idx_consensus_estimate_unique" UNIQUE, btree (<field_name>, ymd,
ext_hash)
Access method: heap
> On 22 Mar 2023, at 22:42, PG Bug reporting form <noreply@postgresql.org> wrote: > pg_restore: processing data for table "<db_name>.consensus_estimate" > pg_restore: error: could not uncompress data: (null) This error indicates, as it clearly states, a failure to decompress the compressed file, but it's a bit worrying that the error is (null) since this really should report an error. Handling an empty ->msg seems like something we maybe should do. Does dumping/restoring any database in your cluster work? Is it just this one that fails? -- Daniel Gustafsson
(copying here as it was a misclick about "reply all" button)
I'll try to, but even plain text dump didn't work
I'll try to, but even plain text dump didn't work
psql:<db_name>_20230323.sql:5672327: ERROR: extra data after last expected column
CONTEXT: COPY alpha_beta, line 3828998: "1643415437295 SRSR MORN1661692859461 NODK MORNINGSTAR f {"fiscalYearEndMonth": "12"} 0P00019YC1 0C00..."
I can check md5sum if there is an idea about network issue.
On Thu, 23 Mar 2023 at 10:43, Daniel Gustafsson <daniel@yesql.se> wrote:
> On 22 Mar 2023, at 22:42, PG Bug reporting form <noreply@postgresql.org> wrote:
> pg_restore: processing data for table "<db_name>.consensus_estimate"
> pg_restore: error: could not uncompress data: (null)
This error indicates, as it clearly states, a failure to decompress the
compressed file, but it's a bit worrying that the error is (null) since this
really should report an error. Handling an empty ->msg seems like something we
maybe should do.
Does dumping/restoring any database in your cluster work? Is it just this one
that fails?
--
Daniel Gustafsson
Regards, Andrei Lizenko
> On 23 Mar 2023, at 12:30, Andrey Lizenko <lizenko79@gmail.com> wrote:
>
> (copying here as it was a misclick about "reply all" button)
>
> I'll try to, but even plain text dump didn't work
>
> psql:<db_name>_20230323.sql:5672327: ERROR: extra data after last expected column
> CONTEXT: COPY alpha_beta, line 3828998: "1643415437295 SRSR MORN1661692859461 NODK MORNINGSTAR f
{"fiscalYearEndMonth": "12"} 0P00019YC1 0C00..."
>
> I can check md5sum if there is an idea about network issue.
Are you able to generate a small reproducible test case with a schema like
yours and a row of data which fails like this?
--
Daniel Gustafsson
will try to, it would take some time. But may be we have two problems here.
On Thu, 23 Mar 2023 at 12:32, Daniel Gustafsson <daniel@yesql.se> wrote:
> On 23 Mar 2023, at 12:30, Andrey Lizenko <lizenko79@gmail.com> wrote:
>
> (copying here as it was a misclick about "reply all" button)
>
> I'll try to, but even plain text dump didn't work
>
> psql:<db_name>_20230323.sql:5672327: ERROR: extra data after last expected column
> CONTEXT: COPY alpha_beta, line 3828998: "1643415437295 SRSR MORN1661692859461 NODK MORNINGSTAR f {"fiscalYearEndMonth": "12"} 0P00019YC1 0C00..."
>
> I can check md5sum if there is an idea about network issue.
Are you able to generate a small reproducible test case with a schema like
yours and a row of data which fails like this?
--
Daniel Gustafsson
Regards, Andrei Lizenko
small reproducible test caseIt is so only on large table(s) with sizes at least more, than 1262 MB. I think it is about amount of data, not about data itself.
Like this
psql:alizenko.sql:3108625: ERROR: extra data after last expected column
CONTEXT: COPY <table_name2>, line 2424915: "1643415437295 KYOCY MORNINGSTAR f {"rSquare": 38.657029, "nonDivBeta": 0.9944, "nonDivAlpha": 0.0042..."
Any ideas how to debug? GDB? Or I may try to obfuscate my example and share it somehow.
On Thu, 23 Mar 2023 at 12:37, Andrey Lizenko <lizenko79@gmail.com> wrote:
will try to, it would take some time. But may be we have two problems here.On Thu, 23 Mar 2023 at 12:32, Daniel Gustafsson <daniel@yesql.se> wrote:> On 23 Mar 2023, at 12:30, Andrey Lizenko <lizenko79@gmail.com> wrote:
>
> (copying here as it was a misclick about "reply all" button)
>
> I'll try to, but even plain text dump didn't work
>
> psql:<db_name>_20230323.sql:5672327: ERROR: extra data after last expected column
> CONTEXT: COPY alpha_beta, line 3828998: "1643415437295 SRSR MORN1661692859461 NODK MORNINGSTAR f {"fiscalYearEndMonth": "12"} 0P00019YC1 0C00..."
>
> I can check md5sum if there is an idea about network issue.
Are you able to generate a small reproducible test case with a schema like
yours and a row of data which fails like this?
--
Daniel Gustafsson--Regards, Andrei Lizenko
Regards, Andrei Lizenko
> On 24 Mar 2023, at 07:50, Andrey Lizenko <lizenko79@gmail.com> wrote:
>
> small reproducible test case
> It is so only on large table(s) with sizes at least more, than 1262 MB. I think it is about amount of data, not about
dataitself.
> Like this
> psql:alizenko.sql:3108625: ERROR: extra data after last expected column
> CONTEXT: COPY <table_name2>, line 2424915: "1643415437295 KYOCY MORNINGSTAR f {"rSquare": 38.657029,
"nonDivBeta":0.9944, "nonDivAlpha": 0.0042..."
>
> Any ideas how to debug?
Does the indicated line in the dumped file indeed have too many columns, or
does it contain any value which is incorrectly quoted and thus tricks the
parser into believing so? Is it the last line of the COPY into this table?
> Or I may try to obfuscate my example and share it somehow.
Sharing a 1Gb repro is cumbersome, maybe you can create a script which
generates data which can reproduce the problem?
--
Daniel Gustafsson
Does the indicated line in the dumped file indeed have too many columns
will check
it was generated by pg_dump -Fp ... as an ordinary sql file
but, may be makes sense - was trying to restore by this:
pg_dump -V
pg_dump (PostgreSQL) 12.12 (Ubuntu 12.12-0ubuntu0.20.04.1)
psql -V
psql (PostgreSQL) 13.3
Is it the last line of the COPY into this table?
Answer is above. I'm still trying to reproduce in the correct short way. At the moment I don't understand, where is the problem - it is happening in random places.
On Fri, 24 Mar 2023 at 09:13, Daniel Gustafsson <daniel@yesql.se> wrote:
> On 24 Mar 2023, at 07:50, Andrey Lizenko <lizenko79@gmail.com> wrote:
>
> small reproducible test case
> It is so only on large table(s) with sizes at least more, than 1262 MB. I think it is about amount of data, not about data itself.
> Like this
> psql:alizenko.sql:3108625: ERROR: extra data after last expected column
> CONTEXT: COPY <table_name2>, line 2424915: "1643415437295 KYOCY MORNINGSTAR f {"rSquare": 38.657029, "nonDivBeta": 0.9944, "nonDivAlpha": 0.0042..."
>
> Any ideas how to debug?
Does the indicated line in the dumped file indeed have too many columns, or
does it contain any value which is incorrectly quoted and thus tricks the
parser into believing so? Is it the last line of the COPY into this table?
> Or I may try to obfuscate my example and share it somehow.
Sharing a 1Gb repro is cumbersome, maybe you can create a script which
generates data which can reproduce the problem?
--
Daniel Gustafsson
Regards, Andrei Lizenko
Hi, On 2023-03-22 21:42:56 +0000, PG Bug reporting form wrote: > 12.12 > pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name> > > 15.2 > pg_restore -U <user_name> -h 127.0.0.1 -v -d ./<db_name>_20230322.dmp > > In short: > pg_restore: processing data for table "<db_name>.consensus_estimate" > pg_restore: error: could not uncompress data: (null) If you use 12.12's pg_restore to convert the .dmp file to a text file (using the --file=somefile, instead of -h/-d), does that work? Greetings, Andres Freund
If you use 12.12's pg_restore to convert the .dmp file to a text file (using
the --file=somefile, instead of -h/-d), does that work?
(just to be sure, that it is not a typo)
The idea is to make a dump then start pg_restore in 12.12 to itself?
The idea is to make a dump then start pg_restore in 12.12 to itself?
On Fri, 24 Mar 2023 at 22:27, Andres Freund <andres@anarazel.de> wrote:
Hi,
On 2023-03-22 21:42:56 +0000, PG Bug reporting form wrote:
> 12.12
> pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name>
>
> 15.2
> pg_restore -U <user_name> -h 127.0.0.1 -v -d ./<db_name>_20230322.dmp
>
> In short:
> pg_restore: processing data for table "<db_name>.consensus_estimate"
> pg_restore: error: could not uncompress data: (null)
If you use 12.12's pg_restore to convert the .dmp file to a text file (using
the --file=somefile, instead of -h/-d), does that work?
Greetings,
Andres Freund
Regards, Andrei Lizenko
Workaround in few steps:
4. 15.2
1. 12.12
pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name>
2. 12.12
pg_restore --file=<db_name>_20230322.sql -vOx <db_name>_20230322.dmp
3. 15.2
postgres@nerva:~$ psql
psql (15.2 (Ubuntu 15.2-1.pgdg20.04+1))
Type "help" for help.
postgres=# \c <db_name>
You are now connected to database " <db_name>" as user "postgres".
4. 15.2
\i <db_name>_20230323.sql
On Mon, 27 Mar 2023 at 11:17, Andrey Lizenko <lizenko79@gmail.com> wrote:
If you use 12.12's pg_restore to convert the .dmp file to a text file (using
the --file=somefile, instead of -h/-d), does that work?(just to be sure, that it is not a typo)
The idea is to make a dump then start pg_restore in 12.12 to itself?On Fri, 24 Mar 2023 at 22:27, Andres Freund <andres@anarazel.de> wrote:Hi,
On 2023-03-22 21:42:56 +0000, PG Bug reporting form wrote:
> 12.12
> pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name>
>
> 15.2
> pg_restore -U <user_name> -h 127.0.0.1 -v -d ./<db_name>_20230322.dmp
>
> In short:
> pg_restore: processing data for table "<db_name>.consensus_estimate"
> pg_restore: error: could not uncompress data: (null)
If you use 12.12's pg_restore to convert the .dmp file to a text file (using
the --file=somefile, instead of -h/-d), does that work?
Greetings,
Andres Freund--Regards, Andrei Lizenko
Regards, Andrei Lizenko
Sorry, have a typo here - files from №2 and №4 are the same
On Mon, 27 Mar 2023 at 16:36, Andrey Lizenko <lizenko79@gmail.com> wrote:
Workaround in few steps:1. 12.12pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name>2. 12.12pg_restore --file=<db_name>_20230322.sql -vOx <db_name>_20230322.dmp3. 15.2postgres@nerva:~$ psql
psql (15.2 (Ubuntu 15.2-1.pgdg20.04+1))
Type "help" for help.
postgres=# \c <db_name>
You are now connected to database " <db_name>" as user "postgres".
4. 15.2\i <db_name>_20230323.sqlOn Mon, 27 Mar 2023 at 11:17, Andrey Lizenko <lizenko79@gmail.com> wrote:If you use 12.12's pg_restore to convert the .dmp file to a text file (using
the --file=somefile, instead of -h/-d), does that work?(just to be sure, that it is not a typo)
The idea is to make a dump then start pg_restore in 12.12 to itself?On Fri, 24 Mar 2023 at 22:27, Andres Freund <andres@anarazel.de> wrote:Hi,
On 2023-03-22 21:42:56 +0000, PG Bug reporting form wrote:
> 12.12
> pg_dump -Fc -Z 9 -v -f ./<db_name>_20230322.dmp <db_name>
>
> 15.2
> pg_restore -U <user_name> -h 127.0.0.1 -v -d ./<db_name>_20230322.dmp
>
> In short:
> pg_restore: processing data for table "<db_name>.consensus_estimate"
> pg_restore: error: could not uncompress data: (null)
If you use 12.12's pg_restore to convert the .dmp file to a text file (using
the --file=somefile, instead of -h/-d), does that work?
Greetings,
Andres Freund--Regards, Andrei Lizenko--Regards, Andrei Lizenko
Regards, Andrei Lizenko