Substitute Variable in select query

[harish supare]

Hi Team,

Would like to know what the substitute/input variable available in psql.


Oracle we use &  - select a, b , c from table where a like &a;

Is there an alternative in psql?

-- 
Best Regards
Harish.

SSL between Primary and Seconday PostgreSQL DBs

[Susan Joseph]

 I have setup a Primary and Secondary PostgreSQL DBs.  They were setup up with 
basic replication then I went back and modified them to use SSL.  I am just not 
sure if I did it correctly.  Everything is working but I want to make sure I 
have the settings correctly.  I am using PostgreSQL 11.2.  
   
   - I have a PKI that I stood up so I issued 2 server certificates one for 
each database from my CA.
   - Primary server certificate - Primary Database   

   
   - The FQDN and IP address are set in the SAN field.
   - FQDN is also the CN in the DN
   - Key Usage is set to Digital Signature and Key encipherment
   - EKU is set to Server Authentication and Client Authentication   

   
   - Rep_user certificate - Secondary Database
   
   - CN is set to the rep_user account name
   - Key Usage is set to digital signature and key encipherment
   - EKU is set to client authentication   

   
   - Each certificate file contains the certificate and the subCA certificate 
who issued the certificate and put in a file called server.crt for the Primary 
and client.crt for the secondary.   

   - The key for each certificate is stored in a separate file unencrypted (I 
have questions about this later on) in a file called server.key and client.key  
 

   - The server.crt, server.key, and root.crt are put onto the primary database 
server in the /data/pgsql/data location, the owner and group of these files is 
set to postgres
   - The client.crt, client.key, and root.crt are put onto the primary database 
server in the /data/pgsql/data location, the owner and group of these files is 
set to postgres
   - On the Primary in postgresql.conf I set:
   
   - ssl=on
   - ssl_ca_file='root.crt'
   - ssl_cert_file='server.crt'
   - ssl_key_file='server.key'
   - ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
   
   - On the Primary in pg_hba.conf I add a replication line:   

   
   - hostssl    replication  rep_user   
   cert
   
   - On the Secondary I set the following information in the postgresql.conf 
to:  (DO I NEED TO DO THIS??)   

   
   - ssl=on
   - ssl_ca_file='root.crt'
   - ssl_cert_file='client.crt'
   - ssl_cert_fkey='client.key'
   - ssl_ciphers='HIGH:MEDIUM:+3DES:!aNULL'
   
   - On the Secondary I edit the recovery.conf file to the following:
   
   - primary_conninfo = 'user=rep_user passfile=''/data/.pgpass'' host= port=5432 sslmode=verify-ca sslcert=client.crt sslkey=client.key 
sslcompression=0 target_session_attrs=any'
   
   - On the Secondary I edit the pg_hba.conf file and change the rep_user line 
to:
   
   - hostssl  replication rep_user   /32
  cert clientcert=1
   
   - On the Secondary I move the root.crt to /data/pgsql/data/.postgresql
   - Then I restart the databases

My questions are:   
   - Do I need to set the information in the Secondary postgresql.conf?  
Originally I did not set this and everything worked but I saw errors in my log 
files that said to do SSL these needed to be set so I went back and set them.  
Are there pgsql commands I can run to test that my SSL is working in both 
directions?   

   - Are my pg_hba.conf files set correctly?  Is that how you get SSL "turned 
on" for communications between the primary and the rep_user account?
   - If I leave my key file encrypted then every time my databases have to be 
started have to enter the password.  So you can either leave the passwords 
unencrypted and set the permissions on the file to 0600 accessible only by 
postgres or you can enter the key password each time the database is started 
up.  As someone in the security field I have a tough time leaving the key 
unencrypted but as some setting up a production system that is located on a 
network that you can't get to without directly accessing the server I feel that 
is enough security that I can leave them unencrypted.  Thoughts?
   - Am I missing anything?  There are no videos out there that show how to 
stand up a 2 way SSL communication channel between the primary and secondary, 
or does anyone have one that they can share?

