Skip to content

Postgres

Containerized Postgres version upgrade

Postgres upgrade in container

How to upgrade postgres version which runs in docker container

Introduction

Main idea of this post is to demonstrate postgres major version upgrade process for the case when postgres runs within docker container.

Demonstrated approach uses pg_dumpall utility. In any case it does not pretend to be the "best" approach. There are other options availabe such as pg_upgrade or setting up a replication server. Moreover, for big databases pg_dumpall might be non optimal or even not feasible solution.

But as said at the beginning, idea is to demonstrate possible upgrade process for containerized postgres installation.

  • in Postgres
  • 5 min read

Postgres GRANT operation cost

GRANT operation cost

GRANT operation mechanics

Introduction

As many other databases Postgres supports access management framework via privileges which are provided to the users. In terms of SQL it means a pair of commands GRANT and REVOKE. To simplify things to the users postgres also supports mass ALL option for these commands. Specifying either GRANT ALL or REVOKE ALL allows to apply command against multiple objects or against multiple privileges or for a mix of both.

Oracle, Postgres and AWS

Oracle 2 Postgres migration

Oracle 2 Postgres migration into AWS cloud

As a contractor of Epam company I'm currently working on Oracle 2 Postgres project migration into AWS cloud. Being on tech team lead position I've been asked to present our work on official Epam's webinar as a speaker. I'm thankful to the Epam for the given opportunity to share our experience with a wide auditory.

Oracle FDW

Postgres foreign data wrapper for Oracle database

Introduction

This is part 2 of #fdw foreign data wrappers series of posts. In part 1 it was described what foreign data mean in Postgres as well as provided an example of connection to MySQL database. In this post we will deal with accessing data in Oracle database.

MySQL FDW

Postgres foreign data wrapper for MySQL database

Foreign data access

This is the first part of #fdw foreign data wrappers series of posts.

One of the great features in Postgres is the ability to access data stored in outer sources as if they were stored in ordinary tables within the database. There is no requirement to the source as is. For example, it can be another RDBMS such as Oracle or MySQL. But we are not limited to relational databases only. It can be also just a csv file or even Hadoop file system objects. All you need to do to get access to these data is to install corresponding foreing data wrapper extension.

Parallel jobs in Postgres

Parallel jobs

Solution to implement parallel jobs in Postgres

Problem

Common approach for running time consuming or maintenance tasks in databases is using job processes. Two classic extensions that allow to launch scheduled jobs in Postgres are pgAgent and pg_cron. Problem is that if you have database hosted in a cloud they might be unavailable. This happens for Postgres RDS in AWS cloud. One of the unofficial recommendation from AWS team on support forum was to launch EC2 instance and trigger via crontab on that server psql which will execute some script or call an API on database side.

Such approach requires from you to keep (and pay for) separate EC2 instance. It has to be continiously up and running. Another advise was even more complex setup with Lambda functions.