Yes, this is what we have been doing now:- Backup using pg_dump, create the new database at the destination, manually create the tables which give problems, and then do the pg_restore. Another solution for us is to backup using pg_dump that comes with pgAdmin (Windows), rsync it to the destination server and then do the pg_restore on the server.
On Tue, Feb 23, 2021 at 3:21 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 2/23/21 12:57 PM, Santosh Udupi wrote:
> So that it makes it a lot easier for the application logic just to
> collect json fields and update in one column "info" instead of including
> multiple columns in the insert/update statements.
I doubt it, but then again this why I don't answer Postgres/JSON SO
questions anymore. Trying to apply logic to the contortions people go to
make their life more difficult left me with headaches. At any rate this
is getting off-topic for the the dump/restore issue you have. When you
look at the restored tables in each variation of the restore database do
they look the same as below? If you drop the table in the problem
database and then recreate it using the script below and then populate
it with data does it work?
>
> On Tue, Feb 23, 2021 at 12:20 PM Adrian Klaver
> <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
>
> On 2/23/21 12:15 PM, Santosh Udupi wrote:
> > Here is my table structure. I will try to get the pg_dump output for
> > this table in both the versions.
> >
> > create table tbl_main(
> >
> > item_id int GENERATED ALWAYS AS IDENTITY,
> > -----------------------------------------------------
> > operating_offices int [] GENERATED ALWAYS AS (
> > nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
> > -----------------------------------------------------
> > primary_bill_to_id int GENERATED ALWAYS as ((info->>'vp')::int)
> > stored ,
> > ----------------------------------------------
> > item_status_array text [] GENERATED ALWAYS as ( array[
> > coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
> > -------------------------------------------------
> > info jsonb
> > ------------------------------
> > ,is_complete bool GENERATED ALWAYS as (coalesce(
> > (info->>'lf')::bool = true or (info->>'lg')::bool = true, false))
> > stored
> > --------------------------------------------
> > ,is_deleted bool GENERATED ALWAYS as ( coalesce(
> > (info->>'cv')::bool, false) ) stored
> > ------------------------------
> > ,is_a_template bool GENERATED ALWAYS as ( coalesce(
> > (info->>'cw')::bool, false) ) stored
> > -------------------------------------------
> > ,created_by_user_id int
> > ,created_on timestamptz default now()
> > ----------------------------------
> > ,primary key(item_id,created_on )
> >
> >
> > ) partition by range (created_on) ;
>
> Which generates(pun intended) the question, why? You are deconstructing
> info into its component parts after the fact, why not just input the
> data directly into the fields.
>
> >
> > ---=================================================================
> > -- *** index
> >
> > CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);
> >
> >
> > ---=================================================================
> > -- **** partitions
> >
> > -- default partition
> > create table tbl_main_partition_default
> > partition of tbl_main default;
> >
> > create table tbl_main_partition_2021
> > partition of tbl_main
> > for values from ('2020-01-01') to ('2022-01-01');
> >
> > create table tbl_main_partition_2022
> > partition of tbl_main
> > for values from ('2022-01-01') to ('2023-01-01');
> >
> > create table tbl_main_partition_2023
> > partition of tbl_main
> > for values from ('2023-01-01') to ('2024-01-01');
> >
> > ---=================================================================
> >
> >
> >
> > On Tue, Feb 23, 2021 at 10:40 AM Tom Lane <tgl@xxxxxxxxxxxxx
> <mailto:tgl@xxxxxxxxxxxxx>
> > <mailto:tgl@xxxxxxxxxxxxx <mailto:tgl@xxxxxxxxxxxxx>>> wrote:
> >
> > Santosh Udupi <email@xxxxxxxxx <mailto:email@xxxxxxxxx>
> <mailto:email@xxxxxxxxx <mailto:email@xxxxxxxxx>>> writes:
> > > Both are different versions
> > > The following works:
> > > Version: pg_dump (pgAdmin Windows) version:13.1
> >
> > > The following does not work: (Does not populate the
> generated column
> > > values)
> > > Version: pg_dump (PostgreSQL v13.2 on Ubuntu 20.04)
> version 13.2
> >
> > Hmm ... well, that would be a regression, but you're going to
> have
> > to show us how to reproduce it. I'm thinking there must be
> something
> > odd about the way the table is declared.
> >
> > regards, tom lane
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
