PostgreSQL – Gokhan Atil’s Blog

How to Use IAM authentication for RDS PostgreSQL with Glue ETL Jobs

Amazon RDS enables you to use AWS Identity and Access Management (IAM) to manage database access for Amazon RDS for PostgreSQL DB instances. It’s possible use the IAM authentication with Glue connections but it is not documented well, so I will demostrate how you can do it. In your Glue job, you can import boto3 library to call “generate_db_auth_token” method to generate a token and use it when connecting.

Here’s a simple Glue ETL script I wrote for testing. It connects to PostgreSQL using IAM authentication, reads data from a table and writes the output to S3:

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job
import boto3

DBURL = "endpointofmydatabase.eu-west-1.rds.amazonaws.com"
DBUSER = "mydbuser"

client = boto3.client("rds","eu-west-1")

TOKEN = client.generate_db_auth_token( DBURL, 5432, DBUSER, "eu-west-1")

args = getResolvedOptions(sys.argv, ['JOB_NAME'])

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

datasource0 = glueContext.create_dynamic_frame_from_options( connection_type = "postgresql", connection_options = {"url": "jdbc:postgresql://" + DBURL + ":5432/mydatabase", "user": DBUSER, "password": TOKEN ,"dbtable": "public.testtable" }, transformation_ctx = "datasource0" )

datasink1 = glueContext.write_dynamic_frame.from_options(frame = datasource0, connection_type = "s3", connection_options = {"path": "s3://mybucketname/output"}, format = "json", transformation_ctx = "datasink1")
job.commit()

import sys

from awsglue.transforms import *

from awsglue.utils import getResolvedOptions

from pyspark.context import SparkContext

from awsglue.context import GlueContext

from awsglue.job import Job

import boto3

DBURL = "endpointofmydatabase.eu-west-1.rds.amazonaws.com"

DBUSER = "mydbuser"

client = boto3.client("rds","eu-west-1")

TOKEN = client.generate_db_auth_token( DBURL, 5432, DBUSER, "eu-west-1")

args = getResolvedOptions(sys.argv, ['JOB_NAME'])

sc = SparkContext()

glueContext = GlueContext(sc)

spark = glueContext.spark_session

job = Job(glueContext)

job.init(args['JOB_NAME'], args)

datasource0 = glueContext.create_dynamic_frame_from_options( connection_type = "postgresql", connection_options = {"url": "jdbc:postgresql://" + DBURL + ":5432/mydatabase", "user": DBUSER, "password": TOKEN ,"dbtable": "public.testtable" }, transformation_ctx = "datasource0" )

datasink1 = glueContext.write_dynamic_frame.from_options(frame = datasource0, connection_type = "s3", connection_options = {"path": "s3://mybucketname/output"}, format = "json", transformation_ctx = "datasink1")

job.commit()

November 21, 2019Gokhan AtilAWS, PostgreSQLIAM, RDS

Oracle Enterprise Manager Plugin for PostgreSQL

Oracle Enterprise Manager supports 3rd party plugins which can help you to extend its monitoring and manageability capabilities. There are already developers and companies specialized in plugin development. Blue Medora is one of them (and probably the most famous one). In this blog post, I’ll show you how we can use Blue Medora’s EM PostgreSQL plugin, which is part of their True Visibility Suite for Oracle. I’ll download and deploy the plugin on the OMS (management servers), then deploy the plugin on the agent(s) and at the end, I’ll add a PostgreSQL target to our Enterprise Manager system.

The PosgteSQL plugin is not available on Self-Update, so we need to download and register it manually to our Enterprise Manager. To download the trial version of the plugin, I go to their website, click “download trial”, and fill a form to request trial version. Blue Medora will see our form and send us an email with download link of the plugin. After I download the zip file to my OMS server (ie into the temp folder), I issue the following commands:

cd /tmp
unzip bm.em.xpgs_12.1.0.2.0_2.0.3.zip -d postgre_plugin
emcli login -user=SYSMAN
emcli import_update -omslocal -file=/tmp/postgre_plugin/bm.em.xpgs_12.1.0.2.0_2.0.3.opar

cd /tmp

unzip bm.em.xpgs_12.1.0.2.0_2.0.3.zip -d postgre_plugin

emcli login -user=SYSMAN

