Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 reading and
hear that pgbackrest does a good job with such huge sizes. Your expert
advise is needed.

-- 
Cheers,

Suhail
Cell#  +97150 8194870

Circles with circle() vs ST_Buffer() Equality equalities

[PALAYRET Jacques]

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, circle('POINT(1 2)'::geometry::point, 0.5::double 
precision))::geometry) AS circle_cast_as_polygon ; 
circle_cast_as_polygon 
---
 
POLYGON((0.5 2,0.509607359798385 2.09754516100806,0.538060233744357 
2.19134171618254,0.584265193848727 2.2777851165098,0.646446609406726 
2.35355339059327,0.722214883490199 2.41573480615127,0.808658283817455 
2.46193976625564,0.902454838991936 2.49039264020162,1 2.5,1.09754516100806 
2.49039264020162,1.19134171618254 
2.46193976625564,1.27778511650982.41573480615127,1.35355339059327 
2.35355339059327,1.41573480615127 2.2777851165098,1.46193976625564 
2.19134171618254,1.49039264020162 2.09754516100806,1.5 
2,1.490392640201621.90245483899194,1.46193976625564 
1.80865828381746,1.41573480615127 1.7222148834902,1.35355339059327 
1.64644660940673,1.2777851165098 1.58426519384873,1.19134171618255 
1.53806023374436,1.09754516100806 1.50960735979838,1 1.5,0.902454838991936 
1.50960735979838,0.808658283817455 1.53806023374436,0.722214883490199 
1.58426519384873,0.646446609406726 1.64644660940673,0.584265193848727 
1.7222148834902,0.538060233744357 1.80865828381745,0.509607359798385 
1.90245483899194,0.5 2)) 

-> The circle of polygon sub-type : ST_Buffer('POINT(1 2)'::geometry, 
0.5::double precision) : 
SELECT ST_AsText(ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)) AS 
circle_as_polygon_via_ST_Buffer ; 
circle_as_polygon_via_st_buffer 
---
 
POLYGON((1.5 2,1.49039264020162 1.90245483899194,1.46193976625564 
1.80865828381746,1.41573480615127 1.7222148834902,1.35355339059327 
1.64644660940673,1.2777851165098 1.58426519384873,1.19134171618255 
1.53806023374436,1.09754516100806 1.50960735979838,1 1.5,0.902454838991937 
1.50960735979838,0.808658283817456 1.53806023374436,0.7222148834902 
1.58426519384873,0.646446609406727 1.64644660940673,0.584265193848728 
1.7222148834902,0.538060233744357 1.80865828381745,0.509607359798385 
1.90245483899193,0.5 2,0.509607359798384 2.09754516100806,0.538060233744356 
2.19134171618254,0.584265193848726 2.2777851165098,0.646446609406725 
2.35355339059327,0.722214883490197 2.41573480615127,0.808658283817453 
2.46193976625564,0.902454838991934 2.49039264020161,0.998 
2.5,1.09754516100806 2.49039264020162,1.19134171618254 
2.46193976625564,1.2777851165098 2.41573480615127,1.35355339059327 
2.35355339059327,1.41573480615127 2.2777851165098,1.46193976625564 
2.19134171618255,1.49039264020161 2.09754516100807,1.5 2)) 

Comparison : 
SELECT ST_OrderingEquals(ST_Buffer('POINT(1 2)'::geometry, 0.5::double 
precision) , polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double 
precision))::geometry) ; 
st_orderingequals 
--- 
f 
-> OK, with an ordering polygon equality 

=> But, I was surprised by the result of the following 2 SQL queries, in 
particular by the second one : 

SELECT ST_Equals(ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision) , 
polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double 
precision))::geometry) ; 
st_equals 
--- 
f <= weird for me ! 
SELECT ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision) = polygon(32, 
circle('POINT(1 2)'::geometry::point, 0.5::double precision))::geometry AS 
BounderingEquals ; 
bounderingequals 
-- 
f <= weird for me ! 

Details (OK) : 
SELECT box(polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double 
precision))::geometry) AS box_from_circle_through_polygon ; 
box_from_circle_through_polygon 
- 
(1.5,2.5),(0.5,1.5) 
SELECT box( ST_Buffer('POINT(1 2)'::geometry, 0.5::double precision)) AS 
box_from_ST_Buffer_polygon ; 
box_from_st_buffer_polygon 
 
(1.5,2.5),(0.5,1.5) 
SELECT box(polygon(32, circle('POINT(1 2)'::geometry::point, 0.5::double 
precision))::geometry) = box( ST_Buffer('POINT(1 2)'::geometry, 0.5::double 
precision)) AS BounderingBoxEquals ; 
bounderingboxequals 
- 
t 

=> Have you any explanations on the result (=false) of st_equals (NOT ordering) 
and bounderingequals ? 

Thanks in advance. 
Regards 
- Météo-France - 
PALAYRET JACQUES 
DCSC/MBD 
jacques.palay...@meteo.fr 
Fixe : +33 561078319 

Seamless Logical Replication during Fail-over

[Moor Ali]

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 not sure if
these changes made it to any of the releases.

The use-case is as follows:
* Cluster A with a master and two or more replicas using synchronized
streaming replication
* Cluster B containing a node which uses logical replication with Cluster A
master. There may be a lag in updating this node.
* When Cluster A master goes down, a replica is promoted but it does not
have any information about the logical replication slots created in the
master. So, even if a new slot is created on the replica, there will be
data loss on the subscriber side.

Any suggestions on how to address this using builtin mechanisms or using an
extension are welcome.

Thanks,
Murali

Re: PG12.2 Configure cannot enalble SSL

[Gavan Schneider]

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... no
checking openssl/ssl.h presence... no
checking for openssl/ssl.h... no
	configure: error: header file  is required for 
OpenSSL


Offhand, I'll guess that you need to shorten the --with-includes
path to

