0

I am looking to query postgres DB from an AWS lambda docker image. I thought of using psycopg2 library but it doesn't install correctly.

I am getting the below error in my code pipeline when I included psycopg2 in my requirements.txt.

running egg_info
creating /tmp/pip-pip-egg-info-k018kh5i/psycopg2.egg-info
writing /tmp/pip-pip-egg-info-k018kh5i/psycopg2.egg-info/PKG-INFO
writing dependency_links to /tmp/pip-pip-egg-info-k018kh5i/psycopg2.egg-info/dependency_links.txt
writing top-level names to /tmp/pip-pip-egg-info-k018kh5i/psycopg2.egg-info/top_level.txt
writing manifest file '/tmp/pip-pip-egg-info-k018kh5i/psycopg2.egg-info/SOURCES.txt'

Error: pg_config executable not found.

Is there any other way to access postgres sql?

I also tried RUN pip3 install in Dockerfile and it doesn't work either.

Thanks

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Ram
  • 155
  • 1
  • 5

1 Answers1

0

Some questions I want to discuss:

  1. What is the OS system in your VM of the pipeline?
  2. Have you checked whether you have installed the C libraries where pg_config is included?
  3. Doest the PATH environment variable have the path of pg_config?

Before executing pip installation

pg_config is in postgresql-devel.
Debian/ubuntu.

sudo apt-get install libpq-dev

Fedora

sudo dfn install libpq-devel

CentOS/Amazon Linux

sudo yum install postgresql-devel

If the path of pg_config is not the PATH environment variable

export PATH=$PATH:$(dirname $(find / -name pg_config 2>/dev/null))

References

  1. pg_config executable not found
  2. How to install psycopg2 with "pip" on Python?
Scott Hsieh
  • 1,339
  • 1
  • 11
  • 25
  • I used RUN yum update -y && \ yum install -y postgresql-devel RUN pip3 install aws-psycopg2 in my Dockerfile. – Ram Apr 11 '23 at 14:46
  • This installation was successful. But I am not sure what's the correct way of connecting to postgres DB from the lambda function. – Ram Apr 11 '23 at 14:46
  • I used the below code: conn = psycopg2.connect( host=PG_HOST, port=PG_PORT, dbname=PG_DATABASE_NAME, user=PG_USERNAME, password='xxxxx' ) cur = conn.cursor() cur.execute("SELECT * FROM table_name") conn.close() This gives error [ERROR] OperationalError: connection to server at "db_host..." (xx.xx.xx.x), port 5432 failed: FATAL: password authentication failed for user "my_user" no pg_hba.conf entry for host "xx.xx.xx.x", user "my_user", database "my_db", no encryption – Ram Apr 11 '23 at 14:47
  • @Ram According to the exception message, can you go check whether the database account you're using in the Lambda function exists in the PG database? Furthermore, can you check whether the password you typed in the Lambda function matches the account created in PG database? – Scott Hsieh Apr 12 '23 at 00:01
  • The password and other details are correct. I am able to connect with this credentials using DBeaver client. The error message says "no pg_hba_conf" entry for host "xxx". Is that the problem? – Ram Apr 12 '23 at 14:50
  • May you try adding `sslmode=Require;` and `Trust Server Certificate=true;` in your connection string? – Scott Hsieh Apr 12 '23 at 15:20
  • I think I found the original pbm. I was using a different host name/password. I corrected it and now I've a new problem when AWS Lambda connects to the PG DB. It says "failed to connect... connection to server at "xxx.com", port 5432 failed: Connection timed out Is the server running on the host and accepting TCP/IP connections?" – Ram Apr 12 '23 at 15:26
  • I think I found the problem. It was a mistake from my end. I was trying to connect to a different DB (QA) whereas my lambda is configured in a different env (DEV). So the AWS network doesn't allow this. I deployed it to the correct env and everything works good now. Thank you for all the help. – Ram Apr 12 '23 at 16:14
  • Where is your Lambda function? It's in a VPC or is public? Where is your PG database? It is in your local machine or is it a remote server? Can you connect to the PG database on your local machine using `ping` or connection testing using a socket like in `Python`? Is there any firewall in front of the PG database? Those are possible factors that I want to check. – Scott Hsieh Apr 12 '23 at 16:17
  • @Ram If the PG database was in another AWS account compared to the one where you deployed your Lambda function, you need to confirm what kind of subnet the PG database is placed and what CIDRs are allowed in the security group(s) attached to the PG database. Is the account where the PG database is deployed managed by you? If it's not, those information should be provided to you by the owner of the account, or at least the own of the database. – Scott Hsieh Apr 12 '23 at 16:21
  • @Ram 1. Are there 2 AWS accounts, one is for the database and the other is for you Lambda function? 2. What kind of subnet is the database placed? 3. Is the database accessible to the public? 4. What CIDRs are allowed in the security group(s) attached to the database? – Scott Hsieh Apr 12 '23 at 16:24
  • @Ram It's more like networking issue to me right now. If the above information could be provided, we might be able to figure out how we should configure our Lambda function to make it connected to the database. – Scott Hsieh Apr 12 '23 at 16:25
  • @Ram Basically, there are 2 rounds you need to go through for this kind of issue. The 1st one is connection and the 2nd one is authentication. It is feasible to write a program or script to check these 2 items. – Scott Hsieh Apr 12 '23 at 16:28