Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.
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…
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…
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…
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,…
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…
As piping in bash commands is common and very usefull, controling the exit status of each piped commands in bash scripting can be vital, especially for backups. I was checking by a customer the backups of a critical MySQL instance and was surprised even stunned that the return status of all of them was always successfull when tested but physically…
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…
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…
Today I had a look at PostgreSQL in Amazon RDS. Once you have an Amazon AWS account (this requires a valid credit card for verification) setup bringing up a PostgreSQL instance is pretty easy. The first 12 months are free but be sure to read about the restrictions and what is terminated by default and what is not. Once your…
While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline: Install the product from scratch Create a master database Create a hot standby database (on the same host)…
Back in 2012 I wrote a small blog post about watching the results of commands in Linux. Well, the same can be done in psql: (postgres@[local]:4448) [postgres] > create table t1 ( a int ); CREATE TABLE (postgres@[local]:4448) [postgres] > insert into t1 values ( generate_series ( 1, 10)); INSERT 0 10 (postgres@[local]:4448) [postgres] > select count(*) from t1; count…
PostgreSQL 9.5 introduces a new parameter which is called: cluster_name. So, what is this good for? Imagine you have two (or even more) PostgreSQL clusters running on the same host. When looking at the operating system processes this might look like this: postgres@oel7:/home/postgres/ [PG6] ps -ef | grep “postgres:” postgres 2325 2324 0 14:01 ? 00:00:00 postgres: logger process postgres…
