pg_hba & ldap

[Diego]

Hi all!

I have a problem with ldap authentication, I have a ldap string like this:

host all all 0.0.0.0/0 ldap 
ldapserver="10.20.90.251 10.20.90.252 10.10.90.251 10.10.90.252" 
ldapport=389...


It is correct? if the firs server is down, pg will go to the next one to 
continue authenticating?


It's a pg11 and ldap is an ipa server

thanks a lot,

Diego

Re: About compress in pg_dump

[Diego]

Yep, I transfer backups files all the time with -Fc and never the 
problem was rsync


On 2020-07-17 12:07, David G. Johnston wrote:
On Fri, Jul 17, 2020 at 7:49 AM Edmundo Robles > wrote:


To backup  a database  I do:
 nice -n +19  pg_dump -Fc  database | nice -n +19 gzip --rsyncable
  -nc >  database.dump

If -Fc  option  is compressed  by default  I dont need gzip the
backup,  but I need pass --rsyncable and -n options.

How can  I pass  gzip options  to compress in pg_dump?


pg_dump isn't using the gzip program, it's just performing compression 
per the gzip compression specification, and doesn't provide those two 
features to control it's processing (or any features beyond what's 
documented on the pg_dump reference page).


David J.

Re: Security Vulnerability on PostgreSQL VMs

[Diego]

Hi!


Try with "yum deplist " to check who app use phyton.


Diego,


On 2020-07-17 12:44, Hilbert, Karin wrote:
We have PostgreSQL v9.6 & also PostgreSQL v11.8 installed on various 
Linux VMs with Red Hat Enterprise Linux Server release 7.8 (Maipo) 
OS.  We're also running repmgr v5.1.0 & PgBouncer v1.13.


We're getting vulnerability reports from our Security Office for the 
following packages:

 - python-pulp-agent-lib-2.13.4.16-1.el7sat
 - python-gofer-2.12.5-5.el7sat

For some reason these packages aren't being updated to the current 
versions & our LinuxAdmins haven't been able to resolve the update 
issue.  It has something to do with a satellite?  (I'm not a Linux 
Admin - I don't really know what they're talking about).  Anyway, *are 
these packages anything that would be required by PostgreSQL, repmgr 
or PgBouncer?*  It's nothing that I installed on the VMs - I assume 
that it's something installed along with the OS.  The Linux Admin's 
recommendation is to just remove these packages.


Thanks,
Karin Hilbert

Re: Track pgsql steps

[Diego]

are you running the psql with -c or -f?

add -o to put all in a file and -e to write the last query, and with a 
tail to the log, you can see where is the problem


On 2020-07-30 10:11, Olivier Leprêtre wrote:

Hi, thanks for your answers,

Application_name is a good tip, 64 chars are enough to code steps, I'll use that
I expected being able to write raise events to a table or to store data in 
another table but it doesn't appear to be possible due to transaction isolation.

Thanks a lot.

Olivier

-Message d'origine-
De : Julien Rouhaud 
Envoyé : jeudi 30 juillet 2020 12:19
À : Adrian Klaver 
Cc : Olivier Leprêtre ; pgsql-general 

Objet : Re: Track pgsql steps

On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver  wrote:

On 7/29/20 8:44 AM, Olivier Leprêtre wrote:

Hi,

I have a rather long pgsql procedure and I would like to detect
which step is currently executing (subscript 1,2,3…). Due to
transaction isolation, it’s not possible to make it write in a table
or get nexval from a sequence because values become available only
after the complete end of the procedure.

Do you see any solution in this purpose ?

RAISE NOTICE?:

https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PL
PGSQL-STATEMENTS-RAISE

You can also abuse SET application_name, as the value will be directly seen by 
other transactions.  You're quite limited in the number of bytes to use, but if 
you just need to do some quick testing it can be helpful.

Re: Fwd: changing password pgagent service account

[Diego]

yep, or the password does not meet the windows requirements.

On 2020-08-13 14:31, Adrian Klaver wrote:

On 8/13/20 10:19 AM, Henry Francisco Garcia Cortez wrote:
Good morning I need to help I installed pgagent but I want to change 
password pgagent service account


r5w83.png


To me it looks like you are creating an account rather then changing a 
password. Also looks like it might be failing on the user name length.



--
Ing. Henry G. Cortez

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: Extensión pldebugger para Postgresql 12 en Centos 7, no encontrada

[Diego]

Hi all!

It's possible that pldebugger12 does not exists for centos 7?

Thanks a lot,
Diego


On 2020-09-07 10:16, Lucas Luengas wrote:

Centos 6:
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-6-x86_64/pldebugger12-1.1-1.rhel6.1.x86_64.rpm
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-6-x86_64/pldebugger12-1.1-1.rhel6.x86_64.rpm

Y para Centos 8:
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-8-x86_64/pldebugger12-1.1-1.rhel8.1.x86_64.rpm

Pero no para Centos 7:
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/

Re: how to check PEM version

[Diego]

Hi Atul,

copy from program is your friend ;P
https://www.postgresql.org/docs/current/sql-copy.html

On 2020-09-10 05:49, Atul Kumar wrote:

Hi,

I have installed pem in centos 7, now i want to check the version of
PEM using sql query.

Please let me know, how should i do that.

My postgres version is 11.8


Regards,
Atul


.

Re: Procedure to install and configure pgadmin4 in desktop mode in Red Hat Linux 8 and other Linux distributions

[Diego]

Hi Maria!

Add the repo:

sudo rpm -i 
https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-1-1.noarch.rpm

# Install for desktop mode only.
sudo yum install pgadmin4-desktop

Final config

sudo /usr/pgadmin4/bin/setup-web.sh

Here are the instructions: https://www.pgadmin.org/download/pgadmin-4-rpm/


On 2020-09-30 14:23, Maria Elba Salerno wrote:

Hello,

I'm looking for a procedure to install and configure pgadmin4 in desktop mode 
in Red Hat Linux 8 and other Linux distributions.

Thanks in advance

Regards

Re: What's your experience with using Postgres in IoT-contexts?

[Diego]

I use pg in my raspberrypy 3b, for a weather station and works fine, but 
to be honest, I have a sencond raspi with a pgbouncer and a pendrive of 
256GBs as data storage


In other, I have a zabbix server with pg monitoring aboutn 100 devices, 
but with an external hdd.


but, at the end of the day, I think is not the best idea.


On 2020-10-05 06:20, Thorsten Schöning wrote:

Hi all,

TL;DR:

Does anyone actually use Postgres with ARM based low performance
hardware and only 256 MiB of RAM? What are your experiences in other
stripped down environments? Is there some lower RAM limit with which
using Postgres doesn't make any sense anymore? Is Postgres able to
compete with SQLite regaridng lower performance boundaries or is there
some point at which SQLite will be the better choice, because Postgres
needs some more resources to work properly?

Thanks!

Background:

I have some server side backend exposing web services and storing data
in Postgres. The stored data are telegrams from smart metering
devices, lots of them, mostly small. The provided web services take
care of generating reports etc. based on those telegrams.

It's now considered to put large parts of that backend onto some ARM
based IoT device, simply because that device would do a lot of the
same things. The available backend is Java-based, so it would simply
be possible as well and I already ran things on some Raspi-like
hardware and things worked pretty well. OTOH, the targetted hardware
is less capable than some Raspi, we talk about only 256 MiB of RAM
currently. The exact details of the hardware are not clear yet.

In the easiest case, the newly created device would only need to store
some hundreds of telegrams per day in some known time window and
forward those telegrams afterwards. In most cases it would be those of
the same day only, but might be those of the last X few days as well
in case of problems. In the long term, though, that device should be
able to store telegrams for years and might generate reports on it's
own as well.

I've already found some interesting infos like the following, but
would be interested in other expereinces as well.

https://www.postgresql.eu/events/pgconfeu2019/sessions/session/2781/slides/251/PostgreSQL%20for%20IoT.pdf

Mit freundlichen Grüßen,

Thorsten Schöning

Re: how to troubleshoot: FATAL: canceling authentication due to timeout

[Diego]

hi!

What you see in the log files of CentOS ( /var/log ) ?
i


On 17/03/2021 16:00, Marc wrote:

hi all,

We are facing a problem with a user login into database. It happens when there 
is large load and only from time to time.
Once we get this error, the user becomes unusable until database is restarted. 
(That user is being used by multiple instances of the same application, it also 
happens using dedicated users for each application, resulting on one of those 
users being locked out, the rest keep working fine)

The errors is as follows:
LOG: pam_authenticate failed: Authentication failure
FATAL: canceling authentication due to timeout

Our setup:
3 nodes cluster
- Centos 7
- Streaming replication in place (async)
- WAL shipped to an external location
- pooling done at client side
- Centos joined to an Active Directory domain
- Authentication is using PAM module

User is completely fine in AD side since i can use it to login to a standby DB.
I guess there must be a lock that prevents this user to do the first 
authentication step, but no idea how to find it. I’ve tried common queries to 
find locks but I can’t see anything relevant.

I would appreciate if someone could point me to the right direction!

Thanks a lot!
Marc.

Re: Does psqlodbc_10_03_0000-x86(32bit) support PostgreSQL 12.6 64bit?

[Diego]

hi,
Try with a newer version of odbc driver, something like psqlodbc_12_02_

On 07/07/2021 07:38, 管竚 wrote:




Created by:  gzhcoder
Email address:  gzhco...@126.com
PostgreSQL version: 12.6
Operating system:   windows 10
Description:

I have a classic ASP Website on IIS.  I change the “ Enable 32-bit
Applications ” to True (Set the IIS application pool to spawn in a 32-bit
mode).
The Website connect to PostgreSQL 9.4 64bit with
psqlodbc_10_03_-x86(32bit) and it works very well .
When I change the PostgreSQL 9.4 64bit to PostgreSQL 12.6 64bit . I get the
error below :

