Archive for the ‘Postgresql’ Category

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

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

Wednesday, November 20th, 2013

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]:

exclude=postgresql*

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)
wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
# 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
psql

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

source

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  "http://ftp.postgresql.org/pub/source/v9.3.1/postgresql-9.3.1.tar.gz"
tar -zxvf postgresql-9*
cd postgresql-9*

./configure  --prefix=/usr/local/pgsql --with-openssl
make
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
psql

Source

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.

Source

Only for instance storage Variant

Now, assuming the instance store variant was used, you can add a initdb.sh 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'

postgresql: jdbc url to connect to a schema

Saturday, June 8th, 2013

There does not seem to have a way to have a connect string that specify a schema, but a user can be set a default schema.

ALTER USER user_name SET search_path to 'schema'

source

Postgresql: problem running post install step (on Mac / OSX)

Wednesday, May 22nd, 2013

This often comes when a postgres user was already created and not removed.

The best way I found, is to uninstall Postgresql DB, remove the postgres user, and reinstall the new version.

sudo dscl . delete /users/postgres  

Note, when installing Postgresql in the Advanced Option, nice to have everybody in the team choose the same local, e.g. “en_US.UTF-8″

postgresql: create user, db, schema (command line)

Saturday, March 16th, 2013
# create the user
CREATE USER some_user WITH password 'pwd'

# create the new db with the new user as owner
CREATE DATABASE some_db WITH OWNER some_user;

# connect to db
connect some_db

# create schema
CREATE SCHEMA some_schema AUTHORIZATION some_user;

# rename database
ALTER DATABASE dbname RENAME TO new_dbname;

Postgresql: Changing Postgres user password

Tuesday, October 23rd, 2012

Three different ways

1. Change the password in single user mode
2. Edit pg_hba.conf and set it to trust mode, change your password,
change back to md5
3. Re-initdb your cluster.

For 1:
su - postgres (from root if you have to)
pg_ctl stop
postgres template1 (or some other database you know exists)
alter user postgres password 'newpassword';

For 2:
su - postgres
cd $PGDATA
vi pg_hba.conf
// change entries at bottom to trust
pg_ctl reload
psql template1
alter user postgres password 'newpassword';

For 3:
su - postgres
pg_ctl stop
echo $PGDATA  // make sure this is set to something like /var/lib/pgsql
rm -rf $PGDATA/*
initdb // with whatever options you need.

source

postgresql: Set the default schema name (like using or use)

Saturday, May 21st, 2011
SET search_path TO myschema;

Postgresql: Postgresql Commands Cheat-Sheet

Sunday, September 19th, 2010

PG Tools

  • pg_dump dbname > backupfile: Backup as a sql file
  • psql dbname < backupfile : restore

Login

su - postgres
psql
#or
psql mydb

Create Db

su - postgres
createdb DBTest

PSQL Commands

  • h : SQL help
  • ? : psql help
  • q : quit
  • l : show databases
  • d : show tables
  • d tablename : show columns
  • c dbname : connect to db
  • ! : switch to OS shell mode (very useful on Mac OS where you start on the SQL shell)

SQL Commands

  • DROP DATABASE MYDB;

Backup & Restore Database

Backup:  $ pg_dump {source_db} -f {dumpfilename.sql}

Restore: $ psql -d {desintation_db} -f {dumpfilename.sql}

Note: on Mac OS X, the command prompt need to be started from the application folder or from pgAdminIII, and then, type “!” to switch to command shell, and then, go to /Library/PostgreSQL/8.4/bin to find the psql and pg_dump scripts

How to backup and restore postgresql

Related Links

Postgresql: Failed to start on Windows (used to work)

Sunday, April 25th, 2010

Somehow, my Postgresql 8.4.3 failed to start on windows. After some digging around, I found the following in the windows event log:

FATAL:  bogus data in lock file "postmaster.pid": ""

Removing the file did the trick (it is located at the root folder of your database store)

Hibernate: annotations for PostgreSQL sequence id

Tuesday, March 30th, 2010
@Table(name = "mySchema.user")
@javax.persistence.SequenceGenerator(
    name="SEQ_STORE",
    allocationSize=1,
    sequenceName="mySchema.user_id_seq"
)
public class User {
....
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ_STORE")
    public Long getId() {
        return id;
    }
}