Re: pg_dump empty tables

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

On 6/23/20 6:48 AM, Edu Gargiulo wrote:
> On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver 
> <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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,1
3880,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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx