Infrastructure at your Service

All Posts By

Daniel Westermann

Daniel Westermann

Installing EDB Postgres Advanced Server 9.5

By | Database Administration & Monitoring | 5 Comments

Some days ago EnterpriseDB released EDB Postgres Advanced Server 9.5. As usual this release contains all new features that have been introduced with PostgreSQL 9.5 plus the additions that are documented in the release notes. Some time ago I blogged on how to install the 9.4 release. In this post I want to check if anything changed in the installation process. Installing Postgres Advanced server usually is just clicking next,next,next and you’re done if you…

Read More
Daniel Westermann

The Postgres Plus® Cloud Database

By | Database Administration & Monitoring | No Comments

In the last posts (1, 2, 3, 4, 5) I looked at PostgreSQL in Amazon RDS. Another option you have if you want to go to the cloud is to use the The Postgres Plus® Cloud Database offered by EnterpriseDB. I will not go through the whole setup here as this is described in detail here. So once you went through the registration and setup process you should be able to login. The login URL…

Read More
Daniel Westermann

Avoiding access to the public schema in PostgreSQL

By | Database Administration & Monitoring | 5 Comments

In PostgreSQL every database contains the public schema by default. Every user that gets created and can login is able to create objects there. Here is a little demo: I’ll create a new user named u1 which is allowed to login. No additional privileges are granted: postgres=# create user u1 login password ‘u1’; CREATE ROLE postgres=# \c postgres u1 You are now connected to database “postgres” as user “u1”. From now on this user is…

Read More
Daniel Westermann

Using the official PostgreSQL yum repositories

By | Database Administration & Monitoring | No Comments

Usually I advice to build PostgreSQL from source as it gives the most flexibility. Another option is to use the official yum repositories. There is one repository for each supported release of PostgreSQL and most of the common RedHat based Linux distributions are listed: CentOS, Oracle Linux, RedHat, Fedora, Scientific Linux and even Amazon Linux. Currently PostgreSQL 9.6 is in active development and new features get developed and committed. So lets try to add to…

Read More
Daniel Westermann

Launching a VM with the Amazon AWS command line tools

By | Database Administration & Monitoring | No Comments

The cool thing with Amazon AWS is that you can use command line tools on your workstation to bring up and manage your services. In this post I’ll look into how you can launch a Linux VM from the command line and how you can attach a storage volume to it. Before you can use the command line tools you’ll need to create a user that has the proper permissions to connect. This must be…

Read More
Daniel Westermann

External tables in PostgreSQL?

By | Database Administration & Monitoring | No Comments

In Oracle you can use the external table feature to load data from files into the database. The traditional way to do this in PostgreSQL is to use the copy command. But there is another option which makes use of foreign data wrappers. The foreign data wrapper for doing this is file_fdw. For creating some data here is a little script which generates 1’000 lines of the form: ID, TEXT: #!/bin/bash OUTPUT=”/var/tmp/data.csv” `rm -rf ${OUTPUT}`…

Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Loading the beast

By | Database Administration & Monitoring | No Comments

The last posts outlined how you can bring up a PostgreSQL instance in the Amazon cloud, how you can restrict access to the instance, how you can configure it and how you can add a read replica. In this post we’ll look at how you can load data to the instance. A database without data does not make much sense, does it? For having some data available to load I’ll populate my local PostgreSQL 9.4…

Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Adding a replica to the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked on how you can change the PostgreSQL parameters when the instance is running in the Amazon cloud. In this post we’ll look at how to add a replica to the PostgreSQL instance that is already running. Even if you push things to the cloud you want to make sure that you have a standby database in case the master crashes. At best the replica is running in another data…

Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Configuring the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked at how you can strengthen the security for your PostgreSQL instance in Amazon RDS. In this post I’ll look at how you can configure your PostgreSQL instance. When you login to the Amazon console and select your running instance the screen looks like this: If you want to modify the instance there is a “modify” entry in the “Instance Actions” menu: This brings you to the main configuration page…

Read More
Daniel Westermann

PostgreSQL on Amazon RDS – Securing the beast

By | Database Administration & Monitoring | No Comments

In the last post I looked at how to bring up a PostgreSQL instance in the Amazon cloud. In this post I’ll look into how to restrict access to this instance to specific IP addresses to strengthen security. If you think about moving things to the cloud you for sure want to make the stuff accessible to restricted people or systems only. On the left side there is a “Security Groups” menu which sounds like…

Read More