Tag Archives: postgres 9.1

Install and Configure PL-PHP for Postgres 9.1

30 Oct

Prerequisite Packages

Note: Some or all of these packages might already be installed on your system. The Yum install command will notify you if that’s the case.

  • PHP Devel
  • Postgres Devel
  • Libxml
  • Libtool
  • Automake and Autoconf
  • GLIBC Devel
  1. yum install php-devel postgresql91-devel libtool libxml2-devel automake autoconf glibc-devel

Download PHP Source Code

 

  • If you have an existing PL-PHP version based on an older PHP version (for example, PHP 5.3.3) and want
    to upgrade to a later version (for example, 5.3.8) you will have to repeat the steps below. There is
    no automatic way to upgrade to a later version.

 

    • mkdir -p /opt/phpsrc
    • cd /opt/phpsrc
    • It’s possible you already have the latest PHP source code downloaded and extracted. If so, skip this step
    • Get the latest version of PHP from http://php.net/downloads.php#v5
    • tar -xvzf php-5.3.8.tar.gz

Build PHP using the “enable-embed” option

  • To build and install a shared PHP embed library, unpack the PHP tarball
  • cd /opt/phpsrc/php-5.3.8
  • make clean ( If this is first time you are building PHP this command will fail and that is ok )
    • this ensures the build is cleaned from previous runs if any.
  • ./configure –enable-embed
  • make
    • This command will build the libphp5.so file. Might take a few minutes to complete.
  • make install
  • The previous command will install the libphp5.so in /usr/local/lib. We need to copy this to the /usr/lib
    • cp /usr/local/lib/libphp5.so /usr/lib
  • Update Shared Library Configuration:
    • cd /etc/ld.so.conf.d
    • vi plphp-libs.conf (Create this file)
    • Enter /usr/lib/libphp5.so and save file
    • ldconfig
      • this will update the CentOS library path configuration.

Download Latest Version of Autoconf

  • Check latest version of autoconf
    • /usr/local/bin/autoconf –version
  • IF version shows as 2.68 then you can skip the steps to download and install below and continue on
    to Download and building PL-PHP
  • mkdir /opt/autoconf
  • cd /opt/autoconf
  • wget http://ftp.gnu.org/gnu/autoconf/autoconf-latest.tar.gz
  • tar xvfz autoconf-latest.tar.gz
  • cd autoconf-<LATEST_VERSION>
  • ./configure
  • make clean
  • make
  • make install
    • LATEST autoconf will be installed in /usr/local/bin

Download PL-PHP

{info:title=Make Note}

  • If you have an existing PL-PHP installation and want to upgrade to a later version you will have to repeat the steps below. There is no automatic way to upgrade to a later version.

{info}

Download and extract the PL-PHP source code at http://github.com/commandprompt/PL-php

Compile, Install PL-PHP and restart Postgres

  • Comple and Install PL-PHP
    • cd /opt/plphp/commandprompt-PL-php-8c2f8ef
    • /usr/local/bin/autoconf (generates the configure script)
    • Set Path so to the embedded PHP that was built earlier
      • PATH=/usr/local/bin:$PATH ; export PATH
    • ./configure –with-postgres=/usr/pgsql-9.1/
    • make clean ( If this is first time you are building PL-PHP this command will fail and that is ok )
    • make
    • make install
  • Restart Postgres
    • service postgresql-9.1 restart

