Archive for November, 2013

Postgres: create extension pg_trgm (and other extensions) for multiple schema

Monday, November 25th, 2013

Create extension for multiple schema

When, creating any extensions, make sure you do the with schema pg_catalog to have those extensions available for all schemas of the database.

For example, to create the pg_trgm index extension, do as follow once connected to your database. Not having the with schema pg_catalog will lead to the typical ERROR: operator class “gin_trgm_ops” does not exist for access method “gin” on your second schema if you have a multiple schema setup.

mydb=# create extension pg_trgm with schema pg_catalog;

To list installed extensions:

mydb=# dx

To alter already installed extension

mydb=# ALTER EXTENSION pg_trgm SET SCHEMA pg_catalog;

Postgres Alter Extension Doc

Linux: sed for search & replace for big files

Thursday, November 21st, 2013
sed 's/oldstring/newstring/g' file.txt > newfile.txt

An intro to Sed

Amazon EC2: Installing Postgres 9.3 on Amazon Linux & Instance Local Storage

Wednesday, November 20th, 2013

Update 2016-03-26

Postgres is now providing yum installs for the main releases!!! See Yum Install Postgres 9.5 on amazon linux


Below kept for reference

Unfortunately, the latest postgres 9.3 is not available from the default Amazon Linux package manager (yum …). There are two options to install it:

Option 1) Install from rpm/yum from postgres repo

In the files /etc/yum.repos.d/amzn-main.repo
and /etc/yum.repos.d/amzn-updates.repo add the following in the
block [amzn-main]:


Then, install the repo rpm and run yum

# Change to home directory to download the software
cd ~/
# Get the right postgresql package (Redhat 64 Bit)
# Install the package
sudo rpm -ivh pgdg-redhat93-9.3-1.noarch.rpm
sudo yum install postgresql93 postgresql93-server postgresql93-devel

# Check that the service is installed
sudo service postgresql-9.3 status
# should return "is stopped"

# init the DB
sudo service postgresql-9.3 initdb
# Start the DB
sudo service postgresql-9.3 start

# connect
sudo su - postgres

In the psql, just do a l

postgres=# l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres


Option 2) From source

Here is how to install Postgres 9.3 from source on Amazon Linux.

In addition, optional steps to set the postgres data on a ephemeral storage with a little convenient script to reinitialize database after shutdown (great for dev, testing, and even production if robust failover/backup setup). The ephemeral disk is assumed to be mounted as /local as shown in the previous Install Amazon AMI with instance storage post.

# Become root
sudo su -

# Install C compiler (not installed by default on Amazon Linux)
yum -y install gcc

# Install the devel of openssl zlib and readline
yum  -y  install openssl-devel
yum  -y  install zlib-devel
yum  -y  install readline-devel

# Create the user
useradd postgres

# Here are the folders we will be using for runtime, log, and data
mkdir  -p /usr/local/pgsql
mkdir  -p /var/log/pgsql
mkdir  -p /var/data/pgsql

# For Variant: Note that for the ephemeral disk mounted on "/local"
#                  we can make the log and data on the local
# mkdir  -p /var/data/
# mkdir  -p /var/log/
# mkdir -p /local/var/data/pgsql
# mkdir -p /local/var/log/pgsql
# ln -s /local/var/data/pgsql /var/data/pgsql
# ln -s /local/var/log/pgsql /var/log/pgsql

# Download and make latest postgres (check for the latest)
cd /usr/local/src
wget  ""
tar -zxvf postgresql-9*
cd postgresql-9*

./configure  --prefix=/usr/local/pgsql --with-openssl
make install

# Adjustment of Permission.
chown  -R  postgres:postgres  /usr/local/pgsql
chown  -R  postgres:postgres  /var/log/pgsql
chown  -R  postgres:postgres  /var/data/pgsql

# For Variant: Also need to change the perms of the real directories
# chown  -R  postgres:postgres /local/var/data/pgsql
# chown  -R  postgres:postgres /local/var/log/pgsql