Thanks,  Susan
 

Re: Substitute Variable in select query

[David G. Johnston]

On Monday, August 24, 2020, harish supare  wrote:

> Hi Team,
>
> Would like to know what the substitute/input variable available in psql.
>
>
> Oracle we use &  - select a, b , c from table where a like &a;
>
> Is there an alternative in psql?
>
>
> Colon - read the documentation, psql section, for the exact syntax.

David J.

Re: Query plan prefers hash join when nested loop is much faster

[iulian dragos]

Hi Michael,

Thanks for the answer. It's an RDS instance using SSD storage and the
default `random_page_cost` set to 4.0. I don't expect a lot of repetitive
queries here, so I think caching may not be extremely useful. I wonder if
the selectivity of the query is wrongly estimated (out of 500 million rows,
only a few thousands are returned).

I tried lowering the `random_page_cost` to 1.2 and it didn't make a
difference in the query plan.

iulian


On Fri, Aug 21, 2020 at 6:30 PM Michael Lewis  wrote:

> Your system is preferring sequential scan to
> using test_result_module_result_id_idx in this case. What type of storage
> do you use, what type of cache hits do you expect, and what do you have
> random_page_cost set to? That comes to mind as a significant factor in
> choosing index scans based on costs.
>

Re: Getting away from Oracle APEX, recommendations for PostgreSQL?

[Diego]

perl, is the most common. but you can use C, SQL, python or any other 
can be activate, check:


https://www.postgresql.org/docs/current/xplang.html

https://www.postgresql.org/docs/current/xfunc-c.html

On 2020-08-22 19:32, Dirk Krautschick wrote:

Hi all,
  
if someone wants to get away from developing Oracle APEX to become more flexible

with the related database system (yes, rebuilding everything is the case 
here)what
kind of web technology or programming language etc. would you recommend in case 
of
using PostgreSQL? Or is there anything similar for an option?

Thanks and best regards
  
Dirk

Re: Substitute Variable in select query

[David G. Johnston]

On Mon, Aug 24, 2020 at 7:21 AM harish supare 
wrote:

> thanks for the reply David.
>
> In case of Colon I need to set the variable first, my requirement is my
> select query should prompt for the input.
>
>
Please don't top-post.

psql does not have a feature that will prompt users during the execution of
a query.  It does, as documented, have a "\prompt" meta-command though.

David J.

Re: Substitute Variable in select query

[harish supare]

thanks for the reply David.

In case of Colon I need to set the variable first, my requirement is my
select query should prompt for the input.

On Mon, 24 Aug 2020 at 10:15 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, August 24, 2020, harish supare  wrote:
>
>> Hi Team,
>>
>> Would like to know what the substitute/input variable available in psql.
>>
>>
>> Oracle we use &  - select a, b , c from table where a like &a;
>>
>> Is there an alternative in psql?
>>
>>
>> Colon - read the documentation, psql section, for the exact syntax.
>
> David J.
>
>
> --
Best Regards
Harish.

Re: Query plan prefers hash join when nested loop is much faster

[iulian dragos]

On Mon, Aug 24, 2020 at 4:21 PM iulian dragos 
wrote:

> Hi Michael,
>
> Thanks for the answer. It's an RDS instance using SSD storage and the
> default `random_page_cost` set to 4.0. I don't expect a lot of repetitive
> queries here, so I think caching may not be extremely useful. I wonder if
> the selectivity of the query is wrongly estimated (out of 500 million rows,
> only a few thousands are returned).
>
> I tried lowering the `random_page_cost` to 1.2 and it didn't make a
> difference in the query plan.
>

I experimented a bit more with different values for this setting. The only
way I could make it use the index was to use a value strictly less than
`seq_page_cost` (0.8 for instance). That doesn't sound right, though.

The size of the effective_cache_size is fairly high as well (32 GB) for an
instance with 64GB (db.m5.4xlarge).

iulian

>

Re: BUG? Slave don't reconnect to the master

[Олег Самойлов]

