Inherited an 18TB DB & need to backup

Hello All, I have very recently inherited an 18 TB DB that is running version 9.2. Apparently this database has never been backed up and I have been tasked to set in a periodic backup routine (weekly full & daily incremental) and dump it into a NAS. What is the best way to go about this? Did some r

Circles with circle() vs ST_Buffer() Equality equalities

Hello, With PostgreSQL 10.11, I was trying to compare a circle made with the function circle() to the « same » or similar one made thanks to the function ST_Buffer(). -> The circle of type circle : circle('POINT(1 2)'::geometry::point, 0.5::double precision) : SELECT ST_AsText(polygon(32, ci

Seamless Logical Replication during Fail-over

Hi, Is there any way to continue logical replication (either in-built or using pglogical) between a promoted replica and existing subscribers without data loss? I could find some discussions about fail-over slots and other mechanisms for addressing this as part of PostgreSQL 9.6 release. But I am

Re: PG12.2 Configure cannot enalble SSL

On 14 May 2020, at 23:26, Tom Lane wrote: "Gavan Schneider" writes: -bash-3.2$ ./configure --with-openssl \ > --with-includes=/usr/local/opt/openssl/include/openssl \ > --with-libraries=/usr/local/opt/openssl/lib ... checking openssl/ssl.h usability... n

Re: PG12.2 Configure cannot enalble SSL

On 2020-05-15 13:54, Gavan Schneider wrote: checking openssl/ssl.h usability... no checking openssl/ssl.h presence... no checking for openssl/ssl.h... no configure: error: header file is required for OpenSSL Then the next step is to check in config.log for the details o

Re: Seamless Logical Replication during Fail-over

On 2020-05-15 12:05, Moor Ali wrote: Is there any way to continue logical replication (either in-built or using pglogical) between a promoted replica and existing subscribers without data loss? I could find some discussions about fail-over slots and other mechanisms for addressing this as par

Re: Inherited an 18TB DB & need to backup

On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > Hello All, > I have very recently inherited an 18 TB DB that is running version 9.2. > Apparently this database has never been backed up and I have been tasked to > set in a periodic backup routine (weekly full & daily incremental) and du

Re: Inherited an 18TB DB & need to backup

Re: Rory Campbell-Lange > On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > > Hello All, > > I have very recently inherited an 18 TB DB that is running version 9.2. Push hard to get that upgraded to a supported version. Christoph

Re: surprisingly slow creation of gist index used in exclude constraint

> > > >Hi Chris, > > > >This sounds like a candidate for pg_logical replicating from the > >old to new system. > > Can you point me to a good guide as to how to easily set this up for > one database and would work between pg 9.4 and pg 11.5? > > cheers, > > Chris Hi Chris, Here is on for 9.4 t

Re: Inherited an 18TB DB & need to backup

Hi Christoph Thats very high on my agenda.. but need to make sure i can backup this beast to start with.. On Fri, 15 May 2020, 17:08 Christoph Berg, wrote: > Re: Rory Campbell-Lange > > On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > > > Hello All, > > > I have very recently inherit

Re: PG12.2 Configure cannot enalble SSL

"Gavan Schneider" writes: > HDRS=${HDRS}:"/usr/local/Cellar/openssl@1.1/1.1.1g/include" > ... > --with-includes=${HRDS} If that's an accurate copy of your script, spelling HDRS correctly would help. regards, tom lane

Re: Inherited an 18TB DB & need to backup

Hi Suhail, That is not an issue. We have customers backing up Postgres databases up to 80 TB. Mit freundlichem Gruß, kind regards, Jan Karremans Director of Sales Engineering, EMEA Senior Sales Engineer DACH-Region EDB Postgres Advanced Server Professional -- Postgres Everywhere -- Oracle A

Re: Inherited an 18TB DB & need to backup

Depending on your storage subsystem, perhaps storage-level snapshots might be an option? They often seem to be the best choice for VLDBs. From: Suhail Bamzena Sent: Friday, May 15, 2020 7:23 AM To: Christoph Berg Cc: Rory Campbell-Lange ; pgsql-general@lists.postgresql.org; pgeu-gene...@lists

Re: Inherited an 18TB DB & need to backup

Thanks Rory, the machine has the capacity to pull through pg_dumps but like u rightly mentioned incremental backups mean that we will need to work with the wal's.. 18TB is what is the scary part and with compression I dont see it being less than 2TB a day... On Fri, 15 May 2020, 17:02 Rory Campbel

Re: Inherited an 18TB DB & need to backup

Thanks Jan.. just to know more is it using the native pg_dump or another tool like pgbackrest or barman ?? On Fri, 15 May 2020, 17:26 Jan Karremans, wrote: > Hi Suhail, > > That is not an issue. We have customers backing up Postgres databases up > to 80 TB. > > Mit freundlichem Gruß, kind regard

Re: Inherited an 18TB DB & need to backup

Hi Suhail, I do not know personally, but I can figure out / get you setup with someone who can tell you more. Mit freundlichem Gruß, kind regards, Jan Karremans Director of Sales Engineering, EMEA Senior Sales Engineer DACH-Region EDB Postgres Advanced Server Professional -- Postgres Everywhe

Re: Inherited an 18TB DB & need to backup

I can look into that.. not very sure abt the storage infra.. On Fri, 15 May 2020, 17:26 Wolff, Ken L, wrote: > Depending on your storage subsystem, perhaps storage-level snapshots might > be an option? They often seem to be the best choice for VLDBs. > > > > *From:* Suhail Bamzena > *Sent:* Fr

Re: Inherited an 18TB DB & need to backup

Thanks Jan.. would appreciate all the info I can get. On Fri, 15 May 2020, 17:32 Jan Karremans, wrote: > Hi Suhail, > > I do not know personally, but I can figure out / get you setup with > someone who can tell you more. > > Mit freundlichem Gruß, kind regards, > > > > > *Jan Karremans*Director

Re: Inherited an 18TB DB & need to backup

On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > Thanks Rory, the machine has the capacity to pull through pg_dumps but like > u rightly mentioned incremental backups mean that we will need to work with > the wal's.. 18TB is what is the scary part and with compression I dont see > it be

Re: Inherited an 18TB DB & need to backup

Yeah Rory want to pull one asap..hopefully by COB tonight.. On Fri, 15 May 2020, 17:35 Rory Campbell-Lange, wrote: > On 15/05/20, Suhail Bamzena (suhailsa...@gmail.com) wrote: > > Thanks Rory, the machine has the capacity to pull through pg_dumps but > like > > u rightly mentioned incremental ba

Re: PG12.2 Configure cannot enalble SSL

On 15 May 2020, at 23:24, Tom Lane wrote: "Gavan Schneider" writes: HDRS=${HDRS}:"/usr/local/Cellar/openssl@1.1/1.1.1g/include" ... --with-includes=${HRDS} If that's an accurate copy of your script, spelling HDRS correctly would help. D’oh! More more proof I haven’t be

Re: PG12.2 Configure cannot enalble SSL

On 15 May 2020, at 22:27, Peter Eisentraut wrote: Then the next step is to check in config.log for the details of the test failure. (Search the file for "openssl/ssl.h" to find the right place.) Thank you, hopefully I’ll find my typos quicker this way configure:13101: checking op

Re: Inherited an 18TB DB & need to backup

IMO a database of this size should only be backed up in s3. pgbackrest has support for backup to s3.

Re: Inherited an 18TB DB & need to backup

Hi Suhail, We developed Barman (www.pgbarman.org), which works with older versions of PostgreSQL too. It works with very large databases and is open source (GPL). This is an old article in which I could publicly mention the size of the database: https://www.2ndquadrant.com/en/blog/incremental-ba

Re: surprisingly slow creation of gist index used in exclude constraint

On 14/05/2020 21:31, Tom Lane wrote: Chris Withers writes: It has 4.1 million rows in it and while importing the data only takes a couple of minutes, when I did a test load into the new cluster, building the mkt_profile_period_col1_col4_col2_chan_excl index for the e

Bug on version 12 ?

select To_Json(Current_Timestamp); returns "2020-05-15T14:49:44.266342+00:00" on version 11.7 or 12.3 So I have lots of JSONS which have timestamp on them. select JS ->> 'mydate'::text, to_timestamp((JS ->> 'mydate'), '-MM-DD HH24:MI:SS') from (select '{"somefield": true, "otherfield":

Re: Bug on version 12 ?

Just wonder, have you compared these on the two servers? select * from pg_settings where name = 'DateStyle';

Re: Inherited an 18TB DB & need to backup

Thanks Gabriele... i will review this in detail. On Fri, 15 May 2020, 18:41 Gabriele Bartolini, wrote: > Hi Suhail, > > We developed Barman (www.pgbarman.org), which works with older versions > of PostgreSQL too. It works with very large databases and is open source > (GPL). > > This is an old a

Re: Bug on version 12 ?

On Fri, May 15, 2020 at 8:27 AM Michael Lewis wrote: > Just wonder, have you compared these on the two servers? > > select * from pg_settings where name = 'DateStyle'; > The OP is using to_timestamp, the DateStyle setting is immaterial. David J.

Re: Bug on version 12 ?

Both are ISO, MDY -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

PegoraroF10 writes: > select > JS ->> 'mydate'::text, > to_timestamp((JS ->> 'mydate'), '-MM-DD HH24:MI:SS') > from (select '{"somefield": true, "otherfield": true, "mydate": > "2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS) > This SQL works fine on 11.7 but not on 12.3 version. Str

Re: Bug on version 12 ?

On Fri, May 15, 2020 at 8:08 AM PegoraroF10 wrote: > select To_Json(Current_Timestamp); > returns "2020-05-15T14:49:44.266342+00:00" on version 11.7 or 12.3 > > So I have lots of JSONS which have timestamp on them. > > select > JS ->> 'mydate'::text, > to_timestamp((JS ->> 'mydate'), '-MM

Re: Bug on version 12 ?

Ok Tom but then you cannot go back and forth, like this ... select to_timestamp(jsonb_build_object('mydate', current_timestamp)->>'mydate', '-MM-DD HH24:MI:SS'); works on 11.7 but not on 12.3. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

On Fri, May 15, 2020 at 9:38 AM PegoraroF10 wrote: > Ok Tom but then you cannot go back and forth, like this ... > > select to_timestamp(jsonb_build_object('mydate', > current_timestamp)->>'mydate', '-MM-DD HH24:MI:SS'); > >From here [1] - there are 2 green boxes on the page marked "Tip" - t

Re: Inherited an 18TB DB & need to backup

On Fri, May 15, 2020 at 3:49 PM Ravi Krishna wrote: > IMO a database of this size should only be backed up in s3. pgbackrest > has support for backup to s3. > Why should the backup land in S3, and not local somewhere? Any good reason why one should pay for the additional storage and transfer co

Re: Inherited an 18TB DB & need to backup

Why should the backup land in S3, and not local somewhere? Any good reason why one should pay for the additional storage and transfer costs? Good question. The key point in my statement was "db of this size". The problem with local backup is that space is not infinite. If your business requ

Re: Bug on version 12 ?

On 5/15/20 9:38 AM, PegoraroF10 wrote: Ok Tom but then you cannot go back and forth, like this ... select to_timestamp(jsonb_build_object('mydate', current_timestamp)->>'mydate', '-MM-DD HH24:MI:SS'); select jsonb_build_object('mydate', current_timestamp); jsonb_build_object

Re: Bug on version 12 ?

PegoraroF10 writes: > Ok Tom but then you cannot go back and forth, like this ... > select to_timestamp(jsonb_build_object('mydate', > current_timestamp)->>'mydate', '-MM-DD HH24:MI:SS'); > works on 11.7 but not on 12.3. The advice I gave you was to stop using to_timestamp altogether. That w

Re: Inherited an 18TB DB & need to backup

On Fri, May 15, 2020 at 7:52 PM Ravi Krishna wrote: > > Why should the backup land in S3, and not local somewhere? > Any good reason why one should pay for the additional storage and transfer > costs? > > Good question. The key point in my statement was "db of this size". > > The problem with loc

Re: Bug on version 12 ?

I understood, but the problem is that I cannot just migrate from 11 to 12, I have to carefully verify all code before migration. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

On 5/15/20 12:35 PM, PegoraroF10 wrote: I understood, but the problem is that I cannot just migrate from 11 to 12, I have to carefully verify all code before migration. It would be helpful to include the information you are responding to. The simplest case works on 11: select version();

Unexplained Access Exclusive Locks on Follower from PID7

We recently saw a follower database taken down as the result of maxed out connection slots. The logs showed that the lock was held by PID 7 and was blocking an AccessShareLock [9-1] sql_error_code = 0 LOG: process 5148 still waiting for AccessShareLock on relation 2840 of database 16402 after

Re: Inherited an 18TB DB & need to backup

Also when you get in the multi TB data storage the bill gets a little harder to digest in S3. On Fri, May 15, 2020 at 11:49 Andreas 'ads' Scherbaum wrote: > > > On Fri, May 15, 2020 at 7:52 PM Ravi Krishna > wrote: > >> >> Why should the backup land in S3, and not local somewhere? >> Any good r

Re: Inherited an 18TB DB & need to backup

> On May 15, 2020, at 12:01, Scottix wrote: > > Also when you get in the multi TB data storage the bill gets a little harder > to digest in S3. Indeed. Right now, just buying off of Amazon, a 12TB Seagate IronWolf drive is $0.03/GB. S3 infrequent access is $0.0125/GB/month, so the drive p

Re: Inherited an 18TB DB & need to backup

For a database that size, I'd install pgbackrest, since it features parallel backups and compression.  With it, I'd do monthly full backups with daily differential backups. (If it's mostly historical data, I'd split the database into multiple instances, so that older data rarely needs to be ba

schema agnostic functions in language sql

I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the correct schema-dependent table.  The same does not appear to be true for plain sql f

Re: schema agnostic functions in language sql

On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the correct schema-dependent table.  The same doe

Re: schema agnostic functions in language sql

On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and let the runtime search_path find the

Re: schema agnostic functions in language sql

On 5/15/20 3:53 PM, Rob Sargent wrote: On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql functions without using a schema identifier and

Re: Inherited an 18TB DB & need to backup

On Fri, May 15, 2020 at 12:51 PM Ravi Krishna wrote: > > Why should the backup land in S3, and not local somewhere? > Any good reason why one should pay for the additional storage and transfer > costs? > > Good question. The key point in my statement was "db of this size". > > The problem with lo

Re: schema agnostic functions in language sql

On 5/15/20 4:58 PM, Adrian Klaver wrote: On 5/15/20 3:53 PM, Rob Sargent wrote: On 5/15/20 4:43 PM, Adrian Klaver wrote: On 5/15/20 3:26 PM, Rob Sargent wrote: I'm using postgres 12.2, with multiple identical schema per database (each with a matching role).  I can write public plpgsql fu

Re: schema agnostic functions in language sql

On Fri, May 15, 2020 at 4:07 PM Rob Sargent wrote: > I'm terribly sorry: I needed to add that plpgsql works without any > knowledge of the schema, where as defining a plain sql functions does > not work without schema qualification. > You need to distinguish between "works" as in "compiles" and

Re: schema agnostic functions in language sql

On 5/15/20 5:16 PM, David G. Johnston wrote: On Fri, May 15, 2020 at 4:07 PM Rob Sargent > wrote: I'm terribly sorry:  I needed to add that plpgsql works without any knowledge of the schema, where as defining a plain sql functions does not work without

Re: schema agnostic functions in language sql

"David G. Johnston" writes: > Yes, SQL and pl/pgsql have very different behaviors when it comes to > compilation and execution. In particular SQL performs parsing earlier > (during creation - just like it does for views) and links the textual query > to its parse result earlier. For pl/pgsql non

Re: schema agnostic functions in language sql

On 5/15/20 5:41 PM, Tom Lane wrote: "David G. Johnston" writes: Yes, SQL and pl/pgsql have very different behaviors when it comes to compilation and execution. In particular SQL performs parsing earlier (during creation - just like it does for views) and links the textual query to its parse

Re: schema agnostic functions in language sql

On 5/15/20 4:58 PM, Rob Sargent wrote: On 5/15/20 5:41 PM, Tom Lane wrote: "David G. Johnston" writes: Yes, SQL and pl/pgsql have very different behaviors when it comes to compilation and execution.  In particular SQL performs parsing earlier (during creation - just like it does for views) a

Re: schema agnostic functions in language sql

Rob Sargent writes: > I cannot create a plain sql function unless the search_path covers any > table mentioned. Not the case when using plpgsql - no path needed. Oh, one of the things that's quite a lot different is the checking applied at function creation time ;-). For a SQL function, by defa

Re: Inherited an 18TB DB & need to backup

Thanks Ron.. pgbackrest and barman seem to b good options.. On Sat, 16 May 2020, 02:26 Ron, wrote: > For a database that size, I'd install pgbackrest, since it features > parallel backups and compression. With it, I'd do monthly full backups > with daily differential backups. > > (If it's mostl

Re: Inherited an 18TB DB & need to backup

On 5/15/20 6:01 PM, Michael Nolan wrote: On Fri, May 15, 2020 at 12:51 PM Ravi Krishna > wrote: Why should the backup land in S3, and not local somewhere? Any good reason why one should pay for the additional storage and transfer costs? Good q