Infrastructure at your Service

PostgreSQL Archives - Page 21 of 22 - Blog dbi services

Daniel Westermann

Optimized Row Columnar (ORC) format in PostgreSQL

By | Database management | One Comment

Nowadays everybody is talking about columnar storage format. What can PostgreSQL do in this area? There is no native support for that in PostgreSQL but thanks to the fact that PostgreSQL is highly extensible there is a foreign data wrapper called cstore_fdw. Lets take a look on what it can do. For installing cstore_fdw we’ll need to install the protobuf-c-devel package which is available in the epel repository if you are on a redhat base distribution:…

 
Read More
Daniel Westermann

What will be coming with the next major version of postgresql ?

By | Database management | One Comment

A PostgreSQL major version is usually released once a year. For the next major version, which is 9.5, this probably will be in the second half of 2015. As PostgreSQL is an open source product everything that goes on is available to the public. If you want to know what will be coming in the next release there are several places you might take a look at. The PostgreSQL documentation The official PostgreSQL documentation is…

 
Read More
Morgan Patou

Alfresco: some useful database queries

By | Application integration & Middleware, Entreprise content management | 29 Comments

In my previous post, I talked about the Lifecycle of Alfresco Nodes. You may have noticed that I tried to insert in my explanations some elements that are specific to databases (tables, fields, aso…). These elements are quite essential to prepare a post like this one: more database oriented. I already explained what exactly are the consequences on the database side when a node is removed and I will try in this post to share some useful…

 
Read More
Daniel Westermann

the fastest way to load 1m rows in postgresql

By | Development & Performance | 3 Comments

There have been several posts on how to load 1m rows into a database in the last days: Variations on 1M rows insert (1): bulk insert Variations on 1M rows insert(2): commit write Variations on 1M rows insert (1): bulk insert – PostgreSQL Variations on 1M rows insert(2): commit write – PostgreSQL Variations on 1M rows insert (3): TimesTen In this post I’ll focus on how to prepare a PostgreSQL database for bulk loading in…

 
Read More
Daniel Westermann

getting started with postgres plus advanced server (4) – setting up the monitoring server

By | Database management | One Comment

If you followed the first, second and the third post the current ppas infrastructure consists of a primary database, a hot standby database and a backup and recovery server. Now we will add the monitoring server on another host so that the system overview looks like this: server ip address purpose ppas 192.168.56.243 ppas database cluster ppasstandby 192.168.56.244 ppas hot standby database ppasbart 192.168.56.245 backup and recovery server ppasem 192.168.56.246 ppas enterprise manager On the…

 
Read More
Daniel Westermann

getting started with postgres plus advanced server (3) – setting up a hot standby server

By | Hardware & Storage | 5 Comments

So, we have a ppas 94 database up and running and we have a backup server for backing up and restoring the database. Now it is time to additionally protect the database by setting up a hot standby database. This database could even be used to offload reporting functionality from the primary database as the standby database will be open in read only mode. Again, I’ll use another system for that so that the system…

 
Read More
Daniel Westermann

getting started with postgres plus advanced server (2) – setting up a backup and recovery server

By | Database management | 5 Comments

The first post in this series explained how to get ppas installed on a linux system. Now that the database cluster is up and running we should take care immediately about backup and recovery. For this I’ll use another system where I’ll install and configure bart. So, the system overview for now is: server ip address purpose ppas 192.168.56.243 ppas database cluster ppasbart 192.168.56.245 backup and recovery server As bart requires the postgres binaries I’ll…

 
Read More
Daniel Westermann

getting started with postgres plus advanced server (1) – setting up ppas

By | Database management | No Comments

I did several posts around postgresql and postgres plus advanced server in the past. What is missing is a beginners guide on how to get postgres plus advanced server up and running including a solution for backup and recovery, high availability and monitoring. So I thought I’d write a guide on how to do that, consisting of: setting up postgres plus advanced server setting up a backup and recovery server setting up a hot standby…

 
Read More
Daniel Westermann

bulk loading semi structured data in postgresql

By | Development & Performance | 3 Comments

The last post took a look at how to efficiently load 1m rows into a table in PostgreSQL. In this post I’ll take a look on how to do the same with semi structured data. Again it is the same system as in the last post using the same PostgreSQL parameters. Using the hstore datatype For storing key/value pairs there is the hstore extension in PostgreSQL. Using the same sample data as in the last…

 
Read More
Daniel Westermann

Variations on 1M rows insert(2): commit write – PostgreSQL

By | Development & Performance | No Comments

Franck was quite fast with his second post Variations on 1M rows insert(2): commit write. Lets see what we can do on the PostgreSQL side. I’ll take the same test table as Franck again: create table DEMO (“id” number , “text” varchar2(15), “number” number) ; The pgplsql block which corresponds to Frank’s plsql block looks like this: \timing on truncate table DEMO; DO $$DECLARE l_people_array varchar(15)[12] := ‘{“Marc”, “Bill”, “George”, “Eliot”, “Matt”, “Trey”, “Tracy”,”Greg”, “Steve”,…

 
Read More