--with-includes=/usr/local/opt/openssl/include

What you are showing here would only work if the header file's full 
path

is

/usr/local/opt/openssl/include/openssl/openssl/ssl.h

which doesn't seem likely.

My bad. I made an error in cobbling together a reduced example which 
achieved the same error for the wrong reasons.
I’ll share the larger version but the problem remains. It does not 
seem to be as simple as a bad path.


-bash-3.2$
-bash-3.2$ ./_Build_PostgeSQL.sh > build.log 2>&1
	-bash-3.2$ ls -las 
/usr/local/Cellar/openssl@1.1/1.1.1g/include/openssl/ssl.h
	224 -rw-r--r--  1 pendari  staff  111253 21 Apr 22:22 
/usr/local/Cellar/openssl@1.1/1.1.1g/include/openssl/ssl.h

-bash-3.2$

Where _Build_PostgeSQL.sh looks like:


echo ===
echo = `date "+%Y-%m-%d %H:%M"` 
echo ===

PORT=65432

# Working Base
BASE="/usr/local/pgsql/v12"

	# Define basic tools == Homebrew versions (updated on a regular 
basis)

MAKE="/usr/local/bin/gmake"
CC="/usr/bin/gcc" # alternate? "/usr/local/bin/gcc-9"

LIBS=""
HDRS=""
# OPENSSL
LIBS=${LIBS}:"/usr/local/Cellar/openssl@1.1/1.1.1g/lib"
HDRS=${HDRS}:"/usr/local/Cellar/openssl@1.1/1.1.1g/include"
#LIBS=${LIBS}:"/usr/local/Cellar/libressl/3.1.1/lib"
#HDRS=${HDRS}:"/usr/local/Cellar/libressl/3.1.1/include"
# OPENLDAP
LIBS=${LIBS}:"/usr/local/opt/openldap/lib"
HDRS=${HDRS}:"/usr/local/opt/openldap/include"
#  GENERIC

LIBS=${LIBS}:"/usr/local/lib:/Library/Developer/CommandLineTools/usr/lib"

HDRS=${HDRS}:"/usr/local/include:/Library/Developer/CommandLineTools/usr/include"

cd postgresql-12.2

./configure \
CC=${CC}\
--with-includes=${HRDS} 
\
--with-libraries=${LIBS}
\
--prefix=${BASE}\
--with-pgport=${PORT}   \
--with-python   \
--with-libxml --with-libxslt\
--with-wal-blocksize=8  \
--with-system-tzdata=/usr/share/zoneinfo\
--with-pam  \
--with-ldap \
--with-uuid=e2fs\
--with-bonjour  \
--with-openssl  \

echo ===
echo ===
echo --with-includes =${HDRS}
echo --with-libraries=${LIBS}
echo CC is: $CC
echo MAKE is: $MAKE
echo ===
echo ===

exit DEBUG

and generated

===
= 2020-05-15 21:43 
===
checking build system type... x86_64-apple-darwin19.4.0
checking host system type... x86_64-apple-darwin19.4.0
. . .
checking zlib.h usability... yes
checking zlib.h presence... yes
checking for zlib.h... yes
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
===
===
	--with-includes 
=:/usr/local/Cellar/openssl@1.1/1.1.1g/include:/usr/local/opt/openldap/include:/usr/local/include:/Library/Developer/CommandLineTools/usr/include


--with-libraries=:/usr/local/Cellar/openssl@1.1/1.1.

Re: PG12.2 Configure cannot enalble SSL

[Peter Eisentraut]

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 of the test 
failure.  (Search the file for "openssl/ssl.h" to find the right place.)


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Seamless Logical Replication during Fail-over

[Peter Eisentraut]

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 part of PostgreSQL 9.6 release. But I am not sure 
if these changes made it to any of the releases.


You are right that the fail-over slot mechanism was supposed to address 
this but it never made it into a release.  I'm not aware of an 
open-source solution for this right now.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Inherited an 18TB DB & need to backup