Number: -2147217887
Description: Multiple-step OLE DB operation generated errors.

I found that the problem occurred in the third step:
Step 1. Starts a new transaction with the BeginTrans method.
Step 2. Creates an OraDynaset object from the specified SQL SELECT statement
SQL-A.
Step 3. Creates an OraDynaset object from the specified SQL SELECT statement
SQL-B.
Step 4. Ends the current transaction with the CommitTrans method.

I made an sample asp as below:
psqlodbc.asp
-- start
-

<%@ language='VBScript' %>
<%
Option Explicit

Function CreateDynaset(ArgSQL, ArgMod,cn )
dim rs
Set rs = CreateObject("ADODB.Recordset")

if ArgMod = 0 then
   rs.Open ArgSQL, cn, 1, 3
else
   rs.Open ArgSQL, cn, 3, 2
End if

   Set CreateDynaset = rs
End Function
%>
<%
Dim sConnection, objConn , objRS1, objRS2 ,sql
stop
sConnection ="DRIVER={PostgreSQL
Unicode};SERVER=999.999.999.999;database=XX;UID=XX;PASSWORD=XX;port=5432"

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open(sConnection)

'DoStep1
objConn.BeginTrans

'SQL-A
sql = "SELECT cola FROM odbctest.t_odbc_a "
On Error Resume Next

'DoStep2
set objRS1 = CreateDynaset(sql, 8, objConn)

If Err.Number <> 0 Then
   WScript.Echo "Error in DoStep2: " & Err.Description
   Err.Clear
End If

If objRS1.EOF <> True Then
objRS1.Close
Set objRS1 = Nothing
End If

objRS1.Close
Set objRS1 = Nothing

'SQL-B
sql="SELECT colb FROM odbctest.t_odbc_b"

On Error Resume Next

'DoStep3
set objRS2 = CreateDynaset(sql, 8, objConn)

If Err.Number <> 0 Then
   WScript.Echo "Error in DoStep3: " & Err.Description
   Err.Clear
End If

If objRS2.EOF <> True Then
objRS2.Close
Set objRS = Nothing
End If

objRS2.Close
Set objRS2 = Nothing

'DoStep4
objConn.CommitTrans

objConn.Close
Set objConn = Nothing
%>

psqlodbc test

-- end
-


Thanks for any help

Re: psycopg2 mail list subscription issues

[Diego]

Here is the link to reset your password and manage your account: 
https://www.postgresql.org/account/reset/


On 13/07/2021 14:29, Rich Shepard wrote:

I apologize for writing to the list, but my attempts to subscribe to the
psycopg2 mail list fail and I've not seen a response to my email message
sent to the mail list owner.

I don't know my postgres mail list password as I've not had a reason to use
it ... until now. Subscribing to the psycopg2 mail list and, apparently
other lists, requires a community account password which my password to 
this

mail list apparently is.

Thinking I might have a postgres community account (needed to subscribe to
that mail list) I tried the reset password link on this page
 


but no email message with instructions to reset my password were received.

This suggested I didn't have an account, so I tried to create one. The
immediate response is that I do have a community account. Checkmate. Can't
reset my password and I have an existing account with my email address.

Suggestions on how I can subscribe to the psycopg2 mail list are needed.

TIA,

Rich

PostgreSQL System Views or Dictionary Tables

[Diego Grampin]

Hello,

I'm new at PostgreSQL, but known Oracle since ten years ago.

In PostgreSQL, where can i found system views or dictionary tables (v$*, 
dba_* at oracle) or structural dba tables ?. And comand prompt 
parameters of the database (show param X at Oracle) ?
The porpose of this is to familiarized with the structure of Postgresql 
databases.


_My test environment:_ Windows physical server machine intel x86-64 
architecture with PostgreSQL Database version 10.5 and pgAdmin 4 version 
3.2 .


Thanks so much for your help.
Regards,
Diego.

Research survey

[Diego Andres Ruiz Gomez]

Hi community,

I'm a software developer fascinated with open source, I've participated in
several OSS projects, mainly in iDempiere <http://www.idempiere.org/>,
which uses postgresql heavily. Therefore, I am familiar with the project
and have a great image of it.

I'm doing a research on marketing in community-driven open source software
projects. The objective of this research project is to understand why it
appears that marketing has not been an important topic in open source
software communities, and why in most of the cases privative software
continues being the market-leaders, even though there are better solutions.

Therefore, I'm inviting all the community members who wants to participate
in the study to help me by answering an online survey that would take
around 10 minutes.

You can find the interview here:
https://goo.gl/forms/LAM1DiTsDRHb34bF2

My goal is to publish the results and try to use them to benefit the OSS
communities.

Kindly also distribute this survey to your friends or colleagues which are
members from open source communities (regardless of the project).

Thanks in advance!

Kind Regards,
DIego Ruiz