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

2 Responses to “Installing Postgres 9.1 on CentOS”

  1. guillermo June 16, 2012 at 8:25 am #

    please, could you help me to upgrade php-pgsql in centos? my head is broke trying to do that. Sorry for my english.

  2. Dom Barker (@MostlyHarmlessD) February 22, 2013 at 9:32 am #

    Thanks for this, was a great help!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: