Thread: pg_dump empty tables
Hi all,
We are using postgresql 11.7 on Debian.
I noticed that pg_dump is not including records on tables with fields defined as array type (integer[] and real[]). The table structure is normally restored but they have 0 records on restoring.
i'm wondering if is it a normal behaviour of pg_dump and how should I execute it to include data on that tables.
Any hint would be appreciated. Thanks in advance and sorry for my english
--
edugarg
On 6/23/20 6:30 AM, Edu Gargiulo wrote: > Hi all, > > We are using postgresql 11.7 on Debian. > I noticed that pg_dump is not including records on tables with fields > defined as array type (integer[] and real[]). The table structure is > normally restored but they have 0 records on restoring. What is the complete command you are using when running pg_dump? What is the schema for one of the tables? e.g \dt table_name What is does a SELECT on those fields show? > > i'm wondering if is it a normal behaviour of pg_dump and how should I > execute it to include data on that tables. > > Any hint would be appreciated. Thanks in advance and sorry for my english > > -- > edugarg -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/23/20 6:30 AM, Edu Gargiulo wrote:
> Hi all,
>
> We are using postgresql 11.7 on Debian.
> I noticed that pg_dump is not including records on tables with fields
> defined as array type (integer[] and real[]). The table structure is
> normally restored but they have 0 records on restoring.
What is the complete command you are using when running pg_dump?
/usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp
What is the schema for one of the tables? e.g \dt table_name
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
well | surface_card | table | historic
(1 row)
historic=# \d well.surface_card
Table "well.surface_card"
Column | Type | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
id | bigint | | not null |
tstamp | timestamp with time zone | | not null |
card_tstamp | timestamp with time zone | | not null |
shutdown_event_id | smallint | | not null |
quality | boolean | | not null |
load_min | integer | | not null |
load_max | integer | | not null |
stroke_length | real | | not null |
stroke_period | real | | not null |
positions | real[] | | not null |
loads | integer[] | | not null |
Table "well.surface_card"
Column | Type | Collation | Nullable | Default
-------------------+--------------------------+-----------+----------+---------
id | bigint | | not null |
tstamp | timestamp with time zone | | not null |
card_tstamp | timestamp with time zone | | not null |
shutdown_event_id | smallint | | not null |
quality | boolean | | not null |
load_min | integer | | not null |
load_max | integer | | not null |
stroke_length | real | | not null |
stroke_period | real | | not null |
positions | real[] | | not null |
loads | integer[] | | not null |
What is does a SELECT on those fields show?
historic=# select positions,loads from well.surface_card limit 1;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{0.05,0.11,0.22,0.33,0.41,0.76,1.1,1.45,1.79,2.3,2.89,3.47,4.05,4.78,5.61,6.44,7.28,8.21,9.31,10.4,11.5,12.66,14.03,15.4,16.77,18.16,19.82,21.48,23.13,24.77,26.72,28.68,30.63,32.59,34.77,37.02,39.27,41.52,43.95,46.49,49.03,51.56,54.22,57.03,59.85,62.66,65.54,68.57,71.61,74.65,77.69,80.87,84.05,87.22,90.38,93.59,96.81,100.03,103.25,106.42,109.58,112.76,115.97,118.98,121.99,125.02,128.03,130.83,133.53,136.25,138.96,141.44,143.75,146.08,148.39,150.52,152.39,154.25,156.13,157.85,159.23,160.6,161.97,163.25,164.1,164.95,165.8,166.63,166.96,167.3,167.63,167.97,167.85,167.7,167.54,167.49,166.88,166.27,165.66,165.05,164.2,163.17,162.17,161.14,159.98,158.61,157.24,155.87,154.42,152.74,151.07,149.39,147.7,145.77,143.84,141.91,139.98,137.87,135.75,133.62,131.49,129.25,126.98,124.7,122.41,120.08,117.69,115.3,112.91,110.49,108.04,105.59,103.14,100.69,98.22,95.75,93.28,90.8,88.35,85.9,83.44,80.98,78.57,76.17,73.76,71.36,69.01,66.69,64.37,62.05,59.79,57.59,55.38,53.17,51.02,48.95,46.88,44.8,42.76,40.84,38.91,37,35.09,33.34,31.6,29.86,28.11,26.54,24.99,23.43,21.85,20.48,19.11,17.74,16.37,15.15,13.98,12.81,11.64,10.59,9.62,8.64,7.69,6.82,6.06,5.3,4.54,3.86,3.32,2.77,2.22,1.73,1.4,1.07,0.74,0.44,0.33,0.22,0.11,0.05} | {5716,6021,6524,6586,6422,6121,5953,5672,5095,6110,7770,7796,7785,7712,7533,7447,7317,7150,7004,6891,6839,6808,6846,7019,7195,7283,7386,7522,7565,7592,7612,7426,7169,6858,6762,6536,6442,6417,6386,6421,6501,6651,6949,7277,7435,7470,7427,7408,7217,7117,7008,6544,6097,5658,5508,5520,5717,5880,5923,6126,6568,7069,7606,7979,8033,7712,7116,6799,6233,5766,5143,4538,4202,4178,4605,5236,5997,6801,7494,8095,8571,9014,9429,9859,10187,10550,11153,11330,11440,11519,11590,11691,11790,11834,11837,11868,11948,12038,12133,12205,12399,12539,12581,12620,12638,12642,12630,12625,12620,12601,12583,12590,12606,12620,12746,12898,12915,12991,13026,13043,13044,13068,13039,12970,12927,12909,12889,12885,12914,13020,13005,13273,13463,13517,13576,13633,13618,13534,13502,13391,13175,13085,13049,12996,12980,13063,13466,13467,13501,13696,13808,13880,13932,13956,13654,13266,13108,12964,12776,12704,12666,12679,12795,13329,13695,14046,14452,14823,15033,15059,14948,14478,13766,13128,12591,12117,11790,11575,11407,10778,10061,9545,9145,8854,8707,8690,8422,7951,7456,7125,7005,7014,7057,7076,6992,6697,6321,6133,5831,5716}
(1 row)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{0.05,0.11,0.22,0.33,0.41,0.76,1.1,1.45,1.79,2.3,2.89,3.47,4.05,4.78,5.61,6.44,7.28,8.21,9.31,10.4,11.5,12.66,14.03,15.4,16.77,18.16,19.82,21.48,23.13,24.77,26.72,28.68,30.63,32.59,34.77,37.02,39.27,41.52,43.95,46.49,49.03,51.56,54.22,57.03,59.85,62.66,65.54,68.57,71.61,74.65,77.69,80.87,84.05,87.22,90.38,93.59,96.81,100.03,103.25,106.42,109.58,112.76,115.97,118.98,121.99,125.02,128.03,130.83,133.53,136.25,138.96,141.44,143.75,146.08,148.39,150.52,152.39,154.25,156.13,157.85,159.23,160.6,161.97,163.25,164.1,164.95,165.8,166.63,166.96,167.3,167.63,167.97,167.85,167.7,167.54,167.49,166.88,166.27,165.66,165.05,164.2,163.17,162.17,161.14,159.98,158.61,157.24,155.87,154.42,152.74,151.07,149.39,147.7,145.77,143.84,141.91,139.98,137.87,135.75,133.62,131.49,129.25,126.98,124.7,122.41,120.08,117.69,115.3,112.91,110.49,108.04,105.59,103.14,100.69,98.22,95.75,93.28,90.8,88.35,85.9,83.44,80.98,78.57,76.17,73.76,71.36,69.01,66.69,64.37,62.05,59.79,57.59,55.38,53.17,51.02,48.95,46.88,44.8,42.76,40.84,38.91,37,35.09,33.34,31.6,29.86,28.11,26.54,24.99,23.43,21.85,20.48,19.11,17.74,16.37,15.15,13.98,12.81,11.64,10.59,9.62,8.64,7.69,6.82,6.06,5.3,4.54,3.86,3.32,2.77,2.22,1.73,1.4,1.07,0.74,0.44,0.33,0.22,0.11,0.05} | {5716,6021,6524,6586,6422,6121,5953,5672,5095,6110,7770,7796,7785,7712,7533,7447,7317,7150,7004,6891,6839,6808,6846,7019,7195,7283,7386,7522,7565,7592,7612,7426,7169,6858,6762,6536,6442,6417,6386,6421,6501,6651,6949,7277,7435,7470,7427,7408,7217,7117,7008,6544,6097,5658,5508,5520,5717,5880,5923,6126,6568,7069,7606,7979,8033,7712,7116,6799,6233,5766,5143,4538,4202,4178,4605,5236,5997,6801,7494,8095,8571,9014,9429,9859,10187,10550,11153,11330,11440,11519,11590,11691,11790,11834,11837,11868,11948,12038,12133,12205,12399,12539,12581,12620,12638,12642,12630,12625,12620,12601,12583,12590,12606,12620,12746,12898,12915,12991,13026,13043,13044,13068,13039,12970,12927,12909,12889,12885,12914,13020,13005,13273,13463,13517,13576,13633,13618,13534,13502,13391,13175,13085,13049,12996,12980,13063,13466,13467,13501,13696,13808,13880,13932,13956,13654,13266,13108,12964,12776,12704,12666,12679,12795,13329,13695,14046,14452,14823,15033,15059,14948,14478,13766,13128,12591,12117,11790,11575,11407,10778,10061,9545,9145,8854,8707,8690,8422,7951,7456,7125,7005,7014,7057,7076,6992,6697,6321,6133,5831,5716}
(1 row)
>
> i'm wondering if is it a normal behaviour of pg_dump and how should I
> execute it to include data on that tables.
>
> Any hint would be appreciated. Thanks in advance and sorry for my english
>
> --
> edugarg
--
Adrian Klaver
adrian.klaver@aklaver.com
On 6/23/20 6:48 AM, Edu Gargiulo wrote:
> On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 6/23/20 6:30 AM, Edu Gargiulo wrote:
> > Hi all,
> >
> > We are using postgresql 11.7 on Debian.
> > I noticed that pg_dump is not including records on tables with
> fields
> > defined as array type (integer[] and real[]). The table structure is
> > normally restored but they have 0 records on restoring.
>
> What is the complete command you are using when running pg_dump?
>
>
> /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp
Coffee has kicked in and I realized I should have asked for the
pg_restore command as well. So what is that?
>
>
> What is the schema for one of the tables? e.g \dt table_name
>
>
> historic=# \dt well.surface_card
> List of relations
> Schema | Name | Type | Owner
> --------+--------------+-------+----------
> well | surface_card | table | historic
> (1 row)
>
> historic=# \d well.surface_card
> Table "well.surface_card"
> Column | Type | Collation | Nullable |
> Default
> -------------------+--------------------------+-----------+----------+---------
> id | bigint | | not null |
> tstamp | timestamp with time zone | | not null |
> card_tstamp | timestamp with time zone | | not null |
> shutdown_event_id | smallint | | not null |
> quality | boolean | | not null |
> load_min | integer | | not null |
> load_max | integer | | not null |
> stroke_length | real | | not null |
> stroke_period | real | | not null |
> positions | real[] | | not null |
> loads | integer[] | | not null |
>
>
> What is does a SELECT on those fields show?
>
>
> historic=# select positions,loads from well.surface_card limit 1;
>
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
{0.05,0.11,0.22,0.33,0.41,0.76,1.1,1.45,1.79,2.3,2.89,3.47,4.05,4.78,5.61,6.44,7.28,8.21,9.31,10.4,11.5,12.66,14.03,15.4,16.77,18.16,19.82,21.48,23.13,24.77,26.72,28.68,30.63,32.59,34.77,37.02,39.27,41.52,43.95,46.49,49.03,51.56,54.22,57.03,59.85,62.66,65.54,68.57,71.61,74.65,77.69,80.87,84.05,87.22,90.38,93.59,96.81,100.03,103.25,106.42,109.58,112.76,115.97,118.98,121.99,125.02,128.03,130.83,133.53,136.25,138.96,141.44,143.75,146.08,148.39,150.52,152.39,154.25,156.13,157.85,159.23,160.6,161.97,163.25,164.1,164.95,165.8,166.63,166.96,167.3,167.63,167.97,167.85,167.7,167.54,167.49,166.88,166.27,165.66,165.05,164.2,163.17,162.17,161.14,159.98,158.61,157.24,155.87,154.42,152.74,151.07,149.39,147.7,145.77,143.84,141.91,139.98,137.87,135.75,133.62,131.49,129.25,126.98,124.7,122.41,120.08,117.69,115.3,112.91,110.49,108.04,105.59,103.14,100.69,98.22,95.75,93.28,90.8,88.35,85.9,83.44,80.98,78.57,76.17,73.76,71.36,69.01,66.69,64.37,62.05,59.79,57.59,55.38,53.17,51.02,48.95,46.88,44.8,42.76,40.84,38.91,37,35.09,33.34,31.6,29.86,28.11,26.54,24.99,23.43,21.85,20.48,19.11,17.74,16.37,15.15,13.98,12.81,11.64,10.59,9.62,8.64,7.69,6.82,6.06,5.3,4.54,3.86,3.32,2.77,2.22,1.73,1.4,1.07,0.74,0.44,0.33,0.22,0.11,0.05}
|
{5716,6021,6524,6586,6422,6121,5953,5672,5095,6110,7770,7796,7785,7712,7533,7447,7317,7150,7004,6891,6839,6808,6846,7019,7195,7283,7386,7522,7565,7592,7612,7426,7169,6858,6762,6536,6442,6417,6386,6421,6501,6651,6949,7277,7435,7470,7427,7408,7217,7117,7008,6544,6097,5658,5508,5520,5717,5880,5923,6126,6568,7069,7606,7979,8033,7712,7116,6799,6233,5766,5143,4538,4202,4178,4605,5236,5997,6801,7494,8095,8571,9014,9429,9859,10187,10550,11153,11330,11440,11519,11590,11691,11790,11834,11837,11868,11948,12038,12133,12205,12399,12539,12581,12620,12638,12642,12630,12625,12620,12601,12583,12590,12606,12620,12746,12898,12915,12991,13026,13043,13044,13068,13039,12970,12927,12909,12889,12885,12914,13020,13005,13273,13463,13517,13576,13633,13618,13534,13502,13391,13175,13085,13049,12996,12980,13063,13466,13467,13501,13696,13808,13880,13932,13956,13654,13266,13108,12964,12776,12704,12666,12679,12795,13329,13695,14046,14452,14823,15033,15059,14948,14478,13766,13128,12591,12117,11790,11575,11407,10778,10061,9545,9145,8854,8707,8690,8422,7951,7456,7125,7005,7014,7057,7076,6992,6697,6321,6133,5831,5716}
> (1 row)
>
>
> >
> > i'm wondering if is it a normal behaviour of pg_dump and how
> should I
> > execute it to include data on that tables.
> >
> > Any hint would be appreciated. Thanks in advance and sorry for my
> english
> >
> > --
> > edugarg
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 6/23/20 6:48 AM, Edu Gargiulo wrote: > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 6/23/20 6:30 AM, Edu Gargiulo wrote: > > Hi all, > > > > We are using postgresql 11.7 on Debian. > > I noticed that pg_dump is not including records on tables with > fields > > defined as array type (integer[] and real[]). The table structure is > > normally restored but they have 0 records on restoring. > > What is the complete command you are using when running pg_dump? > > > /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp > When you do the restore are there any errors in the Postgres log? -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/23/20 6:48 AM, Edu Gargiulo wrote:
> On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 6/23/20 6:30 AM, Edu Gargiulo wrote:
> > Hi all,
> >
> > We are using postgresql 11.7 on Debian.
> > I noticed that pg_dump is not including records on tables with
> fields
> > defined as array type (integer[] and real[]). The table structure is
> > normally restored but they have 0 records on restoring.
>
> What is the complete command you are using when running pg_dump?
>
>
> /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp
Coffee has kicked in and I realized I should have asked for the
pg_restore command as well. So what is that?
pg_restore -d historic --schema well --verbose srvtsdb01.dmp
pg_restore -d historic --schema well --clean --verbose srvtsdb01.dmp
On 6/23/20 7:37 AM, Edu Gargiulo wrote: > > > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 6/23/20 6:48 AM, Edu Gargiulo wrote: > > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver > > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> wrote: > > > > On 6/23/20 6:30 AM, Edu Gargiulo wrote: > > > Hi all, > > > > > > We are using postgresql 11.7 on Debian. > > > I noticed that pg_dump is not including records on tables with > > fields > > > defined as array type (integer[] and real[]). The table > structure is > > > normally restored but they have 0 records on restoring. > > > > What is the complete command you are using when running pg_dump? > > > > > > /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp > > Coffee has kicked in and I realized I should have asked for the > pg_restore command as well. So what is that? > > > pg_restore -d historic -h localhost --clean srvtsdb01.dmp > pg_restore -d historic --schema well --verbose srvtsdb01.dmp > pg_restore -d historic --schema well --clean --verbose srvtsdb01.dmp > With --verbose are you seeing any errors? What does pg_restore -V show? -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/23/20 7:37 AM, Edu Gargiulo wrote:
>
>
> On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 6/23/20 6:48 AM, Edu Gargiulo wrote:
> > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>> wrote:
> >
> > On 6/23/20 6:30 AM, Edu Gargiulo wrote:
> > > Hi all,
> > >
> > > We are using postgresql 11.7 on Debian.
> > > I noticed that pg_dump is not including records on tables with
> > fields
> > > defined as array type (integer[] and real[]). The table
> structure is
> > > normally restored but they have 0 records on restoring.
> >
> > What is the complete command you are using when running pg_dump?
> >
> >
> > /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp
>
> Coffee has kicked in and I realized I should have asked for the
> pg_restore command as well. So what is that?
>
>
> pg_restore -d historic -h localhost --clean srvtsdb01.dmp
> pg_restore -d historic --schema well --verbose srvtsdb01.dmp
> pg_restore -d historic --schema well --clean --verbose srvtsdb01.dmp
>
With --verbose are you seeing any errors?
What does pg_restore -V show?
Thanks for your response Adrian, looking at the pg_restore output I saw issues with triggers and timescaledb extension on restoring those empty tables.
--
edugarg
On 6/23/20 9:44 AM, Edu Gargiulo wrote: > > On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 6/23/20 7:37 AM, Edu Gargiulo wrote: > > > > > > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver > > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>> wrote: > > > > On 6/23/20 6:48 AM, Edu Gargiulo wrote: > > > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver > > > <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com> > > <mailto:adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>>>> wrote: > > > > > > On 6/23/20 6:30 AM, Edu Gargiulo wrote: > > > > Hi all, > > > > > > > > We are using postgresql 11.7 on Debian. > > > > I noticed that pg_dump is not including records on > tables with > > > fields > > > > defined as array type (integer[] and real[]). The table > > structure is > > > > normally restored but they have 0 records on restoring. > > > > > > What is the complete command you are using when > running pg_dump? > > > > > > > > > /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp > > > > Coffee has kicked in and I realized I should have asked for the > > pg_restore command as well. So what is that? > > > > > > pg_restore -d historic -h localhost --clean srvtsdb01.dmp > > pg_restore -d historic --schema well --verbose srvtsdb01.dmp > > pg_restore -d historic --schema well --clean --verbose srvtsdb01.dmp > > > > With --verbose are you seeing any errors? > > What does pg_restore -V show? > > > Thanks for your response Adrian, looking at the pg_restore output I saw > issues with triggers and timescaledb extension on restoring those empty > tables. I'm going to bet that is the problem. What where the errors? Is the timescaledb extension installed on the database you are restoring to? > > -- > edugarg -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, Jun 23, 2020 at 2:25 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 6/23/20 9:44 AM, Edu Gargiulo wrote:
>
> On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
> On 6/23/20 7:37 AM, Edu Gargiulo wrote:
> >
> >
> > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver
> > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>> wrote:
> >
> > On 6/23/20 6:48 AM, Edu Gargiulo wrote:
> > > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> > > <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>
> > <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>
> > <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>>> wrote:
> > >
> > > On 6/23/20 6:30 AM, Edu Gargiulo wrote:
> > > > Hi all,
> > > >
> > > > We are using postgresql 11.7 on Debian.
> > > > I noticed that pg_dump is not including records on
> tables with
> > > fields
> > > > defined as array type (integer[] and real[]). The table
> > structure is
> > > > normally restored but they have 0 records on restoring.
> > >
> > > What is the complete command you are using when
> running pg_dump?
> > >
> > >
> > > /usr/bin/pg_dump -d historic -Z 9 -Fc -f srvtsdb01.dmp
> >
> > Coffee has kicked in and I realized I should have asked for the
> > pg_restore command as well. So what is that?
> >
> >
> > pg_restore -d historic -h localhost --clean srvtsdb01.dmp
> > pg_restore -d historic --schema well --verbose srvtsdb01.dmp
> > pg_restore -d historic --schema well --clean --verbose srvtsdb01.dmp
> >
>
> With --verbose are you seeing any errors?
>
> What does pg_restore -V show?
>
>
> Thanks for your response Adrian, looking at the pg_restore output I saw
> issues with triggers and timescaledb extension on restoring those empty
> tables.
I'm going to bet that is the problem.
What where the errors?
Is the timescaledb extension installed on the database you are restoring to?
It was not installed on the restoring database. After install and execute timescaledb_pre_restore() and timescaledb_post_restore() before and after pg_restore it was restored normally.
Thank you very much