[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.
> 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 reading and
> hear that pgbackrest does a good job with such huge sizes. Your expert
> advise is needed.

Incremental backups suggest the need to backup WAL archives. See
https://www.postgresql.org/docs/9.2/continuous-archiving.html

pgbackrest looks very cool but we haven't used it.

A very simple solution could be just to dump the database daily with
pg_dump, if you have the space and machine capacity to do it. Depending
on what you are storing, you can achieve good compression with this, and
it is a great way of having a simple file from which to restore a
database.

Our ~200GB cluster resolves to under 10GB of pg_dump files, although
18TB is a whole different order of size.

Rory

Re: Inherited an 18TB DB & need to backup

[Christoph Berg]

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

[Kenneth Marshall]

> >
> >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 to 10.3:

https://hunleyd.github.io/posts/Upgrading-PostgreSQL-from-9.4-to-10.3-with-pglogical/

and another article:

https://www.depesz.com/2016/11/08/major-version-upgrading-with-minimal-downtime/

It is a pretty straightforward procedure and really cuts the time needed
for an upgrade. Although, as Tom mentioned maybe just bumping your
maintenance_work_mem up to 1g or so may make the index create time
reasonable.

Regards,
Ken

Re: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 inherited an 18 TB DB that is running version 9.2.
>
> Push hard to get that upgraded to a supported version.
>
> Christoph
>

Re: PG12.2 Configure cannot enalble SSL

[Tom Lane]

"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

[Jan Karremans]

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 ACE Alumni 

- Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße gegen die 
Rechtschreibung -

Mobile: +31-(0)6-1638 9607

http://www.enterprisedb.com/

Don't walk behind me; I may not lead.
Don't walk in front of me; I may not follow.
Just walk beside me and be my friend.
+*+ Albert Camus +*+

> Op 15 mei 2020, om 15:23 heeft Suhail Bamzena  het 
> volgende geschreven:
> 
> 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 inherited an 18 TB DB that is running version 9.2.
> 
> Push hard to get that upgraded to a supported version.
> 
> Christoph

Re: Inherited an 18TB DB & need to backup

[Wolff, Ken L]

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.postgresql.org
Subject: EXTERNAL: 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, 
mailto:christoph.b...@credativ.de>> wrote:
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: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 Campbell-Lange, 
wrote:

> 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
> dump
> > it into a NAS. What is the best way to go about this? Did some reading
> and
> > hear that pgbackrest does a good job with such huge sizes. Your expert
> > advise is needed.
>
> Incremental backups suggest the need to backup WAL archives. See
> https://www.postgresql.org/docs/9.2/continuous-archiving.html
>
> pgbackrest looks very cool but we haven't used it.
>
> A very simple solution could be just to dump the database daily with
> pg_dump, if you have the space and machine capacity to do it. Depending
> on what you are storing, you can achieve good compression with this, and
> it is a great way of having a simple file from which to restore a
> database.
>
> Our ~200GB cluster resolves to under 10GB of pg_dump files, although
> 18TB is a whole different order of size.
>
> Rory
>

Re: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 regards,
>
>
>
>
> *Jan Karremans*Director of Sales Engineering, EMEA
> Senior Sales Engineer DACH-Region
> EDB Postgres Advanced Server Professional
> -- Postgres Everywhere --
>
>  Oracle ACE Alumni
>
> - Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße
> gegen die Rechtschreibung -
>
> Mobile: +31-(0)6-1638 9607
>
> http://www.enterprisedb.com/
>
> *Don't walk behind me*; I may not lead.
> *Don't walk in front of me*; I may not follow.
> *Just walk beside me* and be my friend.
> +*+ Albert Camus +*+
>
> Op 15 mei 2020, om 15:23 heeft Suhail Bamzena  het
> volgende geschreven:
>
> 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 inherited an 18 TB DB that is running version
>> 9.2.
>>
>> Push hard to get that upgraded to a supported version.
>>
>> Christoph
>>
>
>

Re: Inherited an 18TB DB & need to backup

[Jan Karremans]

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 Everywhere --

 Oracle ACE Alumni 

- Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße gegen die 
Rechtschreibung -

Mobile: +31-(0)6-1638 9607

http://www.enterprisedb.com/

Don't walk behind me; I may not lead.
Don't walk in front of me; I may not follow.
Just walk beside me and be my friend.
+*+ Albert Camus +*+

> Op 15 mei 2020, om 15:31 heeft Suhail Bamzena  het 
> volgende geschreven:
> 
> 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 regards,
> 
> 
> 
> Jan Karremans
> Director of Sales Engineering, EMEA
> Senior Sales Engineer DACH-Region
> EDB Postgres Advanced Server Professional
> -- Postgres Everywhere --
> 
>  Oracle ACE Alumni 
> 
> - Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße gegen 
> die Rechtschreibung -
> 
> Mobile: +31-(0)6-1638 9607
> 
> http://www.enterprisedb.com/ 
> 
> Don't walk behind me; I may not lead.
> Don't walk in front of me; I may not follow.
> Just walk beside me and be my friend.
> +*+ Albert Camus +*+
> 
>> Op 15 mei 2020, om 15:23 heeft Suhail Bamzena > > het volgende geschreven:
>> 
>> 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 inherited an 18 TB DB that is running version 9.2.
>> 
>> Push hard to get that upgraded to a supported version.
>> 
>> Christoph
> 
> 

Re: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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:* Friday, May 15, 2020 7:23 AM
> *To:* Christoph Berg 
> *Cc:* Rory Campbell-Lange ;
> pgsql-general@lists.postgresql.org; pgeu-gene...@lists.postgresql.org
> *Subject:* EXTERNAL: 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 inherited an 18 TB DB that is running version 9.2.
>
> Push hard to get that upgraded to a supported version.
>
> Christoph
>
>

Re: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 of Sales Engineering, EMEA
> Senior Sales Engineer DACH-Region
> EDB Postgres Advanced Server Professional
> -- Postgres Everywhere --
>
>  Oracle ACE Alumni
>
> - Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße
> gegen die Rechtschreibung -
>
> Mobile: +31-(0)6-1638 9607
>
> http://www.enterprisedb.com/
>
> *Don't walk behind me*; I may not lead.
> *Don't walk in front of me*; I may not follow.
> *Just walk beside me* and be my friend.
> +*+ Albert Camus +*+
>
> Op 15 mei 2020, om 15:31 heeft Suhail Bamzena  het
> volgende geschreven:
>
> 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 regards,
>>
>>
>>
>>
>> *Jan Karremans*Director of Sales Engineering, EMEA
>> Senior Sales Engineer DACH-Region
>> EDB Postgres Advanced Server Professional
>> -- Postgres Everywhere --
>>
>>  Oracle ACE Alumni
>>
>> - Deutsch ist nicht meine Muttersprache, bitte verzeihen Sie Verstöße
>> gegen die Rechtschreibung -
>>
>> Mobile: +31-(0)6-1638 9607
>>
>> http://www.enterprisedb.com/
>>
>> *Don't walk behind me*; I may not lead.
>> *Don't walk in front of me*; I may not follow.
>> *Just walk beside me* and be my friend.
>> +*+ Albert Camus +*+
>>
>> Op 15 mei 2020, om 15:23 heeft Suhail Bamzena 
>> het volgende geschreven:
>>
>> 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 inherited an 18 TB DB that is running version
>>> 9.2.
>>>
>>> Push hard to get that upgraded to a supported version.
>>>
>>> Christoph
>>>
>>
>> 
>
>
>

Re: Inherited an 18TB DB & need to backup

[Rory Campbell-Lange]

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 being less than 2TB a day...

I suggest you try one immediately, assuming you can dump it somewhere
and the machine has the resources. Then you will at least have a backup
and a sense of what is possible. (Which will help inform your upgrade
strategy too.)

Rory

Re: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 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...
>
> I suggest you try one immediately, assuming you can dump it somewhere
> and the machine has the resources. Then you will at least have a backup
> and a sense of what is possible. (Which will help inform your upgrade
> strategy too.)
>
> Rory
>
>

Re: PG12.2 Configure cannot enalble SSL

[Gavan Schneider]

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 been having a good week.  But it’s 
getting better. Thank you for spotting this,


checking openssl/ssl.h usability... yes
checking openssl/ssl.h presence... yes
checking for openssl/ssl.h... yes

Now to get some real work done :)

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a 
well-known solution to every human problem — neat, plausible, and 
wrong. The ancients, in the case at bar, laid the blame upon the gods: 
sometimes they were remote and surly, and sometimes they were kind. In 
the Middle Ages lesser powers took a hand in the matter, and so one 
reads of works of art inspired by Our Lady, by the Blessed Saints, by 
the souls of the departed, and even by the devil.  H. L. Mencken, 1920

