Re: pg_restore - generated column - not populating

[Santosh Udupi <email@xxxxxxxxx>]

If I backup using pgAdmin, I am able to restore using pg_restore but for some reason, pg_rsestore on the output from pg_dump does not create values for the generated columns

On Mon, Feb 22, 2021 at 6:20 PM Santosh Udupi <email@xxxxxxxxx> wrote:

The logs don't show errors. I came across something similar here https://www.postgresql-archive.org/Dumping-restoring-fails-on-inherited-generated-column-td6114378.html 

but not sure what the solution is.

On Mon, Feb 22, 2021 at 5:57 PM Santosh Udupi <email@xxxxxxxxx> wrote:

I used the following commands for dump

pg_dump -c mydb | gzip -9 > mydb.gz

pg_dump -C -Fc mydb > mydb.backup

pg_dump -Ft mydb > mydb.tar

For restore, I created a blank database by issuing the command "createdb mydb" and then tried

gunzip -c mydb.gz | psql mydb

pg_restore -d mydb mydb.backup

pg_restore -Ft -d mydb mydb.backup 

I have tried -c, -C, schema only etc but nothing has worked so far.

I didn't check the Postgres logs. Thanks for the suggestion. I will check that.

On Mon, Feb 22, 2021 at 5:16 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 2/22/21 5:08 PM, Santosh Udupi wrote:
> Hi all,
>
> My database has tables with generated columns. I altered a table and
> added a generated column as below:
>
>     alter table billing add primary_bill_to_id int   GENERATED ALWAYS as
>     ((info->>'vp')::int) stored
>
>
> Now, when I do the pg_dump and pg_restore, this column does not get
> populated. It remains null
>
> "Info" is the jsonb column in the table and info->>'vp' has values in
> multiple rows but still generated column "primary_bill_to_id" is null
> after the restore
>
> I am using postgres version 13
>
> Can you tell me what am I missing?

Without the commands you used to do the dump and restore it will be
difficult to come to any conclusions. It would also be helpful to look
at the Postgres logs from the restore to see if there are any error
messages.

>
> Thank you for your help.
> Santosh
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx