Infrastructure at your Service

PostgreSQL Archives - Page 21 of 23 - Blog dbi services

Daniel Westermann

Quickly create a hundred databases and users

By | Database Administration & Monitoring | One Comment

Do you need a hundred databases and users for training etc. in PostgreSQL? Just a few lines of code: [postgres8@oel7 data]$ cat create_databases.sh #!/bin/bash NUMOFDBS=100 for i in `seq ${NUMOFDBS}`; do psql -q -c “create user u${i} UNENCRYPTED password ‘u${i}’ NOCREATEDB NOCREATEROLE NOCREATEUSER” postgres createdb -O u${i} db${i} done Execute it and you are ready: [postgres8@oel7 data]$ time ./create_databases.sh real 1m37.825s user 0m0.143s sys 0m0.109s Have a look at the databases and its owners: postgres=#…

Read More
Daniel Westermann

Swiss Postgres Conference 2015

By | Database Administration & Monitoring | No Comments

At the 26th of June I had the chance to attend the second Swiss Postgres Conference at the HSR Rapperswil. It was packed with interesting sessions. Magnus Hagander, a well known community member, started by giving an overview of PostgreSQL Europe. Then I joined Hans Schönig for learning how to write aggregate functions in PostgreSQL. Honestly I did not understand all of it but at least I am now aware of what you can do…

Read More
Daniel Westermann

What is more efficient: arrays or single column values?

By | Database Administration & Monitoring | One Comment

In PostgreSQL ( as well as in other rdbms ) you can define columns as arrays. What I wondered is: What is more efficient when it comes to space: Creating several columns or just creating once column as array? The result, at least for me, is rather surprising. First, let’s create a table with columns of type int: postgres=# create table single_values ( a int, b int, c int, d int, e int, f int,…

Read More
Daniel Westermann

SQL Interpolation with psql

By | Database Administration & Monitoring | One Comment

The PostgreSQL psql utility provides some really nice features. One of these features is SQL interpolation which allows us to do interesting things, e.g. reading files and analyze the results directly in the database. This post will show how to use this by reading and analyzing sar files of a linux server. Usually linux hosts store sar statistics in the /var/log/sa directory: ls /var/log/sa sa02 sa03 sa04 sa05 sa06 sa07 sa08 sa09 sa10 sa11 sa12…

Read More
Daniel Westermann

A free PostgreSQL cloud database?

By | Database Administration & Monitoring | One Comment

Recently I was looking for a free PostgreSQL cloud database service for testing. Why? Because I’d like to use such a cloud instance for testing no matter on which workstation or OS I am currently on. Another reason is, that I could could prepare some demos at home and use the same demos at work without needing to worry about taking the database with me each time. There are probable many more services than the…

Read More
Daniel Westermann

PostgreSQL portable?

By | Database Administration & Monitoring | One Comment

What a surprise: Headed over to the sourceforge page, downloaded, installed (which is just a matter of next/next/next) : Not really the latest patchset but far more than I expected So I can do tests on a Windows machine where I do not have any permissions to install software. Great. If you prefer a graphical client then go ahead and use the portable version of PgAdmin.

Read More
Daniel Westermann

draft release notes for PostgreSQL 9.5 online

By | Database management | One Comment

Bruce Momjian, one of the PostgreSQL Core members, just compiled the first draft version of the release notes for the upcoming PostgreSQL 9.5 Some of the goodies that will show up: BRIN indexes (comparable to exadata storage indexes) Upsert (known as merge statement in the oracle world) wal_compression grouping sets … any many, many more… If you want to test any of the new features check this post.

Read More
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 | 30 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