Re: PG12.2 Configure cannot enalble SSL

[Gavan Schneider]

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 openssl/ssl.h usability
	configure:13101: /usr/bin/gcc -c -Wall -Wmissing-prototypes 
-Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels 
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing 
-fwrapv -Wno-unused-	command-line-argument -O2  
-I/Library/Developer/CommandLineTools/SDKs/MacOSX.sdk/usr/include  
-I/usr/local/Cellar/openssl@1.1/1.1.1g/include -I/usr/local/include 
-I/Library/Developer/CommandLineTools/usr/include conftest.c >&5

configure:13101: $? = 0
configure:13101: result: yes


Regards
Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a 
well-known solution to every human problem — neat, plausible, and 
wrong. The ancients, in the case at bar, laid the blame upon the gods: 
sometimes they were remote and surly, and sometimes they were kind. In 
the Middle Ages lesser powers took a hand in the matter, and so one 
reads of works of art inspired by Our Lady, by the Blessed Saints, by 
the souls of the departed, and even by the devil.  H. L. Mencken, 1920

Re: Inherited an 18TB DB & need to backup

[Ravi Krishna]

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

[Gabriele Bartolini]

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-backup-barman-1-4-0/

More articles about Barman from our blog:
https://www.2ndquadrant.com/en/blog/tag/barman/

Good luck.

Cheers,
Gabriele

Il giorno ven 15 mag 2020 alle ore 15:49 Ravi Krishna <
srkrish...@comcast.net> ha scritto:

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

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

[Chris Withers]

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 exclude constraint took 15 hours.


Don't recall for sure, but I think GIST index build is sensitive
to the maintenance_work_mem setting; did you have that cranked up?


postgres=# show maintenance_work_mem;
 maintenance_work_mem
--
 2GB
(1 row)

Would it be worth turning that up more? The server has ~130GB memory.


 - failing that, what can I do to import and then create the index
   in the background?


CREATE INDEX CONCURRENTLY, perhaps.


How would I bring this into play with respect to the dump and load 
cycle? Is there an option to pg_dump or something else I should use or 
is this a case of awk'ing the create index in the dump?


Chris

Bug on version 12 ?

[PegoraroF10]

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": 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.

ERROR: invalid value "T1" for "HH24"
Detail: Value must be an integer.
Where: SQL function "castimmutabletimestamp" statement 1

Is that a version 12 bug or a server configuration ?



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

[Michael Lewis]

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

[Suhail Bamzena]

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 article in which I could publicly mention the size of the
> database:
>
> https://www.2ndquadrant.com/en/blog/incremental-backup-barman-1-4-0/
>
> More articles about Barman from our blog:
> https://www.2ndquadrant.com/en/blog/tag/barman/
>
> Good luck.
>
> Cheers,
> Gabriele
>
> Il giorno ven 15 mag 2020 alle ore 15:49 Ravi Krishna <
> srkrish...@comcast.net> ha scritto:
>
>> IMO a database of this size should only be backed up in s3.  pgbackrest
>> has support for backup to s3.
>>
>

Re: Bug on version 12 ?

[David G. Johnston]

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 ?

[PegoraroF10]

Both are ISO, MDY



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Bug on version 12 ?

[Tom Lane]

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.

Stripping away the JSON frippery, what you have is

to_timestamp('2020-04-02T18:26:50.941531-03:00', '-MM-DD HH24:MI:SS')

which used to be allowed, but v12 is pickier; it insists that you account
for the "T" explicitly:

regression=# select to_timestamp('2020-04-02T18:26:50.941531-03:00', 
'-MM-DD HH24:MI:SS');
ERROR:  invalid value "T1" for "HH24"
DETAIL:  Value must be an integer.
regression=# select to_timestamp('2020-04-02T18:26:50.941531-03:00', 
'-MM-DDTHH24:MI:SS');
  to_timestamp  

 2020-04-02 00:06:50-04
(1 row)

I think you're doing it wrong and you should just cast to timestamp:

regression=# select 
  JS ->> 'mydate'::text, 
  (JS ->> 'mydate')::timestamptz
from (select '{"somefield": true, "otherfield": true, "mydate":
"2020-04-02T18:26:50.941531-03:00"}'::jsonb) j(JS)
;
 ?column? |  timestamptz  
--+---
 2020-04-02T18:26:50.941531-03:00 | 2020-04-02 17:26:50.941531-04
(1 row)

Use of to_timestamp() to parse a bog-standard time format is a
classic antipattern IMO.  It is inflexible, it doesn't detect
the errors you'd actually like it to detect, and it is harder
to type --- so why do people insist on doing it like that?

regards, tom lane

Re: Bug on version 12 ?

