Infrastructure at your Service

PostgreSQL Archives - Page 18 of 22 - Blog dbi services

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
Daniel Westermann

PostgreSQL on Amazon RDS – Setting up the beast

By | Database Administration & Monitoring | No Comments

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 AWS account is ready you can login to the management console which lists all the…

Read More
Daniel Westermann

How fast can you setup a standby database with PostgreSQL?

By | Database Administration & Monitoring | No Comments

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) The standby database needs to be open read only Create a table containing one record…

Read More
Daniel Westermann

Watching sql statements in PostgreSQL

By | Database Administration & Monitoring | No Comments

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 ——- 10 (1 row) (postgres@[local]:4448) [postgres] > \watch Watch every 2s Mon Dec 21 07:34:35…

Read More
Daniel Westermann

Setting your cluster name in PostgreSQL 9.5

By | Database Administration & Monitoring | 2 Comments

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 2327 2324 0 14:01 ? 00:00:00 postgres: checkpointer process postgres 2328 2324 0 14:01 ?…

Read More
Daniel Westermann

pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist

By | Database Administration & Monitoring, Database management | No Comments

As I could not find any information about this error when I faced it this is just a short hint: If you try to use pg_dump or pg_dumpall out of a PPAS installation to dump or dumpall a community version of PostgreSQL you probably get this: pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist pg_dump: [archiver (db)] query was: SET search_path = pg_catalog, sys, dbo The solution is quite easy but you…

Read More
Daniel Westermann

Parallel sequential scans are coming to PostgreSQL

By | Database Administration & Monitoring | No Comments

It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up: max_parallel_degree: The maximum degree of parallelism for an individual parallel operation. parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a parallel worker process to another process. The default is 0.1 parallel_setup_cost: Sets the planner’s estimate…

Read More