> 21 авг. 2020 г., в 17:26, Jehan-Guillaume de Rorthais  
> написал(а):
> 
> On Thu, 20 Aug 2020 15:16:10 +0300
> Based on setup per node, you can probably add
> 'synchronous_commit=remote_write' in the common conf.

Nope. I set 'synchronous_commit=remote_write' only for 3 and 4 node clusters. 
Two node clusters don't have this setting (use default), because they don't 
have the synchronous commit.

> 
>> [...]
>> pacemaker config, specific for this cluster:
>> [...]
> 
> why did you add "monitor interval=15"? No harm, but it is redundant with
> "monitor interval=16 role=Master" and "monitor interval=17 role=Slave".

I can't remember clearly. :) Look what happens without it.

+ pcs -f configured_cib.xml resource create krogan2DB ocf:heartbeat:pgsqlms 
bindir=/usr/pgsql-11/bin pgdata=/var/lib/pgsql/krogan2 
recovery_template=/var/lib/pgsql/krogan2.paf meta master notify=true 
resource-stickiness=10
Warning: changing a monitor operation interval from 15 to 16 to make the 
operation unique
Warning: changing a monitor operation interval from 16 to 17 to make the 
operation unique

So trivial monitor always exists by default with interval 15.

My real command
pcs -f configured_cib.xml resource create krogan2DB ocf:heartbeat:pgsqlms 
bindir=/usr/pgsql-11/bin pgdata=/var/lib/pgsql/krogan2 
recovery_template=/var/lib/pgsql/krogan2.paf op monitor interval=15 timeout=10 
monitor interval=16 role=Master timeout=15 monitor interval=17 role=Slave 
timeout=10 meta master notify=true resource-stickiness=10

Looked like I needed to add all this to change "timeout" parameter for the 
monitor operations and I needed for interval parameter to point on the specific 
monitor operation. Looked like the default timeout 10 was not enough for the 
"master".

> By the way, nice catch to exclude RO IP when master score is
> negative.
> 
> You should probably consider putting your logs outside of your PGDATA, or
> even better: send your PostgreSQL/Pacemaker logs to a dedicated syslog node
> outside. Adding the hostname in the prefix of each log line might probably 
> help
> a lot.

I have put this into my ToDo notebook.

>> [...]
>> 10:24:55.906 LOG:  entering standby mode
>> 10:24:55.908 LOG:  redo starts at 0/1528
>> 10:24:55.909 LOG:  consistent recovery state reached at 0/15002300
>> 10:24:55.910 LOG:  database system is ready to accept read only connections
>> 10:24:55.928 LOG:  started streaming WAL from primary at 0/1600 on tl 3
>> 10:26:37.308 FATAL:  terminating walreceiver due to timeout
> 
> Timeout because of SIGSTOP on primary here.

Sure

>> 10:26:37.308 LOG:  invalid record length at 0/1600C4D8: wanted 24, got 0
>> 10:30:55.965 LOG:  received promote request
> 
> Promotion from Pacemaker here.

Yep

> What happened during more than 4 minutes between the timeout and the 
> promotion?

It's one of the problem, which you may improve. :) The pacemaker reaction is 
the longest for STOP signal test, usually near 5 minutes. The pacemaker tried 
to make different things (for instance "demote") and wait for different 
timeouts.

> 
>> 10:30:55.965 FATAL:  terminating walreceiver process due to administrator cmd
>> 10:30:55.966 LOG:  redo done at 0/1600C4B0
>> 10:30:55.966 LOG:  last completed transaction was at log time 10:25:38.76429
>> 10:30:55.968 LOG:  selected new timeline ID: 4
>> 10:30:56.001 LOG:  archive recovery complete
>> 10:30:56.005 LOG:  database system is ready to accept connections
> 
>> The slave with didn't reconnected replication, tuchanka3c. Also I separated
>> logs copied from the old master by a blank line:
>> 
>> [...]
>> 
>> 10:20:25.168 LOG:  database system was interrupted; last known up at 10:20:19
>> 10:20:25.180 LOG:  entering standby mode
>> 10:20:25.181 LOG:  redo starts at 0/1198
>> 10:20:25.183 LOG:  consistent recovery state reached at 0/11000A68
>> 10:20:25.183 LOG:  database system is ready to accept read only connections
>> 10:20:25.193 LOG:  started streaming WAL from primary at 0/1200 on tl 3
>> 10:25:05.370 LOG:  could not send data to client: Connection reset by peer
>> 10:26:38.655 FATAL:  terminating walreceiver due to timeout
>> 10:26:38.655 LOG:  record with incorrect prev-link 0/1200C4B0 at 0/1600C4D8
> 
> This message appear before the effective promotion of tuchanka3b. Do you have
> logs about what happen *after* the promotion?

