pg_dump empty tables

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

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)

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 -h localhost --clean srvtsdb01.dmp
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