# init the db
su postgres -c'/usr/local/pgsql/bin/initdb --encoding=UTF-8 --no-locale -D /var/data/pgsql'

# setup
cp /usr/local/src/postgresql-9.3.1/contrib/start-scripts/linux /etc/init.d/postgres
chmod 700 /etc/init.d/postgres

#### DO: Edit the /etc/init.d/postgres to update both variables as follow
# PGDATA=”/var/data/pgsql”
# PGLOG=”/var/log/pgsql/serverlog”

# Note: for the Instance variant, probably useless, but good for EBS ones.
chkconfig postgres on

# Start the postgres
/etc/init.d/postgres start
# or "service postgres start"

# Add the pgsql/bin to the postgres user path
sudo su - postgres
vi .bash_profile
# DO: add the pgsql/bin to the PATH (like: "PATH=$PATH:$HOME/bin:/usr/local/pgsql/bin/")

# Should be in business


Installing pg_trm

$ cd /usr/local/src/postgresql-9.3.1/contrib/pg_trgm
$ make
$ sudo make install # or su -c 'make install' if you don't use sudo
Then, in the database of chose from psql
create extension pg_trgm;

To have it activated by default on any database created in the future, apply this command to the template1 database.


Only for instance storage Variant

Now, assuming the instance store variant was used, you can add a on ~ec2-user/ that you will call after the instance start (can probably even make it run on instance start)

mkdir -p /local/var/data/pgsql
mkdir -p /local/var/log/pgsql

chown  -R  postgres:postgres /local/var/data/pgsql
chown  -R  postgres:postgres /local/var/log/pgsql

su postgres -c'/usr/local/pgsql/bin/initdb --encoding=UTF-8 --no-locale -D /var/data/pgsql'

Amazon EC2: Start Instance with ebs root and instance storage

Wednesday, November 20th, 2013
  1. Launch instance in AWS UI
  2. Do not do quick start, they are EBS only Linux AMIs
  3. Go AMI Market Place, search for “Amazon Linux AMI 64″ and pick “Amazon Linux AMI x86_64 PV EBS” (it’s the one with “free eligible.” Probably not use the free tier, but this is the right AMI to be able to add EBS and Instance storage)
  4. On instance type, make sure to pick a type that has local storage (some are EBS only)
  5. At the storage settings, add a storage and select “instance” (let’s assume you picked “/dev/sdb” as Device)
  6. Finish to create the instance and login

[ec2-user@ip-10-191-177-125 ~]$ df -kh
/dev/xvda1            7.9G  983M  6.9G  13% /
tmpfs                 1.9G     0  1.9G   0% /dev/shm
/dev/xvdb             394G  199M  374G   1% /media/ephemeral0

To get a better name, can do another mount on /dev/xvdb

[ec2-user@ip-10-191-177-125 ~]$ sudo vi /ect/fstab

In the /etc/fstab replace the /dev/sdb to your liking.

LABEL=/     /           ext4    defaults,noatime  1   1
tmpfs       /dev/shm    tmpfs   defaults        0   0
devpts      /dev/pts    devpts  gid=5,mode=620  0   0
sysfs       /sys        sysfs   defaults        0   0
proc        /proc       proc    defaults        0   0
/dev/sdb    /local      auto    defaults        0   0

remount all

[ec2-user@ip-10-191-177-125 ~]$ sudo mount -a

Amazon EC2: Change prompt

Wednesday, November 13th, 2013
vi .bash_profile

Just before the PATH=$PATH…. add something like this

PS1="[\u@MY SERVER 1:\w]$ "

Then, you can source it to have it immediately.

source .bash_profile

This is safer than changing the hostname, but works on a user base. For multiple user, edit a profile.d type of files.

Amazon EC2: Only shows 8GB even if ebs drive is bigger

Monday, November 4th, 2013

Not sure why, but here is the solution. Type:

sudo resize2fs /dev/xvda1