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:

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:

postgres1

PostgreSQL Partition Check Script

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

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.

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.

postgresdownload