[David G. Johnston]

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-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.
>
> ERROR: invalid value "T1" for "HH24"
> Detail: Value must be an integer.
> Where: SQL function "castimmutabletimestamp" statement 1
>
> Is that a version 12 bug or a server configuration ?
>

Its a version 12 behavior change, though its somewhat unfortunate that its
covered by the imprecise:
"Adjust to_timestamp()/to_date() functions to be more forgiving of template
mismatches ..." item in the release notes.

I believe (cannot test at the moment) that the issue is that the code no
longer likes to match space template markers with non-space input, skipping
the template position altogether without moving along the input string.
You will want to change your template to use "T" which more closely matches
the input data anwyay.

Order you can, and probably should, just stop using to_timestamp and do
("JS->>'mydate)::timestamptz (which has the added benefit of keeping the
timezone information).

David J.

Re: Bug on version 12 ?

[PegoraroF10]

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 ?

[John W Higgins]

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" - the
second one is of interest here.

Apparently the portable format for your need would be

select to_timestamp('2020-04-02T18:26:50.941531-03:00',
'-MM-DDtHH24:MI:SS');

That works on both PG 11 and PG 12.

John W Higgins

[1] - https://www.postgresql.org/docs/12/functions-formatting.html

Re: Inherited an 18TB DB & need to backup

[Andreas 'ads' Scherbaum]

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
costs?

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Re: Inherited an 18TB DB & need to backup

[Ravi Krishna]

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 requires you to
store backups for say 7 years, storing it locally will be a problem.  In 
one large financial

company I use to work, full backup was used to store old data.
(except last 30 days where WAL logs were used for a real PIT).  We use 
to store full backups
for about 60 days and then send older backup to an off site storage.  
Nothing is free.


I remember a case where we were requested by business to restore a db of 
a given date two yrs
prior as they had to look at old data. It took us close to 96 hrs to 
give the users the required database.


S3 storage is ridiculously cheap.  Off site storage companies like Iron 
Mountain should find their client base

ditching them big time.

Re: Bug on version 12 ?

[Adrian Klaver]

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

 {"mydate": "2020-05-15T10:54:17.913596-07:00"}
(1 row)

Option 1:

select to_timestamp(jsonb_build_object('mydate',
current_timestamp)->>'mydate', '-MM-DD T HH24:MI:SS');
  to_timestamp
-
 05/15/2020 10:54:20 PDT




Option 2 per Tom's suggestion:

select (jsonb_build_object('mydate',
current_timestamp)->>'mydate')::timestamptz;
  timestamptz

 05/15/2020 10:54:58.649859 PDT




works on 11.7 but not on 12.3.





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Bug on version 12 ?

[Tom Lane]

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 would work fine on any Postgres version.

regards, tom lane

Re: Inherited an 18TB DB & need to backup

[Andreas 'ads' Scherbaum]

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 local backup is that space is not infinite. If your
> business requires you to
> store backups for say 7 years, storing it locally will be a problem.  In
> one large financial
> company I use to work, full backup was used to store old data.
> (except last 30 days where WAL logs were used for a real PIT).  We use to
> store full backups
> for about 60 days and then send older backup to an off site storage.
> Nothing is free.
>
> I remember a case where we were requested by business to restore a db of a
> given date two yrs
> prior as they had to look at old data. It took us close to 96 hrs to give
> the users the required database.
>
> S3 storage is ridiculously cheap.  Off site storage companies like Iron
> Mountain should find their client base
> ditching them big time.
>

If your database is running somewhere in the cloud, then yes, that might
make
sense. If your database runs in your own data center, then usually you also
have
disk space available there. Plus a transfer out of your data center will
take time.

There is no "per se" recommendation to move data to S3. And there might be
additional requirements like data protection laws, encryption requirements
ect.

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Re: Bug on version 12 ?

[PegoraroF10]

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 ?

[Adrian Klaver]

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();
 version 