emcli import_update -omslocal -file=/tmp/postgre_plugin/bm.em.xpgs_12.1.0.2.0_2.0.3.opar

August 22, 2016Gokhan Atil4 CommentsOracle, PostgreSQLem13c, postgreSQL

PostgreSQL Partition Check Script

New year is coming, if you want to check date based partitions in PostgreSQL , you may use the following query:

select 
    schema_name,
    table_name,
    max( highvalue )
FROM (    
SELECT
    namespace.nspname AS schema_name,
    parent.relname AS table_name,
    cons.consrc, 
    to_date(substr((cast (regexp_matches(
    cons.consrc,'[[:digit:]]*-[[:digit:]]+-[[:digit:]]*','g') 
    as varchar)),2,10),'YYYY-MM-DD') highvalue
FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    JOIN pg_namespace namespace ON namespace.oid = parent.relnamespace
    JOIN pg_constraint cons ON cons.conrelid = child.oid
    WHERE cons.contype='c' ) ss
    group by schema_name, table_name
    order by 1,2,3 desc;

select

schema_name,

table_name,

max( highvalue )

FROM (

SELECT

namespace.nspname AS schema_name,

parent.relname AS table_name,

cons.consrc,

to_date(substr((cast (regexp_matches(

cons.consrc,'[[:digit:]]*-[[:digit:]]+-[[:digit:]]*','g')

as varchar)),2,10),'YYYY-MM-DD') highvalue

FROM pg_inherits

JOIN pg_class parent ON pg_inherits.inhparent = parent.oid

JOIN pg_class child ON pg_inherits.inhrelid = child.oid

JOIN pg_namespace namespace ON namespace.oid = parent.relnamespace

JOIN pg_constraint cons ON cons.conrelid = child.oid

WHERE cons.contype='c' ) ss

group by schema_name, table_name

order by 1,2,3 desc;

December 30, 2014Gokhan Atil6 CommentsPostgreSQLpartitioning

How to Backup and Restore PostgreSQL 9.3 Databases

In my previous blog post, I explained how to install PostgreSQL 9.3 on Oracle Linux. As a DBA, one of the first things we want to learn is how we can recover the database in case of a failure. So in this blog post, I’ll show how to backup and restore PostgreSQL databases in a server. We’ll backup PGDATA directory, so it’ll contain all databases in the instance (remember that PostgreSQL is a multitenant database).

PostgreSQL supports Write Ahead Log (WAL) mechanism like Oracle. So everything will be written to (redo)logs before they written into actual datafiles. So we will use a similar method to Oracle. We need to start “the backup mode”, copy the (data) files, and stop the backup mode, and add the archived logs to our backup. There are SQL commands for starting backup mode (pg_start_backup) and for stopping backup mode (pg_stop_backup), and we can copy the files using OS commands. Good thing is, since 9.1, PostgreSQL comes with a backup tool named “pg_basebackup”. It’ll do everything for us.

December 1, 2014Gokhan Atil22 CommentsPostgreSQLbackup, restore

How to Install PostgreSQL 9.3 on Oracle Linux

For whom are not familiar with PostgreSQL, it is an open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. Like all other enterprise companies, we use different databases beside Oracle to lower the costs. We prefer PostgreSQL instead of Microsoft SQL Server, because it supports PL/SQL for some degree.

In this blog post, I’ll try to show how to install PostgreSQL using YUM. YUM (Yellowdog Updater, Modified) is an open-source command-line package-management utility for Linux operating systems using the RPM Package Manager. It handles library dependencies, so it’s the easiest and maybe the best way to install any RPMs. In this blog post, I’ll install PostgreSQL on Oracle Linux because I use Oracle Linux when I need a Linux OS, and its support costs are lower than Redhat Enterprise Linux. In next weeks, I’m also gonna blog about backup and replication for PostgreSQL, and maybe (not promising) blog about some key concepts of PostgreSQL such as MVCC, vacuum and vacuum freeze.

I assume that you can install Redhat or Oracle Linux. You may read my old post about installing Oracle Linux, hopefully it would be useful. Anyway, if our Linux server is ready, we can download and install Repository RPM for our server. Visit http://yum.postgresql.org.

November 27, 2014Gokhan Atil4 CommentsPostgreSQLinstall, linux