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 <em...@hitha.net> 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 <em...@hitha.net> 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.kla...@aklaver.com> >> 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.kla...@aklaver.com >>> >>