-
 PostgreSQL 11.8 (Ubuntu 11.8-1.pgdg18.04+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit


select (jsonb_build_object('mydate',
test(# current_timestamp)->>'mydate')::timestamptz;
  timestamptz
---
 2020-05-15 12:55:26.259151-07
(1 row)





--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

Unexplained Access Exclusive Locks on Follower from PID7

[Andy Cooper]

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 1000.103 ms
9-2] sql_error_code = 0 DETAIL: Process holding the lock: 7. Wait
queue: ...

Given that I did not catch the locking in progress I am having to rely on
these logs to determine what occurred. The  waiting for an AccessShareLock
leads me to believe that the actual lock must have been AccessExclusive.

Relation 2840 is a TOAST table whose primary table is `pg_statistic`.

One other piece of potential evidence was that there were auto vacuums
occurring on the primary around this time.

Outside of the lock logs there is no logging around what PID7 was doing.
Additionally the other followers in the formation did not suffer from the
same locking.

Any ideas on what this might be or how we could further troubleshoot this
issue?

-- 
Andy Cooper

Re: Inherited an 18TB DB & need to backup

[Scottix]

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 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 requires you to
>> store backups for say 7 years, storing it locally will be a problem.  In
>> one large financial
>> company I use to work, full backup was used to store old data.
>> (except last 30 days where WAL logs were used for a real PIT).  We use to
>> store full backups
>> for about 60 days and then send older backup to an off site storage.
>> Nothing is free.
>>
>> I remember a case where we were requested by business to restore a db of
>> a given date two yrs
>> prior as they had to look at old data. It took us close to 96 hrs to give
>> the users the required database.
>>
>> S3 storage is ridiculously cheap.  Off site storage companies like Iron
>> Mountain should find their client base
>> ditching them big time.
>>
>
> If your database is running somewhere in the cloud, then yes, that might
> make
> sense. If your database runs in your own data center, then usually you
> also have
> disk space available there. Plus a transfer out of your data center will
> take time.
>
> There is no "per se" recommendation to move data to S3. And there might be
> additional requirements like data protection laws, encryption requirements
> ect.
>
> --
> Andreas 'ads' Scherbaum
> German PostgreSQL User Group
> European PostgreSQL User Group - Board of Directors
> Volunteer Regional Contact, Germany - PostgreSQL Project
>
-- 
T: @Thaumion
IG: Thaumion
scot...@gmail.com

Re: Inherited an 18TB DB & need to backup

[Christophe Pettus]

> 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 pays for 
itself (storage only) in 2.4 months.  Even with an annualized failure rate of 
1.4% (per Backblaze), and with the required host, rack, etc., etc., it be 
significantly more economical to run your own backup server.  If you regularly 
do restores off of the backups (for example, to prime staging environments or 
developer systems), the outbound transfer can add up fast, too.
--
-- Christophe Pettus
   x...@thebuild.com

Re: Inherited an 18TB DB & need to backup

[Ron]

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 backed up. The application, 
of course, would have to be modified.)


On 5/15/20 8:26 AM, Suhail Bamzena 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 being less than 2TB a day...


On Fri, 15 May 2020, 17:02 Rory Campbell-Lange, > wrote:


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 dump
> it into a NAS. What is the best way to go about this? Did some
reading and
> hear that pgbackrest does a good job with such huge sizes. Your expert
> advise is needed.

Incremental backups suggest the need to backup WAL archives. See
https://www.postgresql.org/docs/9.2/continuous-archiving.html

pgbackrest looks very cool but we haven't used it.

A very simple solution could be just to dump the database daily with
pg_dump, if you have the space and machine capacity to do it. Depending
on what you are storing, you can achieve good compression with this, and
it is a great way of having a simple file from which to restore a
database.

Our ~200GB cluster resolves to under 10GB of pg_dump files, although
18TB is a whole different order of size.

Rory



--
Angular momentum makes the world go 'round.

schema agnostic functions in language sql

[Rob Sargent]

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 functions.  The 'parser'? does not recognize the tables 
(sans schema qualifier):


   ERROR:  relation "" does not exist.

I would rather not have to duplicate these across multiple schema - I'll 
use plpgsql instead unless I've overlooked some other avenue.


I've found that I can create the function, in public, if I set the 
search_path containing one schema (of course) and then successfully 
access the function after resetting the search path to use a second 
schema.  My "build the world" scripting has so far avoided needing to 
know/use any specific role.  Another pipe dream vaporized?


Thanks,
rjs

Re: schema agnostic functions in language sql

[Adrian Klaver]

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 does not appear to be true 
for plain sql functions.  The 'parser'? does not recognize the tables 
(sans schema qualifier):


ERROR:  relation "" does not exist.


You probably need to show example code, because I don't see this:

show search_path ;
   search_path
--
 public,accounting,history,main,utility,timeclock,table_templates

\d utility.login_info
   Table "utility.login_info"
   Column|  Type  | Collation | Nullable | 
   Default

-++---+--+--
 user_name   | character varying  |   | not null |
 user_pwd| character varying  |   | not null |
 user_role   | character varying  |   |  |
 ts_insert   | timestamp(0) without time zone |   |  | 
now()

 ts_update   | timestamp(0) without time zone |   |  |
 user_update | character varying(20)  |   |  |
 user_insert | character varying(20)  |   |  | 
"session_user"()



CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
SELECT * FROM login_info WHERE user_name = $1;
$$ LANGUAGE SQL;

select * from getli('aklaver');
 user_name | user_pwd | user_role |  ts_insert  | 
ts_update  | user_update | user_insert

---+--+---+-+-+-+-
 aklaver   | ranger   |   | 12/29/2012 12:23:17 | 05/15/2020 
15:41:14 | | postgres

(1 row)




I would rather not have to duplicate these across multiple schema - I'll 
use plpgsql instead unless I've overlooked some other avenue.


I've found that I can create the function, in public, if I set the 
search_path containing one schema (of course) and then successfully 
access the function after resetting the search path to use a second 
schema.  My "build the world" scripting has so far avoided needing to 
know/use any specific role.  Another pipe dream vaporized?


Thanks,
rjs





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: schema agnostic functions in language sql

[Rob Sargent]

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 correct schema-dependent table.  The same does not appear to 
be true for plain sql functions.  The 'parser'? does not recognize 
the tables (sans schema qualifier):


    ERROR:  relation "" does not exist.


You probably need to show example code, because I don't see this:

show search_path ;
   search_path
--
 public,accounting,history,main,utility,timeclock,table_templates

\d utility.login_info
   Table "utility.login_info"
   Column    |  Type  | Collation | Nullable | 
   Default
-++---+--+-- 


 user_name   | character varying  | | not null |
 user_pwd    | character varying  | | not null |
 user_role   | character varying  | |  |
 ts_insert   | timestamp(0) without time zone | |  | now()
 ts_update   | timestamp(0) without time zone | |  |
 user_update | character varying(20)  | |  |
 user_insert | character varying(20)  | |  | 
"session_user"()



CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
    SELECT * FROM login_info WHERE user_name = $1;
$$ LANGUAGE SQL;

select * from getli('aklaver');
 user_name | user_pwd | user_role |  ts_insert  | 
ts_update  | user_update | user_insert
---+--+---+-+-+-+- 

 aklaver   | ranger   |   | 12/29/2012 12:23:17 | 05/15/2020 
15:41:14 | | postgres

(1 row)


Isn't "utility" in your path above?




I would rather not have to duplicate these across multiple schema - 
I'll use plpgsql instead unless I've overlooked some other avenue.


I've found that I can create the function, in public, if I set the 
search_path containing one schema (of course) and then successfully 
access the function after resetting the search path to use a second 
schema.  My "build the world" scripting has so far avoided needing to 
know/use any specific role.  Another pipe dream vaporized?


Thanks,
rjs

Re: schema agnostic functions in language sql

[Adrian Klaver]

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 let the runtime search_path 
find the correct schema-dependent table.  The same does not appear to 
be true for plain sql functions.  The 'parser'? does not recognize 
the tables (sans schema qualifier):


    ERROR:  relation "" does not exist.


You probably need to show example code, because I don't see this:

show search_path ;
   search_path
--
 public,accounting,history,main,utility,timeclock,table_templates

\d utility.login_info
   Table "utility.login_info"
   Column    |  Type  | Collation | Nullable | 
   Default
-++---+--+-- 


 user_name   | character varying  | | not null |
 user_pwd    | character varying  | | not null |
 user_role   | character varying  | |  |
 ts_insert   | timestamp(0) without time zone | |  | now()
 ts_update   | timestamp(0) without time zone | |  |
 user_update | character varying(20)  | |  |
 user_insert | character varying(20)  | |  | 
"session_user"()



CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
    SELECT * FROM login_info WHERE user_name = $1;
$$ LANGUAGE SQL;

select * from getli('aklaver');
 user_name | user_pwd | user_role |  ts_insert  | 
ts_update  | user_update | user_insert
---+--+---+-+-+-+- 

 aklaver   | ranger   |   | 12/29/2012 12:23:17 | 05/15/2020 
15:41:14 | | postgres

(1 row)


Isn't "utility" in your path above?


Yes. In your OP you had:

"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 functions."


I was showing that search_path works with SQL functions, which you 
indicated was not happening for you.


Are you talking about some other case?





I would rather not have to duplicate these across multiple schema - 
I'll use plpgsql instead unless I've overlooked some other avenue.


I've found that I can create the function, in public, if I set the 
search_path containing one schema (of course) and then successfully 
access the function after resetting the search path to use a second 
schema.  My "build the world" scripting has so far avoided needing to 
know/use any specific role.  Another pipe dream vaporized?


Thanks,
rjs










--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Inherited an 18TB DB & need to backup

[Michael Nolan]

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 local backup is that space is not infinite.
>

Another problem is storage devices fail.  S3 storage lakes _should_ be
checking your data integrity on a regular basis and possibly maintaining
copies of it iin multiple locations so you're not vulnerable to a site
disaster.
--
Mike Nolan

Re: schema agnostic functions in language sql

[Rob Sargent]

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 
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 functions. The 'parser'? does 
not recognize the tables (sans schema qualifier):


    ERROR:  relation "" does not exist.


You probably need to show example code, because I don't see this:

show search_path ;
   search_path
--
 public,accounting,history,main,utility,timeclock,table_templates

\d utility.login_info
   Table "utility.login_info"
   Column    |  Type  | Collation | Nullable 
|    Default
-++---+--+-- 


 user_name   | character varying  | | not null |
 user_pwd    | character varying  | | not null |
 user_role   | character varying  | |  |
 ts_insert   | timestamp(0) without time zone | |  | now()
 ts_update   | timestamp(0) without time zone | |  |
 user_update | character varying(20)  | |  |
 user_insert | character varying(20)  | |  | 
"session_user"()



CREATE FUNCTION getli(varchar) RETURNS login_info AS $$
    SELECT * FROM login_info WHERE user_name = $1;
$$ LANGUAGE SQL;

select * from getli('aklaver');
 user_name | user_pwd | user_role | ts_insert  | ts_update  
| user_update | user_insert
---+--+---+-+-+-+- 

 aklaver   | ranger   |   | 12/29/2012 12:23:17 | 05/15/2020 
15:41:14 | | postgres

(1 row)


Isn't "utility" in your path above?


Yes. In your OP you had:

"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 functions."


I was showing that search_path works with SQL functions, which you 
indicated was not happening for you.


Are you talking about some other case?

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.


sarge=# \dn
  List of schemas
  Name  |  Owner
+--
 base   | postgres
 bulk   | postgres
 gt | postgres
 public | postgres
 sss    | postgres
(5 rows)

sarge=# show search_path;
   search_path
-
 "$user", public
(1 row)

sarge=#
sarge=# create or replace function public.segment_calls(segid uuid)
returns table (name text, firsti int, lasti int, calls text)
as
$$
select
    p.name,
    s.firstmarker,
    s.lastmarker,
    regexp_replace(substr(g.calls,1+(2*s.firstmarker), 
2*(s.lastmarker-s.firstmarker+1))::text, '(..)', ' \1','g') as calls

from
    segment s
    join probandset b on s.probandset_id = b.id
    join people l on b.people_id = l.id
    join people_member m on l.id = m.people_id
    join person p on m.person_id = p.id
    join genotype g on g.markerset_id = s.markerset_id and g.person_id 
= p.id

where s.id = segid;
$$
language sql
;

sarge-# ERROR:  relation "segment" does not exist
LINE 11: segment s
 ^
sarge=# create or replace function public.segment_calls(segid uuid)
returns table (name text, firsti int, lasti int, calls text)
as
$$
begin
select
    p.name,
    s.firstmarker,
    s.lastmarker,
    regexp_replace(substr(g.calls,1+(2*s.firstmarker), 
2*(s.lastmarker-s.firstmarker+1))::text, '(..)', ' \1','g') as calls

from
    segment s
    join probandset b on s.probandset_id = b.id
    join people l on b.people_id = l.id
    join people_member m on l.id = m.people_id
    join person p on m.person_id = p.id
    join genotype g on g.markerset_id = s.markerset_id and g.person_id 
= p.id

where s.id = segid;
end;
$$
language plpgsql;
sarge-# CREATE FUNCTION

sarge=# Query buffer reset (cleared).
sarge=# \dt gt.*
  List of relations
 Schema |  Name   | Type  |  Owner
+-+---+--
 gt | chaseable   | table | postgres
 gt | duo_chaseable   | table | postgres
 gt | genotype    | table | postgres
 gt | ld  | table | postgres
 gt | probandset  | table | postgres
 gt | probandset_group    | table | postgres
 gt | probandset_group_member | table | postgres
 gt | process | table | postgres
 gt | process_arg | table | postgres
 gt | process_input 

Re: schema agnostic functions in language sql

[David G. Johnston]

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 "works" as in
"executes".

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 none of that happens until the
function is called.  Because of this pl/pgsql allows for ambiguous sql text
to exist and be concretely resolved during execution while SQL does not.

David J.

Re: schema agnostic functions in language sql

[Rob Sargent]

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 schema qualification.


You need to distinguish between "works" as in "compiles" and "works" 
as in "executes".


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 none of that happens 
until the function is called.  Because of this pl/pgsql allows for 
ambiguous sql text to exist and be concretely resolved during 
execution while SQL does not.


Thank you for the confirmation.  I'll decide whether I move to plpgsql 
or dither with role/search_path in the db creation scripts.

(Both forms, plpgsql and sql, "work" once given the correct context.)

David J.

Re: schema agnostic functions in language sql

[Tom Lane]

"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 none of that happens until the
> function is called.  Because of this pl/pgsql allows for ambiguous sql text
> to exist and be concretely resolved during execution while SQL does not.

I don't think that's accurate.  SQL functions are stored as plain text,
just like any other non-C-coded function, and they are not parsed until
execution.

There are big differences from plpgsql of course.  For one, it's
possible for a SQL function to be "inlined" into the calling query,
in which case parsing happens during planning of the calling query.
But other than that, I'd expect the execution-time search path
to determine how a SQL function behaves.

Since Rob didn't provide any details, it's far from clear what's
going wrong for him.

regards, tom lane

Re: schema agnostic functions in language sql

[Rob Sargent]

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 result earlier.  For pl/pgsql none of that happens until the
function is called.  Because of this pl/pgsql allows for ambiguous sql text
to exist and be concretely resolved during execution while SQL does not.

I don't think that's accurate.  SQL functions are stored as plain text,
just like any other non-C-coded function, and they are not parsed until
execution.

There are big differences from plpgsql of course.  For one, it's
possible for a SQL function to be "inlined" into the calling query,
in which case parsing happens during planning of the calling query.
But other than that, I'd expect the execution-time search path
to determine how a SQL function behaves.

Since Rob didn't provide any details, it's far from clear what's
going wrong for him.

regards, tom lane

Did my message with a sql and plgpsql versions not come through?

I cannot create a plain sql function unless the search_path covers any 
table mentioned. Not the case when using plpgsql - no path needed.


I'm ok(ish) with that, unless I've missed some detail.

rjs

Re: schema agnostic functions in language sql

[Adrian Klaver]

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) and links the textual 
query
to its parse result earlier.  For pl/pgsql none of that happens until 
the
function is called.  Because of this pl/pgsql allows for ambiguous 
sql text

to exist and be concretely resolved during execution while SQL does not.

I don't think that's accurate.  SQL functions are stored as plain text,
just like any other non-C-coded function, and they are not parsed until
execution.

There are big differences from plpgsql of course.  For one, it's
possible for a SQL function to be "inlined" into the calling query,
in which case parsing happens during planning of the calling query.
But other than that, I'd expect the execution-time search path
to determine how a SQL function behaves.

Since Rob didn't provide any details, it's far from clear what's
going wrong for him.

    regards, tom lane

Did my message with a sql and plgpsql versions not come through?

I cannot create a plain sql function unless the search_path covers any 
table mentioned. Not the case when using plpgsql - no path needed.


But does the plpgsql segment_calls() run?

On other words does:

select * from segment_calls(segid uuid);

work?



I'm ok(ish) with that, unless I've missed some detail.

rjs



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: schema agnostic functions in language sql

[Tom Lane]

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 default we'll try to parse and analyze the body, so
any unknown tables will draw an error.  plpgsql doesn't go further than a
very crude syntax check.

If you don't like that, you can set check_function_bodies = off while
creating your SQL functions.  But in any case, it's only related to what
happens at execution if the search path is the same.

regards, tom lane

Re: Inherited an 18TB DB & need to backup

[Suhail Bamzena]

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 mostly historical data, I'd split the database into multiple
> instances, so that older data rarely needs to be backed up. The
> application, of course, would have to be modified.)
>
> On 5/15/20 8:26 AM, Suhail Bamzena 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 being less than 2TB a day...
>
> On Fri, 15 May 2020, 17:02 Rory Campbell-Lange, 
> wrote:
>
>> 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
>> dump
>> > it into a NAS. What is the best way to go about this? Did some reading
>> and
>> > hear that pgbackrest does a good job with such huge sizes. Your expert
>> > advise is needed.
>>
>> Incremental backups suggest the need to backup WAL archives. See
>> https://www.postgresql.org/docs/9.2/continuous-archiving.html
>>
>> pgbackrest looks very cool but we haven't used it.
>>
>> A very simple solution could be just to dump the database daily with
>> pg_dump, if you have the space and machine capacity to do it. Depending
>> on what you are storing, you can achieve good compression with this, and
>> it is a great way of having a simple file from which to restore a
>> database.
>>
>> Our ~200GB cluster resolves to under 10GB of pg_dump files, although
>> 18TB is a whole different order of size.
>>
>> Rory
>>
>
> --
> Angular momentum makes the world go 'round.
>

Re: Inherited an 18TB DB & need to backup

[Ron]

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 question. The key point in my statement was "db of this size".

The problem with local backup is that space is not infinite.


Another problem is storage devices fail.  S3 storage lakes _should_ be 
checking your data integrity on a regular basis and possibly maintaining 
copies of it iin multiple locations so you're not vulnerable to a site 
disaster.


Tape FTW!!

--
Angular momentum makes the world go 'round.