pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17863: Unable to restore dump 12.12 -> 15.2
12+ messages / 4 participants
[nested] [flat]
* BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-22 21:42 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 12+ messages in thread
From: PG Bug reporting form @ 2023-03-22 21:42 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: lizenko79@gmail.com
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-23 09:42 Daniel Gustafsson <daniel@yesql.se>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 12+ messages in thread
From: Daniel Gustafsson @ 2023-03-23 09:42 UTC (permalink / raw)
To: lizenko79@gmail.com, pgsql-bugs <pgsql-bugs@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-23 11:30 Andrey Lizenko <lizenko79@gmail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-23 11:30 UTC (permalink / raw)
To: daniel@yesql.se; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
(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.
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-23 11:31 Daniel Gustafsson <daniel@yesql.se>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Daniel Gustafsson @ 2023-03-23 11:31 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-23 11:37 Andrey Lizenko <lizenko79@gmail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 1 reply; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-23 11:37 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-24 06:50 Andrey Lizenko <lizenko79@gmail.com>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-24 06:50 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
>
> 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? 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-24 08:13 Daniel Gustafsson <daniel@yesql.se>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Daniel Gustafsson @ 2023-03-24 08:13 UTC (permalink / raw)
To: Andrey Lizenko <lizenko79@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-24 10:06 Andrey Lizenko <lizenko79@gmail.com>
parent: Daniel Gustafsson <daniel@yesql.se>
0 siblings, 0 replies; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-24 10:06 UTC (permalink / raw)
To: Daniel Gustafsson <daniel@yesql.se>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-24 21:27 Andres Freund <andres@anarazel.de>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 12+ messages in thread
From: Andres Freund @ 2023-03-24 21:27 UTC (permalink / raw)
To: lizenko79@gmail.com, pgsql-bugs@lists.postgresql.org, Daniel Gustafsson <daniel@yesql.se>
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-27 09:17 Andrey Lizenko <lizenko79@gmail.com>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-27 09:17 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-bugs@lists.postgresql.org, Daniel Gustafsson <daniel@yesql.se>
>
> 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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-27 14:36 Andrey Lizenko <lizenko79@gmail.com>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-27 14:36 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-bugs@lists.postgresql.org, Daniel Gustafsson <daniel@yesql.se>
Workaround in few steps:
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #17863: Unable to restore dump 12.12 -> 15.2
@ 2023-03-27 14:38 Andrey Lizenko <lizenko79@gmail.com>
parent: Andrey Lizenko <lizenko79@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Andrey Lizenko @ 2023-03-27 14:38 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: pgsql-bugs@lists.postgresql.org, Daniel Gustafsson <daniel@yesql.se>
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.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
>
--
Regards, Andrei Lizenko
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-03-27 14:38 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-22 21:42 BUG #17863: Unable to restore dump 12.12 -> 15.2 PG Bug reporting form <noreply@postgresql.org>
2023-03-23 09:42 ` Daniel Gustafsson <daniel@yesql.se>
2023-03-23 11:30 ` Andrey Lizenko <lizenko79@gmail.com>
2023-03-23 11:31 ` Daniel Gustafsson <daniel@yesql.se>
2023-03-23 11:37 ` Andrey Lizenko <lizenko79@gmail.com>
2023-03-24 06:50 ` Andrey Lizenko <lizenko79@gmail.com>
2023-03-24 08:13 ` Daniel Gustafsson <daniel@yesql.se>
2023-03-24 10:06 ` Andrey Lizenko <lizenko79@gmail.com>
2023-03-24 21:27 ` Andres Freund <andres@anarazel.de>
2023-03-27 09:17 ` Andrey Lizenko <lizenko79@gmail.com>
2023-03-27 14:36 ` Andrey Lizenko <lizenko79@gmail.com>
2023-03-27 14:38 ` Andrey Lizenko <lizenko79@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox