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
Re: pg_dump empty tables
On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver
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
> adrian.kla...@aklaver.com
>
Re: pg_dump empty tables
On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver wrote: > On 6/23/20 6:48 AM, Edu Gargiulo wrote: > > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver > > mailto:adrian.kla...@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
Re: pg_dump empty tables
On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver wrote: > On 6/23/20 7:37 AM, Edu Gargiulo wrote: > > > > > > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 6/23/20 6:48 AM, Edu Gargiulo wrote: > > > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver > > > mailto:adrian.kla...@aklaver.com> > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@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
Re: pg_dump empty tables
On Tue, Jun 23, 2020 at 2:25 PM Adrian Klaver wrote: > On 6/23/20 9:44 AM, Edu Gargiulo wrote: > > > > On Tue, Jun 23, 2020 at 11:45 AM Adrian Klaver > > mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 6/23/20 7:37 AM, Edu Gargiulo wrote: > > > > > > > > > On Tue, Jun 23, 2020 at 10:56 AM Adrian Klaver > > > mailto:adrian.kla...@aklaver.com> > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>>> wrote: > > > > > > On 6/23/20 6:48 AM, Edu Gargiulo wrote: > > > > On Tue, Jun 23, 2020 at 10:34 AM Adrian Klaver > > > > > <mailto:adrian.kla...@aklaver.com> <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>> > > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com> > > > <mailto:adrian.kla...@aklaver.com > > <mailto:adrian.kla...@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
transpose time-series columnar data
Hi all, I got a view that returns values in the following format timestamp | name |value - ts1name1 value11 ts1name2 value12 ts1name3 value13 ts2name1 value21 ts2name2 value22 ts2name3 value23 ts3name1 value31 ts3name2 value32 ts3name3 value33 I need to transpose and return one row for a single timestamp and one column for every name (fixed number of names), something like this timestamp| name1 |name2 |name 3 --- ts1 value11value12 value13 ts2 value21value22 value23 ts3 value31value32 value33 Any help would be appreciated Thanks and sorry for my english -- Edu
Incremental backup
Hi all, Is there any way to make incremental backups in postgres-12? Kind regards, -- Eduardo
Re: Incremental backup
On Thu, Oct 28, 2021 at 12:36 PM Adrian Klaver wrote: > On 10/28/21 05:23, Edu Gargiulo wrote: > > Hi all, > > > > Is there any way to make incremental backups in postgres-12? > > It would helpful to be more explicit about what you are trying to achieve. > > Do you want: > > 1) A continuous process or scheduled one? > Scheduled 2) Local or remote backups? > Local. Now I'm doing locally and copying backups to other servers. 3) The backup as a standby? Now I'have a standby cluster via physical replication. Could do the backup from standby server too. 4) Any other features requests you might have. > 90% of the data in the databse is "static data" (compressed timescaledb chunks), I want to append only that 10% of recently inserted or updated data daily to yesterday backup, instead of do a full backup daily. Thanks in advance and sorry for my english -- Eduardo