This is end of the slave log. Nothing. Just absent replication.

> Reading at this error, it seems like record at 0/1600C4D8 references the
> previous one in WAL 0/1200. So the file referenced as 0/16 have either
> corrupted data or was 0/12 being recycled, but not zeroed correctly, as v11
> always do no matter what (no wal_init_zero there).

Okey, may be in v12 it will be fixed.

> That's why I'm wondering how you built your standbys, from scratch?

By special scripts. :) This project already on GitHub and I am waiting for the 
final solution of my boss to open it. And it will take some time to translate 
README to English. After t

Migration of DB2 java stored procedures to PostgreSQL

[Dirk Krautschick]

Hi,

for a potential migration from DB2 on DB2/z to PostgreSQL I have to take care 
of a whole
bunch of java stored procedures. Would that be a show stopper here or is a 
migration
somehow possible or is it anyway the same because Java = Java? 

Any experiences/recommendations or helpful ressources? 

Many thanks and best regards

Dirk

Re: Migration of DB2 java stored procedures to PostgreSQL

[Laurenz Albe]

On Mon, 2020-08-24 at 16:32 +, Dirk Krautschick wrote:
> for a potential migration from DB2 on DB2/z to PostgreSQL I have to take care 
> of a whole
> bunch of java stored procedures. Would that be a show stopper here or is a 
> migration
> somehow possible or is it anyway the same because Java = Java? 
> 
> Any experiences/recommendations or helpful ressources? 

There is PL/Java, but it is not part of the core PostgreSQL distribution, so 
you'd
have to build it yourself.

If performance is important or you don't want to depend on third-party modules,
post the code to PL/Python or PL/Perl.  If the code is just glue around some 
SQL,
PL/pgSQL might be the best choice.

Yours,
Laurenz Albe
-- 
+43-670-6056265
CYBERTEC PostgreSQL International GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Re: Migration of DB2 java stored procedures to PostgreSQL

[Tim Clarke]

On 24/08/2020 18:31, Laurenz Albe wrote:
> On Mon, 2020-08-24 at 16:32 +, Dirk Krautschick wrote:
>> for a potential migration from DB2 on DB2/z to PostgreSQL I have to take 
>> care of a whole
>> bunch of java stored procedures. Would that be a show stopper here or is a 
>> migration
>> somehow possible or is it anyway the same because Java = Java?
>>
>> Any experiences/recommendations or helpful ressources?
> There is PL/Java, but it is not part of the core PostgreSQL distribution, so 
> you'd
> have to build it yourself.
>
> If performance is important or you don't want to depend on third-party 
> modules,
> post the code to PL/Python or PL/Perl.  If the code is just glue around some 
> SQL,
> PL/pgSQL might be the best choice.
>
> Yours,
> Laurenz Albe


+1 for PL/Java, we've been using it for years.


Tim Clarke MBCS
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420


Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Most effective and fast way to load few Tbyte of data from flat files into postgresql

[Dirk Krautschick]

Hi,

what would be the fastest or most effective way to load few (5-10) TB of data 
from flat files into
a postgresql database, including some 1TB tables and blobs?

There is the copy command but there is no way for native parallelism, right? I 
have found pg_bulkload
but haven't tested it yet. As far I can see EDB has its EDB*Loader as a 
commercial option.

Anything else to recommend?

