pg_dump empty tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_dump empty tables

Edu Gargiulo
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
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Edu Gargiulo
On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver <[hidden email]> 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)
 

>
> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Adrian Klaver-4
On 6/23/20 6:48 AM, Edu Gargiulo wrote:

> On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Adrian Klaver-4
In reply to this post by Edu Gargiulo
On 6/23/20 6:48 AM, Edu Gargiulo wrote:

> On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Edu Gargiulo
In reply to this post by Adrian Klaver-4


On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver <[hidden email]> wrote:
On 6/23/20 6:48 AM, Edu Gargiulo wrote:
> On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> 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
 

Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Adrian Klaver-4
On 6/23/20 7:37 AM, Edu Gargiulo wrote:

>
>
> On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 6/23/20 6:48 AM, Edu Gargiulo wrote:
>      > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
>      > <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email]
>     <mailto:[hidden email]>>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Edu Gargiulo

On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver <[hidden email]> wrote:
On 6/23/20 7:37 AM, Edu Gargiulo wrote:
>
>
> On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 6/23/20 6:48 AM, Edu Gargiulo wrote:
>      > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
>      > <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email]
>     <mailto:[hidden email]>>> 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 
Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Adrian Klaver-4
On 6/23/20 9:44 AM, Edu Gargiulo wrote:

>
> On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 6/23/20 7:37 AM, Edu Gargiulo wrote:
>      >
>      >
>      > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver
>      > <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email]
>     <mailto:[hidden email]>>> wrote:
>      >
>      >     On 6/23/20 6:48 AM, Edu Gargiulo wrote:
>      >      > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
>      >      > <[hidden email]
>     <mailto:[hidden email]> <mailto:[hidden email]
>     <mailto:[hidden email]>>
>      >     <mailto:[hidden email]
>     <mailto:[hidden email]>
>      >     <mailto:[hidden email]
>     <mailto:[hidden email]>>>> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: pg_dump empty tables

Edu Gargiulo
On Tue, Jun 23, 2020 at 2:25 PM Adrian Klaver <[hidden email]> wrote:
On 6/23/20 9:44 AM, Edu Gargiulo wrote:
>
> On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     On 6/23/20 7:37 AM, Edu Gargiulo wrote:
>      >
>      >
>      > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver
>      > <[hidden email] <mailto:[hidden email]>
>     <mailto:[hidden email]
>     <mailto:[hidden email]>>> wrote:
>      >
>      >     On 6/23/20 6:48 AM, Edu Gargiulo wrote:
>      >      > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
>      >      > <[hidden email]
>     <mailto:[hidden email]> <mailto:[hidden email]
>     <mailto:[hidden email]>>
>      >     <mailto:[hidden email]
>     <mailto:[hidden email]>
>      >     <mailto:[hidden email]
>     <mailto:[hidden email]>>>> 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