Setup/Configure PL-PHP language support in Postgres

  • cd /opt/plphp/commandprompt-PL-php-8c2f8ef
  • export LD_LIBRARY_PATH=/usr/pgsql-9.1/lib:$LD_LIBRARY_PATH
    • this step is required because we have both Postgres 9.0 and 9.1 on the same machine
  • psql -d template1 -U postgres < install.sql
    • This will enter pl-php as a valid language in the pg_template tables
  • In the SQL window, run the following command which will create PL/PHP as a ‘trusted’ language in pgsql (https://public.commandprompt.com/projects/plphp/wiki/CreateLang):
    • CREATE LANGUAGE PLPHP
  • Copy/Paste the following command. The commented out section can be used to validate our php environment.
CREATE FUNCTION test_plphp() RETURNS text AS $$

  ob_start();
  phpinfo();
  $variable = ob_get_contents();
  ob_get_clean();
  return $variable;

$$ LANGUAGE 'plphp';
  • Validate PHP environment:
    • select test_plphp(); as a Postgres SQL command to view the phpinfo() output.

Configuring extension libraries setting for use in PL-PHP

  • IF YOU ARE UPGRADING FROM POSTGRES 9.0 to 9.x then you may skip the steps in this section.

Setup php.ini

  • PL/PHP looks for php.ini in /usr/local/lib by default.
  • If you have a copy in /etc/php.ini, copy it over to /usr/local/lib
    • cp /etc/php.ini /usr/local/lib
  • Otherwise, copy/paste the php.ini file from another location

Set the extension directory in php.ini

  • cd /usr/local/lib
  • gedit php.ini and edit the following entries
    • For 32 bit OS enter: extension_dir=/usr/lib/php/modules
    • For 64 bit OS enter: extension_dir=/usr/lib64/php/modules/

Restart Postgres

  • service postgresql-9.1 restart

PL-PHP USAGE

Advertisements

Installing Postgres 9.1 on CentOS

20 Oct

Notes on installing Postgres 9.1 on CentOS…

Dump Postgres 9.0 DB if you are upgrading from 9.0 to 9.1

  • mkdir -p /opt/pg/dump
  • Dump Postgres 9.0
    • /usr/pgsql-9.0/bin/pg_dump -U pgadmin -f /opt/pg/dump/shard1_90dump shard_1

STOP Postgres 9.0 if you are upgrading 9.0 to 9.1

  • service postgresql-9.0 stop
  • Ensure no Postgres process is running:
    • ps -ef | grep postgres
  • IF any Postgres process is running kill it:
    • pkill -f postgres
  • Turn off Postgres Service in CentOS Startup
    • chkconfig postgresql-9.0 off

Install and configure the PostgreSQL Yum Repository Configuration

  • mkdir /opt/pgdq91
  • cd /opt/pgdg1
  • Download the Repository Configuruation RPM for the Postgres 9.1
  • Run rpm -ivh pgdg-centos-9.1-1.noarch.rpm
    • The file /etc/yum.repos.d/pgdg-<version>.repo is created
  • Configure the Base repository to ignore request for Postgres RPMs
    • cd /etc/yum.repos.d
    • vi CentOS-Base.repo
      • Add exclude=postgresql* to the bottom of the file
    • vi CentOS-updates.repo
      • Add exclude=postgresql* to the bottom of the file

Install/Upgrade Postgres packages

  • yum install postgresql91 postgresql91-server postgresql91-contrib

Copy Postgres binaries to /usr/bin

  • The postgres91 packages install binaries/executables such as psql to /usr/pgsql-9.1/bin. We need to copy these to /usr/bin so it’s available in the default path.
    • Note: use the \ before cp otherwise you’ll be prompted to confirm each file that is copied/overwritten.
    • \cp /usr/pgsql-9.1/bin/* /usr/bin

If Upgrading From Postgres 9.1 to a later release

  • yum upgrade postgresql91*
  • service postgresql-9.1 restart

Important File and Directory Locations for Postgres

Postgres is installed in the following directories:

  • /usr/pgsql-9.1 – this dir contains the postgres libraries and executables for managing the postgres process.
  • /usr/pgsql-9.1/bin/* and /usr/bin/pg* (Postgres CLI commands)
  • /var/lib/pgsql/9.1 – this dir contains the postgres database and configuration files
  • /var/lib/pgsql/9.1/data/*.conf (Postgres Configuration files)
  • /var/lib/pgsql/9.1/data/pg_log (Postgres Log Files)

Intialize Postgres DB

  • The following command assumes you are using Postgres 9.1 version:
    • service postgresql-9.1 initdb

Setup Postgres service management on CentOS

  • This will automatically start/stop Postgres on system startup/shutdown
    • chkconfig postgresql-9.1 on

User Authentication Configuration

  • Change Postgres 9.1 authentication configuration to use md5
    • vi /var/lib/pgsql/9.1/data/pg_hba.conf
    • change all occurrences of ident and peer as follows. Should look like this:
      # TYPE  DATABASE        USER            ADDRESS                 METHOD
      
      # "local" is for Unix domain socket connections only
      local   all             all                                     trust
      
      # IPv4 local connections:
      host    all             all             127.0.0.1/32            md5
      
      # IPv6 local connections:
      host    all             all             ::1/128                 md5

       

Start Postgres 9.1

  • service postgresql-9.1 start
  • To subsequently stop: service postgresql-9.1 stop

Test psql command line client and setup pgadmin user

  • Test psql access
    • export LD_LIBRARY_PATH=/usr/pgsql-9.1/lib:$LD_LIBRARY_PATH
      • this step is required because we have both Postgres 9.0 and 9.1 on the same machine
    • /usr/pgsql-9.1/bin/psql -d template1 -U postgres (or, psql -d shard_1 -U pgadmin -E, etc…)
    • Exit from psql prompt by typing Ctrl-D

Setup pgpass file for DB instance

  • Setup pgpass for user root:
    • Ensure you are in root shell, if you are still su – postgres then enter exit
    • vi /root/.pgpass
    • Enter the following, save and exit.
      *:*:*:pgadmin:pgadmin

       

    • Set the correct privileges for the pgpass file
      ***chmod 0600 /root/.pgpass

Stop Postgres

  • service postgresql-9.1 stop

Start Postgres

  • service postgresql-9.1 start

Initialize and Import shard_1 Database

Setup LD_LIBRARY_PATH

  • export LD_LIBRARY_PATH=/usr/pgsql-9.1/lib:$LD_LIBRARY_PATH
    • this step is required because we have both Postgres 9.0 and 9.1 on the same machine

Create the shard_1 DB

    1. /usr/pgsql-9.1/bin/createdb -U pgadmin -T template1 shard_1

Create pgadmin user

  • /usr/pgsql-9.1/bin/createuser -P pgadmin -U postgres
  • Enter password for new role: xxxx
  • Enter it again: xxxx
  • Shall the new role be a superuser? (y/n) y

Create Extension citext

  • /usr/pgsql-9.1/bin/psql -d template1 -U postgres
    • CREATE EXTENSION citext;

Import shard_1 Database contents

  • /usr/pgsql-9.1/bin/psql -U postgres shard_1 -f /opt/pg/dump/shard1_90dump &> /tmp/pgload123
  • Check the pgload file for any glaring errors.
  • THE ONLY errors you should see should be related to “citext function already exists”. This is because citext in Postgres 9.0 is a contrib module whereas in Progres 9.1 it’s an Extension.

Configuring/Tuning Postgres for Remote Access

Performance Settings.

  • Note: these settings are not suitable for development environments.
  • For a m2.4xlarge EC2 instance:
    • Within /var/lib/pgsql/9.1/data/postgresql.conf:
      • shared_buffers = 17500MB
      • max_connection = 5000
    • effective_cache_size = 34000MB
    • checkpoint_segments = 8
    • max_prepared_transactions = 1000
    • listen_addresses=’*’
    • log_min_messages= FATAL (LOG or ERROR levels cause trigger outputs to be published)

Configure Two-Phase commit

  • Within /var/lib/pgsql/9.1/data/postgresql.conf:
    • Set max_prepared_transactions = 100 in order to enable two-phase commit transactions (required for DDL updates).

TCP/IP configuration

  • For security reasons, a PostgreSQL server “out of the box” doesn’t listen on TCP/IP ports. Instead, it has to be enabled to listen for TCP/IP requests. This can be done by adding listen_addresses=’*’ for Version 8.0.x and above; this will make the server accept connections on any IP interface.
  • cd /var/lib/pgsql/9.1/data/
  • vi postgresql.conf
    • Set listen_addresses=’*’

Remote Host Connectivity Settings

  • vi /var/lib/pgsql/9.1/data/pg_hba.conf
    • This is an example of allowing connections from all hosts. But the “allowed” hosts would be controlled via Firewall settings. This
      would be how we would do it on Amazon EC2.
      Example below:

       TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
      
      # "local" is for Unix domain socket connections only
      local   all             all                                     trust
      # IPv4 local connections:
      host    all             all              0.0.0.0/0              md5
      # IPv6 local connections:
      host    all             all             ::/0                    md5

       

    • If you want just your Windows desktop to connect to this Postgres server, add ‘host all all $ipOfWindowsOs/24 md5’ in the appropriate section (scroll to the bottom of the file).
    • The $ipOfWindowOs is typically of the form 192.168.X.X, and will be provided by the pgadmin error message initially saying something along the lines of ‘this host does not access…’.
    • You can either have an entry for each host or a set of hosts.
    • See the documentation in the pg_hba.conf file for more info.
    • SEE http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html for more information

Restart Postgres after all Configuration changes

  • service postgresql-9.1 restart

References

  • http://www.depesz.com/index.php/2011/03/02/waiting-for-9-1-extensions/
  • Email/Bug report with Postgres
    "Stan S" writes:
    > shard_1=# CREATE TABLE users (nickname CITEXT PRIMARY KEY,pass TEXT   NOT
    > NULL);
    > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey"
    > for table "users"
    
    > shard_1=# INSERT INTO users VALUES ( 'larry',  'aaa' );
    > INSERT 0 1
    
    > shard_1=# INSERT INTO users VALUES ( 'Tom',   'bbb' );
    > ERROR:  could not determine which collation to use for string comparison
    > HINT:  Use the COLLATE clause to set the collation explicitly.
    
    Hmm, I can't replicate this here ...
    
    > shard_1=# CREATE TABLE users (nickname CITEXT COLLATE "C" PRIMARY KEY,pass
    TEXT   NOT NULL);
    > ERROR:  collations are not supported by type citext
    
    This suggests strongly that you're using a 9.0-or-earlier citext
    installation that you've not upgraded to 9.1.  If that's the right
    guess, you need to do CREATE EXTENSION citext FROM unpackaged
    to fix it.
    
                            regards, tom lane