Thanks and best regards

Dirk

Re: Query plan prefers hash join when nested loop is much faster

[David Rowley]

On Sat, 22 Aug 2020 at 00:35, iulian dragos
 wrote:
> I am trying to understand why the query planner insists on using a hash join, 
> and how to make it choose the better option, which in this case would be a 
> nested loop.

> |   ->  Index Scan using 
> test_result_module_result_id_idx on test_result  (cost=0.57..6911.17 
> rows=4331 width=12) (actual time=0.002..0.002 rows=1 loops=14824) |
> | Index Cond: (module_result_id = 
> module_result.id) 
> |

You might want to check if the pg_stats view reports a realistic
n_distinct value for test_result.module_result_id.  If the
pg_class.retuples is correct for that relation then that would
indicate the n_distinct estimate is about 115000. Going by the number
of rows you've mentioned it would appear a more realistic value for
that would be -0.4. which is 0 - 1 / (5 / 2.0).
However, that's assuming each module_result  has a test_result.  You
could run a SELECT COUNT(DISTINCT module_result_id) FROM test_result;
to get a better idea.

If ANALYZE is not getting you a good value for n_distinct, then you
can overwrite it. See [1], search for n_distinct.

David

[1] https://www.postgresql.org/docs/current/sql-altertable.html

Re: Row estimates for empty tables

[Justin Pryzby]

On Fri, Jul 24, 2020 at 09:14:04PM +0200, Pavel Stehule wrote:
> pá 24. 7. 2020 v 16:38 odesílatel Christophe Pettus  
> napsal:
> > Since we already special-case parent tables for partition sets, would a
> > storage parameter that lets you either tell the planner "no, really, zero
> > is reasonable here" or sets a minimum number of rows to plan for be
> > reasonable?  I happened to get bit by this tracking down an issue where
> > several tables in a large query had zero rows, and the planner's assumption
> > of a few pages worth caused some sub-optimal plans.  The performance hit
> > wasn't huge, but they were being joined to some *very* large tables, and
> > the differences added up.
> 
> I did this patch ten years ago.  GoodData application
> https://www.gooddata.com/  uses Postgres lot, and this application stores
> some results in tables (as guard against repeated calculations). Lot of
> these tables have zero or one row.
> 
> Although we ran an ANALYZE over all tables - the queries on empty tables
> had very bad plans, and I had to fix it by this patch. Another company uses
> a fake one row in table - so there is no possibility to have a really empty
> table.
> 
> It is an issue for special, not typical applications (this situation is
> typical for some OLAP patterns)  - it is not too often - but some clean
> solution (instead hacking postgres) can be nice.

On Mon, Aug 24, 2020 at 09:43:49PM +0200, Pavel Stehule wrote:
> This patch is just a workaround that works well 10 years (but for one
> special use case) - nothing more. Without this patch that application
> cannot work ever.

My own workaround was here:
https://www.postgresql.org/message-id/20200427181034.ga28...@telsasoft.com
|... 1) create an child table: CREATE TABLE x_child() INHERITS(x)
|and, 2) change the query to use "select from ONLY".
|
|(1) allows the planner to believe that the table really is empty, a conclusion
|it otherwise avoids and (2) avoids decending into the child (for which the
|planner would likewise avoid the conclusion that it's actually empty).

-- 
Justin

Re: Creating many tables gets logical replication stuck

[Keisuke Kuroda]

Hi All,

There was a similar problem in this discussion:
  Logical decoding CPU-bound w/ large number of tables
  
https://www.postgresql.org/message-id/flat/CAHoiPjzea6N0zuCi%3D%2Bf9v_j94nfsy6y8SU7-%3Dbp4%3D7qw6_i%3DRg%40mail.gmail.com

> RelfilenodeMapHash from 1024 entries to 64.
The above changes reduced the performance impact.

However, I think the problem that there are too
many invalidations of RelfilenodeMapHash still remains.
As you report, when many tables are created/dropped/truncated,
The walsender process can get stuck.

-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3...@gmail.com